Exploring the SMI Database

When you list all the databases on your INFORMIX server, you will see one called "sysmaster." This is a special database and is one of the new features that first appeared in INFORMIX-OnLine DSA 6.x and 7.x. This is a database that contains tables that can be used for monitoring your system. These are referred to as the System Monitoring Interface (SMI) tables. In this chapter we will explore some of the tables and views that are in this database.

The sysmaster database is described as a pseudo database. That means most of its tables are not normal tables on disk, but pointers to shared memory structures in the OnLine engine. The sysmaster database contains over 120 tables. Only 18 of these tables are documented in the INFORMIX-OnLine Dynamic Server Administrator's Guide, Volume 2, Chapter 38. The rest are undocumented and described by Informix as for internal use. The examples and references in this article are based on OnLine 7.23. I have also tested some of the examples with versions 7.10, 7.12, and 7.22. There are some minor changes between versions in the undocumented features and structures of these tables.

A warning: Some of the features discussed in this article are based on undocumented SMI tables and may change or not work in future versions of INFORMIX OnLine DSA.

This article will focus on users, server configuration, dbspaces, chunks, tables, and monitoring I/O using the sysmaster database. We will present how to create scripts to monitor the following:

• List who is using each database.

• Display information about your server configuration.

• Display how much free space is available in each dbspace in a format like the UNIX df command.

• List the status and characteristics of each chunk device.

• Display blocks of free space within a chunk. This allows you to plan where to put large tables without fragmenting them.

• Display I/O statistics by chunk devices.

• Display I/O usage of chunk devices as a percent of the total I/O, and show which chunks are getting used the most.

• Display tables and the number of extents, and number of pages used.

• Present a layout of dbspace, databases, tables, and extents similar to the command "tbcheck -pe."

• Show table usage statistics sorted by which tables have the most reads, writes, or locks.

• Show statistics of users sessions.

• Show locks and users who are waiting on locks.