Exploring the Sysmaster Database

Published on October 26, 2002 by Lester Knutsen

This post is the original article I wrote on Exploring the Sysmaster Database and has been reprinted in the Informix TechNotes, the Informix DBA Survival Guide, Second Edition by Joe Lumbley, the Informix Handbook by Ron Flannery, and many other places.

 

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 IO 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 IO statistics by chunk devices.
  • Display IO usage of chunk devices as a percent of the total IO, 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.

1. A Practical Example - Who is Using What Database

Let’s begin with a very practical example of the sysmaster database’s value.

My interest in this database started a couple of years ago, while consulting on a project for a development group where I needed to know who had a database open and which workstation they were using to connect to the database. This was a development environment and there were continual changes to the database schemas. In order to make updates to the database schema, I would have to get the developers to disconnect from the database. The “onstat -u” utility would tell me which users were connected to the server, but not what database and what workstation they were using. “Onstat -g ses” told me the user and workstation, but not the database. “Onstat -g sql told me the session id and database, but not the user name and workstation. After some debugging, I found all the information I wanted in the sysmaster database. And, because it was a database, I could retrieve it with SQL queries. The following query shows the database, who has it open, the workstation they are connected from, and the session id.

Figure 1. Dbwho SQL script

-- dbwho.sql
select sysdatabases.name database,  -- Database Name
        syssessions.username,       -- User Name
        syssessions.hostname,       -- Workstation
        syslocks.owner sid          -- Informix Session ID
from   syslocks, sysdatabases , outer syssessions
where  syslocks.tabname = "sysdatabases" -- Find locks on sysdatabases
and syslocks.rowidlk = sysdatabases.rowid -- Join rowid to database
and    syslocks.owner = syssessions.sid -- Session ID to get user info
order by 1;

Every user that opens a database opens a shared lock on the row in the sysdatabases table of the sysmaster database that points to that database. First we need to find all the locks in syslocks on the sysdatabases table. This gives us the rowid in sysdatabase which has the database name. Finally, we join with the table syssessions to get the username and hostname. I put all this together in a shell script that can be run from the unix prompt and called it dbwho. Figure 2 contains the shell script.

Figure 2. Dbwho shell script

:
###########################################################################
# Program: dbwho
# Author: Lester Knutsen
# Date: 10/28/1995
# Description: List database, user and workstation of all db users
###########################################################################

echo "Generating list of users by database ..."
dbaccess sysmaster - <<EOF
select
        sysdatabases.name database,
        syssessions.username,
        syssessions.hostname,
        syslocks.owner sid
from syslocks, sysdatabases , outer syssessions
where syslocks.rowidlk = sysdatabases.rowid
and syslocks.tabname = "sysdatabases"
and syslocks.owner = syssessions.sid
order by 1;
EOF