The SQL/Monitoring Facility (SQL/MF) is a state-of-the-art execution-time monitor for DB2/VM with many unique and exciting features.

The product provides database administrators with detailed information about:

• global database performance
• the performance of individual SQL statements

Statement Capturing

In most cases, degradation of DB2 performance is due to SQL statements that do not perform adequately. Correcting these statements will often result in significant performance improvements for the entire database. Therefore, monitoring at the SQL statement level is essential.

The SQL/MF Statement Monitor continuously notes the execution characteristics of each SQL statement in progress and records, for each statement:
 

• detailed runtime statistics (CPU-time, I/O waittime, number of rows and pages processed, number of RDS and DBSS calls, etc.)
• the statement access path (for example, the name of the index)
• the dynamic or compiled statement text (with all statement variables replaced by their contents)


The DBA has real-time access to these data, using the Running Statement List  and Statement Detail functions of the SQL/MF user interface. The interface also allows the DBA to act upon any running statement in order to:
 

• get catalog information for the table, columns or index used by the statement
• perform lock analysis
• execute commands, such as a DB2 FORCE

When an SQL statement completes execution, SQL/MF stores all statistics recorded for the statement in its SQL_Statements table. This table contains:
 
• the text of the statement
• the statement's access path
• the runtime statistics for the last execution


By consulting this table, the DBA can always obtain the monitor information for the last execution of any SQL statement.

For all packages executed during a DB2 session, SQL/MF maintains the package's resource usage in its Package Statistics table. This table is very suitable for accounting purposes.

The RunStats function provides a graphical and ordered presentation of resource usage by users and packages during the current DB2 session. The statistics show users, packages and package statements by descending SQL cost. If requested, the RunStats will be kept in a DB2 table for a user-defined number of days.

An installation may define a number of exception criteria in the SQL/MF configuration file. When an SQL statement exceeds one of these exception limits during its execution, it is recorded in the SQL/MF Exception table. Exception criteria are specified as:
 
• a maximum number of I/O requests
• a maximum statement response time
• a maximum lock wait time
• a maximum idle time
• a defined range of SQLCODEs
• a "lock escalate" event etc..

SQL/MF not only records database performance, its Governor facility also prevents excessive use of database resources by users and packages. The facility continuously monitors all users and forces them off the database, when a resource restriction is violated. These restrictions are defined in the SQL/MF configuration file as a maximum amount of system resources, a user is allowed to consume, for example:
 
• a maximum number of I/O requests
• a maximum statement response time
• a maximum time in lockwait or lockhold
• a maximum idle time while in LUW
• a maximum cost for dynamic SQL statements


The Governor facility can be enabled for all users and applications, both compiled and dynamic. The Governor will automatically break database bottlenecks, such as database locks or excessive I/O load.

The notification facility may be requested to send a message to a designated user when:
 
• an SQL statement performs more than a defined number of buffer lookups or I/O requests
• an SQL statement exceeds a defined response time
• a user session is idle for a defined period
• an SQL statement is in the lockwait state for a defined period
• a dynamic SQL statement exceeds a defined SQL cost

The benchmark facility records the execution statistics of all SQL statements executed by a designated package into the Benchmark table. These table entries show the "behaviour" of the benchmarked statements.

The Statement Recording facility registers all SQL statements executed during the recording period into a Recorder file. The recorder entries show the statement text, the access path and all execution statistics. Recording can be initiated on a periodical basis. Alternatively, recording can be started by command, to record named applications, users or terminals. To achieve acceptable performance, the facility records into a dataspace, which is written asynchronously to the recorder file by a disconnected virtual machine. The Recorder is a CMS file.

If requested, the facility maintains a chronological log of all lockwait events during the DB2 session.

The Checkpoint Recorder notes the occurrence, duration and resource usage of every DB2 system checkpoint.

The purpose of the AutoPrep facility is to reduce the cost of dynamic SQL requests.

Dynamic SQL implies that DB2 determines the access strategy before every execution of the dynamic SQL. However, access path determination is a costly process in terms of CPU usage and catalog contention.

Today, with increased PC database access, e-business and ERP applications, dynamic SQL tends to become a performance bottleneck.
AutoPrep offers a solution by:
 

• detecting frequently executed dynamic SQL statements
• initiating a DB2 prep for these statements (in the AutoPrep server)
• replacing the dynamic sequence with a static one that invokes the generated package.


The solution entirely avoids the cost associated with path determination.

With our SQL/Command Analysis product installed, running statements and statements recorded in the SQL/MF tables may be submitted for EXPLAIN and predicate analysis.

While the facilities described above, monitor individual SQL statements, the SQL/MF System Monitor component provides a global view of database performance. At a user-defined sampling interval, the System Monitor takes a snapshot of the monitored databases and records following data in the System Monitor tables:
 

Global Performance

Records the database resource consumption, as obtained from the DB2 system counters.

Buffer Pool

Records the usage that DBspaces and storage pools are making of the buffer pool and provides information on the distribution of buffer pool accesses among individual DBspaces.

Data Spaces

Records the performance of the DB2/VM Data Spaces facility.

Storage Pools

Records physical DASD space usage and short-on-storage conditions for each pool.

DB2 Log

Records space consumption on the DB2 system log.

User Activity

Records the status of all agents active at the monitor interval.

Locks

Records agents in the LOCK wait state and provides information about the locks being held.

Connections

Records the state of the DB2 connections at each monitor interval.

Checkpoint Monitoring

Records the frequency and the duration of system-initiated checkpoints and signals eventual checkpoint delays.


System Monitor Graphs

The System Graph function provides a graphical view of system activity. It shows, for a given time period, the number of:
 

• LUWs
• RDS calls
• buffer lookups
• I/O requests

All data collected by SQL/MF are examined from a single application, using structured menus and a standard PFkey interface. The user interface is a CMS application.

The user interface provides access to:
 

• the Running Statements function
• the Statement Detail function
• the Statement Monitor tables, using a table editor that comes with SQL/MF
• the System Monitor tables, using the SQL/MF table editor
• the Session Run Statistics
• the Statement Recorder and Lockwait Recorder files
• the graphical data interfaces
• the Host Command interface for submitting DB2 operator commands, SQL/MF MONITOR commands, CP and CMS commands


Catalog Navigator

When examining monitor reports, it is often necessary to consult the DB2 catalogs. While the Objects function of the SQL/MF Table Editor already provides limited catalog access, the Navigator offers a general-purpose, easy-to-use and full-screen interface to every catalog table. In addition, every catalog list provides access to related catalog tables (for example: the indexes for a table, the grants on a package and so on). Since "related" lists can be nested, the Navigator is a versatile tool to quickly locate the requested catalog data. When in a table list, the Navigator can also edit user tables (if the necessary DB2 privileges are present).

Sample Navigator session

User Reports

SQL/MF comes with 63 interactive reports, which are invoked from the Report Menu. Because all SQL/MF tables are regular DB2 tables, an installation can easily write its own monitor reports. These user reports are automatically added to the Report Menu.

Attached processes are user-written REXX programs, invoked during monitoring. The processes have real-time access to the monitored data. They enable an installation to incorporate its own monitoring procedures into SQL/MF.

A connect exit is a REXX program invoked whenever a DB2 user issues an explicit CONNECT statement. The exit receives the connect parameters as invocation arguments and can reject the connect, if desired.

SQL/MF stores its monitoring results in DB2 tables. These tables are processed by the SQL/MF user interface, but are also available for user processing.

The product supports all current versions of z/VM and DB2.