Monitor profiles capture information about the normal performance
of a specific query workload, or monitor for exceptions in the performance
of a set of SQL statements.
Two
types of monitor profiles
are available:
- You use normal monitor profiles to determine
which SQL statements run
in an environment, how often each query runs, and the overall costs
in terms of CPU and elapsed time for each query.
- You use exception
monitor profiles to specify thresholds for query
performance and detect SQL statements that exceed the thresholds.
You can create, schedule, enable, start, stop, and disable monitor
profiles to capture information about the SQL statements that run on DB2® for z/OS® Version
9 new-function mode subsystems and data sharing groups from the .
At specified consolidation intervals, the information about the
statements is written to the subsystem. You can then use the workflow
assistant for query tuning to analyze and tune the workload, or select
individual SQL statements for detailed analysis and tuning. You can also
capture a snapshot of the monitored SQL statements to consolidate the monitored
statements immediately.
Each monitor profile can be enabled
or disabled on the subsystem.
When you start monitoring, all enabled monitor profiles on the same
subsystem are started, including any enabled monitor profiles that
are scheduled to start at a later time.
Monitor profiles have
the following attributes that identify them
and define their function:
- Monitor Name
- The
monitor name must be unique on the subsystem.
- Owner
- The authorization ID that owns the monitor profile.
- Type
- A monitor profile can be a normal
or exception profile.
- Source
- Each
source is a context that SQL statements are captured from. You can
capture either dynamic or embedded statements from each source.
- Filter
- The filter for the monitor profile
is different depending on the
type of monitor profile. For each monitor profile, you can choose
whether to capture the following information:
- EXPLAIN information
for the monitored statements
- The number of executions and
the accumulated CPU time
- A full set of runtime information
for the monitored statements
Capturing the EXPLAIN information
when monitoring statements
requires slightly more resources, but can speed the process of tuning
the captured workload. Capturing the full runtime information can
slow the performance of the SQL statements because this setting can increase
CPU processing of approximately 10%.
- Start
time
- You can start a monitor profile by choosing one of the
following
methods:
- Start the monitor profile immediately when you create
it.
- Schedule a future start time.
- Create the monitor
profile in an enabled state so that it starts
when you start monitoring.
- Create the monitor profile in a
disabled state, so that it does
not start when you start monitoring.
- Consolidation interval
- The statements that are captured
by the monitor profile are consolidated
at the interval that you specify.