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:
-
global database performance
-
the performance of individual
SQL statements
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:
-
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
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:
-
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.
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:
-
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..
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:
-
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.
Notification
Facility
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
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:
-
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.
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.
-
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
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:
-
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 and SQL/MF MONITOR 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
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.