Manage your Informix database with the IDS OpenAdmin Tool, Part 2: Migrate from Informix Server Administrator to the IDS OpenAdmin Tool

What makes OAT special?

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, Associate Software Engineer, IBM

Bharath SriramBharath 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 A. Anbalagan (tanbalag@in.ibm.com), Staff Software Engineer, Common AD Team, IBM

T. AnbalaganThamizhchelvan 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.



10 July 2008

Also available in Chinese Russian

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
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
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
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
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
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
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
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
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:

  1. Select the database from the Database Name drop-down list.
  2. Select the table of choice from the Table Name drop-down list.
  3. 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
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
Currently connected server
Figure 12. Confirmation screen while switching servers
confirmation screen

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
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
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 system-oat 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
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
Task confirmation
Figure 17. Cron task list
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
Task run list

When a task successfully executes, a green check mark, similar to check mark, 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
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.

Listing 1. My Customized Menu
<menu id="MyNewCust" name="My Customized Menu" lang="MyNewCust" expanded="false">

<item name="Online Log" lang="onlinelogC" link="index.php?act=show&
do=showOnlineLogTail" title="Show Online Message Log"/>


<item name="OnBar Act Log" lang="baractlog" link="index.php?act=show&do
=showBarActLogTail title="Show OnBar Activity Log"/>
    
</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 2. Entries in the lang_menu.xml file
<MyNewCust><![CDATA[My Customized Menu!]]></MyNewCust>

<onlinelogC><![CDATA[My Cheetah 11.10 UC1 Log!]]></onlinelogC>


All the language-specific entries need to be added in the OATDIR/lang directory.

Search and display the contents

The example adds the following link in the menu.

Listing 3. Code to add a link in the menu
<item name="Online Log"     lang="onlinelogC"     link="index.php?act=show&

do=showOnlineLogTail" title="Show Online Message Log" />


Each menu link can have variables. Some of them are explained below:

  • 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
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:

  • Configuring the IDSD daemon
  • Starting and stopping servers in the cluster
  • Creating an SD secondary server
  • Managing service-level agreements and failover configurations with the Connection Manager Wizard
Figure 21. MACH page
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
    DB 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
    Table-level privileges
  • Roles: Can be created and viewed in a particular database.
    Figure 24. Roles
    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.

Resources

Learn

Get products and technologies

  • Download the IDS OAT.
  • Download a free trial version of IDS.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


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.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source
ArticleID=319346
ArticleTitle=Manage your Informix database with the IDS OpenAdmin Tool, Part 2: Migrate from Informix Server Administrator to the IDS OpenAdmin Tool
publish-date=07102008