System Administration Certification exam 918 for IBM Informix Dynamic Server 11 prep, Part 2: System activity monitoring

Learn to monitor database server activity for IBM® Informix® Dynamic Server (IDS). Examine various tools to monitor, diagnose problems, and manipulate IDS data. The second in a series of eight tutorials, use this tutorial to help prepare for the IDS 11 exam 918.

Share:

Manjula Panthagani (manjulap@us.ibm.com), Advanced Support Engineer, IBM

Author photo: Manjula PanthaganiManjula Panthagani is an Advanced Support Engineer for Informix Dynamic Server at IBM. She has been in this position supporting IDS for over seven years and participated in the development of the IDS Certification exam.



Sergio R. Eng (sreng@us.ibm.com), Technical Support Engineer, IBM

Author photo:  Sergio EngSergio Eng has developed many database applications through the years. He has also been a DBA and UNIX system administrator for software development platforms. At IBM, Sergio has provided Informix database support for IDS users for Versions 7, 9, and 10.



16 May 2007

Also available in Chinese

Before you start

About this series

Thinking about seeking certification on System Administration for IBM IDS 11 (Exam 918)? If so, you're in the right spot. This series of eight IDS certification preparation tutorials covers all the basics -- the topics you'll need to understand before you read the first exam question. Even if you're not planning to seek certification right away, this set of tutorials is a great place to start learning what's new in IDS 11.

About this tutorial

Tuning and configuring an IDS database can be a complex process that sometimes overwhelms new DBAs. There are, however, a great number of tools, functions, and applications included with IDS that, once mastered, make this task simple.

This tutorial is designed to introduce you to the set of monitoring tools that are available with IDS 11 and to show you how each is used to monitor how well (or how poorly) your database system is operating. In this tutorial:

  • Gain an understanding of how automatic monitoring works
  • Learn how administration API enables you to monitor system activity using SQL commands
  • Find out how tasks and sensors are created in the scheduler and how data is collected
  • Understand how system monitoring interface is used to collect data
  • Learn how onstat and oncheck utilities are used to monitor system
  • See how the message log is used to diagnose system problems

This tutorial is the second in a series of eight tutorials that can help you prepare for the System Administration for IBM IDS V11 Certification (Exam 918). The material in this tutorial primarily covers the objectives in Section 2 of the test, entitled "System Activity Monitoring."

Objectives

After completing this tutorial, you should be able to:

  • Use automatic monitoring
  • Use System-Monitoring Interface
  • Use the onstat utility
  • Use the oncheck utility
  • Create and modify tasks in the scheduler
  • Use operating system commands to monitor

Prerequisites

IDS 11 installation is covered in part 1 of this tutorial series. If you haven't already done so, consider downloading and installing a copy of IBM IDS 11. Installing IDS will help you understand many of the concepts that are tested on the System Administration for IBM IDS V11 Certification exam.

System requirements

You do not need a copy of IDS to complete this tutorial. However, you will get more out of the tutorial if you download the free trial version of IBM IDS 11 to work along with this tutorial.


Automatic monitoring and corrective actions

You can use the administration API, the scheduler, and information stored in the sysadmin database to manage automatic maintenance, monitoring, and administrative tasks. These components of Dynamic Server enable you to simplify the collection of information and maintenance of the server in complex systems.

Administration API

The SQL administration API enables you to perform remote administration using various, specific SQL commands for tasks such as managing spaces, managing configuration, running routine jobs, and system validation.

You can use EXECUTE FUNCTION statements to invoke the built-in admin( ) or task( ) functions to accomplish administrative tasks that are equivalent to executing various administrative utilities of Dynamic Server.

In the EXECUTE FUNCTION statements, items in the argument list specify the utility and its command-line arguments. For example, the following SQL statement, which is equivalent to the oncheck -ce command, instructs the database server to check the extents:

EXECUTE FUNCTION admin('check extents');

Sysadmin database

The sysadmin database contains tables that store task properties. You use the task properties (not configuration parameters) to define the information that the scheduler collects and the statements that the scheduler runs.

The sysadmin database also contains:

  • The built-in task() function
  • The built-in admin() function
  • The command_history table
Table 1. Sysadmin database tables
TableDescription
PH_TASKContains information about how and when each task will be executed
PH_ALERTContains a list of errors, warnings, or information messages that must be monitored
PH_GROUPContains a list of group names. Each task is a member of a group.
PH_RUNLists tasks and contains information about how and when the database server will execute each task
PH_THRESHOLD Contains a list of thresholds that you defined. If a threshold is met, the task can decide to take a different action, such as inserting an alert in the PH_ALERT table.

Each row in the ph_task table is a separate task (defined as a single monitoring event), and each column is a task property. The task properties indicate such items as when to run an SQL statement, stored procedure, or UDR and how to handle the task. An example of a task is the automatic running of a particular job every Monday at midnight.

The scheduler

The scheduler enables the database server to execute database functions and procedures at predefined times or as determined internally by the server. The functions and procedures collect information, and monitor and adjust the server, using an SQL-based administrative system and a set of tasks.

The scheduler manages and executes scheduled maintenance, monitoring, and administration tasks. This tool enables you to monitor activities (for example, space management or automatically backing up any new log data at timed intervals since the last log backup) and create corrective actions that run automatically.

The scheduler manages:

  • Tasks, which provide the means for running a specific job at a specific time or interval
  • Sensors, which collect and save information
  • Startup tasks, which run only once when the database server starts
  • Startup sensors, which run only once when the database starts

A set of task properties, which define what needs to be collected or executed, control the scheduler. The task properties are stored in the ph_task table in the sysadmin database.

Each row in this table is a separate task, and each column is a task property. The task properties indicate to the system such things as when to run an SQL statement, stored procedure, or function and how to handle the task.

For example, you could define tasks to check free log space every hour from 9:00:00 to 19:00:00, daily.

Only task properties, not configuration parameters, define what the scheduler collects and executes. The scheduler executes tasks at predefined times or as determined internally, as required by the database server.

Table 2. Built-in tasks
TaskDescription
mon_command_historyPurges the command history table
mon_configSaves any changes in the ONCONFIG file
mon_config_startupSaves the ONCONFIG file on every server startup
mon_vpsCollects virtual processor information
mon_checkpointSaves information about checkpoints
mon_table_profileSaves table profile information, including the total number of updates, inserts, and deletes that occurred on this table
mon_table_namesSaves the table names along with their creation time
mon_usersSaves profile information about each user
check_backupChecks to ensure that backups have run

You can modify these tasks and set up more tasks.

Setting up tasks

To set up a task, you need to plan the task first. You need to have:

  • A description of the task you want to monitor
  • The table where you want to store data
  • The SQL command, stored procedure, or function to capture data
  • Information on when and how often you want the task to run

You can use SQL to insert a new row into the ph_task table, as shown in the following examples. Also include values that will be displayed in table columns.

The following example in Listing 1 shows the code for the creation of a table, called mon_chunkio, that uses a stored procedure to collect and store data. This task instructs the database server to collect data every five minutes by executing a stored procedure called chunkio. The default scheduling policy (for example, 8:00 A.M. to 5:00 P.M., five days a week) is used, but the data expires and is deleted when it becomes seven days old.

Listing 1. Creating a table that uses a stored procedure to collect and store data
INSERT INTO ph_task
(
tk_description,
tk_result_table,
tk_create,
tk_execute,
tk_frequency,
tk_delete
)
VALUES
(
"Chunk I/O counts and space usage",
"mon_chunkio",
"create table mon_chunkio (ID integer, chunknum smallint, 
   free integer, size integer, reads integer, pagereads integer, 
   writes integer, pageswritten integer)",
"chunkio",
"0 0:05:00",
"7 0:00:00"
);

Setting up sensors

A sensor is a specialized TASK for collecting and saving data. A sensor can be created the same way as a task by inserting a row in the ph_task table, with some additional information.

To set up a new sensor, you need to plan first. You need to have:

  • A description of the sensor
  • The result table to save collect information
  • SQL object, stored procedure, or a function to collect information
  • Information on when and how often the sensor needs to run

The following example in Listing 2 shows the code for a sensor that collects information about the amount of memory that is being used and stores the information in the mon_memory_system table. If that table does not exist, the task creates it. This task, which runs every 30 minutes, deletes any data in the mon_memory_system table that has existed for more than 30 days.

Listing 2. Collecting information about the amount of memory that is being used and storing data in the mon_memory_system table
INSERT INTO ph_task
(tk_name, tk_type, tk_group, tk_description, tk_result_table, tk_create,
tk_execute, tk_stop_time, tk_start_time, tk_frequency, tk_delete )
VALUES
("mon_memory_system",
"SENSOR",
"MEMORY",
"Server memory consumption",
"mon_memory_system",
"create table mon_memory_system (ID integer, class smallint, size int8, 
     used int8, free int8 )",
"insert into mon_memory_system select $DATA_SEQ_ID, seg_class, seg_size, 
     seg_blkused, seg_blkfree FROM sysmaster:sysseglst",
NULL,
NULL,
INTERVAL ( 30 ) MINUTE TO MINUTE,
INTERVAL ( 30 ) DAY TO DAY);

Modifying tasks

You can modify the tasks by modifying the rows in the tables that begin with the characters ph_ in the sysadmin database.

Go to the ph_task or other table that you want to modify in the sysadmin database. Manually change the task information.

Command history table

The command_history table in the sysadmin database contains historical information about the SQL administration API commands executed on this data server.


The system-monitoring interface

The system-monitoring interface (SMI) tables are special tables managed by the database server that contain dynamic information about the state of the database server. You can use SELECT statements on them to determine almost anything you might want to know about your database server.

The buildsmi script

When you bring the database server up for the first time, it runs a script called buildsmi, which is in the $INFORMIXDIR/etc directory. This script builds the sysmaster 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 recreated.

The bldutil.sh script

The sysutils database is the place where OnBar stores information about every backup/restore it performed. Backup information on every dbspaces and logical logs is also stored here. OnBar utilizes 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

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 IDS engine. The sysmaster database contains over 120 tables. Only some of these tables are documented in the Informix Dynamic Server Administrators Guide. The rest are undocumented and meant to be as such for internal use.

These are new tables introduced in Version 11. There are many others sysmaster tables that you can find more details on in the manual.

Table 3. New sysmaster tables
TableDescription
syscheckpointThe information about the checkpoint and associated statistics
systcblstModified the existing table to add wait stats
sysenvsesView Informix s session environment variables
sysenvView the servers environment variables
sysonlinelogView the online.log for the server
sysscblstImprovement to view the memory used by session
sysnetworkioView the network I/O generated by database session
sysdualOracle compatibility feature
syssqlcacheprofDisplays the profile information about each SQL cache
syssqltraceThe SQL statements that have been recently executed on the system
syssqltrace_itrThe list of iterators for the SQL statement
syssqltrace_infoGeneral information about the SQL tracing
sysnetglobalGlobal Network Information
sysnetclienttypeNetwork information based on client type
sysbaract_logThe OnBar activity log file
sysrstcbImprovement to view I/O and lock wait information

Understanding the SMI tables

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 are. 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 these 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:

  • Auditing
  • Disk usage
  • User profiling
  • Database-logging status
  • Tables
  • Chunks
  • Chunk I/O
  • Dbspaces
  • Locks
  • Extents
  • SQL statement cache statistics
  • Virtual-processor CPU usage
  • System profiling

The data in the SMI tables changes dynamically as users access and modify databases that the database server manages.

Accessing SMI tables

Any user can use SQL SELECT statements to query an SMI table, but standard users cannot execute statements other than SELECT. Attempts to do so result in permission errors. The administrator can execute SQL statements other than SELECT, but the results of such statements are unpredictable.

Dynamic Server provides the sysadtinfo and sysaudit tables. Only user informix on UNIX or members of the Informix-Admin group on Windows can query sysadtinfo and sysaudit.

You cannot use dbschema or dbexport 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 schema

You can use SELECT statements on SMI tables wherever you can use SELECT against ordinary tables (from DBAccess, in an SPL routine, with ESQL/C, and so on) with one restriction: You cannot (meaningfully) reference rowid when you query SMI tables. SELECT statements that use rowid 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:

sysmaster[@dbservername]:[owner.]tablename

Example

Intention:: List of users who had a database open and which workstation they were using to connect to the database.

  • The onstat -u utility would tell which users were connected to the server, but not what database and what workstation they were using.
  • Onstat -g ses would tell the user and workstation, but not the database.
  • Onstat -g sql would tell the session ID and database, but not the user name and workstation.
Listing 3. Example
QUERY 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;

OUTPUT database stores_demo username informix hostname istation1 sid 27
database sysmaster username informix hostname istation1 sid 29

The onstat utility

The onstat utility provides a way to monitor database server shared memory from the command line. The onstat utility reads data from shared memory and reports statistics that are accurate for the instant during which the command executes. That is, onstat provides information that changes dynamically during processing, including changes in buffers, locks, indexes, and users.

Monitor the database server status

The onstat output heading indicates the database server status. Whenever the database server is blocked, onstat displays the following line after the banner line: All onstat output includes a header. The onstat - option displays only the output header and is useful for checking the database server mode. The header takes the following form:

Version--Mode (Type)--(Checkpnt)--Up Uptime--Sh_mem Kbytes

Syntax

Listing 4. Onstat syntax
>>-onstat------------------------------------------------------->
.-----------------------------. V (1)
>--+-+-----------------+----+-------------------------+-+------+->
'-filename_source-' +- -a---------------------+ +- -b---------------------+
+- -B---------------------+ +- -c---------------------+ +-
-C---------------------+ +- -d---------------------+ +-
-D---------------------+ +- -f---------------------+ +-
-F---------------------+ +- -g--Monitoring options-+ +-
-G---------------------+ +- -i---------------------+ +-
-k---------------------+ +- -K---------------------+ +-
-l---------------------+ +- -m---------------------+ +-
-o--+---------------+--+ '-filename_dest-' +- -O---------------------+ +-
-p---------------------+ +- -P---------------------+ +-
-r--+---------+--------+ '-seconds-' +- -R---------------------+ +-
-s---------------------+ +- -t---------------------+ +-
-T---------------------+ +- -u---------------------+ +-
-x---------------------+ +- -X---------------------+ '-
-z---------------------' +-
---------------------------------------------------------+ '-
---------------------------------------------------------

Note: Only one occurrence of each item is allowed. More than one option can be specified on a single onstat command invocation.

Table 4. Onstat options
ElementPurpose
-Displays the output header
--Displays a listing of all onstat options and their functions
-aInterpreted as onstat -cuskbtdlp; displays output in that order
-bDisplays information about buffers currently in use, including number of resident pages in the buffer pool
-BObtains information about all database server buffers, not just buffers currently in use. See the entry for -b in this table
-cDisplays the ONCONFIG file: * $INFORMIXDIR/etc/ $ONCONFIG for UNIX * %INFORMIXDIR%\etc\ %ONCONFIG% for Windows
-CPrints B-tree scanner information
-dDisplays information for chunks in each storage space
-DDisplays page-read and page-write information for the first 50 chunks in each dbspace
-fLists the dbspaces currently affected by the DATASKIP feature
-FDisplays a count for each type of write that flushes pages to disk
-g Provides monitoring options
-GPrints global transaction IDs
-iPuts the onstat utility into interactive mode
-jPrints the interactive status of the active onpload process
-kDisplays information about active locks
-lDisplays information about physical and logical logs, including page addresses
-mDisplays the 20 most recent lines of the database server message log
-oSaves copies of the shared-memory segments to filename
-ODisplays information about the Optical Subsystem memory cache and staging-area blobspace
-pDisplays profile counts
-PDisplays for all partitions the partition number and the break-up of the buffer-pool pages that belong to the partition
-rRepeats the accompanying onstat options after they wait the specified seconds between each execution. The default value of seconds is five.
-RDisplays detailed information about the LRU queues, FLRU queues, and MLRU queues
-sDisplays general latch information
-tDisplays tblspace information, including residency state, for active tblspaces
-TDisplays tblspace information for all tblspaces
-uPrints a profile of user activity
-xDisplays information about transactions
-XObtains precise information about the threads that are sharing and waiting for buffers
-zSets the profile counts to zero
filename_destSpecifies destination file for the copy of the shared-memory segments
filename_source Specifies file that onstat reads as source for the requested information
Monitoring optionsSpecifies which onstat -g monitoring option to use
secondsSpecifies number of seconds between each execution of the onstat -r command

onstat g monitoring options

The following onstat -g options are provided for support and debugging only. You can include only one of these options per onstat -g command. For more information, see your IBM Informix Performance Guide.

Table 5. onstat -g options
onstat -g optionTopic or function
-g actActive threads. For example output, see the onstat -g act option.
-g afr pool name session idPrints allocated memory fragments for a specified session or shared-memory pool. Each session is allocated a pool of shared memory. To obtain the pool name, see the -mem option. For example output, see the onstat -g afr pool name session id option.
-g allPrints all multithreading information
-g athPrints all threads. The sqlmain threads represent client sessions. The rstcb value corresponds to the user field of the onstat -u command. For example output, see the onstat -g ath option. For information on using onstat -g ath to print Enterprise Replication threads, see the IBM Informix Dynamic Server Enterprise Replication Guide.
-g cat [modifier]Prints information from the Enterprise Replication global catalog. The global catalog contains a summary of information about the defined servers, replicates, and replicate sets on each of the servers within the enterprise. For more information and sample output, see the onstat -g cat option.
-g cac aggPrints the definitions for user-defined aggregates that are currently in the cache
-g cac stmtPrints the contents of the SQL statement cache. Prints the same output as the -g ssc statement.
-g conPrints conditions with waiters. For example output, see the onstat -g con option.
-g ddrPrints the status of the Enterprise Replication database log reader. If log reading is blocked, data might not be replicated until the problem is resolved. For more information and sample output, see the onstat -g ddr option.
-g dic tableWithout any parameters, prints one line of information for each table cached in the shared-memory dictionary. If given a specific table name as a parameter, prints internal SQL information for that table. For more information, see your IBM Informix Performance Guide. For sample output, see the onstat -g dic table option.
-g disPrints a list of database servers and their status, and information about each database server, INFORMIXDIR, sqlhosts file, ONCONFIG file, and hostname. For example output, see the onstat -g dis option.
-g dllPrints a list of dynamic libraries that have been loaded. For example output, see the onstat -g dis option.
-g driPrints data-replication information. See monitoring High-Availability Data-Replication status in the IBM Informix Administrator's Guide. For example output, see the onstat -g dri option.
-g dscPrints data-distribution cache information. For example output, see the onstat -g dsc option.
-g dss [modifier]Prints detailed statistical information about the activity of individual data sync threads and about user-defined data types. For more information and sample output, see the onstat -g dss option.
-g dtcPrints statistics about the delete table cleaner, which removes rows from the delete table when they are no longer needed. For more information and sample output, see the onstat -g dtc option.
-g envPrints the values of environment variables the database server currently uses. For more information, see the onstat -g env option.
-g ffr pool name session idPrints free fragments for a pool of shared memory. For example output, see the onstat -g ffr pool name session id option.
-g gloPrints global multithreading information. This information includes CPU use information about the virtual processors, the total number of sessions, and other multithreading global counters. On Windows, the virtual processors are operating system threads. The values displayed under the 'pid' field are thread ids, not process ids (Windows). For example output, see the onstat -g glo option.
-g grp [modifier]Prints statistics about the Enterprise Replication grouper. The grouper evaluates the log records, rebuilds the individual log records into the original transaction, packages the transaction, and queues the transaction for transmission. For more information and sample output, see the onstat -g grp option.
-g hisPrints information about the SQLTrace configuration parameter
-g imcPrints information about MaxConnect instances that are connected to the database server. If MaxConnect is not connected to the database server, this command displays No MaxConnect servers are connected.
-g ioaPrints combined information from -g ioq and -g iov. For example output, see the onstat -g ioa option.
-g iobPrints the big buffer usage summary. For example output, see the onstat -g iob option.
-g iofPrints asynchronous I/O statistics by chunk or file. This option is similar to the -D option, except it also displays information on nonchunk, temporary, and sort-work files. For example output, see the onstat -g iof option.
-g iogPrints AIO global information. For example output, see the onstat -g iog option.
-g ioq queue name Prints pending I/O operations for the queue name. If given the gfd or kaio queue name, a queue for each CPU VP is displayed. If queue name is omitted, I/O statistics for all queues are displayed. For example output, see the onstat -g ioq queue name option.
-g iovPrints asynchronous I/O statistics by virtual processor. For example output, see the onstat -g iov option.
-g iplPrints index page logging status
-g lmxPrints all locked mutexes. For example output, see the onstat -g lmx option.
-g lscDisplays information about light scans
-g mem pool name session idPrints statistics for a memory pool. Also displays the pool name, type of shared memory segment that contains the pool, the address of the pool, the total size of the pool, the number of bytes of free memory that it contains, and the number of free and allocated fragments in the pool. If no argument is provided, displays information about all pools. The block pools are listed in a separate section after the main pool list. You also can use ISA to obtain detailed information about a memory pool. If you run an SQL query that allocates memory from the PER_STMT_EXEC and PER_STMT_PREP memory duration pools, onstat -g mem displays information on the PRP.sessionid.threadid pool and the EXE.sessionid.threadid pool. For example output, see the onstat -g mem pool name session id option. For more information, see the IBM Informix DataBlade API Programmer's Guide.
-g mgmPrints Memory Grant Manager resource information. For example output, see the onstat -g mgm option.
-g nbmPrints block bit map for the nonresident segments, one bit per 8-kilobyte block. Bit set indicates block free. For example output, see the onstat -g nbm option.
-g nif [modifier]Prints statistics about the network interface. Useful to determine why data is not replicating. For more information and sample output, see the onstat -g nif option.
-g nsc client idPrints shared-memory status by client id. If client id is omitted, all client status areas are displayed. This command prints the same status data as the nss command. For example output, see the onstat -g nsc client_id option.
-g nsdPrints network shared-memory data for poll threads. For example output, see the onstat -g nsd Option.
-g nss session idPrints network shared-memory status by session id. If session id is omitted, all session status areas are displayed. This command prints the same status data as the nsc command.
-g ntaPrints combined network statistics from -g ntd, -g ntm, -g ntt, and -g ntu. If MaxConnect is installed, this command prints statistics that you can use to tune MaxConnect performance.
-g ntdPrints network statistics by service. For example output, see the onstat -g ntd option.
-g ntmPrints network mail statistics. For example output, see the onstat -g ntm option.
-g nttPrints network user times. For example output, see the onstat -g ntt option.
-g ntuPrints network user statistics. For example output, see the onstat -g ntu option.
-g posPrints $INFORMIXDIR/etc/ .infos.DBSERVERNAME file for UNIX and %INFORMIXDIR%\etc\ .infos.DBSERVERNAME for Windows. For example output, see the onstat -g pos option.
-g ppf partition number 0Prints partition profile for partition number; 0 prints profiles for all partitions. If TBLSPACE_STATS configuration parameter is set to 0, displays: Partition profiles is disabled. For example output, see the onstat -g ppf partition number 0 option.
-g prcPrints information about SPL routine cache. For example output, see the onstat -g prc option.
-g qstPrints queue statistics
-g quePrints statistics for the high-level queue interface (which are common to all the queues of the Enterprise Replication Queue Manager). For more information and sample output, see the onstat -g que option.
-g rbmPrints block bit map for the resident segment (communication message area). For example output, see the onstat -g rbm option.
-g rcv [serverid]Prints statistics about the receive manager, which is a set of service routines between the receive queues and data sync. For more information and sample output, see the onstat -g rcv option.
-g reaPrints ready threads. For example output, see the onstat -g rea option.
-g rep [replname]Prints events that are in the queue for the schedule manager. For more information and sample output, see the onstat -g rep option.
-g rqm [modifier]Prints statistics and contents of the low-level queues (each individual queue) managed by the Reliable Queue Manager (RQM). For more information and sample output, see the onstat -g rqm option.
-g rwmPrints read/write mutexes. For example output, see the onstat -g rwm option.
-g schPrints the number of semaphore operations, spins, and busy waits for each virtual processor. On Windows, the virtual processors are operating system threads. The values displayed under the 'pid' field are thread ids not process ids (Windows). For example output, see the onstat -g sch option.
-g segPrints shared-memory-segment statistics. This option shows the number and size of shared-memory segments that the database server is currently using. For example output, see the onstat -g seg option.
-g ses sessionidPrints session information by sessionid. If sessionid is missing, a one-line summary of each session prints. For more information, see the onstat -g ses option.
-g slePrints all sleeping threads. For example output, see the onstat -g sle option.
-g smb optionPrints detailed information about sbspaces. For more information, see the onstat -g smb option.
-g smxoptionDisplays server multiplexer group connections information
-g spiPrints spin locks that virtual processors have spun more than 10,000 times to acquire. These spin locks are called longspins. The total number of longspins is printed in the heading of the glo command. Excessive longspins might indicate an overloaded system, too many virtual processors for a given computer or node, or an internal problem. To reduce longspins, reduce the number of virtual processors (generally class CPU), reduce the load on the computer, or use the no-age or processor affinity features.
-g sql session idPrints SQL information by session id. If session id is omitted, a one-line summary for each session prints. For more information, see the onstat -g sql option.
-g sscMonitors the number of times that the database server reads the SQL statement in the cache. For example output, see the onstat -g ssc option. Displays the same output as onstat -g cac stmt. For more information, see improving query performance in the IBM Informix Performance Guide.
-g ssc allReports the key-only cache entries as well as the fully cached statements. If the value in the hits column is less than the STMT_CACHE_HITS value, that entry is a key-only cache entry. For more information, see memory utilization in the IBM Informix Performance Guide.
-g ssc poolReports usage of all memory pools for the SQL statement cache. The output displays information on the name, class, address, and total size of the memory pools. For more information, see improving query performance in the IBM Informix Performance Guide.
-g stk tid allDumps stack of thread specified by thread ID or stacks for all threads. This option is not supported on all platforms and is not always accurate. For example output, see the onstat -g stk tid option.
-g stm [session id]Displays the memory that each prepared SQL statement uses. For example output, see the onstat -g stm option. For more information, see memory utilization and improving query performance in the IBM Informix Performance Guide.
-g stsPrints maximum and current stack use per thread. For example output, see the onstat -g sts option.
-g syncShows which sync is active. For example output, see the onstat -g sync option.
-g tpf tidPrints thread profile for a specific thread ID. For example output, see the onstat -g tpf tid option.
-g ufr pool name session idPrints allocated fragments by use
-g vpcacheReturns information about CPU VP memory block cache statistics
-g waiPrints waiting threads; all threads waiting on mutex or condition, or yielding
-g wmxPrints all mutexes with waiters. For example output, see the onstat -g wmx option.
-g wstPrints wait statistics

Statistics called from source file

Use the filename_source parameter with other option flags to derive the requested onstat statistics from the shared-memory segments that filename_source contains. You must first use the onstat -o command to create a file that contains the shared-memory segments.

Example

Listing 5. Calling statistics from source file
Creating a snapshot of the shared memory for further analysis

onstat -o myfile

Checking some information, for example dbspace information

onstat -d myfile

Results


Dbspaces
address  number   flags      fchunk   nchunks  pgsize   flags   owner    name
ad357e8  1        0x60001    1        1        2048     N  B    informix rootdbs
b62a5b0  2        0x60001    2        1        4096     N  B    informix dbsp1
2 active, 2047 maximum

Chunks
address  chunk/dbs offset page Rd page Wr pathname
ad35948  1     1    0     493     5803    /local0/engines/ol_tuxedo/ifmxdata/rootdbs
b62a710  2     2    0     4       20      /local0/engines/ol_tuxedo/ifmxdata/dbsp1
2 active, 32766 maximum

NOTE: The values in the "page Rd" and "page Wr" columns for DBspace chunks are
      displayed in terms of system base page size.

Expanded chunk capacity mode: always

Interactive execution

To put the onstat utility in interactive mode, use the -i option. Interactive mode allows you to enter multiple options, one after the other, without exiting the program. For information on using interactive mode, see onstat -i.>

Continuous onstat execution

Use the seconds parameter with the -r option flag to cause all other flags to execute repeatedly after they wait the specified seconds between each execution.


The oncheck utility

The oncheck utility displays information about the database disk configuration and usage, such as the number of pages used for a table, the contents of the reserved pages, and the number of extents in a table.

The oncheck utility is invoked by executing the oncheck command. The simplest form of the command is shown in Listing 6, below:

Listing 6. Oncheck syntax
oncheck 
<-option>
<database | database:owner.table | tablespacenum logical pagenum ... >
<-x>
<-n>
<-y>
	-x Places a shared lock on the table when you check and print an index
	-n Indicates that no index repair should be performed, 
           even if errors are detected Use with the index repair options
           (-ci, -cI, -pk, -pK, -pl, and -pL).
	-y Repairs indexes when errors are detected.

Options

Table 6. Oncheck options
OptionDescription
-ccChecks system catalog tables for the specified database
-cdReads all pages, except simple large objects from the tblspace for the specified database, table, or fragment, and checks each page for consistency. Also checks tables that use a user-defined access method. Does not check simple or smart large objects.
-cDSame as -cd, but also reads the header of each blobpage and checks it for consistency. Checks simple large objects but not smart large objects.
-ceChecks each chunk-free list, corresponding free space, and each tblspace extent. Also checks smart-large-object extents and sbspace metadata. The oncheck process verifies that the extents on disk correspond to the current control information that describes them.
-ciChecks the ordering of key values and the consistency of horizontal and vertical node links for all indexes associated with the specified table. Also checks indexes that use a user-defined access method.
-cISame as -ci, but also checks that the key value tied to a rowid in an index is the same as the key value in the row
-crChecks each of the root dbspace reserved pages for several conditions
-cRChecks the root dbspace reserved pages, physical-log pages, and logical-log pages
-csChecks smart large object and sbspace metadata for an sbspace
-cSChecks smart large object and sbspace metadata for an sbspace as well as extents
-pBDisplays statistics that describe the average fullness of blobspace blobpages in a specified table. These statistics provide a measure of storage efficiency for individual simple large objects in a database or table. If a table or fragment is not specified, statistics are displayed for the entire database.
-pcSame as -cc, but also displays the system catalog information as it checks the system catalog tables, including extent use for each table None
-pdDisplays rows in hexadecimal format
-pDDisplays rows in hexadecimal format and simple-large-object values stored in the tblspace or header information for smart large objects stored in an sbspace sbpage, and simple large objects stored in a blobspace blobpage
-peSame as -ce but also displays the chunk and tblspace extent information as it checks the chunk free list, the corresponding free space, and each tblspace extent
-pkSame as -ci, but also displays the key values for all indexes on the specified table as it checks them
-plSame as -ci, but also displays the key values. Only leaf-node index pages are checked.
-pLSame as -cI, but also displays the key values and rowids for leaf-node index pages only
-ppDisplays contents of a logical page
-pPSame as -pp, but requires a chunk number and logical page number or internal rowid as input
-prSame as -cr, but also displays the reserved-page information as it checks the reserved pages
-psChecks and displays smart-large-object and sbspace metadata for an sbspace
-pSChecks and displays smart-large-object and sbspace metadata. Lists extents and header information for individual smart large objects.
-ptDisplays tblspace information for a table or fragment
-pTSame as -pt, but also displays index-specific information and page-allocation information by page type (for dbspaces)

Check and repair

The oncheck utility can repair the following types of disk structures:

  • Partition-page statistics
  • Bitmap pages
  • Partition blobpages
  • Blobspace blobpages
  • Indexes
  • Sbspace pages
  • Metadata partitions for sbspaces

If oncheck detects inconsistencies in other structures, messages alert you to these inconsistencies, but oncheck cannot resolve the problem.

Repairing indexes in sbspaces and external spaces

The oncheck utility can repair an index in an sbspace or external space if the index is created using an access method that supports the oncheck -y option. Although the oncheck utility does not repair fragmented indexes, user-defined access methods can repair them.

Locking and oncheck

The oncheck utility places a shared lock on a table during the following operations, so no other users can perform updates, inserts, or deletes until the check has completed:

  • When it checks data
  • When it checks indexes (with -ci, -cI, -pk, -pK, -pl, or -pL), and the table uses page locking
  • When you specify the -x option with -ci, -cI, -pk, -pK, -pl, or -pL, and the table uses row locking

If the table does not use page locking, the database server does not place a shared lock on the table when you check an index with the oncheck -ci, -cI, -pk, -pK, -pl, or -pL options. When no shared lock is on the table during an index check, other users can update rows during the check.

By not placing a shared lock on tables using row locks during index checks, the oncheck utility cannot be as accurate in the index check. For absolute assurance of a complete index check, you can execute oncheck with the -x option. With the -x option, oncheck places a shared lock on the table, and no other users can perform updates, inserts, or deletes until the check has completed.


Message log

The database server message log is an operating system file. The messages contained in the database server message log do not usually require immediate action.

Location

To specify the message log path name, set the MSGPATH configuration parameter. The changes to MSGPATH take effect after you shut down and restart the database server.

Monitoring message log

You should monitor the message log once or twice a day to ensure that processing is proceeding normally and that events are being logged as expected. Use the onstat -m command to obtain the name of the message log and the 20 most recent entries. Use a text editor to read the complete message log. Use an operating system command (such as the UNIX command tail -f) to see the messages as they occur.

Monitor the message log size, because the database server appends new entries to this file. Edit the log as needed, or back it up to tape and delete it.

If the database server experiences a failure, the message log serves as an audit trail for retracing the events that develop later into an unanticipated problem. Often the database server provides the exact nature of the problem and the suggested corrective action in the message log.

You can read the database server message log for a minute-by-minute account of database server processing in order to catch events before a problem develops. However, you do not need to perform this kind of monitoring.

Message categories

Four general categories of unnumbered messages exist, although some messages fall into more than one category:

  • Routine information
  • Assertion-failed messages
  • Administrative action needed
  • Fatal error detected

Examples of message log messages

Listing 7. Example of routine messages
Wed Apr 18 19:01:23 2007

22:56:00  Maximum server connections 284 
22:56:03  Level 1 Archive started on dbsherm05
23:23:40  Logical Log 42173 Complete, timestamp: 0xeee826ad.
23:23:41  Logical Log 42173 - Backup Started
23:24:26  Logical Log 42173 - Backup Completed
23:26:30  Checkpoint Completed: duration was 29 seconds, 36019 buffers not flushed.
23:26:30  Checkpoint loguniq 42174, logpos 0x281d658, timestamp: 0xeeece592
Listing 8. Example of assertion-failed messages
19:12:26   Who: Session(33, informix@rdbms2, 0, 25da90ba8)
		Thread(63, xchg_1.6, 25da5d308, 1)
		File: rspartn.c Line: 2426
19:12:26   Results: Could not complete operation on 'erv:"informix".stmy'
19:12:26   Action: Run 'oncheck -cDI ermes:"informix".avvisi_out_tot_e2'
19:12:26  stack trace for pid 1474 written to /appl/InformixDump/af.4274f8a
19:12:26   See Also: /appl/InformixDump/af.4274f8a
19:12:38  ptmap
19:12:38  Assert Failed: ptmap
Listing 9. Example of administrative action needed messages
11:05:49 Maximum server connections 4 
11:08:05 Logical Log Files are Full -- Backup is Needed
Listing 10. Example of fatal error messages
15:24:33  IBM Informix Dynamic Server Started.
15:24:33  shmget: [EEXIST][17]: key 52584801: shared memory already exists
15:24:33  mt_shm_init: can't create resident segment

15:40:09  Assert Failed: Unexpected virtual processor termination, 
                  pid = 526411, exit = 0x9

Event alarms

The database server provides a mechanism for automatically triggering administrative actions based on an event that occurs in the database server environment. This mechanism is the event-alarm feature. Events can be informative (for example, backup complete) or can indicate an error condition that requires your attention (for example, unable to allocate memory). To use the event-alarm feature, set the ALARMPROGRAM configuration parameter to the full pathname of an executable file that performs the necessary administrative actions.

The database server can execute a program that operates either whenever certain noteworthy event alarms occur or every time any event alarm occurs. Noteworthy event alarms include failure of a database, table, index, chunk, or dbspace taken offline, internal subsystem failure, start-up failure, and detection of long transaction. You can receive notification of an event alarm through e-mail or pagermail.

Use the following parameters in Table 7 to specify:

  • Whether the event-alarm program operates for all or only certain event alarms
  • What actions to take when alarm events occur
Table 7. Event-alarm parameters
ParameterDescription
ALRM_ALL_EVENTSSpecifies whether ALARMPROGRAM runs for all events that are logged in the MSGPATH or only specified noteworthy events
ALARMPROGRAMSpecifies the location of a file that is executed when an event alarm occurs

Operating system tools

UNIX tools

System activity reporter (SAR)

The sar command is useful for monitoring CPU utilization, disk activity, and memory utilization.

Listing 11. sar example
$ sar -u 60 10
09:42:17   %usr %sys %wio %idle
09:43:17    1    5    0    94
09:44:17    1    4    0    95
09:45:17    5    3    0    92
09:46:17    4    6    1    89

The example shown in Lising 11 monitors CPU utilization at intervals of 60 seconds for 10 iterations.

To identify potential or actual performance bottlenecks or problems, it is necessary to continuously monitor your system and capture these data points over an extended period of time.

The UNIX sar command is particularly useful for creating and maintaining long-term performance histories:

  • sar is available on all IBM Informix IDS-hardware platforms
  • When run with a named output file, a single binary-encoded file is created from which all sar reports can be generated at a later time

time or timex

The time and timex commands allow you to time the running of a process. Both time and timex report on real time as well as user and system CPU time.

ps

The ps command is a good source of snapshot information about system processes currently running.

Listing 12. Process status (ps)
$ ps -el
S UID   PID   PPID    STIME      TTY   TIME    CMD
T root  0     0       08:59:54    ?    0:01    sched
S root  1     0       08:59:57    ?    0:00    /etc/init
S root  434   1       09:03:51    ?    0:05    oninit
S root  435   434     09:03:53    ?    0:00    oninit
S root  445   434     09:06:02    ?    0:00    oninit

where PID is Process Id
      STIME is process start time
      TIME is accumulated CPU time for process

iostat

The iostat command provides highly accurate measures of throughput, utilization, queue lengths, transaction rates, and service times.

Listing 13. The iostat command
$ iostat -x 5 1
                                  extended device statistics
device r/s   w/s   Kr/s   Kw/s   wait   actv   svc_t   %w   %b
sd0    6.2   0.0   21.5   0.0    0.0    0.1    24.1    0    15
sd1    1.8   0.0   14.3   0.0    0.0    0.1    41.6    0    7
sd6    0.0   0.0   0.0    0.0    0.0    0.0    0.0     0    0
sd30   0.2   0.2   25.7   0.2    0.0    0.1    22.5    0    13

vmstat

The vmstat command provides information about the status of processes, memory utilization, paging statistics, system activity, and CPU usage.

Listing 14. The iostat command
$ vmstat 5 4
procs       memory            page             disk    faults

r b w   swap     fre    re  mf  pi   po ...  d0 d1 d2 in sy ...
4 1 0   104512   1792   8   0   288  192 ... 4  6  1  23 15 ...
2 3 0   105184   1952   4   0   96   128 ... 0  2  1  14 15 ...
3 2 0   106688   1952   7   0   256  224 ... 4  4  12 29 21 ...
4 2 0   104672   6240   4   0   384  32 ...  3  1  3  2  75 ...

where w is processes swapped out page outs 
      swap is Kbytes of swap space available
      d0 is # of disk operations/sec
      p0 is pageouts

Windows event viewer

The event viewer shows informational, warning, and error messages for the operating system, other applications, and the database server.

To display database server messages on Windows:

  1. Choose Administrative Tools > Event Viewer.
  2. Choose Log > Security.
  3. Double-click any event for a more detailed message.

Windows utilities

The following Informix utilities simplify administration of the database server on Windows.

ixpasswd.exe

ixpasswd.exe changes the logon password for all services that log on as user informix. You can change the password interactively or on the command line using the -y option. This utility saves having to manually change the password for each service whenever you change the informix password.

If you are logged on locally and run ixpasswd, it changes the password for services that log on as the local informix user. If you are logged on domain and run ixpasswd, it changes the password for services that log on as domain\informix.

Usage:

ixpasswd [-y new_password]

ixsu.exe

Launches a command-line window that runs as the specified user. The user is a local user unless you specify a domain name. If you do not specify a user name, the default user is informix. You no longer need to log off as the current user and log on as informix to perform DBA tasks that need to be run as informix. The ixsu utility requires advanced user rights:

Act as part of the operating system

  • Increase quotas
  • Replace a process-level token
  • To configure advanced user rights on Windows NT:
    1. Select User Manager > Policies > User Rights
    2. Check the Advanced User Rights option.

      Note: If you change the advanced user rights for the current user, you need to log off and log back on for the new rights to take effect

Usage:

ixsu [[domain\]username]

The ixsu utility is equivalent to the Windows 2000 runas command. To use runas to run a command shell as another user:

Usage:

runas /user:username cmd

ntchname.exe

ntchname.exe changes the registry entries for Dynamic Server from the old hostname to the new hostname. Run ntchname after you change the hostname. This utility does not change user environment variables. After you execute ntchname, edit the %INFORMIXDIR%\%INFORMIXSERVER%.cmd file and change the INFORMIXSQLHOSTS entry to the new hostname.

Usage:

ntchname old_name new_name

Conclusion

This tutorial introduced you to the set of monitoring tools that are available with IDS 11 and showed you how each are used to monitor how well (or how poorly) your database system is operating. Database monitoring is a vital activity that, when performed on a regular basis, provides continuous feedback on the health of a database system.

The SQL administration API enables you to perform remote administration using various, specific SQL commands for tasks such as managing spaces, managing configuration, running routine jobs, and system validation.

The scheduler manages and executes scheduled maintenance, monitoring, and administration tasks. This tool enables you to monitor activities (for example, space management or automatically backing up any new log data at timed intervals since the last log backup) and create corrective actions that run automatically.

The system-monitoring interface (SMI) tables are special tables managed by the database server that contain dynamic information about the state of the database server. You can use SELECT statements on them to determine almost anything you might want to know about your database server.

The onstat utility provides a way to monitor database server shared memory from the command line. The onstat utility reads data from shared memory and reports statistics that are accurate for the instant during which the command executes. That is, onstat provides information that changes dynamically during processing, including changes in buffers, locks, indexes, and users.

The oncheck utility displays information about the database disk configuration and usage, such as the number of pages used for a table, the contents of the reserved pages, and the number of extents in a table.

The database server message log is an operating system file. You can read the database server message log for a minute-by-minute account of database server processing in order to catch events before a problem develops.

Event alarm feature is the mechanism for automatically triggering administrative actions based on an event that occurs in the database server environment.

Part 3 of the tutorial series helps you troubleshoot the database server problems.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=223516
ArticleTitle=System Administration Certification exam 918 for IBM Informix Dynamic Server 11 prep, Part 2: System activity monitoring
publish-date=05162007