acc_curs_blk - Accepted Block Cursor Requests monitor element
The number of times that a request for an I/O block was accepted.
- Element identifier
- acc_curs_blk
- Element type
- counter
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Connection | event_conn | Always collected |
- Usage
- You can use this element in conjunction
with rej_curs_blk to calculate the percentage of blocking
requests that are
accepted, rejected, or both.
See rej_curs_blk for suggestions on how to use this information to tune your configuration parameters.
act_aborted_total - Total aborted activities monitor element
The total number of coordinator activities at any nesting level that completed with errors. For service classes, if an activity is remapped to a different service subclass with a REMAP ACTIVITY action before it aborts, then this activity counts only toward the total of the subclass it aborts in.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
Usage
Use this element to understand if activities on the system are completing successfully. Activities may be aborted due to cancellation, errors, or reactive thresholds.
act_completed_total - Total completed activities monitor element
The total number of coordinator activities at any nesting level that completed successfully. For service classes, if an activity is remapped to a different subclass with a REMAP ACTIVITY action before it completes, then this activity counts only toward the total of the subclass it completes in.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | - |
Usage
Use this element to determine the throughput of activities in the system.
act_cpu_time_top - Activity CPU time top monitor element
The high watermark for processor time used by activities at all nesting levels in a service class, workload, or work class. This value is reported in microseconds.
The monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the service class or workload in which the activity runs is set to NONE. Activities contribute toward this high watermark only when request metrics are enabled. If the collection of activity metrics is not enabled, a value of 0 is returned.
For service classes, when you remap activities between service subclasses with a REMAP ACTIVITY action, only the act_cpu_time_top high watermark of the service subclass where an activity completes is updated, provided that a new high watermark is reached. The act_cpu_time_top high watermarks of other service subclasses an activity is mapped to but does not complete in are unaffected.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats | Always collected |
| Statistics | event_wcstats | Always collected |
| Statistics | event_wlstats | Always collected |
Usage
Use this element to determine the highest amount of processor time used by an activity on a member for a service class, workload, or work class during the time interval collected.
act_exec_time - Activity execution time monitor element
The act_exec_time element stores
the time spent executing at this member, in microseconds.
For cursors, the execution time is the combined time for
the open, the fetches, and the close. The time when the cursor is
idle is not counted toward execution time. For routines, execution
time is the start to end of routine invocation. The lifetimes of
any cursors left open by routine (to return a result set) after the
routine finishes are not counted toward the routine execution time.
For all other activities, execution time is the difference between
start time and stop time. In all cases, execution time does not include
time spent initializing or queued.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | Always collected |
Usage
This element can be used alone to
know the time spent by the database manager to execute the activity on each member. This element can also be
used together with time_started and time_completed monitor
elements on the coordinator member to compute the idle time for cursor
activities. You can use the following formula:
Cursor idle time = (time_completed - time_started) - act_exec_timeact_rejected_total - Total rejected activities monitor element
The total number of coordinator activities at any nesting level that were rejected instead of being allowed to execute.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | - |
Usage
Use this element to help determine whether predictive thresholds and work actions that prevent execution are effective and whether they are too restrictive.
act_remapped_in - Activities remapped in monitor element
Count of the number of activities to be remapped into this service subclass since the last reset.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats | - |
Usage
Use this count to determine whether the remapping of activities into the service subclass is occurring as desired.
act_remapped_out - Activities remapped out monitor element
Count of the number of activities to be remapped out of this service subclass since the last reset.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats | - |
Usage
Use this count to determine whether the remapping of activities out of the service subclass is occurring as desired.
act_rows_read_top - Activity rows read top monitor element
The high watermark for the number of rows read by activities at all nesting levels in a service class, workload, or work class.
The monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the service class or workload in which the activity runs is set to NONE. Activities contribute toward this high watermark only when request metrics are enabled. If the collection of activity metrics is not enabled, a value of 0 is returned.
For service classes, when you remap activities between service subclasses with a REMAP ACTIVITY action only the act_rows_read_top high watermark of the service subclass where an activity completes is updated, provided that a new high watermark is reached. The act_rows_read_top high watermarks of service subclasses an activity is mapped to but does not complete in are unaffected.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats | Always collected |
| Statistics | event_wcstats | Always collected |
| Statistics | event_wlstats | Always collected |
Usage
Use this element to determine the highest number of rows read by an activity on a member for a service class, workload, or work class during the time interval collected.
act_rqsts_total - Total activity requests monitor elements
The number of individual coordinator and subagent requests completed as part of an activity. For example, a fetch on a cursor activity.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
act_throughput - Activity throughput monitor element
The rate at which coordinator activities are completed at any nesting level. Measured in coordinator activities per second.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_SAMPLE_SERVICE_CLASS_METRICS - Get sample service class metrics | REQUEST METRICS BASE |
| MON_SAMPLE_WORKLOAD_METRICS - Get sample workload metrics | REQUEST METRICS BASE |
| MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| MON_GET_WORKLOAD_STATS table function - Return workload statistics | Always collected |
| WLM_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| WLM_GET_WORKLOAD_STATS table function - Return workload statistics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | Always collected |
| Statistics | event_wlstats (reported in the metrics document) | Always collected |
Usage
When returned by the WLM_GET_SERVICE_SUBCLASS_STATS or the WLM_GET_WORKLOAD_STATS function, this monitor element represents the activity throughput since the last reset of the statistics.
When returned by the MON_SAMPLE_SERVICE_CLASS_METRICS or the MON_SAMPLE_WORKLOAD_METRICS function, this monitor element represents the activity throughput since the function was executed.
act_total - Activities total monitor element
Total number of activities at any nesting level that had work actions corresponding to the specified work class applied to them since the last reset.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_WORK_ACTION_SET_STATS table function - Return work action set statistics | Always collected |
| WLM_GET_WORK_ACTION_SET_STATS table function - Return work action set statistics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_wcstats | Always collected |
Usage
Every time an activity has one or more work actions associated with a work class applied to it, a counter for the work class is updated. This counter is exposed using the act_total monitor element. The counter can be used to judge the effectiveness of the work action set (for example, how many activities have a actions applied). It can also be used to understand the different types of activities on the system.
activate_timestamp - Activate timestamp monitor element
The time when an event monitor was activated.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activity | event_activity | - |
| Activity | event_activitystmt | - |
| Activity | event_activityvals | - |
| Threshold Violations | event_thresholdviolations | - |
Usage
Use this element to correlate information returned by the previously mentioned event types.
active_col_vector_consumers - Active columnar vector memory consumers monitor element
The number of columnar vector memory consumers that are active.
active_col_vector_consumers_top - Active columnar vector memory consumers high watermark monitor element
The high watermark for the number of columnar vector memory consumers that were active at any one time.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_ACTIVITY table function - return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - return information about an activity as an XML document | Always collected |
| MON_GET_DATABASE table function - get database information metrics | Always collected |
| MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document | Always collected |
| MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics | Always collected |
| MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document | Always collected |
| MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| MON_GET_WORKLOAD_STATS table function - Return workload statistics | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activities | event_activity | Always collected |
| Package Cache | pkgcache | Always collected |
| Statistics | event_scstats | Always collected |
| Statistics | event_wlstats | Always collected |
| Unit of Work | uow | Always collected |
active_hash_grpbys - Active hash GROUP BY operations monitor element
The number of GROUP BY operations using hashing as their grouping method that are currently running and consuming sort heap memory.
active_hash_grpbys_top - Active hash GROUP BY operations high watermark monitor element
The high watermark for the number of hash GROUP BY operations that were active at any one time.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_ACTIVITY table function - return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - return information about an activity as an XML document | Always collected |
| MON_GET_DATABASE table function - get database information metrics | Always collected |
| MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document | Always collected |
| MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics | Always collected |
| MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document | Always collected |
| MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| MON_GET_WORKLOAD_STATS table function - Return workload statistics | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activities | event_activity | Always collected |
| Package Cache | pkgcache | Always collected |
| Statistics | event_scstats | Always collected |
| Statistics | event_wlstats | Always collected |
| Unit of Work | uow | Always collected |
active_hash_joins - Active hash joins monitor element
The total number of hash joins that are currently running and consuming memory.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | - |
active_hash_joins_top - Active hash join operations high watermark monitor element
The high watermark for the number of hash join operations that were active at any one time.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_ACTIVITY table function - return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - return information about an activity as an XML document | Always collected |
| MON_GET_DATABASE table function - get database information metrics | Always collected |
| MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document | Always collected |
| MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics | Always collected |
| MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document | Always collected |
| MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| MON_GET_WORKLOAD_STATS table function - Return workload statistics | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activities | event_activity | Always collected |
| Package Cache | pkgcache | Always collected |
| Statistics | event_scstats | Always collected |
| Statistics | event_wlstats | Always collected |
| Unit of Work | uow | Always collected |
active_olap_funcs - Active OLAP Functions monitor element
The total number of OLAP functions that are currently running and consuming sort heap memory.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | - |
active_olap_funcs_top - Active OLAP function operations high watermark monitor element
The high watermark for the number of OLAP function operations that were active at any one time.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_ACTIVITY table function - return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - return information about an activity as an XML document | Always collected |
| MON_GET_DATABASE table function - get database information metrics | Always collected |
| MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document | Always collected |
| MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics | Always collected |
| MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document | Always collected |
| MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| MON_GET_WORKLOAD_STATS table function - Return workload statistics | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activities | event_activity | Always collected |
| Package Cache | pkgcache | Always collected |
| Statistics | event_scstats | Always collected |
| Statistics | event_wlstats | Always collected |
| Unit of Work | uow | Always collected |
active_peas - Active partial early aggregation operations monitor element
The number of partial early aggregation operations that are active.
active_peas_top - Active partial early aggregation operations high watermark monitor element
The high watermark for the number of partial early aggregation operations that were active at any one time.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_ACTIVITY table function - return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - return information about an activity as an XML document | Always collected |
| MON_GET_DATABASE table function - get database information metrics | Always collected |
| MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document | Always collected |
| MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics | Always collected |
| MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document | Always collected |
| MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| MON_GET_WORKLOAD_STATS table function - Return workload statistics | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activities | event_activity | Always collected |
| Package Cache | pkgcache | Always collected |
| Statistics | event_scstats | Always collected |
| Statistics | event_wlstats | Always collected |
| Unit of Work | uow | Always collected |
active_peds - Active partial early distinct operations monitor element
The number of partial early distinct operations that are active.
active_peds_top - Active partial early distinct operations high watermark monitor element
The high watermark for the number of partial early distinct operations that were active at any one time.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_ACTIVITY table function - return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - return information about an activity as an XML document | Always collected |
| MON_GET_DATABASE table function - get database information metrics | Always collected |
| MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document | Always collected |
| MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics | Always collected |
| MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document | Always collected |
| MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| MON_GET_WORKLOAD_STATS table function - Return workload statistics | Always collected |
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activities | event_activity | Always collected |
| Package Cache | pkgcache | Always collected |
| Statistics | event_scstats | Always collected |
| Statistics | event_wlstats | Always collected |
| Unit of Work | uow | Always collected |
active_sort_consumers - Active sort memory consumers monitor element
The total number of currently active sort memory consumers.
Usage
You can use the active_sort_consumers and active_sort_consumers_top monitor elements to help tune the use of sort heap memory and workload concurrency. For example, you can use the MON_GET_DATABASE table function to retrieve the monitor element values for all database members. Knowing the number of concurrently active sort consumers and the high watermark over a period of time can help you adjust the value of the sheapthres_shr configuration parameter to better accommodate the concurrent sort activities.
active_sort_consumers_top - Active sort memory consumers high watermark monitor element
The high watermark for the number of active sort memory consumers.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_ACTIVITY table function - return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - return information about an activity as an XML document | Always collected |
| MON_GET_DATABASE table function - get database information metrics | Always collected |
| MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document | Always collected |
| MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics | Always collected |
| MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document | Always collected |
| MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| MON_GET_WORKLOAD_STATS table function - Return workload statistics | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activities | event_activity | Always collected |
| Package Cache | pkgcache | Always collected |
| Statistics | event_scstats | Always collected |
| Statistics | event_wlstats | Always collected |
| Unit of Work | uow | Always collected |
Usage
You can use the active_sort_consumers and active_sort_consumers_top monitor elements to help tune the use of sort heap memory and workload concurrency. For example, you can use the MON_GET_DATABASE table function to retrieve the monitor element values for all database members. Knowing the number of concurrently active sort consumers and the high watermark over a period of time can help you adjust the value of the sheapthres_shr configuration parameter to better accommodate the concurrent sort activity.
active_sorts - Active Sorts monitor element
The number of sorts in the database that currently have a sort heap allocated.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
- Usage
- Use this value in conjunction with sort_heap_allocated to
determine the average sort heap space used by each sort. If the sortheap configuration
parameter is substantially larger than the average sort heap used,
you may be able to lower the value of this parameter.
This value includes heaps for sorts of temporary tables that were created during relational operations.
active_sorts_top - Active sorts high watermark monitor element
The high watermark for the number of sort operations that were active at any one time.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_ACTIVITY table function - return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - return information about an activity as an XML document | Always collected |
| MON_GET_DATABASE table function - get database information metrics | Always collected |
| MON_GET_DATABASE_DETAILS table function - get database information metrics as an XML document | Always collected |
| MON_GET_PKG_CACHE_STMT table function - get package cache statement metrics | Always collected |
| MON_GET_PKG_CACHE_STMT_DETAILS table function - Get package cache statement metrics as an XML document | Always collected |
| MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses | Always collected |
| MON_GET_WORKLOAD_STATS table function - Return workload statistics | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activities | event_activity | Always collected |
| Package Cache | pkgcache | Always collected |
| Statistics | event_scstats | Always collected |
| Statistics | event_wlstats | Always collected |
| Unit of Work | uow | Always collected |
activity_collected - Activity collected monitor element
This element indicates whether or not activity event monitor records are to be collected for a violated threshold.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Threshold violations | event_thresholdviolations | - |
Usage
Use this element to determine whether to expect an activity event for the activity that violated the threshold to be written to the activity event monitor.
When an activity finishes or aborts and the activity event monitor is active at the time, if the value of this monitor element is 'Y', the activity that violated this threshold will be collected. If the value of this monitor element is 'N', it will not be collected.
activity_id - Activity ID monitor element
Counter which uniquely identifies an activity for an application within a given unit of work.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| MON_GET_AGENT table function - List agents, fenced mode processes, and system entities for the database | Always collected |
| WLM_GET_SERVICE_CLASS_AGENTS table function - list agents running in a service class | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Locking | - | Always collected |
| Activities | event_activity | Always collected |
| Activities | event_activitystmt | Always collected |
| Activities | event_activityvals | Always collected |
| Activities | event_activitymetrics | Always collected |
| Threshold violations | event_thresholdviolations | Always collected |
Usage
Use this element in conjunction with other activity history elements for analysis of the behavior of an activity.
To uniquely identify an activity outside its unit of work, use the combination of activity_id and uow_id plus one of the following monitor elements: appl_id or agent_id.
activity_secondary_id - Activity secondary ID monitor element
The value for this element is incremented
each time an activity record is written for the same activity.
For
example, if an activity record is written once as a result of having
called the WLM_CAPTURE_ACTIVITY_IN_PROGRESS procedure and a second
time when the activity ends, the element would have a value of 0 for
the first record and 1 for the second record.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | - |
| Activities | event_activitystmt | - |
| Activities | event_activityvals | - |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
Usage
Use this element with activity_id, uow_id, and appl_id monitor elements to uniquely identify activity records when information about the same activity has been written to the activities event monitor multiple times.
For
example, information about an activity would be sent to the activities
event monitor twice in the following case:
- the WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure was used to capture information about the activity while it was running
- information about the activity was collected when the activity completed, because the COLLECT ACTIVITY DATA clause was specified on the service class with which the activity is associated
activity_state - Activity state monitor element
The current state of the activity.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities | Always collected |
Usage
Use this monitor element to understand what the activity is currently doing (for example, is the
activity stuck in a queue or waiting for input from the client). Possible values
include:
| Activity State | Description |
|---|---|
| CANCEL_PENDING | Activity was cancelled when there was no agent actively working on a request for the activity. The next time a request is submitted as part of the activity, the activity will be cancelled and the user who submitted the activity will receive an SQL4725N error. |
| EXECUTING | Agents are actively working on a request for the activity. |
| IDLE | There is no agent actively processing a request for the activity. |
| INITIALIZING | Activity has been submitted, but has not yet started executing. During the initializing state, predictive thresholds are applied to the activity to determine whether or not the activity will be allowed to execute. |
| QP_CANCEL_PENDING | Same as the CANCEL_PENDING state, but the activity was cancelled by query patroller rather than by the WLM_CANCEL_ACTIVITY procedure. |
| QP_QUEUED | Activity is queued by Query Patroller. |
| QUEUED | Activity is queued by a workload management queuing threshold. In a partitioned database environment, this state might mean that the coordinator agent has made an RPC to the catalog partition to obtain threshold tickets and has not yet received a response. Seeing this state might indicate that the activity has been queued by a workload management queuing threshold or, over short periods of time, can just indicate that the activity is in the process of obtaining its tickets. To obtain a more accurate picture of whether or not the activity is really being queued, one can determine which agent is working on the activity and find out whether this agent's EVENT_OBJECT at the catalog partition has a value of WLM_QUEUE. |
| TERMINATING | Activity has completed execution and is being removed from the system. |
activity_type - Activity type monitor element
The type of the activity.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | Always collected |
Usage
The possible values
are:
- LOAD
- READ_DML
- WRITE_DML
- DDL
- CALL
- OTHER
The value OTHER is returned for SET statements that do not perform SQL (for example, SET special register, or SET EVENT MONITOR STATE) and the LOCK TABLE statement.
activitytotalruntime_threshold_id - Activity total runtime threshold ID monitor element
The ID of the ACTIVITYTOTALRUNTIME threshold that was applied to the activity.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities | Always collected |
activitytotalruntime_threshold_value - Activity total runtime threshold value monitor element
A timestamp that is computed by adding the ACTIVITYTOTALRUNTIME threshold duration to the activity execution start time. If the activity is still executing when this timestamp is reached, the threshold will be violated.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities | Always collected |
activitytotalruntime_threshold_violated - Activity total runtime threshold violated monitor element
This monitor element returns '1' (Yes) to indicate that the activity violated the ACTIVITYTOTALRUNTIME threshold. '0' (No) indicates that the activity has not yet violated the threshold.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities | Always collected |
activitytotalruntimeinallsc_threshold_id - Activity total runtime in all service classes threshold ID monitor element
The ID of the ACTIVITYTOTALRUNTIMEINALLSC threshold that was applied to the activity.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities | Always collected |
activitytotalruntimeinallsc_threshold_value - Activity total runtime in all service classes threshold value monitor element
A timestamp that is computed by adding the ACTIVITYTOTALRUNTIMEINALLSC threshold duration to the activity execution start time. If the activity is still executing when this timestamp is reached, the threshold will be violated.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities | Always collected |
activitytotalruntimeinallsc_threshold_violated - Activity total runtime in all service classes threshold violated monitor element
This monitor element returns '1' (Yes) to indicate that the activity violated the ACTIVITYTOTALRUNTIMEINALLSC threshold. '0' (No) indicates that the activity has not yet violated the threshold.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities | Always collected |
activitytotaltime_threshold_id - Activity total time threshold ID monitor element
The ID of the ACTIVITYTOTALTIME threshold that was applied to the activity.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
Usage
Use this element to understand which ACTIVITYTOTALTIME threshold, if any, was applied to the activity.
activitytotaltime_threshold_value - Activity total time threshold value monitor element
A timestamp that is computed by adding the ACTIVITYTOTALTIME threshold duration to the activity entry time. If the activity is still executing when this timestamp is reached, the threshold will be violated.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
Usage
Use this element to understand the value of the ACTIVITYTOTALTIME threshold applied to the activity, if any.
activitytotaltime_threshold_violated - Activity total time threshold violated monitor element
This monitor element returns '1’ (Yes) to indicate that the activity violated the ACTIVITYTOTALTIME threshold. '0’ (No) indicates that the activity has not yet violated the threshold.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
Usage
Use this element to determine if the activity violated the ACTIVITYTOTALTIME threshold that was applied to the activity.
adapter_name - Adapter name monitor element
Name of the network adapter on this host.
| Table Function | Monitor Element Collection Level |
|---|---|
| ENV_GET_NETWORK_RESOURCES table function - Return network adapter information | Always collected |
address - IP address from which the connection was initiated
The IP address from which the activity connection was initiated.
| Table Function | Monitor Element Collection Level |
|---|---|
| WLM_GET_SERVICE_CLASS_WORKLOAD _OCCURRENCES table function - list workload occurrences | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | Always collected |
Usage
Use this to identify the IP address from which the activity connection was initiated. Secure domain names are shown converted to an IP address.
adm_bypassed - Admission control bypassed monitor element
Indicates whether the activity bypassed admission control.
- A value of 0 indicates that the activity did not bypass admission control.
- A value of 1 indicates that the activity bypassed admission control.
- A null value indicates that records were returned from non-coordinating members in a partitioned database environment.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Return information about an activity as an XML document | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activity | event_activity | Always collected |
adm_bypass_act_total - Number of times admission control was bypassed monitor element
Count of activities that bypass adaptive admission control. This includes DDL statements, CALL statements, and DML statements with an estimated runtime of less than 1 second.
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activity | event_activitymetrics | ACTIVITY METRICS BASE |
| Package Cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of Work | uow_metrics | REQUEST METRICS BASE |
adm_overflows - Number of times an activity fit as an overflow monitor element
Number of times that Workload Manager adaptive admission control allowed an activity to exceed hard resource entitlement of a service class.
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activity | event_activitymetrics | ACTIVITY METRICS BASE |
| Package Cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of Work | uow_metrics | REQUEST METRICS BASE |
agent_id - Application handle (agent ID) monitor element
A system-wide unique ID for the application. In a single-member database configuration, this identifier consists of a 16-bit counter. In a multi-member configuration, this identifier consists of the coordinating member number concatenated with a 16-bit counter. In addition, this identifier is the same on every member where the application may make a secondary connection.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| MON_GET_CONNECTION table function - Get connection metrics | Always collected |
| MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) | Always collected |
| MON_GET_LOCKS table function - list all locks in the currently connected database | Always collected |
| MON_GET_MEMORY_POOL - Get memory pool information | Always collected |
| MON_GET_UNIT_OF_WORK table function - Get unit of work metrics | Always collected |
| MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl_id_info | Basic |
| Lock | appl_lock_list | Basic |
| DCS Application | dcs_appl_info | Basic |
| Transaction | event_xact | - |
| Event Type | Logical Data Grouping | Monitor Element Collection Level |
|---|---|---|
| Locking | - | Always collected |
| Unit of work | - | Always collected |
| Connections | event_connheader | Always collected |
| Statements | event_stmt | Always collected |
| Statements | event_subsection | Always collected |
| Deadlocks1 | event_dlconn | Always collected |
| Deadlocks with Details1 | event_detailed_dlconn | Always collected |
| Threshold violations | event_thresholdviolations | Always collected |
| Activities | event_activity | Always collected |
| Change history | changesummary | Always collected |
- 1
- This event monitor has been deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR FOR LOCKING statement to monitor lock-related events, such as lock timeouts, lock waits, and deadlocks.
Usage
The application handle, also known as the agent ID, can be used to uniquely identify an active application.
Note: The agent_id monitor
element has different behavior depending on your version of Db2®.
When taking snapshots from Db2
with version SQLM_DBMON_VERSION1 or SQLM_DBMON_VERSION2 to a Db2 (Version
5 or greater) database, the agent_id returned
is not usable as an application identifier, rather it is the agent_pid of
the agent serving the application. In these cases an agent_id is
still returned for compatibility with earlier releases, but internally
the Db2 database
server will not recognize the value as an agent_id.
This value can be used as input to GET SNAPSHOT commands that require an agent ID or to the monitor table functions that require an application handle.
When reading event traces, it can be used to match event records with a given application.
It can also be used as input to the FORCE APPLICATION command or API. On multi-node systems this command can be issued from any node where the application has a connection. Its effect is global.
agent_id_holding_lock - Agent ID Holding Lock monitor element
The application handle of the agent holding a lock for which this application is waiting. The lock monitor group must be turned on to obtain this information.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Lock |
| Lock | appl_lock_list | Lock |
| Lock | lock_wait | Lock |
- Usage
- This element can help you determine which applications are in
contention for resources.
If this element is 0 (zero) and the application is waiting for a lock, this indicates that the lock is held by an indoubt transaction. You can use either appl_id_holding_lk or the command line processor LIST INDOUBT TRANSACTIONS command (which displays the application ID of the CICS® agent that was processing the transaction when it became indoubt) to determine the indoubt transaction, and then either commit it or roll it back.
Note that more than one application can hold a shared lock on an object for which this application is waiting. See lock_mode for information about the type of lock that the application holds. If you are taking an application snapshot, only one of the agent IDs holding a lock on the object will be returned. If you are taking a lock snapshot, all of the agent IDs holding a lock on the object will be identified.
agent_pid - Engine dispatchable unit (EDU) identifier monitor element
The unique identifier for the engine dispatchable unit (EDU) for the agent. Except on the Linux® operating system, the EDU ID is mapped to the thread ID. On the Linux operating system, the EDU ID is a unique identifier that is generated by the database system.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | agent | Statement |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Locking | - | - |
Usage
You can use this element to link database system monitor information to other sources of diagnostic information, such as system traces. You can also use it to monitor how agents working for a database application use system resources.
agent_status - DCS Application Agents monitor element
In a connection concentrator environment, this value shows which applications currently have associated agents.
- Element identifier
- agent_status
- Element type
- information
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| DCS Application | dcs_appl_info | Basic |
- Usage
- Values are:
- SQLM_AGENT_ASSOCIATED (numeric value 1)
The agent working on behalf of this application is associated with it.
- SQLM_AGENT_NOT_ASSOCIATED (numeric value 2)
The agent that was working on behalf of this application is no longer associated with it and is being used by another application. The next time work is done for this application without an associated agent, an agent will be re-associated.
- SQLM_AGENT_ASSOCIATED (numeric value 1)
agent_sys_cpu_time - System CPU Time used by Agent monitor element
The total system CPU time (in seconds and microseconds) used by the database manager agent process.
- Element identifier
- agent_sys_cpu_time
- Element type
- time
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Timestamp |
- Usage
- This element along with the other related
CPU-time elements can help
you understand the level of activity within an application, and may
help you
identify applications that could benefit from additional tuning.
This element includes CPU time for both SQL and non-SQL statements, as well as CPU time for any unfenced user-defined functions (UDFs)
System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.
Note: If this information is not available for your operating system, this element will be set to 0.
agent_tid - Agent thread ID monitor element
The thread ID of the agent or system entity. If this ID is unavailable, the value of the column is null.
| Table Function | Monitor Element Collection Level |
|---|---|
| ENV_GET_DB2_EDU_SYSTEM_RESOURCES - return database engine dispatchable units system information | Always collected |
| MON_GET_AGENT table function - List agents, fenced mode processes, and system entities for the database | Always collected |
| MON_GET_APPL_LOCKWAIT table function - Get information about locks for which an application is waiting | Always collected |
| WLM_GET_SERVICE_CLASS_AGENTS table function - list agents running in a service class | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Locking | lock_participants |
agent_usr_cpu_time - User CPU Time used by Agent monitor element
The total CPU time (in seconds and microseconds) used by database manager agent process.
- Element identifier
- agent_usr_cpu_time
- Element type
- time
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Timestamp |
- Usage
- This element along with the other
CPU-time related elements can help
you identify applications or queries that consume large amounts of
CPU.
This counter includes time spent on both SQL and non-SQL statements, as well as any unfenced user-defined functions (UDFs) or stored procedures executed by the application.
System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.
Note: If this information is not available for your operating system, this element will be returned as 0.
agent_wait_time - Agent wait time monitor element
Time spent by an application queued to wait for an agent under concentrator configurations. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
Usage
The agent_wait_time monitor
element can be used to help evaluate how efficiently your system is
running in a concentrator environment. A high agent wait relative
to the total_request_time monitor element value
indicates that requests are spending a lot of time queued waiting
for agents, which may be indicative of one or more of the following
events:
- The max_coordagents configuration parameter has been configured too small for your workload. You may need to increase the value of max_coordagents configuration parameter, or the ratio of max_coordagents configuration parameter to max_connections configuration parameter if you are running with both parameters set to AUTOMATIC, to ensure that enough coordinator agents are available to service your application requests in a timely manner.
- Your workload is not committing frequently enough. For the concentrator to work efficiently, applications should issue commits relatively frequently to ensure that their agents can be freed up to serve requests on other applications. If your applications do not do frequent commits you may need to configure a proportionally higher number of coordinator agents to reduce the time spent waiting for agents to become available.
agent_waits_total - Total agent waits monitor element
Number of times an application had to wait for an agent to be assigned under concentrator configurations.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
Usage
Use this element in conjunction with the agent_wait_time monitor element to determine the average amount of time an application request spends waiting for an agent in a concentrator environment.
agents_created_empty_pool - Agents Created Due to Empty Agent Pool monitor element
The number of agents created because the agent pool was empty. It includes the number of agents started at db2start time. (num_initagents).
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_INSTANCE table function - Get instance level information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database Manager | db2 | Basic |
- Usage
- In conjunction with agents_from_pool, you can calculate the ratio
of
Agents Created Due to Empty Agent Pool / Agents Assigned From PoolSee agents_from_pool for information about using this element.
agents_from_pool - Agents Assigned From Pool monitor element
The number of agents assigned from the agent pool.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_INSTANCE table function - Get instance level information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database Manager | db2 | Basic |
Usage
This element can be used with the agents_created_empty_pool monitor element to determine how often an agent must be created because the pool is empty.
The following ratio
Agents Created Due to Empty Agent Pool / Agents Assigned From Pool
can
be used to help set an appropriate value for the num_poolagents configuration
parameter.
For most users, the default value of 100 with AUTOMATIC will ensure optimal performance.
This ratio may fluctuate somewhat with the workload. At times of low activity on the system, additional agent creation and termination may occur. At times of high activity on the system, more agent reuse will occur. A low ratio indicates that there is a high amount of agent reuse, which is expected on systems with high activity. A high ratio indicates a higher amount of agent creation than reuse is occurring. If this is a concern, increase the value for the num_poolagents configuration parameter to lower the ratio. However, this will cause additional resources consumption on the system.
agents_registered - Agents Registered monitor element
The number of agents registered in the database manager instance that is being monitored (coordinator agents and subagents).
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_INSTANCE table function - Get instance level information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database Manager | db2 | Basic |
Usage
Use this element to help evaluate your settings for the max_coordagents and max_connections configuration parameters, as well as the intraquery parallelism settings.
agents_registered_top - Maximum Number of Agents Registered monitor element
The maximum number of agents that the database manager has ever registered, at the same time, since it was started (coordinator agents and subagents).
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_INSTANCE table function - Get instance level information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database Manager | db2 | Basic |
Usage
You may use this element to help you evaluate your settings for the max_coordagents and max_connections configuration parameters, as well as the intraquery parallelism settings.
The number of agents registered at the time the snapshot was taken is recorded by the agents_registered monitor element.
agents_stolen - Stolen Agents monitor element
At the database manager snapshot
level, this monitor element represents the number of idle agents associated
with an application which get reassigned to work on a different application.
At
the application snapshot level, this monitor element represents the
number of idle agents associated with a different application which
get reassigned to work on this application.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_INSTANCE table function - Get instance level information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database Manager | db2 | Basic |
| Application | appl | Basic |
Usage
The num_poolagents configuration parameter is set to AUTOMATIC by default. This means that the database system automatically manages the pooling of idle agents, which includes assigning work to idle agents associated with another application.
agents_top - Number of Agents Created monitor element
At the activity level, this is the maximum number of agents that were used when executing the statement. At the database level, it is the maximum number of agents for all applications.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details | Always collected |
| MON_GET_DATABASE table function - Get database level information | Always collected |
| MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Statement |
| Application | stmt | Statement |
- Usage
- An indicator how well intra-query parallelism was realized.
agents_waiting_on_token - Agents Waiting for a Token monitor element
The number of agents waiting for a token so they can execute a transaction in the database manager.
Note: The agents_waiting_on_token monitor
element is deprecated. Using this monitor element will not generate
an error. However, it does not return a valid value and might be removed in a future
release.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database Manager | db2 | Basic |
Usage
You can use this element to help evaluate your setting for the maxcagents configuration parameter.
Each application has a dedicated coordinator agent to process database requests within the database manager. Each agent has to get a token before it can execute a transaction. The maximum number of agents that can execute database manager transactions is limited by the configuration parameter maxcagents.
agents_waiting_top - Maximum Number of Agents Waiting monitor element
The maximum number of agents that have ever been waiting for a token, at the same time, since the database manager was started.
Note: The agents_waiting_top monitor
element is deprecated. Using this monitor element will not generate
an error. However, it does not return a valid value and might be removed in a future
release.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database Manager | db2 | Basic |
Usage
Use this element to help you evaluate your setting of the maxcagents configuration parameter.
The number of agents waiting for a token at the time the snapshot was taken is recorded by the agents_waiting_on_token monitor element.
If the maxcagents parameter is set to its default value (-1), no agents should wait for a token and the value of this monitor element should be zero.
agg_temp_tablespace_top - Aggregate temporary table space top monitor element
The agg_temp_tablespace_top monitor
element stores the high watermark, in KB, for the aggregate temporary
table space usage of DML activities at all nesting levels in a service
class.
The aggregate is computed by summing
the temporary table space usage across all activities in the service
subclass, and this high watermark represents the highest value reached
by this aggregate since the last reset. The monitor element returns
-1 when COLLECT AGGREGATE ACTIVITY DATA for the service class is set
to NONE. An AGGSQLTEMPSPACE threshold must be defined and enabled
for at least one service subclass in the same superclass as the subclass
to which this record belongs, otherwise a value of 0 is returned.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats | Always collected |
Usage
Use this element to determine the highest aggregate DML activity system temporary table space usage reached on a member for a service subclass in the time interval collected.
aggsqltempspace_threshold_id - Aggregate SQL temporary space threshold ID monitor element
The numeric ID of the AGGSQLTEMPSPACE threshold that was applied to the activity.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
Usage
Use this element to understand which AGGSQLTEMPSPACE threshold, if any, was applied to the activity.
aggsqltempspace_threshold_value - AggSQL temporary space threshold value monitor element
The upper bound of the AGGSQLTEMPSPACE threshold that was applied to the activity.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
Usage
Use this element to understand the value of the AGGSQLTEMPSPACE threshold applied to the activity, if any.
aggsqltempspace_threshold_violated - AggSQL temporary space threshold violated monitor element
The optional monitor element when set to '1’ (Yes) indicates that the activity violated the AGGSQLTEMPSPACE threshold that was applied to it. '0’ (No) indicates that the activity has not yet violated the threshold.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
Usage
Use this element to determine if the activity violated the AGGSQLTEMPSPACE threshold that was applied to the activity.
app_act_aborted_total - Total failed external coordinator activities monitor element
The total number of external, non-nested coordinator activities that completed with errors. For service classes, if an activity is remapped to a different service subclass with a REMAP ACTIVITY action before it aborts, the activity counts only toward the total of the subclass in which it aborts.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of Work | uow (reported in the metrics.xml
document) uow_metrics |
REQUEST METRICS BASE |
app_act_completed_total - Total successful external coordinator activities monitor element
The total number of external, non-nested coordinator activities that completed successfully.
For service classes, if an activity is remapped to a different
subclass with a REMAP ACTIVITY action before it completes, this activity
counts only toward the total of the subclass it completes in.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of Work | uow (reported in the metrics.xml
document) uow_metrics |
REQUEST METRICS BASE |
app_act_rejected_total - Total rejected external coordinator activities monitor element
The total number of external, non-nested coordinator activities at any nesting level that were rejected instead of being allowed to execute.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of Work | uow (reported in the metrics.xml
document) uow_metrics |
REQUEST METRICS BASE |
app_rqsts_completed_total - Total application requests completed monitor element
Total number of external (application) requests executed by the coordinator. For service subclasses, this monitor element is updated only for the subclass where the application request completes.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
Usage
Use this monitor element to understand how many requests are being submitted into the system from applications.
appl_action - Application action monitor element
The action or request that the client application is performing.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Locking | lock_participants |
appl_con_time - Connection Request Start Timestamp monitor element
The date and time that an application started a connection request.
- Element identifier
- appl_con_time
- Element type
- timestamp
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Timestamp |
- Usage
- Use this element to determine when the application started its connection request to the database.
appl_id - Application ID monitor element
This identifier is generated when the application connects to the database at the database manager or when Db2 Connect receives a request to connect to a DRDA database.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl_id_info | Basic |
| DCS Application | dcs_appl_info | Basic |
| Lock | appl_lock_list | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Locking 1 | lock_participants | Always collected |
| Unit of work 1 | uow, uow_executable_list, uow_metrics | Always collected |
| Connection | event_conn | Always collected |
| Connections | event_connheader | Always collected |
| Statements | event_stmt | Always collected |
| Transactions 2 | event_xact | Always collected |
| Deadlocks 3 | event_dlconn | Always collected |
| Deadlocks with Details 3 | event_detailed_dlconn | Always collected |
| Activities | event_activitystmt | Always collected |
| Activities | event_activity | Always collected |
| Activities | event_activityvals | Always collected |
| Activities | event_activitymetrics | Always collected |
| Threshold violations | event_thresholdviolations | Always collected |
| Change history | changesummary | Always collected |
- For this event monitor, this monitor element is returned in the column APPLICATION_ID.
- This event monitor has been deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR FOR UNIT OF WORK statement to monitor transaction events.
- This event monitor has been deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR FOR LOCKING statement to monitor lock-related events, such as lock timeouts, lock waits, and deadlocks.
Usage
This ID is known on both the client and server, so you can use it to correlate the client and server parts of the application. For Db2 Connect applications, you will also need to use outbound_appl_id monitor element to correlate the client and server parts of the application.
This
identifier is unique across the network. There are different formats
for the application ID, which are dependent on the communication protocol
between the client and the server machine on which the database manager, Db2 Connect,
or both are running. Each of the formats consists of three parts
separated by periods.
- TCP/IP
- Format
-
IPAddr.Port.Timestamp - IPv4
-
- Example
-
9.26.120.63.43538.090924175700 - Details
- In IPv4, a TCP/IP-generated application ID is composed of three
sections. The first section is the IP address. It is represented as
four decimal numbers of the form
a.b.c.d. The second section is the port number, which is represented as 5 decimal characters. The third section is the approximate timestamp, represented as 12 decimal characters.
- IPv6
-
- Example
-
2002:91a:519:13:20d:60ff:feef:cc64.5309.090924175700 - Details
- In IPv6, a TCP/IP-generated application ID is composed of three sections. The first section contains the IPv6 address of the form a:b:c:d:e:f:g:h, where each of a-h is up to 4 hexadecimal digits. The second section is the port number. The third section is the approximate timestamp identifier for the instance of this application.
- Local Applications
- Format
-
*LOCAL.DB2 instance.Application instance - Example
-
*LOCAL.DB2INST1.930131235945 - Details
- The application ID generated for a local application is made up
by concatenating the string
*LOCAL, the name of the database instance, and a unique identifier for the instance of this application.For multiple database partition instances,
LOCALis replaced withNx, wherexis the partition number from which the client connected to the database. For example,*N2.DB2INST1.0B5A12222841.
Use the client_protocol monitor element to determine which communications protocol the connection is using and, as a result, the format of the appl_id monitor element.
appl_id_holding_lk - Application ID Holding Lock monitor element
The application ID of the application that is holding a lock on the object that this application is waiting to obtain.
- Element identifier
- appl_id_holding_lk
- Element type
- information
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Lock |
| Lock | appl_lock_list | Lock |
| Lock | lock_wait | Lock |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Deadlocks | event_dlconn | Always collected |
| Deadlocks with Details | event_detailed_dlconn | Always collected |
- Usage
- This element can help you determine
which applications are in contention
for resources. Specifically, it can help you identify the application
handle
(agent ID) and table ID that are holding the lock. Note that you may
use the
LIST APPLICATIONS command to obtain information to relate the application
ID with an agent ID. However, it is a good idea to collect this type
of information
when you take the snapshot, as it could be unavailable if the application
ends before you run the LIST APPLICATIONS command.
Note that more than one application can hold a shared lock on an object for which this application is waiting to obtain a lock. See lock_mode for information about the type of lock that the application holds. If you are taking an application snapshot, only one of the application IDs holding a lock on the object will be returned. If you are taking a lock snapshot, all of the application IDs holding a lock on the object will be returned.
appl_id_oldest_xact - Application with Oldest Transaction monitor element
The application ID (which corresponds to the agent_id value from the application snapshot) of the application that has the oldest transaction.
- Element identifier
- appl_id_oldest_xact
- Element type
- information
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
- Usage
- This element can help you determine
which application has the oldest
active transaction. This application can be forced to free up log
space.
If it is taking up a great deal of log space, you should examine the
application
to determine if it can be modified to commit more frequently.
There are times when there is not a transaction holding up logging, or the oldest transaction does not have an application ID (for example, indoubt transaction or inactive transaction). In these cases, this application's ID is not returned in the data stream.
appl_idle_time - Application Idle Time monitor element
Number of seconds since an application has issued any requests to the server. This includes applications that have not terminated a transaction, for example not issued a commit or rollback.
- Element identifier
- appl_idle_time
- Element type
- information
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Statement |
| DCS Application | dcs_appl | Statement |
- Usage
- This information can be used to implement applications that force users that have been idle for a specified number of seconds.
appl_name - Application name monitor element
The name of the application running at the client, as known to the database or Db2 Connect server.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl_id_info | Basic |
| Lock | appl_lock_list | Basic |
| DCS Application | dcs_appl_info | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Locking | - | Always collected |
| Unit of work | - | Always collected |
| Connections | event_connheader | Always collected |
| Activities | event_activity | Always collected |
| Threshold violations | event_thresholdviolations | Always collected |
| Change history | changesummary | Always collected |
Usage
This element can be used with appl_id to relate data items with your application.
In a client-server environment, this name is passed from the client to the server when establishing the database connection. Any non-English characters in the application name will be removed. A CLI application can set the SQL_ATTR_INFO_PROGRAMNAME attribute with a call to SQLSetConnectAttr. When SQL_ATTR_INFO_PROGRAMNAME is set before the connection to the server is established, the value specified overrides the actual client application name and will be the value that is displayed in the appl_name monitor element.
In situations where the client application code page is different from the code page under which the database system monitor is running, you can use codepage_id to help translate appl_name.
appl_priority - Application Agent Priority monitor element
The priority of the agents working for this application.
- Element identifier
- appl_priority
- Element type
- information
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Connection | event_conn | Always collected |
- Usage
- Use this element to check whether applications are running with
the expected priorities. Application priorities can be set by an administrator.
They can be changed by the governor utility (db2gov).
The governor is used by the database system to monitor and change the behavior of applications running against a database. This information is used to schedule applications and balance system resources.
A governor daemon collects statistics about the applications by taking snapshots. It checks these statistics against the rules governing applications running on that database. If the governor detects a rule violation, it takes the appropriate action. These rules and actions were specified by you in the governor configuration file.
If the action associated with a rule is to change an application's priority, the governor changes the priority of the agents in the partition where the violation was detected.
appl_priority_type - Application Priority Type monitor element
Operating system priority type for the agent working on behalf of the application.
- Element identifier
- appl_priority_type
- Element type
- information
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Connection | event_conn | Always collected |
- Usage
- Dynamic priority is recalculated by the operating system based on usage. Static priority does not change.
appl_section_inserts - Section Inserts monitor element
Inserts of SQL sections by an application from its shared SQL workspace.
The working copy of any executable section is stored in a shared SQL workspace. This is a count of when a copy was not available and had to be inserted.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Connection | event_conn | Always collected |
| Database | event_db | Always collected |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of Work | uow_metrics | REQUEST METRICS BASE |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Application | appl | Basic |
appl_section_lookups - Section Lookups monitor element
Lookups of SQL sections by an application from its shared SQL workspace.
Each agent has access to a shared SQL workspace where the working copy of any executable section is kept. This counter indicates how many times the SQL work area was accessed by agents for an application.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Connection | event_conn | Always collected |
| Database | event_db | Always collected |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of Work | uow_metrics | REQUEST METRICS BASE |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Application | appl | Basic |
appl_status - Application status monitor element
The current status of the application.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl_id_info | Basic |
| Lock | appl_lock_list | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Locking | - | Always collected |
| Connection | event_conn | Always collected |
Usage
For a locking event monitor's lock wait, lock timeout, or deadlock event, the lock requester reports the appl_status value that was in effect before entering lock wait status rather than the current lock wait status.
With the APPLICATIONS and SNAPAPPL_INFO Administrative views deprecated, information that is returned from the appl_status monitor element can be obtained by examining the WORKLOAD_OCCURRENCE_STATE monitor element from the MON_GET_CONNECTION interface along with the EVENT_STATE, EVENT_TYPE and EVENT_OBJECT monitor elements from the MON_GET_AGENT interface. For example, to monitor the SQLM_LOCKWAIT application status, the EVENT_STATE, EVENT_TYPE and EVENT_OBJECT columns in the MON_GET_AGENT interface can be used to determine which agents are waiting on locks.
This element can help you diagnose potential application problems. Values for this field are listed in the following table.
| API Constant | Description |
|---|---|
| SQLM_ANONBLOCK_WAIT | Anonymous Block: The application is waiting for an anonymous block to complete. |
| SQLM_AUTONOMOUS_WAIT | Autonomous Wait: The application is waiting for an autonomous routine to complete. |
| SQLM_BACKUP | Backing Up Database: The application is performing a backup of the database. |
| SQLM_COMMIT_ACT | Commit Active: The unit of work is committing its database changes. |
| SQLM_COMP | Compiling: The database manager is compiling an SQL statement or precompiling a plan on behalf of the application. |
| SQLM_CONNECTED | Database Connect Completed: The application has initiated a database connection and the request has completed. |
| SQLM_CONNECTPEND | Database Connect Pending: The application has initiated a database connection but the request has not yet completed. |
| SQLM_CREATE_DB | Creating Database: The agent has initiated a request to create a database and that request has not yet completed. |
| SQLM_DECOUPLED | Decoupled from Agent: There are no agents currently associated with the application. This is a normal state. When the Connection Concentrator is enabled, there is no dedicated coordinator agent, so an application can be decoupled on the coordinator partition. In non-concentrator environments, an application cannot be decoupled on the coordinator partition as there will always be a dedicated coordinator agent . |
| SQLM_DISCONNECTPEND | Database Disconnect Pending: The application has initiated a database disconnect but the command has not yet completed executing. The application may not have explicitly executed the database disconnect command. The database manager will disconnect from a database if the application ends without disconnecting. |
| SQLM_INTR | Request Interrupted: An interrupt of a request is in progress. |
| SQLM_IOERROR_WAIT | Wait to Disable Table space: The application has detected an I/O error and is attempting to disable a particular table space. The application has to wait for all other active transactions on the table space to complete before it can disable the table space. |
| SQLM_LOAD | Data Fast Load: The application is performing
a fast loadof data into the database. |
| SQLM_LOCKWAIT | Lock Wait: The unit of work is waiting for a lock. After the lock is granted, the status is restored to its previous value. |
| SQLM_QUIESCE_TABLESPACE | Quiescing a Table space: The application is performing a quiesce table space request. |
| SQLM_RECOMP | Recompiling: The database manager is recompiling (that is, rebinding) a plan on behalf of the application. |
| SQLM_REMOTE_RQST | Federated request pending: The application is waiting for results from a federated data source. |
| SQLM_RESTART | Restarting Database: The application is restarting a database in order to perform crash recovery. |
| SQLM_RESTORE | Restoring Database: The application is restoring a backup image to the database. |
| SQLM_ROLLBACK_ACT | Rollback Active: The unit of work is rolling back its database changes. |
| SQLM_ROLLBACK_TO_SAVEPOINT | Rollback to savepoint: The application is rolling back to a savepoint. |
| SQLM_TEND | Transaction Ended: The unit of work is part of a global transaction that has ended but has not yet entered the prepared phase of the two-phase commit protocol. |
| SQLM_THABRT | Transaction Heuristically Rolled Back: The unit of work is part of a global transaction that has been heuristically rolled-back. |
| SQLM_THCOMT | Transaction Heuristically Committed: The unit of work is part of a global transaction that has been heuristically committed. |
| SQLM_TPREP | Transaction Prepared: The unit of work is part of a global transaction that has entered the prepared phase of the two-phase commit protocol. |
| SQLM_UNLOAD | Data Fast Unload: The application is
performing a fast unloadof data from the database. |
| SQLM_UOWEXEC | UOW Executing: The database manager is executing requests on behalf of the unit of work. |
| SQLM_UOWQUEUED | UOW Queued: The unit of work is queued, waiting for another activity to complete execution. The unit of work is queued because the threshold for the number of concurrently executing activities has been reached. |
| SQLM_UOWWAIT | UOW Waiting: The database manager is waiting on behalf of the unit of work in the application. This status typically means that the system is executing in the application's code. |
| SQLM_WAITFOR_REMOTE | Pending remote request: The application is waiting for a response from a remote partition in a partitioned database instance. |
application_handle - Application handle monitor element
A system-wide unique ID for the application. In a single-member database configuration, this identifier consists of a 16-bit counter. In a multi-member configuration, this identifier consists of the coordinating member number concatenated with a 16-bit counter. In addition, this identifier is the same on every member where the application may make a secondary connection.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl_id_info | Basic |
| Lock | appl_lock_list | Basic |
| DCS Application | dcs_appl_info | Basic |
| Transaction | event_xact | - |
| Event Type | Logical Data Grouping | Monitor Element Collection Level |
|---|---|---|
| Locking | - | Always collected |
| Unit of work | - | Always collected |
| Connections | event_connheader | Always collected |
| Statements | event_stmt | Always collected |
| Statements | event_subsection | Always collected |
| Deadlocks1 | event_dlconn | Always collected |
| Deadlocks with Details1 | event_detailed_dlconn | Always collected |
| Threshold violations | event_thresholdviolations | Always collected |
| Activities | event_activity | Always collected |
| Change history | changesummary | Always collected |
- 1
- This event monitor has been deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR FOR LOCKING statement to monitor lock-related events, such as lock timeouts, lock waits, and deadlocks.
Usage
This monitor element is an alias of the agent_id monitor element.
When returned
by MON_GET_MEMORY_POOL, this monitor element is NULL except when the
memory pool being described is one of the following types:
- APPLICATION
- STATISTICS
- STATEMENT
- SORT_PRIVATE.
appls_cur_cons - Applications Connected Currently monitor element
Indicates the number of applications that are currently connected to the database.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_DATABASE table function - Get database level information | Always collected |
| MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Lock | db_lock_list | Basic |
- Usage
- You may use this element to help you understand the level of
activity within a database and the amount of system resource being
used.
It can help you adjust the setting of the maxappls and max_coordagents configuration parameters. For example, its value is always the same as maxappls, you may want to increase the value of maxappls. See the rem_cons_in and the local_cons monitor elements for more information.
appls_in_db2 - Applications Executing in the Database Currently monitor element
Indicates the number of applications that are currently connected to the database, and for which the database manager is currently processing a request.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_DATABASE table function - Get database level information | Always collected |
| MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
arm_correlator - Application response measurement correlator monitor element
Identifier of a transaction in the Application Response Measurement (ARM) standard.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | - |
Usage
This element can be used to link an activity collected by the activities event monitor to the applications associated with the activity, if such applications also support the Application Response Measurement (ARM) standard.
associated_agents_top - Maximum Number of Associated Agents monitor element
The maximum number of subagents associated with this application.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_CONNECTION table function - Get connection metrics | Always collected |
| MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Basic |
async_runstats - Total number of asynchronous RUNSTATS requests monitor element
The total number of successful asynchronous RUNSTATS activities performed by real-time statistics gathering for all the applications in the database. Values reported by all the database partitions are aggregated together.
Important: The SQL administrative views
and table functions that return this monitor element are deprecated.
For SQL access to this information, see the total_async_runstats monitor
element.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Statement |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | event_db | Always collected |
Usage
Use this element to determine how many successful asynchronous RUNSTATS activities have been performed by real-time statistics gathering. This value changes frequently. In order to get a better view of the system usage, take a snapshot at specific intervals over an extended period of time. When used in conjunction with sync_runstats and stats_fabrications monitor elements, this element can help you to track the different types of statistics collection activities related to real-time statistics gathering and analyze their performance impact.
This monitor element is an alias for the total_async_runstats monitor element.
audit_events_total - Total audit events monitor element
The total number of audit events generated.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
audit_file_write_wait_time - Audit file write wait time monitor element
Time spent waiting to write an audit record. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
Usage
Use this monitor element to determine the amount of time an agent spends waiting to open and write an audit event synchronously to disk.
In a typical scenario, only one agent attempts to open the audit log file at a time, as the other agents wait for access to the audit common subsystem before opening the file. Therefore, the wait time usually represents the time spent waiting to write the file to disk by the operating system. Audit utilities might lock the audit log file during execution, which causes a longer than normal wait time for agents to open and write to the audit log file. If asynchronous auditing is enabled, audit events that are larger than the asynchronous audit buffer are written directly to disk, instead of to the buffer, and contribute to the wait time.
Outside of the special audit utility scenario, the wait time depends on the speed of the disks and how quickly the operating system can write the data to them. In order to reduce this wait time for a given application and audit configuration, you might tune the operating system or use faster disks.
audit_file_writes_total - Total audit files written monitor element
The total number of times an agent has had to wait to write an audit event directly to disk.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
Usage
Use this monitor element in conjunction with the audit_file_write_wait_time monitor element to determine the average time an application request spends waiting to open and write an audit event synchronously to disk.
audit_subsystem_wait_time - Audit subsystem wait time monitor element
Time spent waiting for space in audit buffer. Waiting occurs when audit buffer is full and agent must wait for audit daemon to write buffer to disk. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
Usage
Use this monitor element to determine the amount of time an agent spends waiting to access the audit common subsystem, while the audit common subsystem is busy handling events for other agents.
Certain common portions of the audit subsystem can only be accessed by a single agent at a time. The value of this monitor element indicates the amount of time that an agent must wait to access the audit common subsystem. This includes time spent by an agent that has filled the current asynchronous buffer waiting for the audit daemon to finish writing out a previous asynchronous buffer to disk. Other agents that are waiting while writing to the audit log file or waiting to make a request of the audit daemon have also accessed the audit common subsystem and wait times there will be reflected in this value.
To reduce this wait time, you might change the value of the audit_buf_sz configuration parameter if asynchronous auditing is in use. You can increase the value of the audit_buf_sz configuration parameter until further increases no longer show any reductions in the audit common subsystem wait time. At this point, the asynchronous buffers are large enough such that the daemon is able to write one full buffer to disk before the next buffer is full, and then the daemon is no longer a bottleneck. If the value of the audit_buf_sz configuration parameter must be increased to such an extent that too many audit records could be lost if a system failure were to occur, then you might reduce the wait time by tuning the operating system or using faster disks. If further reduction in the wait time is necessary, then use audit policies to reduce the number of audit events generated.
audit_subsystem_waits_total - Total audit subsystem waits monitor element
Number of times audit has waited for a buffer write.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
Usage
Use this monitor element to determine the total number of times an agent has had to access the audit common subsystem. The generation of one audit event may need to access the audit common subsystem none, one, or more times to record the event. Use the audit_events_total monitor element to determine the exact number of audit events generated.
auth_id - Authorization ID monitor element
The authorization ID of the user who invoked the application that is being monitored. On a Db2 Connect gateway node, this is the user's authorization ID on the host.
| Table Function | Monitor Element Collection Level |
|---|---|
| PD_GET_DIAG_HIST table function - Return records from a given facility | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl_id_info | Basic |
| Lock | appl_lock_list | Basic |
| DCS Application | dcs_appl_info | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Locking | - | Always collected |
| Unit of work | - | Always collected |
| Connections | event_connheader | Always collected |
Usage
In an explicit trusted connection, the auth_id value does not change immediately when you switch users. Rather, the auth_id is updated the first time you access the database after switching users. This is because the switch user operation is always chained to the subsequent operation.
You can use this element to determine who invoked the application.
authority_bitmap - User authorization level monitor element
The authorities granted to the user
and to the groups to which the user belongs. These include authorities
granted to roles that are granted to the user and to the groups to
which the user belongs.
Authorities granted
to a user or to roles granted to the user are considered user authorities.
Authorities granted to a group to which the user belongs or to roles
granted to the group to which the user belongs are considered group
authorities.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Basic |
| Application | appl_info | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Connection | event_conn | Always collected |
Usage
The authority_bitmap monitor element has the format of an array. Each array element is a single character that represents whether or not the user ID has been granted a specific authority and how the user has received that authority.
Individual array elements are indexed through an index value defined in the sql.h file. The value of an index in the authority_bitmap array is called an authority index. For example, SQL_DBAUTH_SYSADM is the index to determine if the user has SYSADM authority.
The value of one element
in the authority_bitmap array identified by an authority index represents
whether the authority is held by an authorization ID. To determine
how the authorization ID is held, for each array element identified
by the authority index, use the following defines from sql.h:
- SQL_AUTH_ORIGIN_USER
- If this bit is on, then the authorization ID has the authority granted to the user or to a role granted to the user.
- SQL_AUTH_ORIGIN_GROUP
- If this bit is on, then the authorization ID has the authority granted to the group or to a role granted to the group.
For example, to determine if a user holds
DBADM authority, verify the following value:
authority_bitmap[SQL_DBAUTH_DBADM]authority_bitmap[SQL_DBAUTH_DBADM] & SQL_AUTH_ORIGIN_USER authority_lvl - User authorization level monitor element
The highest authority level granted to an application.
Note: The authority_lvl monitor element is deprecated.
Use the authority_bitmap monitor element instead. See authority_bitmap - User authorization level monitor element.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Basic |
| Application | appl_info | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Connection | event_conn | Always collected |
- Usage
- The operations allowed by an application are granted either directly
or indirectly. The following defines from sql.h may be used to determine the authorizations granted explicitly to a user:
- SQL_SYSADM
- SQL_DBADM
- SQL_CREATETAB
- SQL_BINDADD
- SQL_CONNECT
- SQL_CREATE_EXT_RT
- SQL_CREATE_NOT_FENC
- SQL_SYSCTRL
- SQL_SYSMAINT
The following defines from sql.h may be used to determine indirect authorizations inherited from group or public:- SQL_SYSADM_GRP
- SQL_DBADM_GRP
- SQL_CREATETAB_GRP
- SQL_BINDADD_GRP
- SQL_CONNECT_GRP
- SQL_CREATE_EXT_RT_GRP
- SQL_CREATE_NOT_FENC_GRP
- SQL_SYSCTRL_GRP
- SQL_SYSMAINT_GRP
auto_storage_hybrid - Hybrid automatic storage table space indicator monitor element
If the table space is an automatic storage table space with some non-automatic storage containers, this monitor element returns a value of 1. Otherwise, it returns a value of 0.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
Usage
A hybrid automatic storage table space is a table space that has been converted to be managed by automatic storage using the ALTER TABLESPACE command, but has not yet been rebalanced. This table space still has non-automatic storage containers. After the table space is rebalanced, it contains only automatic storage containers, and is no longer considered a hybrid table space.
automatic - Buffer pool automatic monitor element
Indicates whether a particular buffer pool has self-tuning enabled. This element is set to 1 if self-tuning is enabled for the buffer pool; and 0 otherwise.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_BUFFERPOOL table function - Get buffer pool metrics | DATA OBJECT METRICS BASE |