DB2 magazine:ids 11 Database monitoring

The Sysmaster database is a pseudo database and is part of the Informix Dynamic Server (IDS) installation, showing the shared memory structure of the IDS server. This is an excellent tool for monitoring server status and performance. I've done a lot of speeches about this and I've written many articles.

IDS 11 (The Past code name is "Cheetah") adds a number of new features to the Sysmaster database. This column and future articles will provide examples of how you can take advantage of the new sysmaster monitoring table. If you attend this month's IBM information on Demand Symposium, I will also discuss related topics in my speech.

The New Sysmaster database table makes me feel excited about the features that include:

Syscheckpoint, track the last set of checkpoints after the server restarts.

Sysenv, displays the environment variables that are in effect when the server is started.

Sysenvses, displays the environment variables that affect the user's session.

Sysmgminfo, displays Parallel Database Query (PDQ) information.

Sysnetclienttype, Sysnetglobal, and Sysnetworkio display network status.

SYSONLINELOG, tracking online logging.

Syssqltrace, Syssqltrace_info, syssqltrace_iter, display SQL configuration files and trace information.

Systhreads, trace the thread and its wait state.

The new Sysadmin database is part of the IDS Automatic monitoring and Scheduler system, tracking tasks and progress. Here are five of these new features, and in the following articles, I'll explain the command_history table and other topics.

Syscheckpoint

The table monitors and stores the last 20 checkpoints in memory after the IDS server starts. Table 1 lists the fields in the table. Syscheckpoint is not a physical table, but a view of the shared memory structure.

Table 1. Definition of Syscheckpoint table

The checkpoint in IDS 11 has changed radically, as explained in the previous installment of the column. The goal of the checkpoint is to keep the memory buffer synchronized with the disk structure. Without checkpoints, you risk a server crash. In previous versions, there were two types of checkpoints. Standard checkpoints block all user writes until all buffers are written to disk and all memory structures are synchronized. Version 9.x introduces a blur checkpoint that stops user activity only when a list of dirty buffers is logged, and the user activity continues after the dirty buffer is written to disk in the background. IDS 11 introduces a new patented checkpoint algorithm that replaces the two types of checkpoints in the past. Most checkpoints do not block the user from writing when the buffer enters the disk, and the new checkpoint is more reliable than the Blur checkpoint.

The Syscheckpoint table allows you to monitor checkpoints. Questions that can be answered with this data include:

l What causes checkpoints to be called? Is the physical record too small? Or does the logical log (or user) issue the ONMODE-C command to enforce the checkpoint?

How much time does the checkpoint occupy?

How many users (if any) who perform updates are blocked? How are they blocked?

How many buffers have been entered into the disk due to the checkpoint?

Sysenv

Many environment variables affect the operation and server performance characteristics of the Informix server. Once the IDS server starts running, the Sysmaster table provides you with a way to monitor the environment variables that affect the server. Table 2 shows the definition of the table. The IDS server basically does not need to be shut down or restarted. After several months or years of running the IDS server, environment variables that work when the server starts are likely to be forgotten. This table will show what the server is doing when it starts. This table can be used in conjunction with the Sysenvses table to help debug problems with the IDS server and client environment variables not appearing at the same time.

Table 2. Definition of sysenv table

Sysenvses

Each IDS user session can have different environment variables. When you are debugging a user session, it is helpful to see which variables affect the running session. The Sysenvses table provides you with a way to monitor user session variables. Table 3 shows the definition of the table.

Table 3. Definition of sysenvses table