The Informix® Dynamic Server (IDS) OpenAdmin Tool (OAT) is a
PHP-based, open source administration tool, supported on IDS 11.1 and later. In this
article, you'll learn how to migrate from the Informix Server Administrator (ISA) to
the OAT. This article also describes how the basic capabilities of ISA are made available on OAT, along with enhanced functionalities and greater ease-of-use.
Since OAT is an open source tool, it is easy to modify, and you can tune it to meet your needs. Some of the distinct features that make OAT special are support for MACH (a new IDS 11 feature), the ability to manage multiple Informix servers from a single instance, an easy-to-use GUI, and automated update statistics.

Bharath Sriram worked with the integration team and on OAT while at IBM. He is certified in systems administration for IBM Informix Dynamic Server V11. He holds a master's degree in computer science from Ohio State University. His research interests include information retrieval and text mining in social networks.

Thamizhchelvan Anbalagan is a development engineer for the IBM Informix common application development JDBC team with IBM Software Labs, India. For the last three years, he has worked on various Informix-CSDK products for IDS.

Introduction

Part 1 of this series introduced the IDS OAT, a Web-based interface for administering
IDS, supported on IDS 11.10 and later. OAT offers many advantages:

A single installation of OAT can administer multiple data servers.

No additional software is required on the data server, and OAT can be run on any Web browser.

The installation process itself is simplified by an automated installer.

IDS 11 and OAT work in synergy to create an "administration-free" zone.

The ISA is a similar tool used to monitor Informix Server, Version 10.
Although OAT and ISA have similar functionalities, there are certain distinct features that make the OAT stand out.
This article also discusses certain core functionalities that are supported on ISA and how the same task can be performed on OAT with greater ease.

The actual migration process

The SQL editor

On the ISA side

To fire an SQL query on a server using ISA, you have to click the SQL link from
the main ISA menu that will show the available databases in a list box. You choose
the database and write the query that is to be fired on a server, and click Submit. ISA then passes the query to the server and fetches the content to the current Web page. Figure 1 shows the ISA SQL editor.

Figure 1. The ISA SQL Editor

On the OAT side

OAT provides a component called the "SQL Toolbox."
The toolbox consists of the following sub-components:

Databases

Schema Browser

SQL Editor

The first two sub-components are used to display the current databases and the schema layout for each database respectively.
The third component is especially useful to fire SQL queries through OAT. Clicking on the
SQL Toolbox takes you to a screen consisting of a text box where you enter queries.
Currently, OAT supports the entry of one SQL statement at a time.

Figure 2. The OAT SQL Editor

It is also possible to import queries from a text file and execute them from the file instead of manually entering the queries in the text box.
Similarly, it is possible to save the typed query to an external file using the Save Query to File button.

Configuration parameters

On the ISA side

Using ISA, you can create a new instance and edit the configuration from a remote machine.
Users are allowed to edit onconfig, sqlhosts files.
To accomplish this from ISA main menu, click the configuration link, This option lists the various configuration menus that are available with ISA.

Figure 3. Configuration parameters list on ISA

On the OAT Side

The OAT provides an option to view all the configuration parameters that determine the
server's behavior.
The OAT also goes one step further by providing recommendations regarding the parameter
value when an onconfig parameter is not set to the recommended value.
This is shown with a yellow background.
Certain parameters can be dynamically changed through OAT.
Clicking on a specific parameter provides details, such as the description of what that
parameter does, parameter type, constraints (like minimum value and maximum value), and current value.

Figure 4. Configuration parameters list on OAT

View the logs

On the ISA side

It is possible to view the logical, physical, and online log by selecting Logs under the ISA menu and selecting the required type of log.
However, it is not possible to view all the log files simultaneously.
Only the Logical Logs screen is shown in Figure 5.

Figure 5. Viewing the logs on ISA

On the OAT Side

It is possible to view the physical log, logical log and online log contents on the OAT by generating reports.
The OAT also provides a pie chart that gives the data in the graphical format showing used and the free space.
Click Performance Analysis > System Reports.
Check the Logical Logs, Physical Log, Online Log, and press
Create Report, as shown in Figure 6. A report of the log files is created in a new window.

Figure 6. Viewing the logs on OAT

Space administration

On the ISA side

You can directly create or view the DBSpaces using the ISA tool.
From the ISA main menu, click Storage to go to the Spaces screen, where the Show option lists the existing dbspaces.
You can also use the Add dbspace, Add blobspace, and Add sbspace buttons to create respective spaces in the server environment.

Figure 7. Administrating the space on ISA

On the OAT side

Select the Space Administration component and navigate to the DBSpaces tab to display usage information about the server.
Click the Graph or Data buttons to switch between a pie chart view or a two-column table view.
The bottom part of this page displays a list of dbspaces and information about each dbspace at the server, with the following columns:

Number: The number associated with the dbspace

Name: The dbspace name

Type: The type of space (dbspace, sbspace, or temporary dbspace)

Status: Shows the current status of the dbspace

Size: The size of the space

Free: The amount of free space within the dbspace

Used %: The percentage of used space within the dbspace

Number of Chunks: The number of chunks within the dbspace

Page Size: The page size of the dbspace

You can sort the table on any of the columns in ascending or descending order.
To see additional information, and with the proper authorization to perform administrative tasks on the dbspace, click on a dbspace name.
The Summary, Admin, Tables, and Extents tabbed pages are displayed. You can then click on a tab to select its page.
It is also possible to create dbspaces, blob spaces, smart blob spaces, and temporary dbspaces using this feature.

Figure 8. Administrating the space on OAT

System validation

On the ISA side

ISA allows you to run the various oncheck options easily.
To explore this, click the Check option from the main menu. That results in a
database selection wizard, wherein you allowed to choose the database where you want
to perform various checks, namely database, data, index, logs, and space checks.
These are the options that IDS supports.

Figure 9. System validation on ISA

On the OAT side

Use the System Validation page under the Server Administration section of the menu to run
a system validation check on databases and tables on the current database server.
To check the data format for a particular table, perform the following steps:

Select the database from the Database Name drop-down list.

Select the table of choice from the Table Name drop-down list.

Click Check Table.

To check the extent format for a database, select the dbspace from the Dbspace
Name drop-down list and click Check Extent.

Figure 10. System validation on OAT

What makes OAT special?

Monitor multiple IDS instances

First and foremost, a single instance of OAT can be used to monitor multiple database server instances.
This feature is useful especially for a database administrator (DBA).
This makes the whole process of administration geography-independent and monitoring multiple servers is just a click away.
In Figures 11 and 12 show how simple it is to switch between multiple database servers.
Currently, the OAT title bar shows that it is monitoring the "Athens" database server, as
shown in Figure 11.
The drop-down box lists all the current IDS servers that are up and running.
You can switch from the Athens server to, say the Berlin IDS server, by selecting
Berlin from the drop-down box.
A pop-up window asks for a confirmation. Click Yes, and you can instantly monitor
the Berlin IDS server, which is miles away from the Athens IDS server.
Also, one of the other benefits of OAT, in contrast with ISA, is that you don't need to install OAT on the same machine as IDS.

Figure 11. Currently connected server

Figure 12. Confirmation screen while
switching servers

Easy-to-use GUI

The OAT stands out because of the ease of administrating a database server.
The Web interface provided is very simple and provides information that is most likely to be required by the DBA.
Since it has pie charts to represent data, it makes it easier to comprehend the information quickly instead of browsing through a lot of statistical data.
Although there are a lot of features that justify the ease of use of OAT, two such
features are explained below that make the whole experience of using the OAT very pleasant.

First, whenever you need to connect to an IDS server, you need not specify the server and machine details every time.
On the login screen, click Admin and fill out the IDS details once. Then come back to the login screen.

Figure 13. Login screen

The login start-up screen lists the newly added server name.
Click the server name, and all the details including username, password, and host name are automatically filled in for you by OAT.
This reduces the time and the frustration of entering the details again and again.

Secondly, the OAT displays information that is required by the DBA to analyze the
performance of an IDS server and take necessary measures if required.
The Performance Analysis component on the left part of the screen has a Systems Report
sub-component that provides a comprehensive list of all the reports that represents the
current state of IDS in terms of SQL statements summary, memory usage, and network status.

Figure 14. System Reports page

Figure 14 shows that you can either select the report type from the drop-down box or
manually check mark the required reports available below. After check marking the
required report, click Create Report and OAT generates the report dynamically.
Using this feature, you can get the much needed information, like the SQL statements with
the most IO time and the slowest five SQL statements.
If you still have queries regarding the usage of a specific component, a Help button similar to
can be found in the top-right of the OAT screen, which guides you to perform an operation.

Task scheduler wizard

The task scheduler wizard is a tool used to schedule two different types of tasks.
The first is a "typical" task that executes at a specific time to perform a specific
operation, and the other, a specialized form of task called a "sensor" that collects and saves the data as a result of the task.
Using the OAT, you can specify the task type, for instance whether the task is a typical
task or a sensor, specify the time when the task should be executed, frequency of the
task, what the task should do, verify if the task did actually execute successfully, and edit the task details as well if required.
Since doing the same on the server directly involves working with a lot of databases and
tables, OAT makes the whole process of task scheduling and monitoring extremely simple.
OAT also supports specialized tasks called "start-up tasks" and "start-up sensors", which
are executed only once during the server start-up.
Figure 15 is an example of the task scheduler wizard.

Figure 15. Scheduling a new task

For an example, a task called "Sample Task_1" has been scheduled for execution at 10am
and stop time at 11am.
Alternatively, it is possible to specify the stop time as never.
The task is to be executed every two days as specified by the Frequency, and the task
does a simple insert operation into a table.
The task details are specified in Figure 16.
After a task is successfully scheduled, you can see the task details under the Task
Details sub-component as a new row, similar to Figure 17.
Clicking on the task name here (as circled in red) allows you to edit or disable the task.

Figure 16. Task Confirmation

Figure 17. Cron task list

The Task Runtimes sub-component can be used to verify whether a task successfully
executed and other performance details, like the time taken for the task to execute.

Figure 18. Task run list

When a task successfully executes, a green check mark, similar to
,
appears in the Last Execution Status column and the Last Run Time is appropriately filled.

Note: Certain specialized tasks, like deleting a task from the scheduler, are
supported only on Informix Server, Version 11.50 and later.

OAT is open source

One of the most flexible features of the OAT is that it is open source. Therefore, you
can customize the tool as per your needs.
You can change the look and feel, customize menus, change the code to give you the
information that you feel is more relevant, and hide components.
Figure 19 is an example of the page when the background code is manipulated.
Also, a code snippet detailing the changes is mentioned.

Figure 19. Manipulated OAT page

Adding a Menu

OAT has two menu links: admin-based menu links and user-based menu links.
They are stored in OATDIR/admin/menu.xml file and OATDIR/admin/conf/menu.xml respectively.
The following changes need to be done in the conf/menu.xml file to add a new menu item.
The example uses My Customized Menu.

As OAT supports multilingual, whenever you want to display the content in the browser,
you also need to add the same in the specific language file. Here in an example, "My
Customized Menu" is the newly added content, the corresponding entry should be made in
the OATDIR/lang/en/lang_menu.xml file.

Listing 3. Code to
add a link in the menu

act: The module name, which is the same as the file name under the modules
directory that should be invoked. This example has act=show, so the absolute file name is show.php

do: A keyword passed to the module that tells it what to "do", for
example, which function(s) within the module to run.

This example uses do=showOnlineLogTail, so the switch case to be invoked is showOnlineLogTail.
When you click My Cheetah 11.10.UC1.Log, the browser searches a file under the
OATDIR/modules directory and invokes and passes the "do" content to the switch case
of the file specified in the "act" variable.
For example, the browser passes the showOnlineLogTail into the OATDIR/modules/show.php file and that
results in the Online Message Log! content in the browser.

Automated Update Statistics

This feature is available with Informix Server, Version 11.50 and later.
The Auto Update Statistics (AUS) component of OAT is used to simplify the work of the DBA
by running the update statistics command based on specific policies.
These policies are based on various configuration parameters, like AUS_AGE, AUS_CHANGE, AUS_SMALL_TABLES, AUS_AUTO_RULES, and AUS_PDQ.
Update statistics is run only on logged databases.

Why Auto Update Statistics?

To simplify the repetitive work DBAs are required to undertake to ensure optimal performance.

To ensure optimal performance of the database engines. Many customers new to
Informix do not realize they must update statistics, and see poor performance due to the lack of optimizer statistics.

The time it takes a DBA to develop an optimal plan to run update statistics is
complex and time consuming, so most DBAs settle for a non-optimal plan or lack the understanding to develop an optimal plan.

Figure 20. Automated Update Statistics

The screen shows the databases and the tables requiring refresh of the statistics based on the previous evaluation done by AUS.
To get the latest details, click Refresh Evaluation.
In brief, each of the red-circled tab buttons does the following:

Info: Provides when the AUS evaluation and refresh are performed. It is possible to change the settings for these tasks

Alerts: Provides details regarding tables missing "statistics update"

List: Provides the actual "Execution Command" to run update statistics

Config: Allows the user to set the AUS parameters, like AUS_AGE, AUS_CHANGE, AUS_SMALL_TABLES, AUS_AUTO_RULES, and AUS_PDQ

The OAT Help Option for AUS provides the detailed description of all the AUS
configuration parameters.

Support for multi-node active cluster for high availability (MACH)

Since there are a lot of features present on OAT to support MACH, just the core
functionalities of OAT with respect to MACH are mentioned:

Figure 21. MACH page

Database Privileges Manager

Database and table permissions can be easily added or modified through OAT.
The Database Privileges Manager requires you to first select a database.
Then you are able to modify the privileges within that database.
You can switch between databases at any time through a database drop-down box on the top
of the page.

Database-level privileges: It has three options. You can view the current
privileges, grant a new database privilege to a user, and modify the existing privilege.

Figure 22. Database-level privileges

Table-level privileges: Using this option, you can view, grant, and modify the existing privileges of a particular table.

Figure 23. Table-level privileges

Roles: Can be created and viewed in a particular database.

Figure 24. Roles

Conclusion

The article has explained the migration process from ISA to OAT.
Core functionalities on ISA can be performed on OAT with greater ease and, in some cases, with enhanced functionality.
Also, there are certain features specific to the OAT that make it special.
Snap-shots of some such features have been provided that make the whole process of using the OAT easy for the user.
IDS is a powerful database engine. OAT provides an easy to use GUI to monitor IDS and
makes the job of a DBA much easier.

"Informix
Unleashed" by John McNally, Glenn Miller, Jim Prajesh, and Jose Fortuny: Using a hands-on approach, this guide serves as a high-level tutorial for users who are new to Informix and as a helpful reference for those who know the product but need additional tips, tricks, and workarounds.

The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.