Monitor profiles capture information about the normal performance
of a specific query workload that runs on DB2® for z/OS®,
or monitor for exceptions in the performance of a set of SQL statements
that run on DB2 for z/OS.
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.