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.
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.
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
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
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
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
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
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.
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.
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.
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.
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:
- 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
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
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.
Learn
-
developerWorks Informix
page: Read articles and tutorials and connect to other resources to expand your Informix skills.
-
"Using
IBM Informix Dynamic Server on Microsoft Windows" (developerWorks, December 2007): View a demo showing you how to install Apache, PHP, and the OpenAdmin tool.
-
"Use the Informix Dynamic Server scheduler and SQL API" (developerWorks, September 2007): Learn how the IDS 11 scheduler and the SQL API make a DBA's daily tasks easier.
-
"IBM Informix
Dynamic Server 11 Information Center": Learn more about Informix.
-
"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.
-
developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
-
Stay current with developerWorks
technical events and webcasts.
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 (Undergoing maintenance)
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
