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
Table 1. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Table 2. Event Monitoring Information
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.

Table 4. Event Monitoring Information
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.

Table 5. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_SAMPLE_SERVICE_CLASS_METRICS - Get sample service class metrics REQUEST METRICS BASE
MON_SAMPLE_WORKLOAD_METRICS - Get sample REQUEST METRICS BASE
Table 6. Event Monitoring Information
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.

Table 7. Event Monitoring Information
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.
Table 8. Event Monitoring Information
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_time

act_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.

Table 10. Event Monitoring Information
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 11. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses Always collected
Table 12. Event Monitoring Information
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 13. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses Always collected
Table 14. Event Monitoring Information
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.

Table 15. Event Monitoring Information
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.

Table 17. Event Monitoring Information
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 19. Event Monitoring Information
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 20. Table Function Monitoring Information
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
Table 21. Event Monitoring Information
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.

Table 22. Event Monitoring Information
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

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 25. Event monitoring information
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

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 28. Event monitoring information
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

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 32. Event monitoring information
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.

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 36. Event monitoring information
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

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 39. Event monitoring information
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

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 42. Event monitoring information
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 45. Event monitoring information
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.

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 49. Event monitoring information
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.

Table 50. Event Monitoring Information
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 52. Event Monitoring Information
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.
Table 53. Event Monitoring Information
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 54. Table Function Monitoring Information
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 55. Table Function Monitoring Information
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
Table 56. Event Monitoring Information
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 57. Table Function Monitoring Information
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 58. Table Function Monitoring Information
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 59. Table Function Monitoring Information
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 60. Table Function Monitoring Information
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 61. Table Function Monitoring Information
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 62. Table Function Monitoring Information
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 63. Table Function Monitoring Information
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 64. Table Function Monitoring Information
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 65. Table Function Monitoring Information
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.

address - IP address from which the connection was initiated

The IP address from which the activity connection was initiated.

Table 67. Table Function Monitoring Information
Table Function Monitor Element Collection Level
WLM_GET_SERVICE_CLASS_WORKLOAD _OCCURRENCES table function - list workload occurrences Always collected
Table 68. Event Monitoring Information
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 70. Event monitoring information
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.

Table 71. Table function monitoring information
Table function Monitor element collection level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics REQUEST METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE table function - get aggregated execution metrics for routines REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics REQUEST METRICS BASE
MON_GET_TABLE table function - get table metrics REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 72. Event monitoring information
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.

Table 73. Table function monitoring information
Table function Monitor element collection level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
MON_GET_BUFFERPOOL table function - Get buffer pool metrics REQUEST METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE table function - get aggregated execution metrics for routines REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics REQUEST METRICS BASE
MON_GET_TABLE table function - get table metrics REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 74. Event monitoring information
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 75. Table Function Monitoring Information
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
Table 76. Snapshot Monitoring Information
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 -
Table 77. Event Monitoring Information
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.

Table 78. Snapshot Monitoring 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.

Table 79. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application agent Statement
Table 80. Event Monitoring Information
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
Table 81. Snapshot Monitoring 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.

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
Table 82. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Timestamp
For snapshot monitoring at the application level, this counter can be reset. This counter cannot be reset at other levels.
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 84. Event Monitoring Information
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
Table 85. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Timestamp
For snapshot monitoring, this counter can be reset.
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.

Table 87. Event Monitoring Information
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.

Table 89. Event Monitoring Information
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 90. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INSTANCE table function - Get instance level information Always collected
Table 91. Snapshot Monitoring Information
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 Pool

See 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 92. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INSTANCE table function - Get instance level information Always collected
Table 93. Snapshot Monitoring Information
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 94. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INSTANCE table function - Get instance level information Always collected
Table 95. Snapshot Monitoring Information
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 96. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INSTANCE table function - Get instance level information Always collected
Table 97. Snapshot Monitoring Information
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 98. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INSTANCE table function - Get instance level information Always collected
Table 99. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Basic
Application appl Basic
For snapshot monitoring, this counter can be reset.

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 101. Snapshot Monitoring Information
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.
Table 102. Snapshot Monitoring Information
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.
Table 103. Snapshot Monitoring Information
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.
Table 104. Event Monitoring Information
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 105. Table Function Monitoring Information
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 106. Table Function Monitoring Information
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 107. Table Function Monitoring Information
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.

Table 108. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses Always collected
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
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
Table 109. Event Monitoring Information
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.
Table 110. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses Always collected
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
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
Table 111. Event Monitoring Information
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.

Table 112. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses Always collected
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
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
Table 113. Event Monitoring Information
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.

Table 115. Event Monitoring Information
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.

Table 116. Event Monitoring Information
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
Table 117. Snapshot Monitoring Information
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.

Table 119. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_id_info Basic
DCS Application dcs_appl_info Basic
Lock appl_lock_list Basic
Table 120. Event Monitoring Information
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
  1. For this event monitor, this monitor element is returned in the column APPLICATION_ID.
  2. 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.
  3. 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.
  1. 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.
  2. 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, LOCAL is replaced with Nx, where x is 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
Table 121. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Lock
Lock appl_lock_list Lock
Lock lock_wait Lock
Table 122. Event Monitoring Information
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
Table 123. Snapshot Monitoring 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
Table 124. Snapshot Monitoring 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.

Table 126. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_id_info Basic
Lock appl_lock_list Basic
DCS Application dcs_appl_info Basic
Table 127. Event Monitoring Information
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
Table 128. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Table 129. Event Monitoring Information
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
Table 130. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Table 131. Event Monitoring Information
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.

Table 133. Event Monitoring Information
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
Table 134. Snapshot Monitoring Information
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.

Table 136. Event Monitoring Information
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
Table 137. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl Basic
For snapshot monitoring, this counter can be reset.

appl_status - Application status monitor element

The current status of the application.

Table 138. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_id_info Basic
Lock appl_lock_list Basic
Table 139. Event Monitoring Information
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 load of 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 unload of 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.

Table 141. Snapshot Monitoring Information
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 -
Table 142. Event Monitoring Information
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 143. Table function monitoring information
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
Table 144. Snapshot Monitoring Information
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 145. Table function monitoring information
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
Table 146. Snapshot Monitoring Information
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.

Table 147. Event Monitoring Information
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 148. Table function monitoring information
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
Table 149. Snapshot Monitoring Information
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.
Table 150. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Statement
For snapshot monitoring, this counter can be reset.
Table 151. Event Monitoring Information
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.

Table 152. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 153. Event Monitoring Information
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.

Table 154. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 155. Event Monitoring Information
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.

Table 156. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 157. Event Monitoring Information
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.

Table 158. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 159. Event Monitoring Information
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.

Table 160. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 161. Event Monitoring Information
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 162. Table Function Monitoring Information
Table Function Monitor Element Collection Level
PD_GET_DIAG_HIST table function - Return records from a given facility Always collected
Table 163. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_id_info Basic
Lock appl_lock_list Basic
DCS Application dcs_appl_info Basic
Table 164. Event Monitoring Information
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.
Table 165. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Application appl_info Basic
Table 166. Event Monitoring Information
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]
To determine if the DBADM authority is held directly by the user, verify the following:
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.
Table 167. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Application appl_info Basic
Table 168. Event Monitoring Information
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 169. Table Function Monitoring Information
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 170. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE