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:
Monitor information is available
for dynamic queries and compiled applications.
global database performance
the performance of individual
SQL/MF monitors all DB2
clients, including DRDA and TCP/IP clients.
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
When an SQL statement completes
execution, SQL/MF stores all statistics recorded for the statement in its
table. This table contains:
the text of the statement
the statement's access path
the runtime statistics for the
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
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
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
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
a maximum time in lockwait or
a maximum idle time while in
a maximum cost for dynamic SQL
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
a user session is idle for a
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
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:
Records the database resource
consumption, as obtained from the DB2 system counters.
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.
Records the performance of the
DB2/VM Data Spaces facility.
Records physical DASD space
usage and short-on-storage conditions for each pool.
Records space consumption on
the DB2 system log.
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.
Records the frequency and the
duration of system-initiated checkpoints and signals eventual checkpoint
Graph function provides a graphical view of system activity. It shows,
for a given time period, the number of:
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
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
the graphical data interfaces
the Host Command interface for
submitting DB2 operator commands, SQL/MF MONITOR commands, CP and CMS commands
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).
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 Process facility
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.