Category Archives: informix

informix: drop if table exists

In an SQL script, the below 2 scenarios will through an error:

(1) trying to drop a temp table that doesn’t exist

(2) Creating a temp table that already exists.

The below IF EXISTS feature helps to solve this problem:





How to remove shared memory segments in IBM Informix?

The below link explains the steps:


Informix/IDS: dbschema utility

You can use dbschema utility to get the schema of a database and redirect the dbschema output to a file.

To get the complete syntax of dbschema just type “dbschema” from command prompt and press enter.

Example: dbschema –d new_testdb new_testdb.sql

The above dbschema command will generate the schema into new_testdb.sql file.

Example: To get a specific table schema
dbschema -t table99 -s all -p all -r all -f all -d db_name -ss table99.sql

To get schema for whole database:
dbschema -t all -s all -p all -r all -f all -d db_name -ss db_name.sql

Informix/IDS: Checkpoints

A checkpoint is a point in time in the log when a known and consistent state for the database system is established.

A checkpoint can occur in one of these situations:
• When a DBSPACE is added. It’s a blocking checkpoint
• When resource limitations occur. For example, when physical log is 75% full
• Automatic checkpoints (AUTO_CKPTS)

Please note that the fast recovery depends on CKPTINTVL & RTO_SERVER_RESTART parameters.

Informix/IDS: Miscellaneous command options

The below chart will be very handy :

Command Description
oncheck -ci Check indexes for specified database/table
oncheck -pe Print detailed extents information
oncheck -pt Print information for specified table
onstat – Print version, status, uptime and memory usage
onstat -u Print user threads
onstat -d Print dbspaces and chunks
onstat -g sql ‘session #’ Print sql information for session #
onstat -l display logical log status
onmode -l move to next logical log
onparams -p Physical log dbspace changes
onstat -c Display ONCONFIG file
onstat -k print locks
onstat -m pring message log
onmode -z “session #” Kill session
oncheck -pr Pring page information
onstat -p Print profile information
onparams -a

onparams -a -i

Add logical log
onstat -g ckp To get configuration recommendations
onmode -Y “session id” To SET EXPLAIN ON dynamically for a session
onstat -r -r ==> Flag is to repeat command
oncheck -cc Checks system catalog tables for give database
onmode -wf Update the value of the specified configuration parameter and save  the new value to the onconfig file
onmode -D To change the value of MAX_PDQPRIORITY while the database server is online
oninit -j Initialize shared memory, leave in single user mode
onstat -g glo Amount of system CPU time and user CPU time per virtual processor
onmode -BC 1|2 To control the availability of large chunks, chunks greater than 2 GB
oncheck Database disk configuration usage
onperf Database graphical monitoring tool
onstat Tool to monitor shared memory
onmode -c [block | unblock] Do checkpoint. Block or unblock server.
onmode -wm To update configuration parameter values for current session
onstat -g env Server startup environment
onstat -g stm Prints all prepared statements
onmode -j Change from quiescent/online to administration mode in which you want to execute SQL statements
onstat -b Buffer size
onmode -a To add a segment of specified size to virtual shared memory
onstat -o To capture a statis snapshot of database server shared memory for later analysis and comparision
onstat -g seg Info about memory segments
onstat -g arc dbspaces archive status


Cheers! RK

Informix/IDS: Monitoring log file

The message file records the activities performed on Informix server instance such as starting, stoping etc.
There are 2 ways you can monitor the message file:

(1)Using onstat –m: Prints message log file
Below is an example output from onstat -m:
ids9> onstat -m

IBM Informix Dynamic Server Version 9.40.HC7 — On-Line — Up 212 days 21:16:43 — 105360 Kbytes

Message Log File: /opt/informix/logs/ids9/ids9.log

12:19:44 Maximum server connections 45
12:24:47 Fuzzy Checkpoint Completed: duration was 0 seconds, 161 buffers not flushed.
12:24:47 Checkpoint loguniq 8727, logpos 0x2ff47cc, timestamp: 0xfa89961e

12:24:47 Maximum server connections 45
12:26:23 Logical Log 8727 Complete, timestamp: 0xfa8b79ad.
12:26:24 Process exited with return code 156: /bin/sh /bin/sh -c /opt/informix/run32/etc/ 2 23 “Logical Log 8727 Complete, timestamp: 0xfa8b79ad.” “Logical
12:29:50 Fuzzy Checkpoint Completed: duration was 0 seconds, 162 buffers not flushed.
12:29:50 Checkpoint loguniq 8728, logpos 0x257d8, timestamp: 0xfa8bee6b

12:29:50 Maximum server connections 45
12:34:53 Fuzzy Checkpoint Completed: duration was 0 seconds, 163 buffers not flushed.
12:34:53 Checkpoint loguniq 8728, logpos 0x3b7e4, timestamp: 0xfa8c1085

12:34:53 Maximum server connections 45
12:39:56 Fuzzy Checkpoint Completed: duration was 0 seconds, 163 buffers not flushed.
12:39:56 Checkpoint loguniq 8728, logpos 0xdf7e4, timestamp: 0xfa8c1ba9

12:39:56 Maximum server connections 45


(2) Using tail –f command
First, find out the MSGPATH variable from $INFORMIXDIR/etc/$ONCONFIG file.
The MSGPATH value looks as below
# Message Files
MSGPATH /opt/informix/logs/ids9/ids9.log #System message log file path

Now, tail –f opt/informix/logs/ids0/ids9.log will help you to monitor the writes into message log file.


Checking Informix Instance Status

Since, a single UNIX server can have multiple Informix instances running, follow the below steps to find out a particular instance status:

1.Login to UNIX (or windows) box using informix userid/password

2.If you have multiple Informix server instances running on the UNIX (or windows) box then your DBA might have already setted up environment files for each instance. Source the respective environment file

3.Each instance environment file must have the below environment variables:

INFORMIXDIR: Is the directory where the Informix server is installed
INFORMIXSERVER: The Informix instance name
INFORMIXSQLHOSTS: This is optional one. If no entry specified for this then the default entry for this is: $INFORMIXDIR/etc/sqlhosts
ONCONFIG: This tell the Informix instance configuration parameters
PATH: The path for executables. The $INFORMIXDIR/bin should be added to this.
LD_LIBRARY_PATH: Tells where to look for Informix library files on non AIX platform
SHLIBPATH: Tells where to look for Informix library files on AIX platforms
*************Sample Informix instance Environment file******************
export INFORMIXDIR=/opt/informix/run32
export ONCONFIG=onconfig.ids9
export TERMCAP=$INFORMIXDIR/etc/termcap

4.To check the instance status type “onstat -“ from command prompt as shown in below screenshot

ids9> onstat –

Server Status.png
“On-Line” from above screenshot means the instance is up and running.
9.40.HC7: is the Informix version number
and the instance is on line from 212 days 19:25:41

Note: onstat is an utility that comes with Informix installation. The path of this utility is: $INFORMIXDIR/bin/onstat



About IBM Informix Dynamic Server(IDS) Architecture

IBM Informix is a relational database system developed and marketed by IBM. In Informix we can have more than one database under one Informix server instance. When you install Informix software and bring up an instance by default Informix gives us 3 to 4 databases, which are used for administration, Authentication and auditing purposes.

What is Informix instance?

An Informix instance is a single connection to shared memory. It is represented by one or more oninit processes.

The below diagram explains the Informix architecture at a very high level:



Note1: Server Instance in the above diagram is Informix database server instance. Please don’t get confused with UNIX server here.

Note2: A single Informix server product can have N number of Informix server instances and each instance can have N number of databases. All these instances can run on a single UNIX server or multiple UNIX servers. It all depends on the client, hardware requirements and the way DBA configures the instances.

Note3: Clients/applications/users only talks to Informix server instance, which is listening at a particular port, but never with databases directly.


Cheers! RK

Informix Dynamic Server (IDS) Logging

IDS knows below lock types:

  • Shared lock
  • Update lock
  • Exclusive lock
  • Intent lock

Locking granularity in IDS:

Granularity: It is the extent to which a system is broken down into small parts, either the system itself or its description or observation. It is the “extent to which a larger entity is subdivided. For example, a yard broken into inches has finer granularity than a yard broken into feet”

IDS allows to place locks on below objects:

  • Database locks
  • Table Locks
  • Page locks
  • Row locks
  • Index key locks


Share Lock:

Share locks could be placed on rows that don’t have an exclusive lock on it. Other users might place additional shared locks or update locks on the same row, but no other exclusive locks are allowed.

Update Lock:

An update lock is a special kind of lock generated by a cursor that has been declared with the for update clause. Update locks could only be placed on a row that currently has no update or exclusive lock on it.

Once an update lock is placed on a row, it is promoted to an exclusive lock as soon as the row is updated.

Exclusive Lock:

An exclusive lock can only be placed on rows that do not have any other kind of lock on it. Once an exclusive lock is placed on a row, no other locks can be placed on the same row. It is exclusively reserved for this database session.

Example: Open two sessions with userid: informix

From session1: open a database, ex:phase1, in exclusive mode as below

Database phase1 exclusive;

From session2: Try to access the phase1 database as “dbaccess phase1”, then you will get an error.

Remember that, even though you are Informix user in both session, you are still not able to access from another session.

Intent Lock:

Intent locks are special kinds of locks. If, for example, a row is update, an exclusive lock is placed on the row and intent exclusive lock is placed on the table. The intent exclusive table lock ensures that no other session could place a share or exclusive lock on the table as long as individual rows in the table have been exclusively locked.

Database Locks:

Databases could be locked explicitly in exclusive or shared mode.

Database share lock:

A share lock is automatically placed on the database as soon as you open the database. This ensures that no other session could place an exclusive lock on the database or drop the database.

Example: Type command “dbaccess phase1” from session1.

From session2, type “onstat –k”. This will display the following output:

address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7210c 0        HDR+S    100002   205         0

HDR+S: Share lock

Rowid: is the hexadecimal row ID of the row describing this database in table sysdatabases in the sysmaster database.

Databases and their hexadecimal row IDs could be selected with the following SQL query:

Database sysmaster;

Select name, hex(rowid) FROM sysdatabases;

Database exclusive lock:

An exclusive lock can be explicitly set on a database. Utilities like dbexport places exclusive lock on the database to be exported.

Example: database phase1 exclusive

Now, output from onstat –k:

address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7210c 0        HDR+X    100002   205         0

Here, you can see an exclusive lock (HDR + X) on the database with the hexadecimal rowid=205

Table Locks:

Like databases, tables could either be locked in exclusive or share mode.

Locking a table exclusive mode prevents anybody else from reading or changing data in the locked table. An exception to this are sessions running with an isolation level of dirty read (i.e, read uncommited). These sessions are still able to read (possible inconsistent) data from the exclusively locked table.

Locking a table in share mode allows others to select data from this table but prevents data modifications.

To place a shared lock, you need to be in transaction as below:

begin; lock table orders in share mode

Output from onstat –k:


address  wtlist   owner    lklist            type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7b46c 8e741078 HDR+S    10010d   0           0

You see a share lock (HDR+S) and a row ID of zero (rowid=0). A zero row ID represents a table lock. The tablespace number (tblsnum=10010d) conforms to the hexadecimal partition number of this table.

The affected table could be identified in two ways:

  • Oncheck command

Oncheck –pt 0x0010010d | more

  • Data dictionary query command

select tabname, dbinfo(“dbspace”, partnum), hex(partnum) from systables where hex(partnum) = “0x0010010D”

Notice that you need to put a 0x00 in front of the hexadecimal number gathered from the onstat –k output, and you need to convert the letters to uppercase (10010d à 0x0010010D) in order to find the table through the dictionary query.

If it is a fragmented table you have to query, the sysfragments dictionary table:

select st.tabname, dbinfo(“dbspace”, sf.partn, hex(sf.partn) from systables st, sysfragments sf where st.tabid = sf.tabid and sf.fragtype = “T” and hex(partnum) = “0x0010010D”

Table exclusive lock:

An exclusive lock can be explicitly set on a table or might be implicitly set by statements like alter table.

Example: begin; lock table orders in exclusive mode

Output from onstat –k:


address  wtlist   owner    lklist            type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7b46c 8e741078 HDR+X    10010d   0           0

Page or row locks:

The lock granularity (page or row) could be specified during the creation of a table  or later can be changedwith the alter table statement

Example1: create table t1 (c1 int) lock mode (row)

Example2: alter table t1 modify lock mode (page)

You can determine lock mode of a table using,

  • Oncheck utility

Oncheck –pt  database_name:owner_name.table_name

  • Dictionary query

SELECT tabname, locklevel FROM systables WHERE tabname=”table_name”

The following abbreviations for the locklevel are shown by IDS:

  • B – Views (Check the locklevel of underlying table)
  • P – Page locking
  • R – Row Locking

Page Locks:

Page locks are placed on a database page during the processing of a row if the table has been configured for page level locking.

Page locks are represented with a double zero (00) at the end of a row ID.

Row Lock:

Row locks are placed on a database page during the processing of a row if the table has been configured for row-level locking.

Identifying a locked row:


Database phase1;

Set isolation to dirty read;

SELECT * FROM customer WHERE hex(rowid)=”0x0000001A”;

Index Key Locks

IDS also places locks on the index keys like data rows in order to protect those index keys. Key value locking guarantees that unique keys remain unique because it doesn’t allow a second transaction to insert a key with the same value until the transaction that deleted the unique key is committed.

Index key locks are identified by a K in the key#/bsiz column of the onstat –k output.

IBM Informix pops up big in China!

IBM Informix becomes THE RDBMS for the government of the Popular Republic of China! See the details of this game changer IBM deal in IIUG website: