OpenAdmin Tool for IDS


Contents

Overview
How do I connect to a database server?
Logging in to a server
Logging in to an OAT group
Switching to another server
How do I monitor system status?
How do I gather log information?
How do I manage system space?
Viewing allocation of dbspaces
Managing dbspaces and sbspaces
Creating a space
Dropping a space
Viewing chunk statistics
Compressing and uncompressing data
Estimating table and table fragment compression
Compressing a table or table fragment
Uncompressing a table or table fragment
Consolidating free space in a table or table fragment (repack)
Returning free space to the dbspace (shrink)
Managing recovery logs
Viewing checkpoint history
Forcing checkpoints
Viewing logical log statistics
Adding logical logs
Dropping logical logs
Viewing physical log statistics
Moving physical logs
How do I administer the data server?
Managing high availability clusters
Configuring connectivity for high availability clusters
Finding high availability clusters
Viewing the topology of a cluster
Viewing the status of servers in a cluster
Adding an SDS server
Starting or stopping a server in a cluster
Creating a service level agreement
Modifying a service level agreement
Configuring load balancing for a high availability cluster
Creating or modifying a failover configuration
Managing IDS configuration parameters
Viewing IDS configuration parameter values
Reconfiguring dynamic parameters
Viewing parameter value recommendations
Running system validation checks
Validating one or more databases or tables at the current server
Validating the extents of one or more dbspaces on the current server
Managing virtual processors
Administering database, table, and role privileges
Granting database privileges to a user
Modifying database privileges of a user
Revoking database privileges of a user
Granting table privileges to a user
Modifying table privileges of a user
Revoking table privileges of a user
Managing Auto Update Statistics
Configuring and enabling Auto Update Statistics
Viewing refreshes and evaluations at the server
Requesting an immediate evaluation of statistics
Determining when statistics are updated next
Viewing the current Auto Update Statistics settings
Monitoring the Auto Update Statistics settings
Viewing alerts generated by Auto Update Statistics
Cleaning up abnormal runs of Auto Update Statistics
How do I schedule Informix Dynamic Server tasks?
Adding Scheduler tasks
Adding a new task
Adding a new sensor
Enabling Scheduler tasks
Changing the Scheduler task allowable time interval
Deleting a Scheduler task
How do I explore performance information?
Analyzing query performance with SQL Explorer
Turning on SQL tracing
Turning off SQL tracing
Specifying options for SQL tracing
Suspending SQL tracing from the ON state
Resuming SQL tracing from a suspended state
Switching between Live and Saved Data
Drilling down from the activity summary
Drilling down by transaction
Drilling down by SQL statement type
Optimizing SQL statements
Exploring user sessions
Killing a user session
Running system performance reports
Monitoring servers with the onstat utility
How do I manage databases and tables?
Reviewing the databases on a server
Connecting to a database
Viewing schemas
Reviewing procedures and routines for a database
Performing standard SQL operations: query, insert, update, delete
Performing a query
Updating a table row
Deleting a table row
Inserting a table row
How do I set up access to a server?
Creating an OAT group
Creating a server connection
Exporting connection information
Importing connection information
How do I configure security for the OpenAdmin Tool?
Creating an OAT group with read-only privileges
Read-only group restrictions
Limiting access to the Admin configuration pages
Providing password protection for the Admin configuration pages: Apache example
Configuring HTTPS for OpenAdmin Tool clients
Disabling or enabling password protection for SQL Toolbox
How do I manage plug-ins for the OpenAdmin Tool?
Installing a plug-in for the OpenAdmin Tool
Upgrading a plug-in for the OpenAdmin Tool
Disabling a plug-in for the OpenAdmin Tool
How do I customize the main menu for the OpenAdmin Tool?
Customizing the main menu for the OpenAdmin Tool
Notices
Trademarks
Copyright

Overview

The OpenAdmin Tool (OAT) for IBM® Informix® Dynamic Server (IDS) provides a graphical interface for IDS administration, along with various performance analysis tools. A PHP-based Web browser administration tool, the OpenAdmin Tool for IDS provides the ability to administer multiple database server instances from a single location. Some tasks that you can perform with the OpenAdmin Tool include:

You can add your own plug-ins to the OpenAdmin Tool to create the functionality you need. The OpenAdmin Tool is an open source program.

How do I connect to a database server?

Logging in to a server

Log in to the OpenAdmin Tool (OAT) and connect to a database server.

Tip:
Alternatively, if the server is a member of an OAT group, you can log in to the OAT group.

  1. On the Login page, enter the following information in the Server Details fields:
  2. Click Login.

Logging in to an OAT group

Connect to a server by using the connection that is defined in an OpenAdmin Tool (OAT) group.

When you are connected to the server, and you can switch to other servers in the OAT group without logging in again.

To log in to an OAT group:

  1. On the OAT Login page, under Quick Login, for Oat Group, select the group that contains a server that you want to connect to.
  2. Enter the password for the group.
  3. Click Get Servers.
  4. Select the server from the list of servers. The Server Details fields are populated with the server information.
  5. If the Password field under Server Details is populated, click Login. If the Password field under Server Details is not populated, in the Password field under Quick Login, enter a valid password, and click Login.

Switching to another server

Connect to another server in an OpenAdmin Tool (OAT) group.

To switch to another server in an OAT group, select the server in the Server list at the top of the page.

How do I monitor system status?

Viewing system alerts and messages

To view alerts, use one of the following types of system alerts:

Displaying the server state with the dashboard

To access the dashboard from the left navigation pane, expand Health CenterDashboard. The dashboard provides a current view of the server memory usage, number of transactions run, space usage, and the number of locks.

Use the Refresh Rate slider to specify how often to refresh the data. The range is between 0 and 60 seconds.

Access the report pages by clicking on the tabs; there are typically two reports per page.

Running integrity checks

You can run system integrity checks, as well as checks on specific tables, dbspaces, and extents. See Running system validation checks.

How do I gather log information?

Displaying administration commands from the command_history table

To view administration commands run for this database server instance, from the left navigation pane, expand LogsAdministration Commands.

Viewing messages from the online log

To view the messages in the online log for this database server instance, expand LogsOnline Log.

Viewing the ON-Bar activity report

To view the ON-Bar backup and restore history for this database server instance, expand LogsOnBar Activity Report.

How do I manage system space?

Viewing allocation of dbspaces

To view the allocation of dbspaces for a server, expand Space Administrationdbspaces.

Managing dbspaces and sbspaces

  1. Expand Space Administrationdbspaces.
  2. Select a dbspace, and then click the Admin tab.
  3. From the Admin page, you can add chunks to the dbspace, add or drop dbspaces, and run integrity checks on your tables and extents.

Creating a space

  1. Expand Space Administration dbspaces.
  2. Scroll down to the bottom of the page and complete the Create a Space fields:
  3. Click Create to create the space.

Dropping a space

  1. Expand Space Administrationdbspaces
  2. Select a dbspace name and then click the Admin tab.
  3. In the Drop Space field, select Yes and click Drop.

Viewing chunk statistics

To view chunk status and statistics for the current server, expand Space AdministrationChunks.

From this page you can view chunks statistics and check the chunk I/O data by clicking on the Chunk IO tab.

In the Graph view, each chunk has its own color value, which is the sum of the number of reads and writes. In the Data view, the first column is the number of read and write operations, and the second column is the chunk identifier.

Compressing and uncompressing data

You can compress and uncompress tables and table fragments, consolidate free space in tables and fragments (repack), and return free space to the dbspace (shrink). You can also estimate the amount of space that you can save if you compress data.

For more information about compression, see the Administrator's Guide in the IDS information center.

Estimating table and table fragment compression

Estimate the amount of space that you can save if you compress data in a table or table fragment.

You can use the compression estimate to determine if you want to compress data in a table or table fragment. If a compression estimate is available for a table or table fragment, it is shown on the Compression pages, in the Usage column. The time of the latest estimate is indicate in the hover help. You can update the estimate from these pages.

To update the table and table fragment compression estimate:

  1. On the OAT menu, expand Space Administration and then click Compression.
  2. Select a table or table fragment on the Databases or Dbspaces page.
  3. Click the estimate button for the row.
  4. Review the Usage column.

Compressing a table or table fragment

Compress data in a table or in a table fragment to reduce the amount of needed disk space.

Restriction:
Review the size of the table or table fragment. There must be at least 2,000 rows in each fragment of the table, not just a total of 2,000 rows in the table as a whole.

When you compress a table or table fragment, the compression task creates a compression dictionary and compresses rows without moving them. You can also consolidate free space in the table or table fragment (repack) and return free space to the dbspace (shrink).

To compress a table or table fragment:

  1. On the OAT menu, expand Space Administration, and then click Compression.
  2. Select a table or table fragment on the Databases or Dbspaces page.
  3. Click the action button at the end of the row.
  4. Select Compress to build a compression dictionary and compress the selected table or table fragment.
  5. Optional: To consolidate free space in the table or fragment that you are compressing, select Repack.
  6. Optional: To return any free space in the table or fragment that you are compressing to the dbspace, select Shrink.

    To monitor the progress of the task, click the Compression Task Status tab.

Uncompressing a table or table fragment

Uncompress a table or table fragment that was previously compressed.

Uncompressing a compressed table or fragment deactivates compression for new insert and update operations, uncompresses all compressed rows, deactivates the compression dictionary, and allocates new pages for rows that no longer fit on their original pages.

To uncompress a table or table fragment:

  1. On the OAT menu, expand Space Administration, and then click Compression.
  2. Select a table or table fragment on the Databases or Dbspaces page.
  3. Click the action button at the end of the row.
  4. Select Uncompress.
  5. To place an exclusive lock on the table or fragment to prevent access to the data during the uncompress operation, select Offline.
  6. Click OK.

    To monitor the progress of the task, click the Compression Task Status tab.

Consolidating free space in a table or table fragment (repack)

Consolidate (repack) free space in a table or table fragment without compressing.

When you consolidate free space in the table or table fragment (repack), you can also return free space to the dbspace (shrink) at the same time.

To repack a table or table fragment:

  1. On the OAT menu, expand Space Administration, and then click Compression.
  2. Select a table or table fragment on the Databases or Dbspaces page.
  3. Click the action button at the end of the row.
  4. Select Repack.
  5. Optional: Select additional actions:
  6. Click OK.

    To monitor the progress of the task, click the Compression Task Status tab.

Returning free space to the dbspace (shrink)

Return free space to the dbspace (shrink the space) without compressing or repacking.

Returning free space reduces the total size of the fragment or table.

To shrink a table or table fragment:

  1. On the OAT menu, expand Space Administration, and then click Compression.
  2. Select a table or table fragment on the Databases or Dbspaces page.
  3. Click the action button at the end of the row.
  4. Select Shrink.
  5. Click OK.

    To monitor the progress of the task, click the Compression Task Status tab.

Managing recovery logs

To view and manage the recovery logs for the server, expand Space AdministrationRecovery Logs.

From these pages you can do the following tasks:

Viewing checkpoint history

Expand Space Administration Recovery Logs and click the Checkpoints tab.

Forcing checkpoints

  1. Expand Space AdministrationRecovery Logs and click the Admin tab.
  2. Select the type of checkpoint:
  3. Click Do Checkpoint.

Viewing logical log statistics

Expand Space AdministrationRecovery Logs and click the Logical Logs tab.

Adding logical logs

  1. Expand Space AdministrationRecovery Logs and click the Admin tab.
  2. Under Add Logical Logs, select the dbspace to which the log will be added.
  3. In the Size field, specify the size of the log in kilobytes.
  4. In the Attributes field, select whether to add the log after the current log, or at the end of the existing logs.
  5. Click Add to add the log.

Dropping logical logs

  1. Expand Space Administration > Recovery Logs and click the Admin tab.
  2. Under Drop Logical Logs, select a log from the Logical Log Number list.
  3. Select Drop Logical Log from the Confirm list.
  4. Click Drop to drop the log.

Viewing physical log statistics

Expand Space AdministrationRecovery Logs and click the Physical Logs tab.

Moving physical logs

  1. Expand Space AdministrationRecovery Logs and click the Admin tab.
  2. Under Move Physical Logs, select a dbspace from the DBSpace Name list.
  3. Enter the size of the log, in kilobytes, in the Size field.
  4. Select Move the Physical Log from the Confirm list.
  5. Click Move to move the log.

How do I administer the data server?

Managing high availability clusters

Configuring connectivity for high availability clusters

Set up the idsd daemon on an IDS server to enable starting a server in a high-availability cluster or adding a shared disk secondary (SDS) server.

Prerequisite:
You need root privileges on the IDS server for this procedure.

To use the OpenAdmin Tool (OAT) for remote administration tasks including starting a server in a high-availability cluster, or adding a secondary server, install and configure the following daemons on the IDS server:

Restriction:
You cannot use the remote administration features on Windows® or MAC OS.

To configure an internet daemon and the idsd daemon:

  1. Ensure that the idsd daemon is installed with IBM Informix Dynamic Server, Version 11.50, in the $INFORMIXDIR/bin directory. Ensure that the owner and group are informix, and the permissions are 0754: For example:
     [informix@myhost]/usr/informix/bin% ls -la idsd          
    -rwxr-xr--  1 informix informix 23717 Oct 15 16:34 idsd
  2. On the server, set up encryption for the connection using the IBM Global Security Kit. For more information, see the IBM Global Security Kit Secure Sockets Layer Introduction and iKeyman User's Guide, which is available through the IDS information center.
    1. Create a key database using the following gsk7cmd command line parameters for your platform:
      gsk7cmd -keydb -create -db keydb_name.kdb -pw password -type cms -stash
      For example:
      gsk7cmd -keydb -create -db mydb.kdb -pw mypassw0rd -type cms -stash
    2. Create a self-signed certificate using the following gsk7cmd command line parameters options:
      gsk7cmd -cert -create -db keydb_name.kdb -pw password -label label_text -dn distinguished name -size <1024 | 512 | 2048> -default_cert yes 
      Where: distinguished name is an X.500 distinguished name. The distinguished name is a quoted string with the following format: CN=common name (required), O=organization, OU=organization unit, L=location, ST=state or province, C=country.

      For example:

      gsk7cmd -cert -create -db mydb.kdb -pw mypassw0rd -label mycertificatelabel -dn "CN=lenexa.ibm.com,O=ibm,OU=IBM HTTP Server,ST=KS,C=US" -size 1024 -default_cert yes
  3. Open the xinetd or inetd configuration file for editing and add a service entry for the idsd daemon.

    For example:

  4. Open the file /etc/services for editing and add a service named idsd. For example:
    idsd 10000/tcp
    In this example, port number 10000 is used, but you can use any other port greater than 1024 that is not already in use. However, the name must be idsd, and the protocol must be tcp.
  5. Restart the xinetd or inetd service daemons. In most implementations, you can restart by sending the SIGHUP signal to the running inetd or xinetd process. However, on some Linux platforms, you can run the following command as root:
    service xinetd restart

Finding high availability clusters

Identify high availability clusters so that they can be monitored and managed.

Prerequisite:
Add the servers in the high availability clusters to the OpenAdmin Tool (OAT) connections database. Only the clusters on servers that are in the OAT connections database are displayed on the High Availability Clusters page. To add a server to the OAT connections database, on the Admin menu, expand Manage Connections, and then click Add Connection.

To find high availability clusters:

  1. On the OAT menu, expand Server Administration and then click MACH.
  2. Click Find Clusters.

    When a cluster is found, the New Cluster page is displayed, listing the servers in the cluster: primary, high-availability data replication (HDR), shared disk secondary (SDS), and remote standalone secondary (RSS).

  3. Accept the default name or enter a new, unique name for the cluster. Repeat this step for each cluster that is found.

Viewing the topology of a cluster

View the topology of a high availability cluster.

Prerequisite:
Find and name the clusters in the OpenAdmin Tool (OAT). See Finding high availability clusters.

To view the topology of a cluster:

  1. On the OAT menu, expand Server Administration and then click MACH.
  2. Under Clusters, select the cluster. The Cluster Topology and list of servers are displayed.

    The topology of the selected cluster shows the relationships between the primary server and the secondary servers, including high-availability data replication (HDR), shared disk secondary (SDS), and remote standalone secondary (RSS) servers.

Viewing the status of servers in a cluster

View the status of servers in a high availability cluster.

Prerequisite:
Find and name the clusters in the OpenAdmin Tool (OAT). See Finding high availability clusters.

To view the status of servers in a cluster:

  1. On the OAT menu, expand Server Administration and then click MACH.
  2. Under Clusters, select the cluster. The Cluster Topology and list of servers are displayed.

    The list displays the status of the primary server and the secondary servers, including high-availability data replication (HDR), shared disk secondary (SDS), and remote standalone secondary (RSS) servers.

Adding an SDS server

Add a shared disk secondary (SDS) server connection to a high availability cluster.

Prerequisite:

To create an SDS server connection:

  1. On the OAT menu, expand Server Administration and then click MACH.
  2. Under Clusters, select the cluster. The Cluster Topology and list of servers are displayed.
  3. Click Add SDS. The Add SDS Server wizard opens.
  4. Enter the following values:
  5. Click Next to continue entering the server information:
  6. For Informix Directory, verify the directory on the server in which IDS is installed: $INFORMIXDIR.

    The following fields are populated based on the information on the previous page:

  7. Click Finish to add the server.

Starting or stopping a server in a cluster

Change a server in a high availability cluster to online or offline mode.

Prerequisite:

Important:
When you stop a shared disk secondary (SDS) server, the server no longer appears in the cluster in OAT. To restore the SDS server to the OAT display, manually restart the server, and then click the Find Cluster button. When the cluster is found, the SDS server appears in the list of servers for the cluster.

To start or stop a secondary server:

  1. On the OAT menu, expand Server Administration and then click MACH. The High Availability Clusters page is displayed.
  2. Under Clusters, select the cluster. The Cluster Topology and list of servers are displayed.
  3. In the list of servers, for the server that you want to stop or start, click Modify. The Modify Server page displays the status of the server and indicates whether you can start or stop the server.
  4. Click Start or Stop.

Creating a service level agreement

Create a service level agreement for a high availability cluster by using the Connection Manager.

Prerequisite:
Find and name the clusters in the OpenAdmin Tool (OAT). See Finding high availability clusters.

A service level agreement (SLA) is a contract between specified client applications and their service providers, in this case, IDS servers. Based on service level agreements, the Connection Manager connects the client applications to the appropriate servers. The Connection Manager accepts client connection requests and either redirects the requests to the appropriate client server or functions as a proxy server between the client applications and the IDS servers.

To create an SLA:

  1. On the OAT menu, expand Server Administration and then click MACH.
  2. Under Clusters, select the cluster.
  3. Click Connection Manager, and add the SLA by using the Connection Manager wizard.

Modifying a service level agreement

Modify a service level agreement for a high availability cluster by using the Connection Manager.

Prerequisite:
Find and name the clusters in the OpenAdmin Tool (OAT). See Finding high availability clusters.

A service level agreement (SLA) is a contract between specified client applications and their service providers, in this case, IDS servers. Based on service level agreements, the Connection Manager connects the client applications to the appropriate servers. The Connection Manager accepts client connection requests and either redirects the requests to the appropriate client server or functions as a proxy server between the client applications and the IDS servers.

To modify an SLA:

  1. On the OAT menu, expand Server Administration and then click MACH.
  2. Under Clusters, select the cluster.
  3. Click Connection Manager and modify the SLA by using the Connection Manager wizard.

Configuring load balancing for a high availability cluster

Configure load balancing in a service level agreement (SLA) for a high availability cluster by using the Connection Manager.

Prerequisite:
Find and name the clusters in the OpenAdmin Tool (OAT). See Finding high availability clusters.

The Connection Manager can perform load balancing, in which the connection between a client application and a server is based on the server work load. The Connection Manager gathers information from each server in the cluster regarding the type of server, the unused workload capacity, and the state of the server. Based on this information, the Connection Manager connects the client application to the server that has the least amount of activity. You can configure load balancing when you create or modify an SLA.

To configure load balancing:

  1. On the OAT menu, expand Server Administration and then click MACH.
  2. Under Clusters, select the cluster.
  3. Click Connection Manager to display the Connection Manager wizard.
  4. Select the Connection Manager and click Next.
  5. Select the SLA and click Edit, or to create an SLA, click New.
  6. Click Create Group. An empty load-balancing group is added to the Servers in the SLA list.
  7. Add servers or server types to the load-balancing group.

Creating or modifying a failover configuration

Create or modify the failover configuration for a high availability cluster by using the Connection Manager.

Prerequisite:
Find and name the clusters in the OpenAdmin Tool (OAT). See Finding high availability clusters.

A failover configuration (FOC) specifies the secondary server that takes over the role of the primary server if the primary server fails.

To create a failover configuration:

  1. On the OAT menu, expand Server Administration and then click MACH.
  2. Under Clusters, select the cluster.
  3. Click Connection Manager, and add or modify the FOC by using the Connection Manager wizard.

Managing IDS configuration parameters

Viewing IDS configuration parameter values

To view the values of the configuration parameters on the current server, expand Server AdministrationConfiguration and click All to display the full parameter list.

You can search for a specific parameter name with the browser Find function.

You can filter the parameter list by selecting a view from the Onconfig Option list:

Reconfiguring dynamic parameters

  1. Expand Server AdministrationConfiguration. A list of parameters is displayed. You can display a list of parameters that can be reconfigured by selecting Dynamic Only from the Onconfig Option list.
  2. Click the parameter to be reconfigured.
  3. Update the value of the parameter.
  4. Specify one of the following Save Option values:
  5. Click Save to save the change.

Viewing parameter value recommendations

Expand Server AdministrationConfiguration. A list of parameters is displayed. You can display a list of parameters that have recommendations by selecting Recommendations from the Onconfig Option list.

Running system validation checks

Validating one or more databases or tables at the current server

Expand Server AdministrationSystem Validation.

To validate one or more databases or tables at the current server, use one of the following options:

Validating the extents of one or more dbspaces on the current server

  1. Expand Server AdministrationSystem Validation.
  2. Select the name of the dbspace, or select “ALL” to select all dbspaces.
  3. Click Check Extent. Status messages are displayed for each extent that is checked, verifying whether the check was successfully completed.

Managing virtual processors

Expand Server AdministrationVirtual Processors. A global view of the processors is displayed.

You can add or drop a virtual processor from this page, or select a class of virtual processors to view from the VP Classes table. You can also add or drop a class from the VPs of a Class page.

Administering database, table, and role privileges

Granting database privileges to a user

  1. Expand Server AdministrationUser Privileges.
  2. Select the non-system database in the Database list.
  3. Select Database-Level Privileges. The Database-Level Privileges page is displayed.
  4. Enter a user name in the User Name field. For example, myUserName.
    Restriction:
    On Windows, the database server does not support user names that are longer than 20 characters.
  5. Select one of the following privileges from the list:
    Privilege name Description
    CONNECT Allows the user to open a database, issue queries, and create and place indexes on temporary tables.
    RESOURCE Allows the user to create permanent tables.
    DBA Includes all the capabilities of the Resource privilege and allows the user to perform several additional functions as the DBA.
  6. Click Grant.

Modifying database privileges of a user

  1. Expand Server AdministrationUser Privileges.
  2. Select the database from the Database list and select Database-Level Privileges. The Database-Level Privileges table is displayed.
  3. In the row that contains the user name, click Modify.
  4. Select one of the following privileges from the list:
    Privilege Description
    CONNECT Allows the user to open a database, issue queries, and create and place indexes on temporary tables.
    RESOURCE Allows the user to create permanent tables.
    DBA Includes all the capabilities of the Resource privilege and allows the user to perform several additional functions as the DBA.
    To cancel the privileges of a user, select Revoke.
  5. Click Save to modify the privileges. The Database-Level Privileges table is refreshed and the row with the user name is updated.

    The Default Role column specifies any default roles assigned by an administrator to individual users or to the PUBLIC group for a particular database.

Revoking database privileges of a user

  1. Expand Server AdministrationUser Privileges.
  2. Select the database from the Database list and select Database-Level Privileges. The Database-Level Privileges table is displayed.
  3. In the row that contains the user name, click Modify.
  4. Select Revoke from the list.
  5. Click Save to save your changes. The Database-Level Privileges table is refreshed and the row with the user name is removed.

Granting table privileges to a user

  1. Expand Server Administration User Privileges.
  2. Select the database name from the Database list and select the Table-Level Privileges button. The Table-Level Privileges page is displayed.
  3. Enter a user name in the User Name field. For example, myUserName.
  4. Click one or more of the following privilege check boxes:
    Privilege Description
    Select Allows a user to retrieve the contents of the table, including temporary tables.
    Update Allows a user to modify existing rows.
    Insert Allows a user to add new rows.
    Delete Allows a user to delete rows.
    Index Allows a user to create and alter indexes on the table. Grant the References privilege only to users who understand the data model well and who you trust.
    Alter Allows a user to use the ALTER TABLE statement on the table, including the ability to add and drop columns and reset the starting point for SERIAL columns.
    References Allows a user to impose referential constraints on a table. Grant the References privilege only to users who understand the data model well.
    Under Allows you to control whether a user can use a typed table as a supertable in an inheritance hierarchy. The Under privilege is granted to PUBLIC automatically when a table is created (except in ANSI-compliant databases).
  5. Click Grant. The new user name is displayed in the table, along with the alphabetic symbol of the privileges granted to the user.

Modifying table privileges of a user

  1. Expand Server AdministrationUser Privileges.
  2. Select the database from the Database list and select Table-Level Privileges. The Table-Level Privileges table is displayed.
  3. In the row that contains the user name, click Modify.
  4. Select or clear the privilege check boxes for the user. For a description of the options, see Granting table privileges to a user.
  5. Click Save to modify the privileges. The Table-Level Privileges table is refreshed and the row with the user name is updated.

Revoking table privileges of a user

  1. Expand Server AdministrationUser Privileges.
  2. Select the database from the Database list and select Table-Level Privileges. The Table-Level Privileges table is displayed.
  3. In the row that contains the user name, click Modify.
  4. Clear all the privilege check boxes for the user. For a description of the options, see Granting table privileges to a user.
  5. Click Save to modify the privileges. The Table-Level Privileges table is refreshed and the row with the user name is removed.

Managing Auto Update Statistics

Auto Update Statistics (AUS) is a maintenance system for updating statistics, that can automate the DBA task of updating table statistics. AUS can also evaluate whether a table needs to have statistics updated at the scheduled time. You specify the policies that help with the evaluation.

AUS has two major parts:

Configuring and enabling Auto Update Statistics

  1. Expand Server AdministrationAuto Update Statistics Config.
  2. Based on your system performance and requirements, determine if the default values are valid for your system. For more information about setting these parameters, see the discussion of Auto Update Statistics expiration policies, in the Performance Guide in the IDS information center.
    1. To specify how often statistics are rebuilt, specify the number of days for the AUS_AGE parameter, such as 30, 60, or 90.
    2. To specify what percent of change to a table initiates a statistics rebuild, enter a percentage number, such as 20 or 50 for the AUS_CHANGE parameter.
    3. To specify whether AUS uses the recommended guideline rules, select On or Off from the list for the AUS_AUTO_RULES parameter. The default is On.
    4. To specify the least number of rows a table needs in order to have statistics rebuilt, enter a number for the AUS_SMALL_TABLES parameter, such as 100.
    5. To specify the PDQ priority, enter a number from -1 to 100 for the AUS_PDQ parameter. The default is 10. The Parallel Database Queries (PDQ) priority is one of the factors that determines how the database server uses resources for parallel processing.
  3. Click Save to keep your changes.
  4. To determine whether the AUS tasks are enabled, click the Info tab and then click these tasks: The Task Details page is displayed.
  5. Scroll down to the Enable Task field and ensure that it is selected.
  6. Specify how often the tasks are executed with the fields that set the run window. Because AUS is a resource-intensive operation, a specific run window can be defined. The run window definition includes a start time and end time, and the days of the week to run. By default the run time window is 1:00 AM – 5:00 AM daily.
  7. Click Save to save any changes.

Viewing refreshes and evaluations at the server

You can view the pending and completed UPDATE STATISTICS commands by expanding Server AdministrationAuto Update StatisticsList. Select either Pending Commands or Completed Commands from the list.
If there is no work for the refresh task to do, the refresh task disables itself in the Scheduler. However, the next time the evaluation task runs, and creates work for the refresh task, the evaluation task automatically enables the refresh task.

Requesting an immediate evaluation of statistics

Expand Server AdministrationAuto Update Statistics General and click Refresh Evaluation. Auto Update Statistics (AUS) analyzes and incorporates the engine feedback from previous updates, but does not update the statistics.

Determining when statistics are updated next

Expand Server AdministrationAuto Update Statistics General. Beneath the graph, review the Auto Stats will be run in ... status field, which specifies the number hours and minutes until the next time statistics are updated.

Viewing the current Auto Update Statistics settings

You can view the following information about Auto Update Statistics (AUS):

Monitoring the Auto Update Statistics settings

  1. Expand Server AdministrationAuto Update Statistics General.
  2. Review the Auto Update Statistics by Database table. This table displays the status of statistics updates for the current database.

Viewing alerts generated by Auto Update Statistics

Expand Server AdministrationAuto Update StatisticsAlerts. This page displays the alerts that Auto Update Statistics (AUS) has generated when running statistics in a database. Each alert is categorized by level of importance with a colored icon (Red, Yellow, or Green), and the type of alert (Error, Warning, or Informational).

Cleaning up abnormal runs of Auto Update Statistics

Expand Server Administration Auto Update StatisticsGeneral and click Cleanup.

How do I schedule Informix Dynamic Server tasks?

Adding Scheduler tasks

You can create Scheduler tasks that run either tasks or sensors at specified times and frequencies.

Adding a new task

Important:
The time at which you create the task affects the first execution of the task.

To add a new task:

  1. Click Add a New Task from the Task Schedule page.
  2. Click Task under What type of task would you like to add? and then click Next.
  3. Enter a name in the Task Name field. For example: MyTask.
  4. Select a category from the Task Group list.
  5. Add a description of the new task in the text field and click Next.
  6. Specify the allowable time frame in which the task can run, such as between ten o'clock (10:00) in the morning and 6 o'clock (18:00) in the evening, every hour, each day.
    1. Specify the start time of the time interval, using the 24-hour clock format: hh:mm:ss For example: 10:00:00
    2. Specify the stop time of the time interval, using the 24-hour clock format: hh:mm:ss For example: 18:00:00. You can also specify NEVER.
    3. Specify the frequency for how often the task can run during the allowable time interval, in days, hours, and minutes: d Days h Hours m Minutes For example: 0 Days 1 Hours 00 Minutes.
  7. Click Next.
  8. Specify the command statement that the task is to run. For example, if you want to delete miscellaneous tasks, you could enter the following statement:
    delete from ph_task where tk_group='MISC'
  9. Click Next. The confirmation page is displayed.
  10. Verify the entries for each selection:

Adding a new sensor

  1. From the Task Schedule page, click Add a New Task.
  2. Click Sensor under What type of task would you like to add? and then click Next.
  3. Enter a name in the Sensor Name field. For example: MySensor.
  4. Select a category from the Sensor Group list.
  5. Add a description of the new task in the text field and click Next.
  6. Specify the allowable time frame in which the sensor can run, such as between 10:00 AM and 6:00 PM, every hour, each day.
    1. Specify the start time of the time interval, using the 24-hour clock format: hh:mm:ss. For example: 10:00:00
    2. Specify the stop time of the time interval, using the 24-hour clock format: hh:mm:ss. For example: 18:00:00
    3. Specify the frequency for how often the task can run during the allowable time interval, in days, hours, and minutes: d Days h Hours m Minutes. For example: 0 Days 1 Hours 00 Minutes.
    4. Specify the data delete time, in days, hours, and minutes: d Days h Hours m Minutes. For example: 1 Days 0 Hours 0 Minutes.
  7. Click Next.
  8. In the Result Table Name field, specify the name of the result table to hold the sensor data. For example: MyResults
  9. Click Next.
  10. Complete the SQL statement for the results table by entering the column names and definitions in the SQL Statement field. For example, if you want to collect the return code of taskA (with task_id 10) in table TaskA_ReturnCode, you could enter the following SQL statement:
    create table TaskA_ReturnCode(task_name varchar(20), errorcode integer)
  11. Specify the command statement that the sensor is to run. For example:
    insert into TaskA_ReturnCode values('TaskA', (select run_retcode from ph_run where run_task_id=10))
  12. Verify the entries for each selection.

Enabling Scheduler tasks

Enable a Scheduler task that is disabled.

To enable a Scheduler task:

  1. Expand Task SchedulerScheduler.
  2. Click the task. The Task Details page for the task is displayed.
  3. Select Enable Task.
  4. Click Save.

Changing the Scheduler task allowable time interval

The allowable time interval is the time frame in which the task can run, for example between 10:00 AM to 6:00 PM.

  1. Expand Task SchedulerScheduler. A list of tasks with schedule details is displayed in the Task Details page.
  2. Click a task name to change the frequency or specifics of the scheduled run times. You can update the days or times at which the task runs.
  3. Specify the start time of the time interval, using the 24-hour clock format: hh:mm:ss. For example, specifying 10:00:00 represents 10 o'clock in the morning.
  4. Specify the stop time of the time interval, using the 24-hour clock format: hh:mm:ss. For example, specifying 18:00:00 represents 6 o'clock in the evening.
  5. Specify the frequency for how often the task can run during the allowable time interval, in days, hours, and minutes: d Days h Hours m Minutes; For example: 0 Days 1 Hours 00 Minutes.
  6. Select Disable for any days of the week for which you do not want the task to run.
  7. Click Save to save your changes.

Deleting a Scheduler task

Restriction:
System tasks cannot be deleted.

To delete a user-defined task:

  1. Expand Task SchedulerScheduler.
  2. Click Delete a Task. The Delete Task column is added to the Cron Task List table. Each task has a Delete button; for system tasks, the Delete button is deactivated.
  3. Locate the task name and click Delete. If the task is for a sensor, you are prompted to delete the result table associated with the sensor.
  4. Determine what to do with the table.

How do I explore performance information?

Analyzing query performance with SQL Explorer

You must turn on SQL tracing to use the SQL Explorer.

Use the SQL Explorer to perform query drill down. The SQL Explorer uses SQL tracing to gather statistical information about each SQL statement executed on the system and shows statement history. The following sections explain how to turn on SQL tracing and to use the SQL Explorer:

Turning on SQL tracing

  1. Navigate to Performance AnalysisSQL Explorer. If SQL tracing is not running, the following message is displayed SQL TRACING is currently off. Use the Admin option to switch back on.
  2. Click Admin to navigate to the SQL Trace Profile page
  3. From the Trace State list, select ON.
  4. Scroll down to the bottom of the page on click Save to turn on SQL tracing.

Turning off SQL tracing

  1. Navigate to Performance AnalysisSQL Explorer. If SQL tracing is on, you can select the tabs for the SQL Explorer. If SQL tracing is already off, the following message is displayed: SQL TRACING is currently off. Use the Admin option to switch back on.
  2. Click the Tracing Admin tab to navigate to the SQL Trace Profile page
  3. From the Trace State list, select OFF.
  4. Scroll down to the bottom of the page on click Save to turn off SQL tracing.

Specifying options for SQL tracing

SQL Explorer displays detailed information about SQL statements executed at the current server. You can specify what information is traced and available in the SQL Explorer from the Tracing Admin page.

To set SQL Explorer details:

  1. Navigate to Performance AnalysisSQL ExplorerTracing Admin.
  2. Specify the following SQL tracing options:
  3. Scroll down to the bottom of the page on click Save to save your changes.

Trace Mode list for IDS servers v11.50xC3 and higher

For IDS servers V11.50.xC3 and higher: Select a global or user mode from Trace Mode list:

Suspending SQL tracing from the ON state

For IDS servers V11.50.xC3 and higher.

To suspend SQL tracing without releasing resources:

  1. Navigate to Performance AnalysisSQL ExplorerTracing Admin.
  2. From the Trace State list, select Suspend.
  3. Scroll down to the bottom of the page on click Save to save your changes. Tracing is suspended.

Resuming SQL tracing from a suspended state

For IDS servers V11.50.xC3 and higher: To resume SQL tracing:

  1. Navigate to Performance AnalysisSQL ExplorerTracing Admin.
  2. From the Trace State list, select Resume.
  3. Scroll down to the bottom of the page on click Save to save your changes. Tracing is resumed.

Switching between Live and Saved Data

Switch between live SQL trace data and saved SQL trace data for a period that you specify.

To view SQL trace data, you must turn on SQL tracing. On the Tracing Admin page, set the Trace State to On. See Turning on SQL tracing.

To switch to saved data:

  1. On the OAT menu, expand Performance Analysis and then click SQL Explorer.
  2. To display historical SQL tracing data, click Switch to Saved Data.
  3. To set the time period for the historical data, at the top of the page, specify the Start and End date and time.
  4. To return to the current data, click Switch to Live Data.

Drilling down from the activity summary

SQL Tracing must be set to On. See Turning on SQL tracing for more information.

To perform SQL statement drill down:

  1. Expand Performance AnalysisSQL Explorer The statements that have been executed on the system are grouped by type along with summary statistics for each SQL statement group. The statement types include SELECT, DELETE, UPDATE, CREATE, DROP, and so on.
  2. Select the type of SQL statement (for example, SELECT) to see statement-level details, where all identical statements are grouped together.
  3. Locate the SQL statement of interest and click Drill Down to get detailed statistics about each invocation of this SQL statement.
  4. Locate a specific invocation of an SQL statement of interest and click Drill Down to get a detailed profile about this SQL statement.

Drilling down by transaction

SQL Tracing must be set to On. See Turning on SQL tracing for more information.

To perform drill down by transaction time:

  1. Expand Performance AnalysisSQL Explorer. The SQL Explorer pages are displayed.
  2. To display a list of transaction times for the 100 most recent transactions, click the Transaction tab.
  3. Click Drill Down for the transaction to be explored. A list of SQL statements that has been run for the transaction is displayed. You can click Drill Down to display statistics for the transaction.

Drilling down by SQL statement type

SQL Tracing must be set to On. See Turning on SQL tracing for more information.

To perform drill down by SQL type:

  1. Expand Performance AnalysisSQL Explorer. The SQL Explorer pages are displayed.
  2. To display a list of SQL statements sorted by SQL type, click the SQL tab.
  3. Click Drill Down for the statement to be explored. A list of each SQL statement that has been run is displayed. You can continue to drill down to the next level of exploration by clicking Drill Down for the statements be explored. Statistics for the statement are displayed.

Optimizing SQL statements

Create an external directive to optimize an SQL statement.

You can create an external directive to apply to an SQL statement. Optimizer directives are comments that instruct the query optimizer how to execute a query. External optimizer directives provide a way for you to change the query execution plan of an SQL statement. After you apply external directives to an SQL statement, the query execution plan of the SQL statement changes accordingly every time you rerun the SQL statement.

Note:
When you rerun an SQL statement, it must exactly match the SQL statement that you applied the directives on. For example, it must have the same letter capitalization and the same spacing.

To create an external directive:

  1. On the OAT menu, expand Performance Analysis and then click SQL Explorer.
  2. From the Activity Summary or Transactions page, drill down to the SQL Profile page for the statement.
  3. Click the Optimize tab.
  4. Create the directive:
  5. Specify how to apply the directive. The default is Inactive: Do not apply the directive to the SQL statement.
  6. Click Apply.

Exploring user sessions

You can view and kill user sessions using the Session Explorer.

To see what sessions are currently running at the server, expand Performance AnalysisSession Explorer.

To view any of the sessions running at the server:

  1. Expand Performance AnalysisSession Explorer and click the session ID number (SID). The Session Info page opens. This page contains statistics about the session, including user name, when the user connected to the server, memory used, and open tables.
  2. Click one of the following tabs to display additional performance information about the session:
    Tab name Description
    SQL SQL commands run by the session.
    Locks Locks held by the session.
    Threads Threads run by the session.
    Memory Memory used by the session
    Network I/O data for the session.
    Environment Environment variable values for the session.
    Profile Summary performance information about the session.

Killing a user session

To kill a user session, expand Performance AnalysisSession Explorer to view the list of sessions and click kill in the last column for the row.

Running system performance reports

  1. Expand Performance AnalysisSystem Reports.
  2. Specify the reports you want to run:

Monitoring servers with the onstat utility

Display performance and status information for a database server by using the onstat utility.

You can use the onstat utility to check the status and monitor the activities of a database server.

For descriptions of the onstat utility commands sorted by functional category, see the onstat portal in the Administrator's Reference in the IDS information center.

To use the onstat utility:

  1. On the OAT menu, expand Performance Analysis and then click onstat Utility.
  2. Type an onstat option. You do not need to type onstat.
  3. Click Run.

How do I manage databases and tables?

Reviewing the databases on a server

Expand SQL Toolbox, and then click Databases. The page displays a list of the databases on the current server and a chart of the space used by the six largest databases.

Connecting to a database

When you connect to a database, you can review database tables, schemas, procedures, and routines.

To connect to a database:

  1. Expand SQL Toolbox, and then click Databases. The page displays a list of the databases on the current server and a chart of the space used by the six largest databases.
  2. In the list of Databases, click the name of the database. A list of tables in the database is displayed on the Tables page. The name of the selected database is displayed in the Database field at the right top of the page.
  3. Optional: To include the catalog tables name, select Include Database Catalog Tables? and click Submit. A combined list of the catalog tables and user tables is displayed.

Viewing schemas

You must be connected to a database. See Connecting to a database.

To view schemas:

  1. Expand SQL ToolBox, and then click Schema Browser.

    A list of tables and their schemas is displayed. The name of the database that you are connected to is displayed in the Database field at the right top of the page.

  2. Select the browse icon for the table that you want to view.

    The result set from a SELECT * statement for the table is displayed.

Reviewing procedures and routines for a database

You must be connected to a database. See Connecting to a database.

To review the procedures and routines for a database:

  1. Expand SQL ToolBox, and then click Schema Browser.

    A list of tables and their schemas is displayed. The name of the database that you are connected to is displayed in the Database field at the right top of the page.

  2. Select the SPL/UDR tab.

    The Procedures/Functions list is displayed.

Performing standard SQL operations: query, insert, update, delete

Create a simple query to display specific rows in a table and then update or delete rows, or insert new rows in a table by using Query By Example.

Performing a query

Create a simple query to display specific rows in a table by using Query By Example.

You can perform a query on a table, and then review, update, or delete the rows that are displayed as a result.

To perform a query:

  1. On the OAT menu, expand SQL Toolbox, and then click Query By Example.
  2. To find the table that you want to query, begin typing its name in the Find field to display the tables that match, and then select the table in the list. You can also expand a database to display the tables.
    Note:
    The list displays only the tables that you can work with in Query By Example (QBE). The tables that you cannot work with in QBE do not appear, including tables that are system tables or tables in a system database (other than the sysadmin database) and tables that contain data types that are not supported by QBE: BLOB, BYTE, CLOB, DISTINCT TYPES, LIST, MULTISET, NCHAR, NVARCHAR, ROW, SET, TEXT, and UDTs.

    The columns in the selected table are displayed on the Query page.

  3. To enter the query criteria, click the button by a column to open the Enter query criteria window.
  4. Select an operator from the list of operators, enter a value, and click Add.

    The query criterion is displayed in the table. When you add another criterion, first select AND or OR.

  5. Click OK.

    The window closes and the query criteria are displayed for the column.

  6. To perform the query, click Submit.

    The page displays the rows that result from your query one row at a time. If you perform the query without entering any query criteria, all the rows in the table are displayed.

    To scroll through the rows, use the arrow buttons.

Updating a table row

Update a table row on the database server by using Query By Example.

Note:
Only rows with unique identifiers can be updated by using Query By Example. Unique identifiers include primary key, unique constraint, unique index, and rowid if the table is not fragmented.

To update a table row on the database server:

  1. Perform a query by using Query By Example. See Performing a query.
  2. If necessary, use the arrow buttons to scroll to the row.
  3. Enter the changes for the row in the fields.
  4. Click Update Row.

    The row is updated on the database server.

Deleting a table row

Delete a table row on the database server by using Query By Example.

Note:
Only rows with unique identifiers can be deleted by using Query By Example. Unique identifiers include primary key, unique constraint, unique index, and rowid if the table is not fragmented.

To delete a table row on the database server:

  1. Perform a query by using Query By Example. See Performing a query.
  2. If necessary, use the arrow buttons to scroll to the row.
    Important:
    When you delete a row, it is permanently deleted on the database server. You cannot undo a delete.
  3. Click Delete Row.

    The row is deleted on the database server.

Inserting a table row

Insert a row in a table on the database server by using Query By Example.

To insert a row in a table on the database server:

  1. On the OAT menu, expand SQL Toolbox, and then click Query By Example.
  2. To find the table that you want, begin typing its name in the Find field to display the tables that match, and then select the table in the list. You can also expand a database to display the tables.
    Note:
    The list displays only the tables that you can work with in Query By Example (QBE). The tables that you cannot work with in QBE do not appear, including tables that are system tables or tables in a system database (other than the sysadmin database) and tables that contain data types that are not supported by QBE: BYTE, TEXT, BLOB, CLOB, NCHAR, NVARCHAR, SET, MULTISET, LIST, ROW, DISTINCT TYPES, and UDTs.

    The columns in the selected table are displayed on the Query page.

  3. Click the Insert tab.
  4. Type the values in the fields.
    Tip:
    Hover help for each field identifies the required data type, for example, CHAR, DECIMAL, or DATETIME.
  5. Click Add Row.

    The row is inserted in the table on the database server.

How do I set up access to a server?

Creating an OAT group

Create an OpenAdmin Tool (OAT) group.

OAT groups provide a way to manage a group of database servers and to log on to a group rather than each individual server.

To add an OAT group:

  1. On the OAT Admin menu, expand Manage Connections and then click Add Group.
  2. In the Group Name field, enter an alphanumeric name of the group.
  3. In the Password field, enter an alphanumeric password.
  4. Optional: To specify that the group has read-only privileges, select Read Only.
    Important:
    Read-only groups prevent users from modifying a server; they do not prevent users from accessing potentially sensitive data contained within the server. Limit membership of read-only groups to users who are authorized to view the detailed data at the server.

    For more information about the restrictions for read-only groups, see Read-only group restrictions.

  5. Click Add to save the group information and add the group.

Next add server connections to the group. See Creating a server connection

Creating a server connection

Add a server connection to an OpenAdmin Tool (OAT) group.

  1. On the OAT Admin menu, expand Manage Connections and then click Add Connection.
  2. From the Group Name list, select the OAT group to add the server connection to.
  3. Enter the connection information.
    Important:
    If you are using an I-Connect or Client SDK version before V3.0, for each new connection you add, ensure that there is a corresponding SQLHOSTS entry for that connection on the Web server computer.
  4. Click Save to add the connection.

Exporting connection information

Export connection information for an OpenAdmin Tool (OAT) group to an XML file.

To export connection information:

  1. On the Login page, click Admin. The administration menu and pages for OpenAdmin Tool are displayed.
  2. Expand Manage Connections, and then click Connection Admin.
  3. To include the user name and password in the XML file, select Include sensitive password information in exported XML file. By default, the exported information does not include the OAT group user name and password.
  4. Click Export to XML.

Importing connection information

Import connection information for an OpenAdmin Tool (OAT) group from an XML file.

The connections file that you import must have the same format as the connections files exported by OAT. Click the help button on the Connection Admin page for a description of the file.

To import connection information:

  1. On the Login page, click Admin. The administration menu and pages for OpenAdmin Tool are displayed.
  2. Expand Manage Connections, and then click Connection Admin.
  3. Under Import connection information from an XML file, browse for the XML file that contains the connection information.
  4. To replace the existing connections data, select Remove all existing connection information. Or leave the option cleared to append the information to the existing connections data.
  5. Click Import from XML.

How do I configure security for the OpenAdmin Tool?

Creating an OAT group with read-only privileges

Create an OpenAdmin Tool (OAT) group whose members can view information at a server, but cannot modify it.

Important:
Read-only groups prevent users from modifying a server; they do not prevent users from accessing potentially sensitive data contained within the server. Limit membership of read-only groups to users who are authorized to view the detailed data at the server.

For more information about the restrictions for read-only groups, see Read-only group restrictions.

To create a group with read-only privileges:

  1. On the OAT Admin menu, expand Manage Connections and then click Add Group.
  2. In the Group Name field, enter an alphanumeric name of the group.
  3. In the Password field, enter an alphanumeric password.
  4. To specify that the group has read-only privileges, select Read Only.
  5. Click Add to save the group information and add the group.

Next add server connections to the group. See Creating a server connection

Read-only group restrictions

Read-only groups have the following restrictions:

Limiting access to the Admin configuration pages

It is important to limit access the Admin pages accessed from the Login page because they can be accessed by anyone that access the Login page. These pages configure OpenAdmin Tool, including specifying access to server groups.

To prevent unauthorized access to these pages, use one or both of the following methods:

Providing password protection for the Admin configuration pages: Apache example

It is recommended that you limit access to the OpenAdmin Tool administration pages for security reasons. Protecting these pages with a password is important because the Admin pages are accessible from the Login page and, therefore, open to everyone.

One method is to use the built-in authentication provided by the Web server. For example, you can protect the installation directory, OATINSTALL/admin/.htaccess.

Example: To protect the OpenAdmin Tool installation directory from within Apache:

  1. Create a password file in a directory that is not accessible from the Web using the htpasswd utility that came with Apache.
  2. Add authentication directives to one of the following files:
  3. Put the directives inside a <Directory> section. For example:
    <Directory OATINSTALL/admin>
    AuthType Basic
    AuthName "OAT Admin"
    AuthUserFile PASSWORDFILE
    Require ValidUser
    </Directory>
    Where PASSWORDFILE is the directory in which you created the password file with the htpasswd utility.

See Apache HTTP Server Version 2.0: Authentication, Authorization and Access Control for more information and examples.

Configuring HTTPS for OpenAdmin Tool clients

Hypertext Transfer Protocol over Secure Socket Layer, or HTTPS, protects the OpenAdmin Tool (OAT) Web server from unauthorized eavesdropping, tampering, and message forgery, or from those who are trying to secretly listen or interfere with the network.

In this section, client refers to an OpenAdmin Tool client (such as the web browser used to view OAT) and Web server refers to the Web server configured for use with the OpenAdmin Tool client.

Enabling HTTPS encrypts messages originating from the OpenAdmin Tool client before sending it to the Web server, which prevents unauthorized users from listening over the line and stealing sensitive information. Enabling HTTPS also allows the client to authenticate with the OpenAdmin Tool Web server, preventing unauthorized users from deceiving the client with a false Web server.

Important:
HTTPS encrypts communication only between the OpenAdmin Tool Web server and the client. It does not encrypt communication between an IDS database server and the Web server. IBM Informix Dynamic Server (since Version 9.4), enables encryption of data between the IDS server and OAT Web server using an encryption communication support module. You can find more information about encryption with IDS in the following article on developerWorks®: "Encryption over the wire with IDS 9.40."

Enabling HTTPS in OpenAdmin Tool involves the following steps:

  1. Replacing the Apache Web server installed with OpenAdmin Tool with another Apache Web server enabled for the mod_ssl cryptography module.
  2. Creating an encryption key and a certificate for the new Web server so that OpenAdmin Tool clients can authenticate the Web server based on your certificate.
  3. Configuring the httpd.conf (the Apache configuration file) to enable HTTPS.

For detailed information, see the following developerWorks article: "HTTPS on OAT."

Disabling or enabling password protection for SQL Toolbox

OpenAdmin Tool V2.22 and later has automatic password protection for SQL Toolbox. When you connect to the SQL Toolbox, you must provide a valid user ID and password.

Prerequisite:
To disable or enable password protection for SQL Toolbox, you must have access to the OAT Admin configuration pages, which can also be password protected.
Important:
For security purposes, provide password protection for the Admin configuration pages. For more information, see Providing password protection for the Admin configuration pages: Apache example.

To disable password protection:

  1. On the Admin menu, click OAT Config.
  2. Clear the Require login credentials when using SQL Toolbox.
  3. Click Save to save your changes.
  4. To re-enable password protection, select Require login credentials when using SQL Toolbox and click Save.

How do I manage plug-ins for the OpenAdmin Tool?

Installing a plug-in for the OpenAdmin Tool

Tip:
The OpenAdmin Tool (OAT) installation package includes the Enterprise Replication plug-in and the Schema Manager plug-in. These plug-ins are installed automatically with OAT and can be enabled during installation.

To install a plug-in for OAT:

  1. Copy the compressed plug-in file into the OATDirectory/plugin_install. For example: C:\Program Files\OpenAdmin\Apache_2.2.4\htdocs\openadmin\plugin_install.
  2. On the OAT Login page, click Admin.
  3. On the Admin menu, click Plug-in Manager.
  4. In the Plug-ins not yet installed list, in the row that displays the plug-in, click Install.

    The plug-in is displayed in the Installed Plug-ins list and on the main menu in OAT. To change the position of the plug-in menu item in OAT, use the Menu Manager page. See Customizing the main menu for the OpenAdmin Tool.

Upgrading a plug-in for the OpenAdmin Tool

To upgrade a plug-in for the OpenAdmin Tool (OAT):

  1. Copy the compressed plug-in file into the OATDirectory/plugin_install. For example: C:\Program Files\OpenAdmin\Apache_2.2.4\htdocs\openadmin\plugin_install.
  2. On the OAT Login page, click Admin.
  3. On the Admin menu, click Plug-in Manager. The new version of the plug-in is displayed in the Plug-ins not yet installed list.
  4. In the Plug-ins not yet installed list, in the row that displays the plug-in, click Upgrade. The previous version of the plug-in is overwritten with the newer version. The updated version of the plug-in is displayed in the Installed Plug-ins list.

Disabling a plug-in for the OpenAdmin Tool

To disable a plug-in for the OpenAdmin Tool (OAT):

  1. On the OAT Login page, click Admin.
  2. On the Admin menu, click Plug-in Manager.
  3. In the Installed Plug-ins list, in the row that displays the plug-in, clear the Enabled check box.

How do I customize the main menu for the OpenAdmin Tool?

Use the Menu Manager page to change the order of the items in the OpenAdmin Tool main menu.

Customizing the main menu for the OpenAdmin Tool

To customize the main menu for the OpenAdmin Tool (OAT):

  1. On the OAT Login page, click Admin.
  2. On the Admin menu, click Menu Manager.
  3. To move an item, drag it to a different location in the list.
  4. To display the items in a menu, click the arrow in front of it. The number after a menu indicates the number of items in the menu. For example, Health Center (3) indicates that there are three items in the menu.
  5. To save the menu order, click Save.

Notices

This information was developed for products and services offered in the U.S.A.

IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative for information on the products and services currently available in your area. Any reference to an IBM product, program, or service is not intended to state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or service that does not infringe any IBM intellectual property right may be used instead. However, it is the user's responsibility to evaluate and verify the operation of any non-IBM product, program, or service.

IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not grant you any license to these patents. You can send license inquiries, in writing, to:

IBM Director of Licensing
IBM Corporation
North Castle Drive
Armonk, NY 10504-1785
U.S.A.

For license inquiries regarding double-byte (DBCS) information, contact the IBM Intellectual Property Department in your country or send inquiries, in writing, to:

Intellectual Property Licensing
Legal and Intellectual Property Law
IBM Japan Ltd.
1623-14, Shimotsuruma, Yamato-shi
Kanagawa 242-8502 Japan

The following paragraph does not apply to the United Kingdom or any other country where such provisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in certain transactions, therefore, this statement may not apply to you.

This information could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein; these changes will be incorporated in new editions of the publication. IBM may make improvements and/or changes in the product(s) and/or the program(s) described in this publication at any time without notice.

Any references in this information to non-IBM Web sites are provided for convenience only and do not in any manner serve as an endorsement of those Web sites. The materials at those Web sites are not part of the materials for this IBM product and use of those Web sites is at your own risk.

IBM may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you.

Licensees of this program who wish to have information about it for the purpose of enabling: (i) the exchange of information between independently created programs and other programs (including this one) and (ii) the mutual use of the information which has been exchanged, should contact:

IBM Corporation
J46A/G4
555 Bailey Avenue
San Jose, CA 95141-1003
U.S.A.

Such information may be available, subject to appropriate terms and conditions, including in some cases, payment of a fee.

The licensed program described in this document and all licensed material available for it are provided by IBM under terms of the IBM Customer Agreement, IBM International Program License Agreement or any equivalent agreement between us.

Any performance data contained herein was determined in a controlled environment. Therefore, the results obtained in other operating environments may vary significantly. Some measurements may have been made on development-level systems and there is no guarantee that these measurements will be the same on generally available systems. Furthermore, some measurements may have been estimated through extrapolation. Actual results may vary. Users of this document should verify the applicable data for their specific environment.

Information concerning non-IBM products was obtained from the suppliers of those products, their published announcements or other publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products.

All statements regarding IBM's future direction or intent are subject to change or withdrawal without notice, and represent goals and objectives only.

All IBM prices shown are IBM's suggested retail prices, are current and are subject to change without notice. Dealer prices may vary.

This information is for planning purposes only. The information herein is subject to change before the products described become available.

This information contains examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples include the names of individuals, companies, brands, and products. All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental.

COPYRIGHT LICENSE:

This information contains sample application programs in source language, which illustrate programming techniques on various operating platforms. You may copy, modify, and distribute these sample programs in any form without payment to IBM, for the purposes of developing, using, marketing or distributing application programs conforming to the application programming interface for the operating platform for which the sample programs are written. These examples have not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of these programs. The sample programs are provided "AS IS", without warranty of any kind. IBM shall not be liable for any damages arising out of your use of the sample programs.

Each copy or any portion of these sample programs or any derivative work, must include a copyright notice as follows:

© (your company name) (year). Portions of this code are derived from IBM Corp. Sample Programs.

© Copyright IBM Corp. _enter the year or years_. All rights reserved.

If you are viewing this information softcopy, the photographs and color illustrations may not appear.

Trademarks

IBM, the IBM logo, and ibm.com® are trademarks or registered trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at “Copyright and trademark information” at http://www.ibm.com/legal/copytrade.shtml.

Adobe®, the Adobe logo, and PostScript® are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States, and/or other countries.

Intel®, Itanium®, and Pentium® are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries.

Java™ and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Microsoft®, Windows, and Windows NT® are trademarks of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Other company, product, or service names may be trademarks or service marks of others.

Copyright

This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such.

When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you.

Copyright International Business Machines Corporation 2007, 2009. US Government Users Restricted Rights -- Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.