IDS Experts

For a complete list of current SQL reserved words, see the IBM Informix Guide to SQL: Syntax.

Sanjit Chakraborty[Read More]

New Shared Memory Segment

cheetahblog 1,115 Visits

A new shared memory segment has been introduced in the Informix version 12.10. The 12.10.xC2 onwards the bufferpool has been moved out of the resident segment and new bufferpool segment has been added  which shown as class 'B' in the 'onstat -g seg' output. The BUFFERPOOL configuration parameter determines the amount of buffers allocated to this new segment when the database server is started.


IBM Informix Dynamic Server Version 12.10.UC2DE -- On-Line -- Up 00:20:35 -- 85899522520 Kbytes

Segment Summary:
id         key        addr     size       ovhd     class blkused  blkfree
32768      52564801   44000000 3424256    243124   R     836      0       
65537      52564802   44344000 67108864   394328   V     6776     9608    
98306      52564803   48344000 110305280  1        B     26930    0       
Total:     -          -        180838400  -        -     34542    9608    

-Rashmi Chawak

Modified by cheetahblog

New Storage Manager

cheetahblog 1,729 Visits

Informix introduced the PSM (Primary Storage Manger); a new backup storage manager in version 12.10 that replaced the previous ISM (Informix Storage Manager). The PSM manages storage devices used for backup and restore requests that are issued by Onbar. This storage manager supports both serial and parallel processing for backup and restore requests. PSM is easier to set up and use, even in embedded environments. You just need to perform four simple steps to configure PSM and take an Onbar backup:


      1) Decide where you will create the PSM catalog. The default location is $INFORMIXDIR/etc/psm.

       2) Add devices for DBSPOOL and LOGPOOL

            - onpsm -D add   /backups/psm_spaces -g DBSPOOL -p HIGH -t FILE

            - onpsm -D add   /backups/psm_logs -g LOGPOOL -p HIGH -t FILE

       3) Onconfig settings - you can keep the default values

            - Set BAR_BSALIB_PATH to full path to

            - Set PSM_CATALOG_PATH to full path to PSM catalog directory

            - Set PSM_DBS_POOL   DBSPOOL

            - Set PSM_LOG_POOL   LOGPOOL

        4) Take an onbar backup


You use the Informix onpsm utility to manage storage for Onbar backup and restore operations, including parallel backups, that use file devices (disks). The onsmsync utility provides new commands that you can use to export backups to, and import them from, Informix PSM external device pools.


-Sanjit Chakraborty

Modified by cheetahblog

Non-blocking Checkpoints - Part 1

cheetahblog Tags:  non-blocking interval checkpoint 1,425 Visits

Prior to IDS Version 9, IDS supported a blocking checkpoint algorithmthat causes the system to block all transactional updates while thebufferpool is flushed to disk. In IDS Version 9 onwards, an additionalcheckpoint algorithm, called Fuzzy Checkpoint, was introduced. FuzzyCheckpoints limit the number of updates required to be flushed to diskduring checkpoint processing.

With the new non-blocking checkpoint algorithm in Cheetah, IDS ensuresthat we virtually don't block transactional updates while the entirebufferpool is flushed to disk. Transactionalupdates are blocked only for very small duration of time required to flush thepartition partitions (tablespace tablespaces) to the bufferpool and tograb the restart point. (Restart point is wherefastrecovery starts. We log the checkpoint and use that LSN as the restartpoint for the checkpoint.)

As part of this feature, the new "onstat-g ckp" command helps DBAs to view and analyze checkpointinformation and performance advisories. The "onstat -g ckp" command isexplained in more detail in thispost.

ScottLashley's whitepaper on developerWorks explains the internalsabout non-blocking checkpoints in detail.

In Part 2, we shall learn how to tune checkpoints and modify the relevant onconfig parameters (RTO_SERVER_RESTART, AUTO_CKPTS,AUTO_LRU_TUNING, etc.)

Mirav Kapadia[Read More]

OAT Group Summary Dashboard

cheetahblog Tags:  openadmin dashboard oat informix 7,250 Visits

OAT Group Summary Dashboard

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.



imageimage   image

 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:

  1. Use the following table to find the associated Health Advisor alarm name
  2. 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
  3. Go to the Heath Center > Health Advisor page in OAT.
  4. On the Profile tab, ensure that the Default profile is loaded.
  5. Go the Alarms tab, find the associated alarm name, and click the Modify Thresholds link.
  6. 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.

- Erika Von Bargen

OAT Version 2.23 Available Now!

cheetahblog Tags:  openadmin oat 1,855 Visits

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.
  • Screenshot of the Replicate Explorer

  • The Node Details -> Configuration page now supports the editing and updating of ER configuration parameters

Download OAT Version 2.23 now at

For additional information on OpenAdmin Tool for IDS, including feature details, screenshots and demos, go to

Erika Von Bargen

[Read More]

ON-Bar whole system parallelize backup/restore

cheetahblog 1 Comment 1,582 Visits

Prior to Cheetah,  a whole-system backup (onbar -b-w)  was serial backup of all storage spaces andlogical logs based on a single checkpoint. That time is stored with thebackup information. Theadvantage of using a whole-system backup was  that you canrestore the storage spaces with or without the logical logs.Because the data in all storage spaces is consistent in a whole-systembackup, you do not need to restore the logical logs to make the dataconsistent. Level 0, 1, or 2 backups are supported.

One of the new features of Cheetah is Backup/Restore dbspacesin parallel with whole system functionality .  With parallelbackup , multiple processes run simultaneously  each processbacking up a different dbspace . In most cases, parallel backups complete fasterthan serialbackups, which use only one process.

ONCONFIG parameter  "BAR_MAX_BACKUP" isused for whole system backups/restores as well. TheBAR_MAX_BACKUP parameter specifies the maximum number of parallelprocesses that are allowed for each onbar command. Both UNIX andWindows support parallel backups. Although the database server defaultvalue for BAR_MAX_BACKUP is 4, the onconfig.std value is 0.

To specify parallel backups and restores, including parallel wholesystem backups and restores, set BAR_MAX_BACKUP to a value higher than1. For example, if you set BAR_MAX_BACKUP to 3 and execute anON–Bar command, ON–Bar will spawn the maximum 3 processes concurrently.

If  BAR_MAX_BACKUP to 0, the system creates as manyON–Bar processes as needed. The number of ON–Barprocesses is limited only by the number of storage spaces or the amountof memory available to the database server, whichever is less.

To perform a serial backup or restore, including a serialwhole system backup or restore, set BAR_MAX_BACKUP to 1. 

Also in Cheetah,  storage manager efficiency isimproved  by improvingdbspace ordering .  "Biggest" dbspaces are backed up first and the "smallest"last.Dbspace with most pages used is the "biggest" and will be backed upfirst. This ensures better parallelism, no matter how BAR_MAX_BACKUP isset or how many pages are to be backed up in different dbspaces. Neworder of backup/restore is also effective for non-wholesystembackup/restore. Restore is done in the same order as backup to ease storagemanager's  access to stored objects.

To implement new ordering of dbspaces for backup and restore changes are made to the bar_instance table in the sysutils Database .
New column "ins_backup_order" (integer default 0 not null) is added to the table bar_instance .
Also changes are made to ixbar file :
New field with backup sequence integer (Last field in the line ) is added in ixbar file.
Value of the new field is always "0" (zero) for log backup objects.

Even if the BAR_MAX_BACKUP is set to 1 for serial backup,the dbspaces will be backed up using the new dbspace order .

Backup Operation
Only 1 checkpoint for all dbspaces - just before backup of rootdbs .
Rootdbs is backed up first, without parallelism (no change here).
Before image processor threads "arcbackup2" are started at this time, one for each dbspace  (more threads running in parallel.)
As each dbspace backup completes, the respective "arcbackup2" thread exits ( less "arcbackup2" threads as backup progresses. )
New order of backup. The order is based on the used-pages count at the start time of backup. Dbspace with most pages used is the "biggest" and will be backed up first.

Restore Operation
Not much changed, only it is now parallel.
Rootdbs is restored first, without parallelism (no change here).
Restore dbspaces in same order as they were backed up. Use the value of "ins_backup_order" to determine correct order. If BAR_MAX_BACKUP is changed between backup and restore, the objects will still be restored in the same order, however the timing relative to each other may differ significantly. This could have negative effects on SM performance .

Rashmi Chawak[Read More]

Onbar backup order on Cheetah

cheetahblog 3 Comments 1,817 Visits

Onbar backup order on Cheetah

1> Root dbsapce , SBLOBspace, BLOB space, and normal dbspace onbar backup order.
Rootdbspace is always first, then SBLOB space, BLOB space, normal dbspace is the last one.
For example:
Onstat –d output as following:

/home/informix/cheetah:onstat -d

IBM Informix Dynamic Server Version 11.10.UC1 -- On-Line -- Up 00:19:47 -- 44672 Kbytes

address number flags fchunk nchunks pgsize flags owner name
44cdf7f0 1 0x60001 1 1 2048 N B informix rootdbs
45ca6ea8 2 0x40001 2 1 2048 N B informix ddbs1
45c26ea8 3 0x40001 3 1 2048 N B informix ddbs2
45ca7a00 4 0x48001 4 1 2048 N SB informix sbdbs
45bbeeb0 5 0x60011 5 1 8192 N BB informix bdbs
5 active, 2047 maximum

Note: For BLOB chunks, the number of free pages shown is out of date.
Run 'onstat -d update' for current stats.

address chunk/dbs offset size free bpages flags pathname
44cdf950 1 1 0 50000 15421 PO-B /home/informix/cheetah/dsk/rootdbs
45c98e38 2 2 0 5000 4947 PO-B ./dsk/ddbs1
45ca73b0 3 3 0 5000 3449 PO-B ./dsk/ddbs2
45ca7b60 4 4 0 5000 4587 4587 POSB ./dsk/sbdbs
Metadata 360 268 360
45bb0e48 5 5 0 5000 ~1250 1250 POBB ./dsk/bdbs
5 active, 32766 maximum

After onbar –b –w ,and the backup order can be showed in the bar_act.log infor as following:

2007-06-15 04:41:10 5218 5216 /home/informix/cheetah/bin/onbar_d -b -w
2007-06-15 04:41:10 5218 5216 Archive started on rootdbs, sbdbs, bdbs, ddbs2, ddbs1 (Requested Level 0).
2007-06-15 04:41:11 5218 5216 Begin level 0 backup rootdbs.
2007-06-15 04:41:11 5218 5216 Successfully connected to Storage Manager.
2007-06-15 04:41:11 5218 5216 Completed level 0 backup rootdbs (Storage Manager copy ID: 751421824 0).
2007-06-15 04:41:12 5223 5218 Process 5223 5218 successfully forked.
2007-06-15 04:41:12 5223 5218 Begin level 0 backup sbdbs.
2007-06-15 04:41:12 5223 5218 Successfully connected to Storage Manager.
2007-06-15 04:41:12 5224 5218 Process 5224 5218 successfully forked.
2007-06-15 04:41:12 5224 5218 Begin level 0 backup bdbs.
2007-06-15 04:41:12 5224 5218 Successfully connected to Storage Manager.
2007-06-15 04:41:12 5225 5218 Process 5225 5218 successfully forked.
2007-06-15 04:41:12 5225 5218 Begin level 0 backup ddbs2.
2007-06-15 04:41:12 5226 5218 Process 5226 5218 successfully forked.
2007-06-15 04:41:12 5226 5218 Begin level 0 backup ddbs1.

2> Normal dbspace backup order
The onbar –b –w backup on Normal dbsapces doesn’t depend on the order which the dbspace is created but depend on the size is used on that dbsapce.
In the above example, the ddbs2’s usage is bigger than the ddbs1’s usage, the onbar backup’s order is the ddbs2 first and then the ddbs1 without following the create order.

[Read More]

onstat -g pqs

cheetahblog Tags:  diagnostic sql analysis 1 Comment 2,999 Visits

In version 10.00 IDS made available a new onstat command. The onstatcommand was onstat -g pqs. For those familiar with the old X-based toolcalled xtree, onstat -g pqs allows you to dynamically track a session'squery plan/tree. The syntax is as follows:

onstat -g pqs {0||<session id>}

The 0 will actually attempt to grab the current step of each query planfor all sessions.

Two considerations

  1. onstat -g pqs only shows all phases that have currentlyprocessed, plus the current phase.
  2. onstat -g pqs only shows active queries, once a query iscomplete the plan is gone.

Below is a sample based on the following query.

SELECT c.customer_num, fname,lname, company, o.order_num
FROM customer c, orders o, items i
WHERE c.customer_num= o.customer_num
AND o.order_num = i.order_num
GROUP BY c.customer_num, o.order_num, fname, lname, company
ORDER BY c.customer_num, lname, fname, company, order_num

Onstat -g pqs output

As you can see an additional drawback is that the tables themselves are not named, but it does clearly show you the query tree as it was executed.

Mark Jamison[Read More]

OpenAdmin Tool for IDS - new version

cheetahblog Tags:  oat openadmin idsadmin 2,631 Visits

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.


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:


  • modify the parameter memory_total to 256M.

3. Install IBM I-Connect or Client SDK.

4. Install the OpenAdmin Tool For IDS package.
  • Extract the OAT package into your web server document root directory.
  • Examples:
    /usr/local/apache2/htdocs/oat (UNIX/Linux)

    c:\xampp\htdocs\oat (Windows)
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:

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.


Installing an Apache webserver with PHP is not a straight forward task, thankfully there are 3rd party solutions available. A popular choiceis XAMPP.


XAMPP is an easy to install Apache distribution containing MySQL , PHPand Perl. XAMPP can be obtainedfrom,

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

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

Informix PDO Driver

The following developerWorks article contains useful information to assistwith building an Informix PDO driver:

Guy Bowerman[Read More]

OpenAdmin Tool for IDS XAMPP tutorial updated

cheetahblog Tags:  idsadmin oat 1,551 Visits

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.

Guy Bowerman[Read More]

OpenAdmin Tool Security - HTTPS and ONSOCSSL

cheetahblog Tags:  https onsocssl encryption openadmin oat security 4,925 Visits

The two major ways to secure OAT's network communication is with HTTPS and ONSOCSSL.


  • Encrypts communication between web browsers and OAT's webserver.
  • Uses digital certificates to certify identity of OAT's webserver.
  • OAT webmasters can setup OAT's webserver, so that only trusted users can access OAT
Click here for detailed steps on how to setup HTTPS on OAT

  • Encrypts communication between OAT's webserver and IDS database.
  • Uses IBM Global Security Kit (IBM GSKit is bundled with IDS server)
  • onsocssl is supported on all informix platforms, excluding MacOS.
Click here for detailed steps on how to setup ONSOCSSL on OAT

Leo Chan Wai Hon

[Read More]

OpenAdmin Tool Version 2.24 Available Now!

cheetahblog Tags:  openadmin oat 1 Comment 4,169 Visits


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


Download OAT Version 2.24 now at

For additional information on OpenAdmin Tool for IDS, including feature details, screenshots and demos, go to  To post comments or questions about OAT, use the IIUG OAT forum.

Erika Von Bargen

[Read More]

OpenAdmin Tool Version 2.25 Available Now!

cheetahblog Tags:  oat openadmin 1 Comment 5,351 Visits

What's new in OAT 2.25?

  • Schema Manager Plug-in version 1.0
  • 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

    Download OAT Version 2.25 now at And starting following OAT on Twitter:!

    For additional information on OpenAdmin Tool for IDS,including feature details, screenshots and demos, go to To post comments or questions about OAT, use the IIUG OAT forum.

    Erika Von Bargen

[Read More]

OpenAdmin Tool Version 2.27 Has Arrived!

cheetahblog Tags:  openadmin oat 5,021 Visits

OpenAdmin Tool Version 2.27 Has Arrived!

What's new in OAT 2.27?

  • UI Translations in 13 languages
  • 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)
    • Drop tables
    • Truncate tables


    A demo of the new create table and load from external table functionality is available at

    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.

    Download OAT Version 2.27 today, for free, at

    More information on OpenAdmin Tool for IDS, including demos, can be found at

    To post comments or questions about OAT, use the IIUG OAT forum. Feedback is always welcome!

    Erika Von Bargen

OpenAdmin Tool XAMPP Installation on Linux

cheetahblog Tags:  idsadmin oat linux 3 Comments 8,738 Visits

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:

  1. Install CSDK 3.00.UC1 (or later)
  2. Download and extract the XAMPP 1.6.4 runtime package and development package
  3. Download and extract PDO_INFORMIX 1.1.0
  4. Configure PDO_INFORMIX
  5. Update the php configuration file
  6. Download and extract the OpenAdmin package
  7. Start the Apache web server
  8. 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.

2. Download XAMPP and extract files.

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

3. Download and extract PDO_INFORMIX

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

    This illustration is a screen capture after executing the /opt/lampp/bin/phpize command.

    Sample output after the “./configure” command:

    This illustration is a screen capture after executing the ./configure command.

    Sample output after the "make” and “make install” commands:

    This illustration is a screen capture after executing the make commands.

  • After executing “make install”, verify that 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:

  1. Locate the “Dynamic Extensions” section in the php.ini file and add the pdo_informix extension.

  2. 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.

  1. Download the OpenAdmin tar file from the Open Source download site to a temporary location.
  2. Create a new directory under /opt/lampp/htdocs to contain the OpenAdmin Tool files (For example: /opt/lampp/htdocs/openadmin)
  3. Untar oatids.tar to this new directory.
  4. 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.

  1. Verify INFORMIXDIR is set to the Informix CSDK directory.
  2. 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:

This image is a screen capture of a web browser displaying the OpenAdmin Tool installation screen. It shows that PDO, pdo_informix, and pdo_sqlite PHP modules have been found and are ready for installation.

Enter the base URL for your machine, language defaults, and optionallythe Google maps key for your machine URL:

This image is a screen capture of a web browser showing the OpenAdmin Tool configuration parameters, including the default language to use, the directory for connections database, the BASEURL for links, where everything is installed, the number of seconds to pausewith a redirect, the Google map key, and how often to check server status (in seconds).

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 .

This image is a screen capture of the OpenAdmin tool login screen. The user is required to fill out the following fields: Informix Server, Host Name, Port, Username, and Password.

Once a connection is defined you can return to the main screen and connect:

This image is a screen capture of the main OpenAdmin Tool after a successful login.

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.

Erika Von Bargen[Read More]

OpenAdmin Tool: Use Heat Map Graphs to monitor Extent and Buffer Pool Usage

cheetahblog Tags:  heat pool informix extent graph oat map buffer openadmin 4,304 Visits

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.


- Erika Von Bargen