SQL/Monitoring Facility: Product Summary

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

The product provides database administrators with detailed information about:

Monitor information is available for dynamic queries and compiled applications. SQL/MF monitors all DB2 clients, including DRDA and TCP/IP clients.

Monitoring 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:  

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:  

Statement Statistics

When an SQL statement completes execution, SQL/MF stores all statistics recorded for the statement in its SQL_Statements table. This table contains:   By consulting this table, the DBA can always obtain the monitor information for the last execution of any SQL statement.  

Package Statistics

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.  

Session Run Statistics

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.  

Exception Logging

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:   Governor Facility

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:

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.  

Notification Facility

The notification facility may be requested to send a message to a designated user when:  

Package Benchmarking

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.  

Statement Recording

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 subtask, running in the SQL/MF service partition. The Recorder file is a VSAM cluster.  

Lockwait Recording

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

Checkpoint Recording

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

AutoPrep Facility

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:  

The solution entirely avoids the cost associated with path determination.  

Statement Analysis

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.

System Monitoring

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.
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.
Records agents in the LOCK wait state and provides information about the locks being held.
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:  


SQL/MF User Interface

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 CICS application. The user interface provides access to:

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  

Customizing SQL/MF

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.
User Attached Process facility
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.
Connect exit
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.
Monitor Tables
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.

Software prerequisites

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