Discovering IBM Informix Instances and Databases On A Specified Host

por | 21 enero, 2019

Scott O'Rourke's picture

 Submitted by Scott O’Rourke on Mon, 09/06/2014 – 16:56


This article describes how to “discover” IBM Informix instances on a specified host and then gather further information about databases within each discovered instance.


You’re a DBA and have just started at a new site where several IBM Informix instances are already up and running. How do you discover where IBM Informix is installed and gather information about the configured instances? Perhaps you’re already established, but need to keep check on any new instances or databases created. IBM Informix provides a built in utility which can be used to assist in this requirement.

A Unix or GNU/Linux host which has had the IBM Informix Server product installed, and one or more instances have been initialised, will have the directory /INFORMIXTMP present. Despite its name, this directory is important, and should never be modified or deleted. Under this directory, there should be a readable file named .infxdirs; within this file there is a line present detailing each product installation directory.

Discover instances …

Using the above information you could trawl through the etc directory under each installation directory to work out which instances have been configured and which sqlhost files they are using; however, there is an easier way.

For each install directory in .infxdirs run the following commands, substituting {dir} for the directory:

${INFORMIXDIR}/bin/onstat -g dis

Server: from the instance
Server Number : in onconfig
Server Type: 
Server Status: Up|Down
Server Version: Full IDS version
Shared Memory: Shared Memory Base Address
INFORMIXDIR: Product Installation Directory
ONCONFIG: Path and onconfig file in use
SQLHOSTS: Path and sqlhosts file in use
Host: Hostname of server

Unlike other onstat commands, «onstat -g dis» only needs INFORMIXDIR set to work; this pieces the information in the etcdirectory together for you, and produces the following output for each instance found.

An actual output is shown below:

> onstat -g dis
IBM Informix Dynamic Server Version 12.10.FC3 -- On-Line -- Up 1 days 22:42:37 -- 432856 Kbytes
There are 1 servers found
Server : jj_prepsuse_a_1_t
Server Number : 0
Server Type : IDS
Server Status : Up
Server Version: IBM Informix Dynamic Server Version 12.10.FC3
Shared Memory : 0x44000000
INFORMIXDIR : /opt/IBM/informix/ids1210
ONCONFIG : /opt/IBM/informix/ids1210/etc/onconfig.jj_prepsuse_a_1
SQLHOSTS : /opt/IBM/informix/ids1210/etc/sqlhosts
Host : jj-prepsuse-a

Discover Databases …

So now we have a list of instances, but how many databases are present? For each instance discovered, set the environment for INFORMIXDIRONCONFIG and INFORMIXSQLHOSTS, and make sure PATH includes ${INFORMIXDIR}/bin. You can now query the sysmaster database to obtain a full list of databases:

dbaccess sysmaster <<EOF
SELECT name, owner, created, is_logging
FROM sysdatabases

From here you could go on to gather further information such as the number of extents or pages used and/or allocated by linking to other sysmaster tables, but this is beyond the scope of this article.


Detailed information about where IBM Informix is installed and the instances configured under each installation directory can be gleaned with little or no knowledge of the prior setup. These commands can be invaluable when working on a site for the first time, or if used as part of a regularly run script to monitor instance and database growth.