Abstract

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

Content

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:

Copy to Clipboard


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

An actual output is shown below:

Copy to Clipboard


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:

Copy to Clipboard


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.

Conclusion

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.

Disclaimer

The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.