Monitoring and isolating problems using system databases
The database server creates and maintains the sysmaster database. It is analogous to the system catalog for databases, which is described in the IBM Informix Guide to SQL: Reference. Just as a system catalog for every database managed by the database server keeps track of objects and privileges in the database, a sysmaster database for every database server keeps track of information about the database server.
The sysmaster database contains the system-monitoring interface (SMI) tables. The SMI tables provide information about the state of the database server. You can query these tables to identify processing bottlenecks, determine resource usage, track session or database server activity, and so on. This chapter describes these SMI tables, which are slightly different than ordinary tables.
There are many tables in the sysmaster database. Check the manual for details. Table 1 shows a list of the new tables that are introduced in Informix version 11.
Table 1. New sysmaster tables
|sysbaract_log||The OnBar activity log file|
|syscluster||High-availability cluster information|
|syscmsmsla||Connection Manager information|
|syscmsmtab||Connection Manager information|
|syscompdicts_full||Compression dictionary information|
|sysdual||A single-row table|
|sysenv||Online server's startup environment|
|sysenvses||Session-level environment variable|
|sysha_lagtime||Secondary-server lagtime statistics|
|sysha_type||Information about connected servers|
|sysha_workload||Secondary-server workload statistics|
|sysipl||Index-page logging information|
|sysmgminfo||Memory Grant Manager/Parallel Data Query information|
|sysnetclienttype||Client-type network activity|
|sysnetglobal||Global network information|
|sysonlinelog||Online log information|
|sysrsslog||RS secondary-server information|
|sysrstcb||Improvement to view input/output and lock wait information|
|sysscblst||Improvement to view the memory used by session|
|syssqlcacheprof||Profile information about each SQL cache|
|syssqltrace||SQL statement information|
|syssqltrace_info||SQL profile trace system information|
|syssqltrace_iter||SQL statement iterators|
|syssrcrss||RS secondary-server statistics|
|syssrcsds||SD secondary-server statistics|
|systcblst||Existing table modifications to add wait stats|
|systrgrss||RS secondary-server statistics|
|systrgsds||SD secondary-server statistics|
The SMI consists of tables and pseudo-tables that the database server maintains automatically. While the SMI tables appear to the user as tables, they are not recorded on disk as normal tables. Instead, the database server constructs the tables in memory, on demand, based on information in shared memory at that instant. When you query an SMI table, the database server reads information from the shared-memory structures. Because the database server continually updates the data in shared memory, the information that SMI provides lets you examine the current state of your database server.
The SMI tables provide information about the following topics:
- Chunk input/output
- Database-logging status
- Disk usage
- Environment variables
- SQL statement cache statistics
- SQL statements
- System profiling
- User profiling
- Virtual-processor CPU usage
You can use SELECT statements on SMI tables wherever you can use SELECT against
ordinary tables (for example, from dbaccess, in an SPL
routine, with Informix ESQL/C, and so on) with one restriction: you cannot (meaningfully)
reference row ID when you query SMI tables. SELECT statements that use row ID do not return an error, but the results are unpredictable.
All standard SQL syntax, including joins between tables, sorting of output, and so on, works with SMI tables.
For example, if you want to join an SMI table with a non-SMI table, name the SMI table with the following standard syntax:
Any user can use SQL SELECT statements to query an SMI table, but standard users cannot run statements other than the SELECT statement. Users who attempt to run other statements receive permission errors. The administrator can run SQL statements other than SELECT, but the results of such statements are unpredictable.
Tip: For more predictable results, query the views that are associated with each table instead of querying the tables directly.
Dynamic Server includes the sysadtinfo and sysaudit tables. Only the user informix on UNIX or members of the Informix-Admin group on Windows can query the sysadtinfo and sysaudit tables.
You cannot use the dbschema or dbexport utilities on any of the tables in the sysmaster database. If you do, the database server generates the
following error message:
Database has pseudo tables - can't build
You can create a trigger on an SMI table, but it never gets executed. Triggers are activated only when an INSERT, UPDATE, or DELETE statement occurs on a table. The updates to the SMI data occur within the database server, without the use of SQL, so a trigger on an SMI table is never activated.
To create an event alarm, you can query for a particular condition at predefined intervals and execute an SPL routine if the necessary conditions for the alarm are met.
You can access SMI tables from within an SPL routine. When you reference SMI tables, use the same syntax that you use to reference a standard table.
The information in the SMI tables changes based on the database server activity. However, the database server does not update the information using SQL statements. When you use SMI tables with an isolation level that locks objects, it prevents other users from accessing the object, but it does not prevent the data from changing. In this sense, all the SMI tables have a permanent Dirty Read isolation level.
You might want a list of users who have a database open and a list of which workstations
those users are using to connect to the database.
indicates which users are connected to the
server, but not which databases and workstations they are using.
onstat -g ses indicates the users and workstations, but not which
onstat -g sql indicates the session IDs and
databases, but not the user names and workstations.
Listing 1 shows an example query.
Listing 1. Query SMI tables in sysmaster
SELECT sysdatabases.name database, syssessions.username, syssessions.hostname, syslocks.owner sid FROM syslocks, sysdatabases, outer syssessions WHERE syslocks.tabname = "sysdatabases" AND syslocks.rowidlk =sysdatabases.rowid AND syslocks.owner = syssessions.sid;
Listing 2 shows the resulting output.
Listing 2. Result of querying SMI tables in sysmaster
database stores_demo username informix hostname istation1 sid 27 database sysmaster username informix hostname istation1 sid 29
Look at the syslocks table in the sysmaster database and see how it can help troubleshoot locking problems.
The syslocks table tracks locks and is analogous to the output from
onstat -k. Listing 3 shows a sample line of
onstat -k output.
Listing 3. Output from onstat -k
Locks address wtlist owner lklist type tblsnum rowid key#/bsiz 700000011aee2a8 0 7000000704d3d30 0 S 100002 204 0 7000000157f72a8 0 7000000704d4568 0 HDR+S 100002 204 0 70000001d2092a8 0 7000000704d34f8 0 S 100002 204 0 3 active, 2000000 total, 262144 hash buckets, 0 lock table overflows
There is a fair amount of data here, but it involves some work to get meaningful information.
Listing 4 shows the same locks by executing the command in sysmaster. Note that the
where clause is omitted.
Listing 4. Querying the syslocks table in sysmaster
Select * from syslocks dbsname stores_demo tabname customer rowidlk 256 keynum 0 type X owner 28 waiter
Instead of a cryptic partnum (tblsnum), there is an actual database name and a table name. In real life this is
more meaningful. Instead of the address for the owner given in the
onstat -k output, the SELECT statement actually
given a session ID. So, you can run an
onstat-g ses, an
onstat -g sql, and an
onstat -g stm to
find out what a user is doing that caused the locking.
Note is that the results of querying the sysmaster database are usually given in decimal format, whereas onstat output may be given in hexadecimal format. This can cause some confusion if you are trying to track down a problem using both modalities.
There are many pseudo-tables that are usually intuitively named to correspond with the resource they are tracking. Check the Table 2 for some common pseudo-tables and their corresponding onstat commands.
Table 2. Common pseudo-tables
|Table name||Corresponding onstat command|
|syschunks||onstat -d (chunks part)|
|sysdbspaces||onstat -d (dbspaces part)|
|syslogs||onstat -l (logical logs part)|
|sysprofile||onstat -p ?onstat -F|
When you bring the database server up for the first time, it runs a script called buildsmi, which is in the etc directory. This script builds the database and tables that support SMI. The database server requires approximately 1750 free pages of logical-log space to build the sysmaster database. If you receive an error message that directs you to run the buildsmi script, a problem probably occurred while the database server was building the SMI database, tables, and views.
When you use buildsmi, the existing sysmaster database is dropped and then re-created. This script must be run as user informix on UNIX, or as a member of the Informix-Admin group on Windows, after ensuring that no connections to the sysmaster database are made during the build of the database. For example, if a scheduler task is running when the buildsmi script begins, the script fails when the scheduler attempts to access any of the sysmaster tables. Errors issued while the buildsmi script runs are written on UNIX to the file /tmp/buildsmi.out, or on Windows to the file %INFORMIXDIR%\etc\buildsmi_out.%INFORMIXSERVER%, where %INFORMIXSERVER% is the name of the Informix database server.
The sysutils database is where OnBar stores information about every backup or restore it performs. Backup information about each dbspaces log and logical log is also stored here. OnBar uses these tables when performing warm restores.
When you initialize the database server for the first time, it runs a script called bldutil.sh on UNIX or bldutil.bat on Windows. This script builds the sysutils database. If it fails, the database server creates an output file in the tmp directory. The output file is bldutil.process_id on UNIX and bldutil.out on Windows. The messages in this output file reflect errors that occurred during the script execution.
The sysmaster database contains many tables that you can use to monitor your system. The database server supports the following SMI tables:
- The syscheckpoint table provides information and statistics about checkpoints. The corresponding onstat is
onstat -g ckp
- The syschunks table contains a description of each of the chunks that the database
server manages. The corresponding onstat is
- The syslocks table provides information about all the currently active locks in the
database server. The corresponding onstat is
- The syssqltrace table provides detailed information about the SQL statement. The corresponding
onstat -g his
- The syslogs table provides information about space use in logical-log files. The
corresponding onstat is
- The syssessions table provides general information about each user connected to the database
server. The corresponding onstat is
onstat -g ses