Forum Index
An Open Source of Knowledge
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

The Sysmasters Database Overview

Post new topic   Reply to topic Forum Index -> Informix
View previous topic :: View next topic  
Author Message
HowTo Grandmaster
HowTo Grandmaster

Joined: 30 Jul 2006
Posts: 17

PostPosted: Wed Sep 06, 2006 1:54 am    Post subject: The Sysmasters Database Overview Reply with quote

Exploring the Sysmaster Database

by Lester Knutsen

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 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 database,
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;

One of the first things you will notice is that this script is slow. This led me to start digging into what was causing the slow performance. Running this query with set explain turned on (this shows the query optimizer plan) shows that there is a lot of work going on behind the scenes. Syslocks is a view, and it takes a sequential scan of six tables to produce the view. A temp table is created to hold the results of the syslocks view, and this is then joined with the other two tables. The tables sysdatabase and syssessions are also views. And the view syssessions uses a stored procedure, called bitval. Figure 3 contains the output from turning set explain on. In spite of these queries sometimes being a bit slow, these tables are a tremendous value and make it much easier to monitor your database server.

Figure 3: Output from "set explain on" for dbwho.sql

create view "informix".syslocks
as select x1.dbsname ,x1.tabname ,x0.rowidr ,x0.keynum ,
x4.txt [1,4] ,x3.sid ,x5.sid
from "informix".syslcktab x0 ,
"informix".systabnames x1 ,
"informix".systxptab x2 ,
"informix".sysrstcb x3 ,
"informix".flags_text x4 ,
outer("informix".sysrstcb x5 )
where ((((((x0.partnum = x1.partnum )
AND (x0.owner = x2.address ) )
AND (x2.owner = x3.address ) )
AND (x0.wtlist = x5.address ) )
AND (x4.tabname = 'syslcktab' ) )
AND (x4.flags = x0.type ) ) ;
Estimated Cost: 713
Estimated # of Rows Returned: 51

1) informix.syslcktab: SEQUENTIAL SCAN
2) informix.flags_text: SEQUENTIAL SCAN
Filters: informix.flags_text.tabname = 'syslcktab'
Dynamic Hash Filters: informix.syslcktab.type = informix.flags_text.flags
3) informix.systxptab: SEQUENTIAL SCAN
Dynamic Hash Filters: informix.syslcktab.owner =
4) informix.systabnames: SEQUENTIAL SCAN
Filters: informix.systabnames.tabname = 'sysdatabases'
Dynamic Hash Filters: informix.syslcktab.partnum
5) informix.sysrstcb: SEQUENTIAL SCAN
Dynamic Hash Filters: informix.systxptab.owner = informix.sysrstcb.address
6) informix.sysrstcb: SEQUENTIAL SCAN
Dynamic Hash Filters: informix.syslcktab.wtlist =

select database,
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

Estimated Cost: 114
Estimated # of Rows Returned: 11
Temporary Files Required For: Order By

1) (Temp Table For View): SEQUENTIAL SCAN
2) informix.sysdbspartn: INDEX PATH
(1) Index Keys: ROWID
Lower Index Filter: informix.sysdbspartn.ROWID = (Temp Table For
3) informix.sysscblst: INDEX PATH
(1) Index Keys: sid (desc)
Lower Index Filter: informix.sysscblst.sid = (Temp Table For
4) informix.sysrstcb: AUTOINDEX PATH
Filters: informix.bitval(informix.sysrstcb.flags ,'0x80000' )= 1
(1) Index Keys: scb
Lower Index Filter: = informix.sysscblst.address

2. How the Sysmaster Database is Created

The sysmaster database keeps track of information about the database server just like the system tables keep track of information in each database. This database is automatically created when you initialize OnLine. It includes tables for tracking two types of information: the System Monitoring Interface (SMI) tables, and the On-Archive catalog tables. This article will focus on the SMI tables. There is a warning in the documentation not to change any information in these tables as it may corrupt your database server. Also there is a warning that OnLine does not lock these tables, and that all selects from this database will use an isolation level of dirty read. This means that the data can change dynamically as you are retrieving it. This also means that selecting data from the sysmaster tables does not lock any of your users from processing their data. As mentioned above, the SMI tables are described as pseudo-tables which point directly to the shared memory structures in OnLine where the data is stored. That means they are not actually on disk. However, because many of the SMI tables are really views, selecting from them does create temporary tables and generate disk activity.

A script located in your directory $INFORMIXDIR/etc. named sysmaster.sql contains the SQL statements to create the sysmaster database. The process of creating it is interesting and outlined as follows:

* First the script creates real tables with the structures of the pseudo tables.
* Then, the table structures of the real tables are copied to temp tables.
* The real tables are then dropped.
* The column in systables that contains partnum is updated to indicate they point to pseudo tables in shared memory.
* The flags_text table is created which has the interpretations for all the text descriptions and flags used in the SMI tables.
* The stored procedures are created that are used to create the views, two of which may be interesting:

- bitval() is a stored procedure for getting the boolean flag values
- l2date() is a stored procedure for converting unix time() long values to dates

* Finally the script creates the SMI views.
* After the sysmaster script is run the system will execute another script to create the on-archive tables and views in the sysmaster database.

Warning: The sysmaster database is created the first time you go into online mode after you first initialize your system. Do NOT start creating any other database until this process is complete or you may corrupt your sysmaster database. You will need 2000 KB of logical log space to create the sysmaster database. If there are problems creating the sysmaster database, shut your OnLine server down and restart it. This will re-create the sysmaster database. Monitor your online.log file until you see the messages showing the successful completion of building the sysmaster database in the online.log (Figure 4).

Figure 4. Online.log messages showing successful creation of sysmaster database

12:10:24 On-Line Mode
12:10:24 Building 'sysmaster' database ...
12:11:02 Logical Log 1 Complete.
12:11:03 Process exited with return code 1: /bin/sh /bin/sh -c
/u3/informix7/ 2 23 "Logical Log 1 Complete." "Logical Log 1 Complete."
12:11:22 Logical Log 2 Complete.
12:11:23 Process exited with return code 1: /bin/sh /bin/sh -c
/u3/informix7/ 2 23 "Logical Log 2 Complete." "Logical Log 2 Complete."
12:11:26 Checkpoint Completed: duration was 3 seconds.
12:11:40 Logical Log 3 Complete.
12:11:41 Process exited with return code 1: /bin/sh /bin/sh -c
/u3/informix7/ 2 23 "Logical Log 3 Complete." "Logical Log 3 Complete."
12:11:59 Logical Log 4 Complete.
12:12:00 Process exited with return code 1: /bin/sh /bin/sh -c
/u3/informix7/ 2 23 "Logical Log 4 Complete." "Logical Log 4 Complete."
12:12:25 'sysmaster' database built successfully.

Back to top
Display posts from previous:   
Post new topic   Reply to topic Forum Index -> Informix All times are GMT
Page 1 of 1

Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group