Informix 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 4,287 Views

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 on by cheetahblog

New Storage Manager

cheetahblog 5,035 Views

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 on by cheetahblog

Non-blocking Checkpoints - Part 1

cheetahblog Tags:  non-blocking interval checkpoint 4,378 Views
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 13,828 Views

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 4,095 Views

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 3,608 Views

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]

On-Disk Encryption available in 12.10.xC8

Pradeep Natarajan 2,599 Views

Hear Ye! Hear Ye!Have you heard? IBM Informix 12.10.xC8 is publicly available for download! The most significant update to the product in this release includes on-disk encryption, also known as Encryption At Rest (EAR). The EAR feature allows you to encrypt the data at the lowest level, on the disk. Now you can rest easy, knowing that your data is encrypted and secure in the event of a physical disk loss or theft. In a world where low cost computers are physically accessible, increasingly used for temporary storage, aggregation, and analytics of data, it is vital to keep the data secure. You can start encrypting your storage spaces by upgrading to 12.10.xC8.

Here is a nice blog written by our architect@Shawn Moe (smoe) to help you get started. ​For complete information on this feature, please refer to our documentation page

Onbar backup order on Cheetah

cheetahblog 3 Comments 5,070 Views

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]

One Listener, Three Ways to Connect your Applications to Informix

Erika Von Bargen Tags:  nosql rest informix listener mqtt 3,127 Views

Hopefully, you've already heard about the Informix wire listener. But did you know that there are actually three different types of listeners? This means that the listener provides application developers with three completely different paradigms for connecting their applications to the Informix database server.


Suppose you are rapidly developing a new application and crave the flexibility and power that the schemaless "NoSQL" data model will provide you to adapt to use cases going forward that you cannot necessarily foresee at the current moment. Or maybe you want a quick and easy way to get data into and out of Informix for a web application developed with the newest and hottest web framework, and you want to do so in a way that doesn't make you think or worry about whether the underlying data is in a relational, time series, or JSON schema. Or maybe you are even developing an Internet of Things applications for a wide array of sensors that will be gathering data that needs to be pushed to your database in a way that is simple, light weight, and asynchronous. The Informix wire listener can help you build such solutions, and many more! And it allows you to build such applications all the while harnessing the performance, scalability, and reliability in the enterprise-class, embeddable database that is Informix.


So what are these three listener types?

  • The Mongo listener

The Mongo listener was the first and original listener type and allowed applications developed for MongoDB to run against Informix without modification. This enabled an entirely different type of application -- flexible, schemaless, and in line with a rapid application development paradigm -- to be run against the Informix database server. It also enabled a whole new ecosystem of modern and open source drivers, those developed for the MongoDB protocol, to be used to connect applications to Informix.

But the original Mongo listener type was about more than just Mongo compatibility. It enabled a new "hybrid" application model -- one that combined traditional relational data with the new NoSQL, JSON-based data. The listener was developed to seamlessly work with any type of data that Informix supports, be it relational, JSON, or time series.

Want to learn more about using Mongo drivers to connect to Informix? Check out a great tutorial written by Martin Fuerderer and hosted on the Informix and NoSQL blog. This tutorial takes you step by step through understanding the listener, NoSQL data, and how to use Mongo syntax and tools to connect to Informix.

  • The REST listener

REST. So ubiquitous in the world of web applications. This was the next evolution of the Informix wire listener. The listener's REST interface allows any application to get data into and out of Informix using HTTP. A simple interface that opened up Informix connectivity to just about every modern programming language without the need for an Informix-specific driver.

Want to learn about using REST to communicate with Informix? Head on over to Martin Fuerderer's article First Steps with the REST API and then check out the REST API syntax page in the Informix Knowledge Center to get more details and examples of the REST syntax for accessing and querying data.

  • The MQTT listener

New to 12.10.xC7, the MQTT listener type enables you to use the MQTT publish model to insert data into the Informix database server. This is perfect for Internet of Things scenarios where data collected on the "edge" needs to be published and stored in a central data store in a light weight and asynchronous way.

Want to learn more about using MQTT to publish data to Informix? Check out  Working with the new 12.10.xC7 MQTT Listener over on Shawn Moe's The Lenexa View blog for an in-depth discussion of the MQTT listener, including some sample Java code.


And last but not least, it is worth reiterating that all three listener types work seamlessly on relational, JSON, timeseries data, or even a combination. No matter the underlying way in which the data is stored, the Informix wire listener will manage those details automatically for you, allowing you as the application developer to focus on your application instead of the nuances of data access.

Want to get started, but need more info on how to start different or multiple listener types? You can find that here in the IBM Informix Knowledge Center.

Modified on by Erika Von Bargen

onstat -g pqs

cheetahblog Tags:  diagnostic sql analysis 1 Comment 6,998 Views
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 5,195 Views
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 3,708 Views
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 oat openadmin security 7,483 Views

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 7,564 Views


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 9,059 Views

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 7,844 Views

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:  oat idsadmin linux 3 Comments 15,141 Views

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]