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:
Monitoring SQL statements
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:
Package StatisticsFor 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 StatisticsThe 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 LoggingAn 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:
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 FacilityThe notification facility may be requested to send a message to a designated user when:
Package BenchmarkingThe 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 RecordingThe 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 RecordingIf requested, the facility maintains a chronological log of all lockwait events during the DB2 session.
Checkpoint RecordingThe Checkpoint Recorder notes the occurrence, duration and resource usage of every DB2 system checkpoint.
AutoPrep FacilityThe 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:
System MonitoringWhile 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:
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 InterfaceAll data collected by SQL/MF are examined from a single application, using structured menus and a standard PFkey interface.
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
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.