OpenAdmin Tool for IDS, the PHP-based administration console for IDS, is now available for downloadfrom the Free Product download site.
Updated 2/14/08 with new recommended PHP and XAMPP versions which are good for Cheetah and Cheetah 2.
Note: This article was updated for the latest version of OpenAdmin Tool on July 30 2007.
Installation of OpenAdmin can appear complex - this article provides anexample of one way of installing OpenAdmin. This example uses an XAMPP package on Windows. XAMPP is a bundle of open-source utilities (such as Apache, PHP and PEAR) thatare used by OpenAdmin. Installing XAMPP simplifies the OpenAdmin install,but many other installation and configuration options are possible.
High-level Summary of Install steps Detailed steps are provided below. This is the high-level sequence of stepsyou will follow:
Install CSDK or Connect 3.00.TC1 (or later)
Download and extract the XAMPP 1.6.4 zip file
Run XAMPP setup
Edit the php configuration file
Download and extract the OpenAdmin package
Start the Apache Webserver
Install and configure OpenAdmin
1. Install Informix Client SDK or Connect
If not already installed, install the Informix Client software. Rememberwhere you install it; the INFORMIXDIR value will need to be set as an environment variable when you start thewebserver. You can set INFORMIXDIR in the system environment using Control Panel->System. (Note: CSDK or Connect 3.00.TC1 or later is recommended.)
Unzip XAMPP to the destination directory of your choice. (In this example we'll assume the zip was extracted to c:\, which would put the XAMPP software in c:\XAMPP\.)
3. Run XAMPP setup
cd to the XAMPP directory and run setup_xampp. When the batch file finishes running, press any key to continue.
4. Update the php configuration file (php.ini)
Navigate to the xampp\apache\bin subdirectory, and open the php.ini file in a text editor. (Note: the XAMPP package contains more than onephp.ini file - the correct one is in xampp\apache\bin (i.e., c:\xampp\apache\bin\php.ini).)
Edit php.ini to enable the SQLITE and Informix PDO drivers:
Locate the following lines and remove any comment indicators from in front of them: extension=php_sqlite.dll extension=php_pdo_sqlite.dll
Add the following line to the file: extension=php_pdo_informix.dll
Create a new directory under c:\xampp\htdocs to contain the zipped files (For example: c:\xampp\htdocs\openadmin)
Unzip oatids.zip to this new directory.
6. Start the Apache Webserver.
Verify INFORMIXDIR is set to the Informix Connect or CSDK directory.
Run c:\xampp\xampp-control.exe.
Start the Apache Webserver.
7. Install and Configure OpenAdmin.
Go to http://localhost/openadmin/install and follow the installation instructions. After the first install screen a check will be made to ensure you have the correct PDO drivers:
Enter the base URL for your machine, language defaults, and optionallythe Google maps key for your machine URL:
8. Start using OpenAdmin
Once installation is complete you can go to the main page (http://localhost/openadmin in this example), click Admin and start adding IDS 11.10 connections. Important: If you are using a version of CSDK prior to 3.00.TC1, there must be aSQLHOSTS entry on the Webserver machine for each connection you add (use setnet32 to add Informix servers to SQLHOSTS on Windows).
Once a connection is defined you can return to the main screen and connect:
The connection administration allows you to create groups of connections, and assign a password for each group, making it easier to administer a large number of instances.
A new version of OpenAdmin Tool for IDS (formerly known as IDSAdmin) is available to download from the IDS 11.10 Open Beta site (sign-in required). This new version has a simpler installation, new graphics, and many new features.
Please keep in mind that:
The product is still listed as IDSAdmin on the Beta download site.
The Readme file downloadable from the Beta site is (at the time of writing) the out of date one from March - do not use it, instead unzip the oatids package and refer to the Readme in there.
Here is a copy of the current Readme:
OpenAdmin Tool For IDS - v2.10
- July 2007
OpenAdmin Tool For IDS ( OAT ) , is a PHP based administration consolewhich can be used to administer one or more IBM Informix Dyanamic Server11.10 instances.
Dependencies
OAT requires the following products to be installed:
A Webserver (Apache 2.2.3)
IBM I-Connect or CSDK (3.00)
PHP 5 compiled with PDO, PDO_SQLITE, GD and SOAP enabled. (5.2.2)
Informix PDO Module.
Note: the versions in brackets indicate the versions that OAT has been testedwith.
Installation Instructions
1. Install and set up a working web server that has been configured toserve php pages. (For more information see the NOTES section below.)
2. Update the php configuration file.
edit the php.ini
add two new lines to the 'extension' section of the configuration fileif they are not present:
5. Change the ownership of the "<OAT>/install" directoryto the user and group that runs the apache ( httpd ) server.
Find the user and group that runs the apache ( httpd ) server from the httpd.conf.
chown <user>:<group> <OAT>/install ( Unix/Linux )
6. Start the webserver, making sure INFORMIXDIR points to the Client SDKor I-Connect install location in the webserver environment.
7. Launch the OAT installer by visiting the web page:
http://SERVERNAME/LOCATION/install
where: SERVERNAME = the name of your machine LOCATION = where you extracted the tar file (e.g. oat )
Follow the instructions in the OAT installation screens, including obtainingan optional Google Maps API key for your domain if required.
Once installation is complete, point your browser at the OAT root URL.From there you can click on Admin and add a new IDS 11.10 Connection tothe default group or create a new group. If using an I-Connect or ClientSDK version prior to 3.0 then for each new connection you add, there needsto be a corresponding SQLHOSTS entry for that connection on the webservermachine.
Once a connection is created the "Get Servers" link from theOAT root page will retrieve the list of connections you have created.
NOTES
Installing an Apache webserver with PHP is not a straight forward task, thankfully there are 3rd party solutions available. A popular choiceis XAMPP.
Using XAMPP
XAMPP is an easy to install Apache distribution containing MySQL , PHPand Perl. XAMPP can be obtainedfrom, http://www.apachefriends.org/en/xampp.html
The XAMPP version 1.6.2 for Windows contains everything you need to runOAT (with the exception of IBM I-Connect or CSDK ), download and installationinformation is available at http://www.apachefriends.org/en/xampp-windows.html
XAMPP ( also called as LAMPP ) is also available for Linux, however you will need to download the 'Development Package' in order to compile the Informix PDO Driver. Download and installation information is available at http://www.apachefriends.org/en/xampp-linux.html
Informix PDO Driver
The following developerWorks article contains useful information to assistwith building an Informix PDO driver:
In March an article on installing IDSAdmin in a XAMPP environment was published on this blog. This article has just been updated by Inge Halilovic with instructions and screen shots for the latest available version of OpenAdmin Tool downloadable from the Free Product Download Site. The new version of OAT has improved graphics, simpler installation, new features such as support for Mach 11 topology, and several bug fixes.
The Database Admin System is a frameworkthat can simplify many tasks for DBAs,application developers and end users. In addition, these tasks can be seamlessly integrated into a graphical admin system, such as, the OpenAdmin Tool for IDS.
We will examine how a DBA can take advantage of the Database Admin System to solve a real life problem. The problem we are going to exploreis to how to remove users who have been idle for more than a specified length of time, onlyduring work hours. Prior to the database admin system a DBA would utilize several different operating system tools,such as, shell scripting and cron. In addition, if this is pre-packaged system these new scripts and cron entries will have to be integrated intoan installed script. Lastly this needs tobe portable across all supported platforms.
If you are to utilize the database Admin system youonly have to add a few lines to your schema file andyou are done. Since this is only SQL you will havethe advantage of being portable across different flavors of UNIX and Windows.
The components we are going to utilize are:
Database Scheduler
Alert System
User Configurable Thresholds
SQL Admin API
We are going to break the above problem into three separate parts.
Lastly we are going to view screen shots of the completed work in the OpenAdmin Tool for IDS. This will graphically depict all the tasks in the system and allow users to drill down and see the scheduling details and parameters of a specific task.
To create a threshold which can be easily changed we insert a rowinto the ph_threshold table in the sysadmin database. Thistable stores all the threshold or configuration values used by thescheduler. When creating a threshold we must supply the followinginformation:
Column
Description
name
The Parameter name
task_name
The name of the task in the ph_task table associated with this threshold
value
The value associated with the parameter
value_type
The data type of this paramater, (STRING or NUMERIC)
description
A description of what this threshold does.
/* * Insert a new parameter that can be updated to * reflect the current idle timeout period * saving the work of having to re-write the * stored procedure if conditions change. * Allow the OpenAdmin tool to display * this as a configurable item for the task. */INSERT INTO ph_threshold(name,task_name,value,value_type,description)VALUES("IDLE TIMEOUT", "Idle Timeout","60","NUMERIC","Maximum amount of time in minutes for non-informix users to be idle.");
When creating a stored procedure to be called by the schedulerit can optionally take in two parameters which lets the procedureknow when it was invoked and what is this procedure'sunique task id. This is often useful when passing information on to other parts of the Database Admin System, such as, the Alert System.
CREATE FUNCTION idle_timeout( task_id INT, task_seq INT)
Next we need to retrieve the thresholds from the ph_threshold table. This isdone using a simple select and casting the result into our desired data type (an integer).
SELECT value::integer INTO time_allowed FROM ph_threshold WHERE name = "IDLE TIMEOUT"
The main part of our stored procedure is the select statement to findall users who have been idle for more than a specified number of minutes. We select from the systcblst table the last time a thread hasexecuted on a virtual processor. If this time is longer than our predetermined idle threshold and this thread is an sqlexec thread (i.e. not a system thread) then we pass the session id (sid) tothe SQL Admin API call admin(). The admin function has beensetup to call onmode -z to terminate a session.
SELECT admin("onmode","z",A.sid), A.username, A.sid, hostname INTO rc, sys_username, sys_sid, sys_hostname FROM sysmaster:sysrstcb A , sysmaster:systcblst B, sysmaster:sysscblst C WHERE A.tid = B.tid AND C.sid = A.sid AND lower(name) in ("sqlexec") AND CURRENT - DBINFO("utc_to_datetime",last_run_time) > time_allowed UNITS MINUTE AND lower(A.username) NOT IN( "informix", "root")
The last piece of the stored procedure checks the return code of the admin() procedure to see if the session was successfully terminated. If the session was terminated successfully then an alert is inserted, logging the termination of an idle user.The optional arguments to the stored procedures which are used to uniquely identify the task and the task sequence are required by the alert system. This allows the alert system to know who generated this alert and when this alert was generated.Several other items are required by the alert system, such as the alert_type (ERROR, WARNING, INFO) or the alert_color (ERROR, WARNING, INFO) and a message indicating what has happened.
Once an alert is created, theOpenAdmin Tool for IDSwill display it under the Health Center menu option, Show Alerts. If you do not see this alert at first, make sure that you have the ADDRESSED checkbox checked.
Complete Stored Procedure
Below you will find the complete code for the idle_timeout stored procedure.
/* ************************************************************** * Create a function which will find all users that have * been idle for the specified time. Call the SQL admin API to * terminate those users. Create an alert so we can track which * users have been terminated. ************************************************************** */CREATE FUNCTION idle_timeout( task_id INT, task_seq INT)RETURNING INTEGER
{*** Get the maximum amount of time to be idle ***} SELECT value::integer INTO time_allowed FROM ph_threshold WHERE name = "IDLE TIMEOUT";
{*** Find all users who are idle longer than the threshold ***} FOREACH SELECT admin("onmode","z",A.sid), A.username, A.sid, hostname INTO rc, sys_username, sys_sid, sys_hostname FROM sysmaster:sysrstcb A , sysmaster:systcblst B, sysmaster:sysscblst C WHERE A.tid = B.tid AND C.sid = A.sid AND lower(name) in ("sqlexec") AND CURRENT - DBINFO("utc_to_datetime",last_run_time) > time_allowed UNITS MINUTE AND lower(A.username) NOT IN( "informix", "root")
{*** If we sucessfully terminated a user log ***} {*** the information into the alert table ***} IF rc > 0 THEN INSERT INTO ph_alert ( ID, alert_task_id,alert_task_seq, alert_type, alert_color, alert_state, alert_object_type, alert_object_name, alert_message, alert_action ) VALUES ( 0,task_id, task_seq, "INFO", "GREEN", "ADDRESSED", "USER","TIMEOUT", "User "||TRIM(sys_username)||"@"||TRIM(sys_hostname)|| " sid("||sys_sid||")"|| " terminated due to idle timeout.", NULL ); END IF
The last piece is to schedule the idle_timeout stored procedure above to runat regular intervals. This is accomplished by inserting a rowinto the ph_task table in the sysadmin database. While thereare many scheduling options we are going to keep it simple.In this example, the idle_timeout procedure is set to run every day between the hours of 6AM and 6PM at 10 minute intervals. While most of the insert statement below looks straightforward, the one key point tohighlight is the tk_execute column is receiving the name of the storedprocedure to run.
Column
Description
tk_name
The name of the task, must be a unique name
tk_type
The type of task (TASK, SENSOR, STARTUP TASK, STARTUP SENSOR)
tk_group
The name of the group to associate the task with, for organization purposes, see the tk_group table for more details
tk_description
A comment to describe what this task is doing
tk_execute
A function name or SQL statement to execute
tk_start_time
Time of day to start executing this task
tk_stop_time
Time of day to stop executing this task
tk_frequency
How often to execute this task
/* ************************************************************** * Create a task which will schedule the idle_timeout SPL * to be run between 6 AM and 6 PM. ************************************************************** */INSERT INTO ph_task(tk_name,tk_type,tk_group,tk_description,tk_execute,tk_start_time,tk_stop_time,tk_frequency)VALUES("Idle Timeout","TASK","USER","Remove all idle users from the system.","idle_timeout",DATETIME(06:00:00) HOUR TO SECOND,DATETIME(18:00:00) HOUR TO SECOND,INTERVAL ( 10 ) MINUTE TO MINUTE);
Once we have built these three components we can view how the OpenAdmin Tool for IDS will display the tasks. If we look at the current overview of the task, the task we added, Idle Timeout is displayed in the overview.
If you drill down into the details of the Idle Timeout task you willsee a complete list of scheduling details along with any associated parameters. In this example we have one parameter called IDLE TIMEOUT .
OpenAdmin Tool for IDS, the PHP-based administration console for IDS, is now available for downloadfrom the Open Source download site.
Updated 2/14/08 with new recommended PHP and XAMPP versions which are good for Cheetah and Cheetah 2.
Note: This article is based upon the “Installing IDSAdmin in a Windows XAMPP environment” article by Guy Bowerman and Inge Halilovic. The Windows version of this article is available on the IBM IDS Experts Blog.
Installation of OpenAdmin can appear complex - this article provides anexample of one way of installing OpenAdmin. This example uses an XAMPP package on Linux. XAMPP is a bundle of open-source utilities (such as Apache, PHP and PEAR) thatare used by OpenAdmin. Installing XAMPP simplifies the OpenAdmin install,but many other installation and configuration options are possible.
High-level Summary of Install steps Detailed steps are provided below. This is the high-level sequence of stepsyou will follow:
Install CSDK 3.00.UC1 (or later)
Download and extract the XAMPP 1.6.4 runtime package and development package
Download and extract PDO_INFORMIX 1.1.0
Configure PDO_INFORMIX
Update the php configuration file
Download and extract the OpenAdmin package
Start the Apache web server
Install and configure OpenAdmin
1. Install Informix Client SDK
If not already installed, install the Informix Client SDK and set INFORMIXDIR in your environment to be the location where CSDK is installed. (Note: CSDK 3.00.UC1 or later is recommended.)
To install Informix CSDK, extract the product files and run as root installclientsdk. Follow the instructions on screen to install CSDK.
Important: Even if you are installing on a Linux-x86_64 machine, you will need a Linux 32-bit version of Informix CSDK. This is because Linux XAMPP is a 32-bit build; so to build a compatible PDO_INFORMIX module, you will need the Linux 32-bit version of Informix CSDK.
Important: You must download both the Linux runtime and development packages; the development package is necessary in order to be able to compile the PDO_INFORMIX driver.
To install, all you need to do is untar both XAMPP packages in the /opt directory. (If you do not want to install directly in the /opt directory, you can create a symbolic link which links /opt/lampp to the directory you choose for installation.)
tar xvfz xampp-linux-1.6.4.tar.gz –C /opttar xvfz xampp-linux-devel-1.6.4.tar.gz –C /opt
Now XAMPP is installed in the /opt/lampp directory
Untar the PDO_INFORMIX tar file into the /opt/lampp/lib/php/extensions directory.
tar xvfz PDO_INFORMIX-1.1.0.tgz –C /opt/lampp/lib/php/extensions
4. Configure PDO_INFORMIX
Set INFORMIXDIR in your environment to the directory where Informix Client SDK is installed.
Make sure your PATH is set so that the php and phpize executables are picked up from the /opt/lampp/bin directory. If you have another version of PHP installed and your PATH is not set correctly to pick up php from /opt/lampp/bin, you may get errors when executing make for PDO_INFORMIX due to version incompatibility.
Important: If you are installing on a Linux-x86_64 machine, you also need to set CFLAGS in your environment to “-m32”. This is because XAMPP for Linux is a 32-bit build and therefore you will need to compile PDO_INFORMIX into a 32-bit shared object file instead the default 64-bit version. This also means that the version of Informix CSDK must be the Linux 32-bit version.
To configure the PDO_INFORMIX module, execute the following 5 commands:
cd /opt/lampp/lib/php/extensions/PDO_INFORMIX-1.1.0/opt/lampp/bin/phpize./configuremakemake install
Example output for these commands are shown below.
Sample output after the “/opt/lampp/bin/phpize” command:
Sample output after the “./configure” command:
Sample output after the "make” and “make install” commands:
After executing “make install”, verify that pdo_informix.so was copied into your extension directory /opt/lampp/lib/php/extensions/no-debug-non-zts-20060613/
Note: For more information about building the PDO_INFORMIX module, go to the DeveloperWorks article on that topic.
5. Update the php configuration file (php.ini)
Navigate to the /opt/lampp/etc directory, and open the php.ini file in a text editor.
Edit php.ini to enable the Informix PDO driver:
Locate the “Dynamic Extensions” section in the php.ini file and add the pdo_informix extension. extension="pdo_informix.so"
Modify the memory_limit parameter to be at least 256M. Memory_limit = 256M
6. Download and Extract the OpenAdmin tar file in the htdocs directory.
Create a new directory under /opt/lampp/htdocs to contain the OpenAdmin Tool files (For example: /opt/lampp/htdocs/openadmin)
Untar oatids.tar to this new directory.
Within the OpenAdmin directory, grant write permissions to the conf and install directories and all of their contents.
cd /opt/lampp/htdocs/openadminchmod ugo+w install conf -R
7. Start the Apache web server.
Verify INFORMIXDIR is set to the Informix CSDK directory.
Start the web server by running /opt/lampp/lampp start as root.
You should see output similar to the following:
Starting XAMPP for Linux 1.6.4...XAMPP: Starting Apache with SSL (and PHP5)...XAMPP: Starting MySQL...XAMPP: Another FTP daemon is already running.XAMPP for Linux started.
8. Install and Configure OpenAdmin.
Using a web browser, go to http://< machine_name >/openadmin/install where < machine_name > is the name of the machine where you have installed the products. Follow the installation instructions to install OpenAdmin Tool. After the first install screen a check will be made to ensure you have the correct PDO drivers:
Enter the base URL for your machine, language defaults, and optionallythe Google maps key for your machine URL:
8. Start using OpenAdmin
Once installation is complete you can go to the main page (http://< machine_name >/openadmin in this example), click Admin and start adding IDS 11.10 connections. Important: If you are using a version of CSDK prior to 3.00.UC1, there must be aSQLHOSTS entry on the web server machine for each connection you add .
Once a connection is defined you can return to the main screen and connect:
The connection administration allows you to create groups of connections, and assign a password for each group, making it easier to administer a large number of instances.
OpenAdmin Tool for IDS has been greatly enhanced in version 2.20 with a completely redesigned user interface, a new automated installer, and lots of new IDS admin functionality. OpenAdmin Tool for IDS (OAT) is a PHP-based Web browser administration tool for IDS 11 and IDS 11.5 that provides the ability to administer multiple database server instances from a single location. OAT makes administration easy by allowing you to drill down on resource usage and events, view query performance statistics, and much more. And since the tool is written in PHP and available as open source, you can customize it with your own business logic and installation requirements.
New feature highlights of OpenAdmin Tool for IDS version 2.20 include:
Automated Installer: For users who want an easy, hassle-free install, there is a new automated GUI installer that will install and configure Apache, PHP, I-Connect, and OAT. For users who want to use or install their own Apache/PHP configurations, OAT is also released as a stand-alone package.
All-New Look: OAT version 2.20 has a completely new and improved UI
Automated Update Statistics: Configure rules and define a schedule to have IDS automatically maintain your database server's statistics
Onconfig Recommendations: Get recommendations for your onconfig settings that are tuned to your specific database server instance; modify dynamic onconfig parameters directly through OAT
Enhanced Mach11 Support: Including a Connection Manager interface, an SDS Setup Wizard, and the ability to start and stop Mach11 servers remotely
Historical Performance Graphs: View and monitor historical trends of key performance measurements
Task Scheduler Wizard: Use OAT’s new wizard to define new tasks in the scheduler
Read-Only Access: Define groups in OAT that can monitor IDS database servers but not perform administrative tasks
And the list goes on…. Virtual Processor Administration, System Integrity Checks, a Privileges Manager, environment variable support on OAT connections, the ability to kill database sessions remotely through OAT, and more!
The newest version of OpenAdmin Tool for IDS, Version 2.22, is available now! The latest features center on a Enterprise Replication (ER) monitoring and security.
ER Plug-in Version 1.1: Version 1.1 of OAT’s ER plug-in greatly enhances OAT’s Enterprise Replication monitoring capability.
The ER Routing Topology page has been transformed to allow monitoring of all nodes in the ER domain from a single page without having to drill-down on each node. Users can set thresholds for key ER statistics and then use the Routing Topology page to monitor alerts and profile data for each node in their domain. (Requires IDS server version 11.50xC2.)
The ER Node Details pages have been expanded to show errors for the current node or the entire ER domain (Errors tab) and to list current values of the ER configuration parameters (Configuration tab).
Secure SQLToolbox: OAT admins can now choose to turn on an additional level of security for the SQL Toolbox pages. If “Secure SQLToolbox” is turned on, OAT users will have to re-authenticate in order to view schema data or use the SQL Editor. This additional layer of security can be used to ensure that OAT users are not automatically allowed free access to databases or tables as the user informix.
The newest version of OpenAdmin Tool for IDS, Version 2.23, has just been released! Download the new version today to get the enhanced SQL Explorer and version 2.0 of the ER Plugin.
New feature highlights:
The SQL Explorer has been newly redesigned. New features include
Filters and search fields for viewing SQL tracing data.
Support for different SQL trace levels - global or user - so you can manage what kinds of SQL information are traced for IDS V11.50.xC1 servers.
Support for suspending and resuming history tracing, without releasing resources, on IDS V11.50.xC1 servers.
ER Plug-in Version 2.0: Version 2.0 of OAT’s ER plug-in includes a new Replicate Explorer and becomes the first step in supporting ER administration graphically through OAT.
The Replicate Explorer now lets you monitor your replicates and replicate sets.
The Node Details -> Configuration page now supports the editing and updating of ER configuration parameters
It's been five months since the last release of OAT, but hopefully it's been worth the wait. OpenAdmin Tool version 2.24 is now here to make IDS administration even easier!
What's new in OAT 2.24?
Compression
The Storage Optimization feature is new to IDS in 11.50.xC4. And OAT 2.24 provides the graphical interface that makes saving disk space easy.
Compress, uncompress, repack, and shrink operations are all available through OAT. Save disk space by compressing tables and table fragments, consolidate free space in tables and fragments through a repack operation, and return free space to the dbspace through the shrink operation. OAT also helps you to decide which tables or fragments to compress by graphically showing you the estimated the amount of space that compression will save.
SQL Trace for Historical Data
The popular SQL Explorer feature just keeps getting better. In OAT 2.24, the SQL Explorer now allows you to permanently store SQL Trace data in the sysadmin database. Then you can use the SQL Explorer to perform a query drill-down to gather statistical information not only on live SQL statements, but historical SQL as well!
Also new to the SQL Explorer, a Query Optimization Workbench which allows you to create and save external directives to modify a query's behavior.
Query By Example
Query By Example is a new addition to OAT's SQL Toolbox. Query by Example is a easy-to-use CRUD interface that can be used to perform these standard SQL operations on a table: query, insert, update, and delete.
Import/Export Connections
OAT now supports importing and exporting IDS connection information using XML. This feature, available on the OAT administration page, will save you time in setting up OAT as it allows for the ability to export OpenAdmin Tool connection information to an XML file and import it into another instance of OAT.
Enterprise Replication Plug-in version 2.1
The Enterprise Replication plug-in has been building with each release. Version 2.1 of the ER plugin marks a major stride forward - with its support for ER administration and setup. The ER plugin can now be used to do remote graphical setup, administration, and monitoring of Enterprise Replication - on the domain level, on the node level, and on the replication level.
The new ER setup and administration support in OAT includes the ability to:
Define ER servers
Define replicates and replicate sets
Define and realize templates
Start, stop, suspend, and resume replicates and replicate sets
Check replicates and replicate sets for data inconsistencies
Repair data inconsistencies in replicates and replicate sets
Synchronize data to repair inconsistencies in replicates and replicate sets
Repair failed transactions by using Aborted Transaction Spooling (ATS) and Row Information Spooling (RIS) files
For additional information on OpenAdmin Tool for IDS, including feature details, screenshots and demos, go to www.openadmintool.org. To post comments or questions about OAT, use the IIUG OAT forum.
This article will provide tips on the easiest ways to upgrade your OpenAdmin Tool installation.
But first, we must start with describing the two different ways to install OAT:
Automated Installer
The automated OAT installer is provides an automated way to install and configure OAT and all of its prerequisites. The following products are included as part of the automated installer:
OpenAdmin Tool for IDS (and the ER plug-in)
IBM Informix I-Connect 3.50
Apache 2.2.4
PHP 5.2.4
PDO_INFORMIX 1.2.6
What platforms? The automated installer is only available on the following platforms: Windows, Linux, MacOS.
When to use it? The automated installer is really intended for first time installations. It's purpose is to drastically simplify the initial step of installing and configuring OAT's prerequisites. The automated installer is also limited to those who do not require custom web server configurations. And those installing on Windows, Linux, or MacOS platforms.
Manual Installation
The manual installation is when you install and configure your own Apache, PHP, PDO_INFORMIX, and Informix CSDK or I-Connect prior to installing OAT. Under this installation method, OAT comes as a zip or tar file that you can just extract into the webserver's document root directory after all prerequisites have been installed and configured.
What platforms? Any platform for which you can get OAT's prerequisite's installed. Although, for some platforms this can be very tricky.
When to use it? You are installing on a platform other than Windows, Linux, or MacOS. Or you have special web server configuration needs that are not supported by the Apache web server shipped with the automated installer. Or you already have a web server/PHP setup, which is exactly your situation if you are upgrading OAT!
Upgrading OAT
So now that we are clear on the initial installation choices, we can finally get to talking about upgrading OAT.
The Automated Installer only supports full installations. It does not support installing individual components (e.g. OAT only), or upgrading components of the installation. Therefore using the automated installer for upgrading OAT only makes sense if you want a completely new installation of the webserver, PHP, and OAT. (Note: on Windows, this will require completely removing your old installation first.)
Therefore, the recommended method for upgrading OAT is to use the manual install package. This makes sense because you already have the webserver, PHP, PDO_INFORMIX, and I-Connect installed and configured. When upgrading, all you need is the new version of the OAT product -- which is just a set of php files. To upgrade, download the OAT zip or tar file and extract it into your web servers document root directory and then visit the OAT install URL (http://<servername>/<oat_directory>/install) to upgrade.
Now when you choose the manual upgrade of just extracting the new version of OAT into your webserver's document root directory, you have two choices:
Extract over the existing installation of OAT
This method is fast and easy. You just extract the newest OAT tar or zip file over the existing OAT directory, visit the OAT install URL (http://<servername>/<oat_directory>/install) to upgrade your OAT connections database, and you're on your way. The disadvantage is that you lose your previous OAT installtion, including any custom changes you may have made to the files shipped as part of the OAT product.
Extract into a new directory in your webserver's document root (Recommended)
This allows you to preserve your OAT existing installation should you ever want to go back to it. But what about preserving the connection information from your previous OAT installation? Again there are two options:
You can make a copy of your previous connections.db (see the OAT Admin Config page for its location) and point your new installation of OAT to the copy. Then when you visit the the OAT install URL (http://<servername>/<oat_directory>/install) of the new installation, it will automatically upgrade the connections.db to the latest version and preserve all of your connection information. But since the OAT connections.db stores OAT plug-in information in addition to IDS server connection information, when you use this method, you will either have to install the same plug-ins into the new version of OAT or copy the OAT plugin directory from your previous installation into the new version of OAT.
The other option for preserving IDS server connection information when upgrading OAT is to use the new OAT 2.24 import/export connections feature. This allows you to export OAT's IDS connection information into an XML file and then import it into another installation of OAT. Of course, here too, if you want the same plugins as your previous installation of OAT, you'll still need to use the Plug-in Manager to install the plug-ins in the new OAT installation.
For additional information on OpenAdmin Tool for IDS, including feature details, screenshots, demos, and the download link, go to www.openadmintool.org. Any questions about OAT or its installation/upgrade can be posted on the IIUG OAT forum.
Introducing a new plug-in for OAT: theSchema Manager. Use this plug-infor a dashboard-like of view databases and tables on your IDS databaseserver. For databases, see stored procedures, functions, sequences,UDTs, privileges, DataBlades, aggregetes, casts, and opclasses from onesingle view. For tables, views, and synonyms, the Schema Manager showsyou information about its columns, indexes, references, constraints,privileges, UDTs, fragments, and triggers. The Schema Manager alsoprovides you with ability to drill-down on any of its pods for moredetailed information.
A demo of the new Schema Manager funcationality isavailable here. More enhancements to this plug-in will be coming soon, so staytuned!
Enterprise Replication Plug-in version 2.2
The Enterprise Replication plug-in administrationcapability has been expanded to allow the modification ofobjects:
Modify a replicate set by adding or deleting replicates
Modify a replicate set by changing the replication frequency for the replicates in the set
For additional information on OpenAdmin Tool for IDS,including feature details, screenshots and demos, go to www.openadmintool.org. To post comments or questions about OAT, use the IIUG OAT forum.
Brazilian Portuguese, Chinese Simplified, Chinese Traditional, Czech, German, Hungarian, Italian, Japanese, Korean, Polish, Russian, Slovak, and Spanish
Connection Manager Proxy Support
The Connection Manager wizard on OAT's MACH page now allows you to configure the Connection Manager as a proxy server when clients connect to Informix data servers from outside a firewall (new to 11.50.xC6).
Onstat Utility Support
Ever want to run onstat commands remotely? Now you can. OAT 2.27 supports running any onstat command you like directly from your web browser!
Schema Manager Plug-in version 1.2
The Schema Manager plug-in arrives with its first installation of 'admin' actions.
Create tables, including external tables
Load and unload data from external tables (11.50.xC6 only)
Monitor the status of load and unload tasks (11.50.xC6 only)
More schema management and administration actions to come, so stay tuned!
Enterprise Replication Plug-in version 2.4
ER plug-in version 2.4 now contains virtually all administration commands available for IDS Enterprise Replication! Besides rounding out the suite of administration commands supported, the focus on the ER plug-in version 2.4 was usability.
Improve the performance of the consistency check and synchronization tasks, using WHERE clauses and parallelism options.
Change the master replicate server for a replicate.
Instiate a template on multiple ER servers simultaneously.
Delete ER objects including servers, replicates, replicate sets, and templates. Also delete information about completed check and synchronization tasks.
You can use HTTPS to protect the IBM OpenAdmin Tool (OAT) for Informix web server from eavesdropping, tampering, and message forgery. When HTTPS is enabled, messages from OAT clients are encrypted before they are sent to the OAT web server. Encryption prevents hackers from listening over the line and stealing sensitive information. When HTTPS is enabled, OAT clients can also authenticate with the OAT host, so that hackers cannot deceive OAT clients with fake OAT web servers.
OpenAdmin Tool: Use Heat Map Graphs to monitor Extent and Buffer Pool Usage
OpenAdmin Tool (OAT) version 3.11 includes new Heat Map graphs to aid
in the performance analysis of your Informix database server. View
extent and buffer pool usage in a heat map, also known as a treemap graph.
Go to the new Performance Analysis > System Reports > Graphs page
to view graphs of the number of extents in databases or the percentage
of cached pages in the buffer pools. Use the drop-down controls at the
top of the page to choose the type of data to graph (extents or buffer
pool) and to filter by a particular database.
Extents Heat Map
Use the Extents graph to monitor the number of extents across your
databases. Monitoring extents is important because a large number of
extents can impact performance by requiring the database server to spend
extra time finding the data.
Use the size and color cues for each object to interpret the extents
graph. Each rectangle within a database represents an object, for
example a table or an index. The size of the rectangle indicates the
relative size of the object. The color indicates the number of extents
as indicated by the legend for the graph. Hover over any object in the
graph to see the number of extents and the number of total pages used.
If any table or index has a large number of extents, consider increasing the size of the extents to improve performance.
Screenshots 1 and 2 show examples of the Extents Heat Map in OAT.
Screenshot 1: Extents Heat Map for the sysadmin database
Screenshot 2: Extents Heat Map for all databases
Buffer Pool Heat Map
Use the Buffer Pool graph to monitor the cached pages in your buffer
pool. Both the size of the buffer pool and which table and indexes
pages are cached in the buffer pool can have a big impact on the
performance of queries against the database server.
Use the size and color cues for each object to interpret the buffer
pool graph. Each rectangle within a database represents an object, for
example a table or an index. The size of the rectangle indicates the
relative size of the object. The color indicates the percentage of
pages that are cached as indicated by the legend for the graph. Red
indicates a “hot” table or index that has 100% of its pages cached in
the buffer pool, whereas blue indicates a “cold” table or index that has
0% of its pages cached. Hover over any object in the graph to see the
total rows, total pages, buffered pages, and percentage of pages that
are cached for that object.
A higher percentage of cached pages can improve performance. Queries
that are run on tables with a high cache percentage are faster than
queries that are run on tables with a low cache percentage. If a table
has a low percentage of cached pages and queries are slow, consider
increasing the size of the buffer pool.
Screenshots 3 and 4 show examples of the Buffer Pool Heat Map in OAT.
Screenshot 3: Buffer Pool Heat Map for the sysadmin database
Screenshot 4: Buffer Pool Heat Map for all databases
For more information about the OpenAdmin Tool and the new features in version 3.11, go to the OpenAdmin Tool webpage.
Manage access to OAT by managing permissions to the SQL Admin API commands for administering the Informix database server
With Informix version 12.10 and OpenAdmin Tool version 3.11, you can now manage SQL Admin API privileges for individual users. Individual users can be granted privileges to run some or all SQL Admin API commands. Privilege groups identify which SQL Admin API commands a user can run and thereby determine which features the user can access in OAT.
This functionality frees OAT from the requirement that all users must login as the user informix. Once a certain user has been granted access a set of SQL Admin API privileges, that user can log in to OAT using their own user name and monitor and administer the Informix database server as themselves. Security is enhanced when all users log in to OAT using their own user name, instead of everyone sharing the common informix account.
This feature involves two separate steps, which are described in detail in the following sections.
Step 1: Grant SQL Admin API privileges
Step 2: Log in to OAT as your own user name
Step 1: Grant SQL Admin API Privileges
To begin using this feature, you must first grant SQL Admin API privileges to individual users. By default, only the user informix can run SQL Admin API commands and only the user informix can grant and revoke SQL Admin API privileges to other users.
The Server Administration > User Privileges > SQL Admin API Privileges page in OAT allows you to manage SQL Admin API privileges on the database server. Before anyone can log in to OAT using their own user name, the user informix must first log in to OAT and grant SQL Admin API privileges to individual users.
The following screenshot shows the SQL Admin API Privileges page in OAT.
SQL Admin API Privilege Groups
The SQL Admin API commands are broken down into privilege groups which can be used to control which users can run which administration commands on the database server.
There are three global privilege groups:
Operator which gives access to all SQL Admin API commands except the commands for granting and revoking access to the SQL Admin API
Admin which gives access to all SQL Admin API commands including the commands for granting and revoking access to the SQL Admin API
Monitor which gives access to all read-only SQL Admin API commands but excludes access from all commands that make changes to the database server
The remaining privilege groups break down the SQL Admin API commands by functional category. If you desire granular control of which types of commands a certain user has privileges to run, you can use these categories to, for example, grant access for a particular user to only the backup commands. A list of all SQL Admin API privilege groups can be found in Table 1.
Table 1: SQL Admin API Privilege Groups
Privilege Group
Description
Operator
All the SQL Admin API commands except grant and revoke
Admin
All the SQL Admin API commands including grant and revoke
Monitor (Read-only)
All read-only SQL Admin API commands (i.e. only those that do not make changes on the database server).
Backup
Backup and restore commands
Files
General operating system file commands
High availability
High availability replication commands
Onstat
onstat commands
Replication
cdr commands for Enterprise Replication
Storage
Storage and space commands
SQL
SQL commands
SQL Tracing
SQL tracing commands
Warehouse
Informix Warehouse Accelerator stored procedures
Miscellaneous
Miscellaneous commands for a variety of tasks
Grant
Grant and revoke privileges for the SQL Admin API commands
Granting any SQL Admin API privileges automatically grants a user access to the sysadmin database. Users who are granted Monitor privileges will be given read-only access to the sysadmin database. Users who are granted access to any other privilege group will automatically be granted read/write access to the sysadmin database.
The following screenshot shows the OAT pop-up for granting SQL Admin API privileges to a user.
Granting SQL Admin API Privileges Outside of OAT
If you are not an OAT user, but would like to take advantage of the ability to grant other users access to the SQL Admin API, you can use the following new SQL Admin API commands.
To grant privileges
EXECUTE FUNCTION ADMIN('grant admin', <user>, <privilege group>);
To revoke a specific privilege
EXECUTE FUNCTION ADMIN(‘revoke admin', <user>, <privilege group>);
To revoke all privileges
EXECUTE FUNCTION ADMIN(‘revoke admin’, <user>);
Examples:
EXECUTE FUNCTION ADMIN('grant admin', ‘john’, 'operator');
EXECUTE FUNCTION ADMIN('grant admin', 'jane', 'storage');
EXECUTE FUNCTION ADMIN('revoke admin', 'joe', ‘onstat');
Step 2: Log in to OAT as your own user name
Once you have been granted access to the SQL Admin API, you can log into OAT using your own user name and password. Security and access management to OAT and the Informix database server are enhanced when all users use their own user name and password rather than everyone sharing the common informix user.
The Informix database server enforces the permissions to the SQL Admin API commands, not OAT. This means that all OAT pages are visible to all users, but the ability to perform certain actions will be restricted by the database server based on the access level of the currently logged in user. If a user tries to run an action that they do not have the corresponding SQL Admin API privileges for, they will get an error message that they are ‘not authorized to run the command’ as show in the following screenshot.
SQL Admin API commands are logged in the sysdmin:command_history table and can be viewed on the Logs > Admin Command page in OAT. This page can be used to track which users have run which administration commands against the database server.
Monitor multiple Informix database server instances from a single dashboard
OpenAdmin Tool version 3.11 has a new Health Center > Dashboard > Group Summary page that allows you to monitor all of the database servers in your OAT group simultaneously from a single page.
This page saves you time if you have multiple Informix instances. No longer do you have to log in to each server individually. No longer do you have to click through multiple pages in OAT to check on everything from alerts, space, memory, sessions, and backups. Now all of this information is presented in a user-friendly way from a single screen.
For each server in your OAT group, you are presented with high-level summary information about the server status and activity. This information includes:
Server status (online or offline)
Number of alerts
Number of recent errors in the online activity log
CPU usage
Memory status
Space usage
I/O activity
Backup status
Number of current sessions
If you click on any of the cells on the Group Summary page, you will get a pop-up with more details as well as a drill-down link to where you can go in OAT to get further information or act on any issues identified. The following screenshots provide some examples of the pop-ups.
Status Thresholds
The Memory, Space, I/O, and Backups columns use red, yellow, or green icons to indicate status. These are determined by configurable thresholds.
The status thresholds for memory, space, and the chunk percentage of total I/O are configurable in the IBM® Informix Health Advisor Plug-in for OAT. To change the dashboard status thresholds, you change the thresholds for the associated alarm in the Default profile on the Health Center > Health Advisor > Alarms page. Use these steps to change a threshold in the Health Advisor:
Use the following table to find the associated Health Advisor alarm name
Dashboard status column
Health Advisor plug-in alarm name
Memory
OS Free Memory
Space
Dbspace Free Space
I/O: Chunk percentage of total I/O
Chunk I/O Operations
Go to the Heath Center > Health Advisor page in OAT.
On the Profile tab, ensure that the Default profile is loaded.
Go the Alarms tab, find the associated alarm name, and click the Modify Thresholds link.
Edit the thresholds and click Save.
The status thresholds for backups are determined by the threshold in the sysadmin:ph_threshold table that indicates the maximum number of days between level-0 backups (REQUIRED LEVEL 0 BACKUP). To configure this value, go to the Task Scheduler > Scheduler page and click on the task named "check_backup". On the Task Details page, click the Edit Parameter button, edit the value, and click Save.
Data Refresh Rate
The server status information on the Group Summary page is refreshed based on the Refresh rate that is set by the slider at the top of the Dashboard page. However, to improve performance, the page is refreshed with data that is cached locally in the OAT connections database. The refresh interval for the cached data is set from the Admin > OAT Config page (the default value is 5 minutes). Every time the Group Summary page refreshes, it pulls data from the OAT cache. If the data is the cache is older than the refresh interval on the Admin > OAT Config page, OAT will query the database server to refresh the data in the cache. Otherwise, it will just display what is in the cache. The time that this data was last retrieved from the database server is shown in the Last Updated column. You can bypass the cache and refresh the data directly from a specific server by clicking the Refresh icon for the server.
For more information about the OpenAdmin Tool and the new features in version 3.11, go to the OpenAdmin Tool webpage.
This article provides step-by-step instructions for manually compiling Apache and PHP on Linux for OpenAdmin Tool for Informix.
Note that there is an automated installation option for OpenAdmin Tool (OAT) that installs and configures Apache and PHP for you. To use the automated installation, install OAT as part of the IBM Informix Client SDK installer or the IBM Informix bundle installer. OAT is available as part of the Client SDK and bundle installers for Linux 32, Linux 64, Windows 32, and Mac OS 64 for Informix version 11.70 and higher.
By contrast, this article is intended for advanced users who would prefer to compile their own Apache and PHP on Linux.
In this article, I will be using Apache HTTP Server version 2.4.2, Apache Portable Runtime version 1.4.6, Apache Portable Runtime Utility version 1.4.1, PHP version 5.4.4, and PDO_INFORMIX version 1.2.7. These are the versions that are currently shipped with the OAT that is part of the automated installer. Feel free to try with more recent versions of these software components if you like.
1. Install the Informix Client SDK
The Informix Client SDK is required to run OAT. It will also be required to compile the PDO_INFORMIX driver with PHP in step 3.
(a) Install the Informix Client SDK
(b) Set the INFORMIXDIR environment variable to point the directory where the Client SDK was installed.
(b) Download the UNIX source for the Apache Portable Runtime (APR) and Apache Portable Runtime Utility (APR-util) from https://apr.apache.org/
(c) Extract the Apache HTTP Server source code.
tar xvfz httpd-2.4.2.tar.gz
(d) Extract the APR and APR-util packages into the Apache srclib directory. Rename the directories to apr and apr-util respectively.
cd httpd-2.4.2/srclib/
tar xvfz apr-1.4.6.tar.gz
mv apr-1.4.6/ apr
tar xvfz apr-util-1.4.1.tar.gz
mv apr-util-1.4.1/ apr-util cd ..
(e) Set the APACHE_PREFIX environment variable to the directory where you want the Apache HTTP Sever to be installed. For example, /work/oat/apache_2.4.2
(f) Run the following commands to build and install Apache HTTP Server.
./configure --prefix=$APACHE_PREFIX --enable-so --without-pgsql --with-included-apr
make clean
make make install
Note #1: to build Apache HTTP Server with the mod_ssl module which is required for HTTPS, add "--enable-ssl --with-ssl=/usr/bin" as an additional argument to the configure command above. You will need OpenSSL installed to build the mod_ssl module. If OpenSSL is installed to a different directory from /usr/bin, substitute the appropriate directory name.
Note #2: If libpcre is not installed on your system, you will also have to compile PCRE and add "--with-pcre=/opt/pcre" as an additional argument to the Apache configure command.
(g) Edit the Apache httpd.conf file in the $APACHE_PREFIX/conf directory.
Uncomment the line with the ServerName directive and specify the host name and port number for your web server. For example: ServerName myhostname.ibm.com:8080
Locate the Listen directive and ensure that it is set to the same port number you specified in the ServerName directive.
Change the DirectoryIndex directive to DirectoryIndex index.html index.php
Change all instances of AllowOverride none to AllowOverride All
(h) Edit the $APACHE_PREFIX/bin/envvars file to include the following lines, where everything in angle brackets (<< >>) is replaced by the actual path.
./configure --with-pdo-informix=$INFORMIXDIR --with-php-config=$PHP_PREFIX/bin/php-config
make clean
make
mkdir $PHP_PREFIX/lib/php/extensions
cp ./modules/pdo_informix.so $PHP_PREFIX/lib/php/extensions/pdo_informix.so cd ../..
Note: If libxml2 is not installed on your system, you will also have to compile libxml2 (e.g. version 2.8.0) and add "--with-libxml-dir=/opt/libxml2" as an additional argument to the PHP configure command.
(g) Copy the php.ini configuration file and the libphp5 module to your PHP installation directory
cp php.ini-production $PHP_PREFIX/lib/php.ini
cp libs/libphp5.so $PHP_PREFIX/libphp5.so
(h) Edit the php.ini configuration file
Set doc_root to the htdocs directory of your Apache installation ($APACHE_PREFIX/htdocs).
Set extension_dir to the lib/php/exensions directory of your PHP installation ($PHP_PREFIX/lib/php/extensions)
Add the line "extension=pdo_informix.so" to load the PDO_INFORMIX driver
Set max_execution_time to the number of seconds you want the web server to wait before timing out and set memory_limit to at least 256 M
(i) Edit the Apache httpd.conf file ($APACHE_PREFIX/conf/httpd.conf)
Comment the existing LoadModule php5_module modules/libphp5.so directive
Add the following lines, where everything in angle brackets (<< >>) is replaced by the actual path:
4. Extract and install the OpenAdmin Tool Community Edition
(a) Download the OpenAdmin Tool Community Edition from the IBM Informix free product downloads page. The OAT Community Edition is available as a zip or tar file.
(b) Create an openadmin directory under the Apache htdocs directory ($APACHE_PREFIX/htdocs) and extract the OAT Community Edition zip or tar file.
(c) Use your web browser to navigate to the URL http://<ServerName>:<PortNumber>/openadmin/install/index.php .
(d) Follow the on-screen instructions to install OpenAdmin Tool, which involves reading and accepting the license term, verifying your configuration, building the connections database, and installing plug-ins.
(e) The Adobe Flash Player is required to use OAT after it is installed, so make sure to install the Flash Player plug-in into your web browser if you do not already have it. For example, to install the Flash Player plugin in Firefox on Linux, untar the Flash Player into the Firefox plugin directory (e.g. /usr/lib/mozilla/plugins/).
95
