C

call_sql_stmts - CALL SQL statements executed monitor element

The number of CALL statements that were executed.

Table 3. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_wlmmetrics REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE

call_stmt_routine_id - Call statement routine identifier monitor element

For CALL statements, this is the routine ID of the procedure being invoked.

The element returns NULL if the row does not correspond to a CALL statement.
Table 4. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine Always collected

Usage

This element can be used to recreate the hierarchy of embedded and recursive routine calls.

call_stmt_subroutine_id - Call statement subroutine identifier monitor element

For CALL statements to a subroutine, this is the subroutine ID of the procedure being invoked.

Table 5. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine Always collected

Usage

This element can be used to recreate the hierarchy of embedded and recursive routine calls.

cat_cache_heap_full - Catalog cache heap full monitor element monitor element

The number of times that an insert into the catalog cache failed due to a heap-full condition in the database heap.

Table 6. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Connections event_conn Always collected

Usage

The catalog cache draws its storage dynamically from the database heap and even if the cache storage has not reached its limit, inserts into the catalog cache may fail due to a lack of space in the database heap. If the catalog cache heap full count is not zero, then this insert failure condition can be corrected by increasing the database heap size or reducing the catalog cache size.

cat_cache_inserts - Catalog cache inserts monitor element

The number of times that the system tried to insert table descriptor or authorization information into the catalog cache.

Table 8. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl Basic
For snapshot monitoring, this counter can be reset.
Table 9. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
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

In conjunction with "Catalog Cache Lookups", you can calculate the catalog cache hit ratio using the following formula:
    1 - (Catalog Cache Inserts / Catalog Cache Lookups)

See the cat_cache_lookups monitor element for more information about using this element.

cat_cache_lookups - Catalog cache lookups monitor element

The number of times that the catalog cache was referenced to obtain table descriptor information or authorization information.

Table 11. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl Basic
For snapshot monitoring, this counter can be reset.
Table 12. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
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

This element includes both successful and unsuccessful accesses to the catalog cache. The catalog cache is referenced whenever:
  • a table, view, or alias name is processed during the compilation of an SQL statement
  • database authorization information is accessed
  • a routine is processed during the compilation of an SQL statement

To calculate the catalog cache hit ratio use the following formula:

 
  (1 - (cat_cache_inserts / cat_cache_lookups))

indicates how well the catalog cache is avoiding catalog accesses. If the ratio is high (more than 0.8), then the cache is performing well. A smaller ratio might suggest that the catalogcache_sz configuration parameter should be increased. You should expect a large ratio immediately following the first connection to the database.

The execution of Data Definition Language (DDL) SQL statements involving a table, view, or alias will evict the table descriptor information for that object from the catalog cache causing it to be re-inserted on the next reference. In addition, GRANT and REVOKE statements for database authorization and execute privilege of routines will evict the subject authorization information from the catalog cache. Therefore, the heavy use of DDL statements and GRANT/REVOKE statements may also increase the ratio.

cat_cache_overflows - Catalog Cache Overflows monitor element

The number of times that the catalog cache overflowed the bounds of its allocated memory.

Table 13. 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 14. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl Basic
For snapshot monitoring, this counter can be reset.
Table 15. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected

Usage

Use this element with thecat_cache_size_top monitor element to determine whether the size of the catalog cache needs to be increased to avoid overflowing.

Catalog cache space is reclaimed by evicting table descriptor information for tables, views, or aliases, or authorization information that is not currently in use by any transaction.

If the value of the cat_cache_overflows monitor element is large, the catalog cache may be too small for the workload. Enlarging the catalog cache may improve its performance. If the workload includes transactions which compile a large number of SQL statements referencing many tables, views, aliases, user-defined functions, or stored procedures in a single unit of work, then compiling fewer SQL statements in a single transaction may improve the performance of the catalog cache. Or if the workload includes binding of packages containing many SQL statements referencing many tables, views, aliases, user-defined functions, or stored procedures, you can try splitting packages so that they include fewer SQL statements to improve performance.

cat_cache_size_top - Catalog cache high watermark monitor element

The largest logical size reached by the catalog cache.

Table 16. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 17. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected

Usage

This element indicates the maximum number of bytes the catalog cache required logically for the workload run against the database since it was activated.

The catalog cache is managed by logical size, which does not include memory management usage. The pool_watermark element in the database snapshot provides the physical high water mark value for memory used by the catalog cache. The logical size rather than physical size should be used for catalog cache monitoring and tuning efforts.

If the catalog cache overflowed, then this element contains the largest size reached by the catalog cache during the overflow. Check the cat_cache_overflows monitor element to determine if such a condition occurred.

You can determine the minimum size of the catalog cache required by your workload by:
   maximum catalog cache size / 4096
Rounding the result up to a whole number, indicates the minimum number of 4K pages required by the catalog cache to avoid overflow.

catalog_node - Catalog Node Number monitor element

The node number of the node where the database catalog tables are stored.

Table 18. 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 19. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 20. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
The catalog node is the node where all system catalog tables are stored. All access to system catalog tables must go through this node.

catalog_node_name - Catalog Node Network Name monitor element

The network name of the catalog node.

Element identifier
catalog_node_name
Element type
information
Table 21. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 22. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
Use this element to determine the location of a database.

cf_wait_time - cluster caching facility wait time monitor element

The cf_wait_time monitor element stores the amount of time spent communicating with the cluster caching facility. This time does not include time spent performing any of the processing that may have been requested by, or that may occur as a result of the communications, such as granting locks or performing page reclaims. The unit of measurement is milliseconds.
Table 23. 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 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 package cache statement metrics as an XML document 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) Not applicable; reports whichever elements are in the XML document that is provided as input to the formatting function.
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 REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 24. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) 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 - REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE

Usage

This value is an indicator of the amount of time DB2® has spent waiting while communicating with the cluster caching facility.

cf_waits - Number of cluster caching facility waits monitor element

The number of times that the DB2 database system waited while it communicated with a cluster caching facility.

Table 25. 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 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 package cache statement metrics as an XML document 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 REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 26. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) 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 - REQUEST METRICS BASE
Package cache - ACTIVITY METRICS BASE

cfg_collection_type - Configuration collection type monitor element

Indicates when the configuration parameter value was collected:

Table 27. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History DBDBMCFG Always collected

Usage

The change history event monitor collected this value as:
I
The initial value that was captured when the event monitor was activated.
U
Updated value

cfg_name - Configuration name monitor element

Name of the configuration parameter.

Table 28. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History DBDBMCFG Always collected

Usage

For the change history event monitor, this element identifies the configuration parameter that was updated as part of a DBCFG or DBMCFG event, or captured at event monitor startup as part of a DBCFGVALUES or DBMCFGVALUES event. These events represent the following occurrences:
DBCFG
Changing a database configuration parameter
DBMCFG
Changing a database manager configuration parameter
DBCFGVALUES
Capturing database configuration parameter values at event monitor startup, if a database configuration parameter was changed while the event monitor was inactive
DBMCFGVALUES
Capturing database manager configuration parameter values at event monitor startup, if a database manager configuration parameter was changed while the event monitor was inactive

cfg_old_value - Configuration old value monitor element

The old value for the configuration parameter or the in memory configuration parameter value.

Table 29. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History DBDBMCFG Always collected

Usage

For the change history event monitor:
  • If the event is a change to a database configuration parameter (DBCFG) or a database manager configuration parameter (DBMCFG), this is the old configuration parameter value.
  • If the event is a capture of database configuration parameter values (DBCFGVALUES) or database manager configuration parameter values (DBMCFGVALUES) that changed while the event monitor was inactive, this is the current in-memory configuration parameter value. This is the configuration parameter value currently in use.

cfg_old_value_flags - Configuration old value flags monitor element

This flag indicates how the old configuration parameter value was determined.

Table 30. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History DBDBMCFG Always collected

Usage

For the change history event monitor, this element indicates how the old configuration parameter value was determined:
  • AUTOMATIC
  • COMPUTED
  • NONE
If the event is a capture of database configuration parameter values (DBCFGVALUES) or database manager configuration parameter values (DBMCFGVALUES) that changed while the event monitor was inactive, the flags represent the current in-memory value for the configuration parameter.

cfg_value - Configuration value monitor element

The new value for the configuration parameter or the on-disk configuration parameter value.

Table 31. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History DBDBMCFG Always collected

Usage

For the change history event monitor:
  • If the event is a change to a database configuration parameter (DBCFG) or a database manager configuration parameter (DBMCFG), this is the new value for the configuration parameter.
  • If the event is a capture of database configuration parameter values (DBCFGVALUES) or database manager configuration parameter values (DBMCFGVALUES) that changed while the event monitor was inactive, this is the on-disk configuration parameter value. The on-disk configuration parameter value is the most current value and might not be in effect yet.

cfg_value_flags - Configuration value flags monitor element

This flag indicates how the new configuration parameter value was determined.

Table 32. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History DBDBMCFG Always collected

Usage

For the change history event monitor, this element indicates how the new configuration parameter value was determined:
  • AUTOMATIC
  • COMPUTED
  • NONE
If the event is a capture of database configuration parameter values (DBCFGVALUES) or database manager configuration parameter values (DBMCFGVALUES) that changed while the event monitor was inactive, the flags represent the current on-disk value for the configuration parameter.

ch_auto_tuning - FCM channel auto-tuning indicator monitor element

Indicates whether the number of fast communication manager (FCM) channels is set and tuned automatically. A value of 1 means "Yes"; a value of 0 means "No".

Table 33. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM - Get FCM metrics Always collected

Usage

FCM channel auto-tuning is enabled by setting the fcm_num_channels configuration parameter to AUTOMATIC.

ch_free - Channels Currently Free monitor element

This element indicates the number of FCM communication channels that are currently free.

Table 34. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM - Get FCM metrics Always collected
Table 35. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager fcm Basic

Usage

To calculate the percentage of free FCM channels, use the following formula:
(ch_free/ch_total) * 100

If the percentage of free FCM channels falls below 20% and if the FCM channel auto-tuning is enabled, then DB2 database manager will adjust the FCM channel numbers.

If the percentage of free FCM channels falls below 20% and if the FCM channel auto-tuning is not enabled, then you need to tune the fcm_num_channels configuration parameter.

ch_free_bottom - Minimum Channels Free monitor element

The lowest number of free FCM communication channels reached during processing.

Table 36. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM - Get FCM metrics Always collected
Table 37. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager fcm Basic

Usage

Use this monitor element in conjunction with the fcm_num_channels configuration parameter to determine the maximum connection entry utilization.

ch_max - Maximum possible number of FCM channels monitor element

Maximum number of fast communication manager (FCM) channels that can be allocated based on the amount of virtual memory reserved when the instance was started.

Table 38. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM - Get FCM metrics Always collected
Table 39. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager fcm Basic

Usage

This internal monitor element is used by IBM Support only.

ch_total - Number of currently allocated FCM channels monitor element

Number of fast communication manager (FCM) channels currently allocated. This number includes both in-use channels and free channels.

Table 40. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM - Get FCM metrics Always collected
Table 41. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager fcm Basic

Usage

If the ch_auto_tuning monitor element indicates that FCM is being tuned automatically, then the value of the ch_total monitor element is adjusted based on the demand for FCM channels.

To determine the number of FCM channels currently in use, use the following formula:
ch_total - ch_free
To calculate the percentage of free FCM channels, use the following formula:
(ch_free/ch_total) * 100

If the percentage of free FCM channels falls below 20% and if the FCM channel auto-tuning is enabled, then DB2 database manager will adjust the FCM channel numbers.

If the percentage of free FCM channels falls below 20% and if the FCM channel auto-tuning is not enabled, then you need to tune the fcm_num_channels configuration parameter.

client_acctng - Client accounting string monitor element

The data passed to the target database for logging and diagnostic purposes, if the sqleseti API was issued in this connection. The current value of the CLIENT_ACCTNG special register for this connection, unit of work, or activity.

Note: This element is reported for the coordinating member only. On remote members, the value reported is a string with a length of 0.
This monitor element is synonymous to the tpmon_acc_str monitor element. The client_acctng monitor element is used for monitoring table functions and event monitors that write to unformatted tables, which were introduced in DB2 Version 9.7. The tpmon_acc_str monitor element is used for snapshot monitors and event monitors that write to tables, files, and pipes.
Table 43. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Locking - Always collected
Unit of work - Always collected
Threshold violations event_thresholdviolations Always collected
Change history changesummary Always collected

Usage

Use this element for problem determination and accounting purposes.

client_applname - Client application name monitor element

Identifies the server transaction program performing the transaction, if the sqleseti API was issued in this connection. The current value of the CLIENT_APPLNAME special register for this connection, unit of work, or activity.

Note: This element is reported for the coordinating member only. On remote members, the value reported is a string with a length of 0.
This monitor element is synonymous to the tpmon_client_app monitor element. The client_applname monitor element is used for monitoring table functions and event monitors that write to unformatted tables, which were introduced in DB2 Version 9.7. The tpmon_client_app monitor element is used for snapshot monitors and event monitors that write to tables, files, and pipes.
Table 45. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Locking - Always collected
Unit of work - Always collected
Threshold violations event_thresholdviolations Always collected
Change history changesummary Always collected

Usage

Use this element for problem determination and accounting purposes.

client_db_alias - Database Alias Used by Application monitor element

The alias of the database provided by the application to connect to the database.

Element identifier
client_db_alias
Element type
information
Table 46. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_id_info Basic
Lock appl_lock_list Basic
Table 47. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Connections event_connheader Always collected
Usage
This element can be used to identify the actual database that the application is accessing. The mapping between this name and db_name could be done by using the database directories at the client node and the database manager server node.

This is the alias defined within the database manager where the database connection request originated.

This element can also be used to help you determine the authentication type, since different database aliases can have different authentication types.

client_hostname - Client hostname monitor element

The hostname of the machine the client application is connecting from.

Table 48. 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 (reported in DETAILS XML document) 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 49. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Unit of work - Always collected
Threshold violations event_thresholdviolations Always collected
Change history changesummary Always collected

client_idle_wait_time - Client idle wait time monitor element

This monitor element records time spent waiting for the client to send its next request. The value is given in milliseconds.

Table 51. 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 determine the amount of time spent waiting for requests from a client, as opposed to working on requests. A high client idle time may indicate performance issues that need to be addressed on the client rather than the server.

client_ipaddr - Client IP address monitor element

Contains the IP address of the current client as returned by the operating system. If the client did not connect using the TCP/IP or SSL protocols, the NULL value is returned.

client_nname - Client name monitor element

This monitor element is deprecated. The value returned is not a valid value.

Table 53. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Transaction event_connheader  
Statement event_connheader  
Deadlock event_connheader  
Connections event_connheader  

client_pid - Client process ID monitor element

The process ID of the client application that made the connection to the database.

Table 55. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
Application appl Basic
DCS Application dcs_appl_info Basic
Table 56. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Unit of work - Always collected
Connections event_connheader Always collected
Threshold violations event_thresholdviolations Always collected
Change history changesummary Always collected

Usage

You can use this element to correlate monitor information such as CPU and I/O time to your client application.

In the case of a DRDA AS connection, this element will be set to 0.

client_platform - Client operating platform monitor element

The operating system on which the client application is running.

Table 57. 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 (reported in DETAILS XML document) Always collected
Table 58. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
Application appl Basic
DCS Application dcs_appl_info Basic
Table 59. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Unit of work - Always collected
Connections event_connheader Always collected
Threshold violations event_thresholdviolations Always collected
Change history changesummary Always collected

Usage

This element can be used for problem determination for remote applications. Values for this field can be found in the header file sqlmon.h.

client_port_number - Client port number monitor element

For TCP/IP connections, the port number on the client machine the application is using to communicate with the database server.

Table 60. 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 (reported in DETAILS XML document) 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 61. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Unit of work - Always collected
Threshold violations event_thresholdviolations Always collected
Change history changesummary Always collected

client_prdid - Client product and version ID monitor element

The product and version that is running on the client. This monitor element is a synonym for the client_product_id monitor element.

Table 62. 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 (reported in DETAILS XML document) Always collected
Table 63. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_id_info Basic
DCS Application dcs_appl_info Basic
Table 64. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Unit of work - Always collected
Connections event_connheader Always collected
Threshold violations event_thresholdviolations Always collected

Usage

You can use this element to identify the product and code version of the IBM® data server client. It is in the form PPPVVRRM, where:
  • PPP identifies the product, which is SQL for the DB2 products
  • VV identifies a 2-digit version number (with high-order 0 in the case of a 1-digit version)
  • RR identifies a 2-digit release number (with high-order 0 in the case of a 1-digit release)
  • M identifies a 1-character modification level (0-9 or A-Z).

client_protocol - Client communication protocol monitor element

The communication protocol that the client application is using to communicate with the server.

Table 65. 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 (reported in DETAILS XML document) Always collected
Table 66. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
Application appl Basic
DCS Application dcs_appl_info Basic
Table 67. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Unit of work - Always collected
Connections event_connheader Always collected
Threshold violations event_thresholdviolations Always collected
Change history changesummary Always collected

Usage

This element can be used for problem determination for remote applications. Values for this field are:
SQLM_PROT_UNKNOWN
The client is communicating using an unknown protocol. This value will only be returned if future clients connect with an earlier level of the server.
SQLM_PROT_LOCAL
The client is running on the same node as the server and no communications protocol is in use.
SQLM_PROT_TCPIP
TCP/IP

client_userid - Client user ID monitor element

The client user ID generated by a transaction manager and provided to the server, if the sqleseti API is used. The current value of the CLIENT_USERID special register for this connection, unit of work, or activity.

Note: This element is reported for the coordinating member only. On remote members, the value reported is a string with a length of 0.
This monitor element is synonymous to the tpmon_client_userid monitor element. The client_userid monitor element is used for monitoring table functions and event monitors that write to unformatted tables, which were introduced in DB2 Version 9.7. The tpmon_client_userid monitor element is used for snapshot monitors and event monitors that write to tables, files, and pipes.
Table 69. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Locking - Always collected
Unit of work - Always collected
Threshold violations event_thresholdviolations Always collected
Change history changesummary Always collected

Usage

Use this element in application server or Transaction Processing monitor environments to identify the end-user for whom the transaction is being executed.

client_wrkstnname - Client workstation name monitor element

Identifies the client's system or workstation (for example CICS® EITERMID), if the sqleseti API was issued in this connection. The current value of the CLIENT_WRKSTNNAME special register for this connection, unit of work, or activity.

Note: This element is reported for the coordinating member only. On remote members, the value reported is a string with a length of 0.
This monitor element is synonymous to the tpmon_client_wkstn monitor element. The client_wrkstnname monitor element is used for monitoring table functions and event monitors that write to unformatted tables, which were introduced in DB2 Version 9.7. The tpmon_client_wkstn monitor element is used for snapshot monitors and event monitors that write to tables, files, and pipes.
Table 71. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Locking - Always collected
Unit of work - Always collected
Threshold violations event_thresholdviolations Always collected
Change history changesummary Always collected

Usage

Use this element to identify the user's machine by node ID, terminal ID, or similar identifiers.

codepage_id - ID of Code Page Used by Application monitor element

The code page identifier.

Table 72. 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 73. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Event Log Header event_log_header Always collected
Connections event_connheader Always collected
Usage
For snapshot monitor data, this is the code page at the partition where the monitored application started. This identifier may be used for problem determination for remote applications. You may use this information to ensure that data conversion is supported between the application code page and the database code page (or for DRDA host databases, the host CCSID). For information about supported code pages, see the Administration Guide .

For event monitor data, this is the code page of the database for which event data is collected. You can use this element to determine whether your event monitor application is running under a different code page from that used by the database. Data written by the event monitor uses the database code page. If your event monitor application uses a different code page, you may need to perform some character conversion to make the data readable.

col_object_l_pages - Column-organized logical pages monitor element

The number of logical pages used on disk by column-organized data contained in this table.

Table 74. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLE table function - get table metrics Always collected

Usage

This value might be:
  • More than the amount of space allocated for the object. This can happen when you use the RECLAIM EXTENTS ONLY option with the REORG TABLE command. In this case, reclaimed extents are included in the logical number of pages returned by MON_GET_TABLE.
  • Less than the amount of space physically allocated for the object. This can happen when you use the REUSE STORAGE option of the TRUNCATE statement. This option causes storage allocated for the table to continue to be allocated, although the storage will be considered empty. In addition, the value for this monitor element might be less than the amount of space logically allocated for the object, because the total space logically allocated includes a small amount of additional meta data.

col_object_l_size - Column-organized data object logical size monitor element

Amount of disk space logically allocated for the column-organized data in the table, reported in kilobytes.

Table 75. Table function monitoring information
Table function Monitor element collection level
ADMIN_GET_TAB_INFO table function - retrieve table size and state information Always collected

Usage

This size represents the logical size of the base column-organized table data only.

col_object_p_size - Column-organized data object physical size monitor element

Amount of disk space physically allocated for the column-organized data in the table, reported in kilobytes.

Table 76. Table function monitoring information
Table function Monitor element collection level
ADMIN_GET_TAB_INFO table function - retrieve table size and state information Always collected

Usage

The size returned takes into account full extents allocated for the table and includes the EMP extents for objects created in DMS table spaces. This size represents the physical size of the base columnar data only. Space consumed by LOB data, Long Data, Indexes and XML objects are reported by other columns.

comm_exit_wait_time - Communication exit wait time monitor element

Time spent waiting for the return from a communication exit library API function. The value is given in milliseconds.

Table 77. 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 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 package cache statement metrics as an XML document 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 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 78. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Package Cache pkgcache_metrics ACTIVITY METRICS BASE
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

comm_exit_waits - Communication exit number of waits monitor element

The number of times a communication exit library API function is called.

Table 79. 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 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 package cache statement metrics as an XML document 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 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 80. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Package Cache pkgcache_metrics ACTIVITY METRICS BASE
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

comm_private_mem - Committed Private Memory monitor element

The amount of private memory that the instance of the database manager has currently committed at the time of the snapshot. The comm_private_mem value returned is only relevant on Windows operating systems.

Table 81. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Basic

commit_sql_stmts - Commit Statements Attempted monitor element

The total number of SQL COMMIT statements that have been attempted.

Table 82. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Database dbase_remote Basic
Application appl Basic
Application appl_remote Basic
DCS Database dcs_dbase Basic
DCS Application dcs_appl Basic
For snapshot monitoring, this counter can be reset.
Table 83. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
Usage
A small rate of change in this counter during the monitor period may indicate that applications are not doing frequent commits, which may lead to problems with logging and data concurrency.
You can also use this element to calculate the total number of units of work using the following expression:
 
   commit_sql_stmts
 + int_commits
 + rollback_sql_stmts
 + int_rollbacks
Note: The units of work calculated will only include those since the later of:
  • The connection to the database (for database-level information, this is the time of the first connection)
  • The last reset of the database monitor counters.

This calculation can be done at a database or application level.

comp_env_desc - Compilation environment monitor element

This element stores information about the compilation environment used when compiling the SQL statement.

Table 84. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache Always collected
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries Always collected
Table 85. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks with Details History Values event_stmt_history Always collected
Deadlocks with Details History event_stmt_history Always collected
Activities event_activitystmt Always collected
Package cache - Always collected

Usage

This monitor element stores the compilation environment description in a binary large object. To view this information in readable form, use the COMPILATION_ENV table function.

You can provide this element as input to the COMPILATION_ENV table function, or to the SET COMPILATION ENVIRONMENT SQL statement.

completion_status - Completion status monitor element

The status of the unit of work.

Table 86. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Unit of work - Always collected

Usage

Use this element to determine if the unit of work ended due to a deadlock or abnormal termination. The possible values are listed in the sqllib/misc/DB2EvmonUOW.xsd file:
  • UNKNOWN
  • COMMIT
  • ROLLBACK
  • GLOBAL_COMMIT
  • GLOBAL_ROLLBACK
  • XA_END
  • XA_PREPARE

con_elapsed_time - Most Recent Connection Elapsed Time monitor element

The elapsed time that the DCS application that most recently disconnected from this host database was connected.

Element identifier
con_elapsed_time
Element type
time
Table 87. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Database dcs_dbase Timestamp

Usage

Use this element as an indicator of the length of time that applications are maintaining connections to a host database.

This element is composed of two subelements that report time spent as seconds and microseconds (one millionth of a second). The names of the subelements can be derived by adding "_s" and "_ms" to the name of this monitor element. To retrieve the total time spent for this monitor element, the values of the two subelements must be added together. For example, if the "_s" subelement value is 3 and the "_ms" subelement value is 20, then the total time spent for the monitor element is 3.00002 seconds.

con_local_dbases - Local Databases with Current Connects monitor element

The number of local databases that have applications connected.

Table 88. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INSTANCE table function - Get instance level information Always collected
Table 89. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Basic
Usage
This value gives an indication of how many database information records you can expect when gathering data at the database level.

The applications can be running locally or remotely, and may or may not be executing a unit of work within the database manager

con_response_time - Most Recent Response Time for Connect monitor element

The elapsed time between the start of connection processing and actual establishment of a connection, for the most recent DCS application that connected to this database.

Element identifier
con_response_time
Element type
time
Table 90. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Database dcs_dbase Timestamp

Usage

Use this element as an indicator of the time it currently takes applications to connect to a particular host database.

This element is composed of two subelements that report time spent as seconds and microseconds (one millionth of a second). The names of the subelements can be derived by adding "_s" and "_ms" to the name of this monitor element. To retrieve the total time spent for this monitor element, the values of the two subelements must be added together. For example, if the "_s" subelement value is 3 and the "_ms" subelement value is 20, then the total time spent for the monitor element is 3.00002 seconds.

concurrent_act_top - Concurrent activity top monitor element

The high watermark for the concurrent activities (at any nesting level) in a service subclass since the last reset.

Note: This element monitors the highest concurrent execution of all activities, including those activities that do not participate in the CONCURRENTDBCOORDACTIVITIES threshold. For example, although CALL statements do not count toward the concurrency that is enforced by the CONCURRENTDBCOORDACTIVITIES threshold, they are included in the concurrent activity high watermark measurement.
Table 92. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats Always collected

Usage

Use this element to know the highest concurrency of activities (including nested activities) reached on a member for a service subclass in the time interval collected.

concurrent_connection_top - Concurrent connection top monitor element

High watermark for concurrent coordinator connections in this service class since the last reset. This field has the same value in every subclass of the same superclass.

Table 93. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_SERVICE_SUPERCLASS_STATS table function - Return statistics of service superclasses Always collected
WLM_GET_SERVICE_SUPERCLASS_STATS table function - Return statistics of service superclasses Always collected
Table 94. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats Always collected

Usage

This element may be useful in determining where to place thresholds on connection concurrency by showing where the current high watermark is. It is also useful for verifying that such a threshold is configured correctly and doing its job.

concurrent_wlo_act_top - Concurrent WLO activity top monitor element

High watermark for concurrent activities (at any nesting level) of any occurrence of this workload since the last reset.

Table 95. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_WORKLOAD_STATS table function - Return workload statistics Always collected
WLM_GET_WORKLOAD_STATS table function - Return workload statistics Always collected
Table 96. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_wlstats Always collected

Usage

Use this element to know the highest number of concurrent activities reached on a member for any occurrence of this workload in the time interval collected.

concurrent_wlo_top - Concurrent workload occurrences top monitor element

The high watermark for the concurrent occurrences of a workload since the last reset.

Table 98. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_wlstats Always collected
Statistics event_scstats Always collected

Usage

Use this element to know the highest concurrency of workload occurrences reached on a member for a workload in the time interval collected.

concurrentdbcoordactivities_db_threshold_id - Concurrent database coordinator activities database threshold ID monitor element

The ID of the CONCURRENTDBCOORDACTIVITIES database threshold that was applied to the activity.

Table 99. 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 CONCURRENTDBCOORDACTIVITIES database threshold, if any, was applied to the activity.

concurrentdbcoordactivities_db_threshold_queued - Concurrent database coordinator activities database threshold queued monitor element

This monitor element returns '1' (Yes) to indicate that the activity was queued by the CONCURRENTDBCOORDACTIVITIES database threshold. A value of ’0’ (No) indicates that the activity was not queued.

Table 100. 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 if the activity was queued by the CONCURRENTDBCOORDACTIVITIES database threshold applied to the activity.

concurrentdbcoordactivities_db_threshold_value - Concurrent database coordinator activities database threshold value monitor element

This monitor element returns the upper bound of the CONCURRENTDBCOORDACTIVITIES database threshold that was applied to the activity.

Table 101. 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 CONCURRENTDBCOORDACTIVITIES database threshold applied to the activity, if any.

concurrentdbcoordactivities_db_threshold_violated - Concurrent database coordinator activities database threshold violated monitor element

This monitor element returns '1' (Yes) to indicate that the activity violated the CONCURRENTDBCOORDACTIVITIES database threshold. A value of ’0’ (No) indicates that the activity has not yet violated the threshold.

Table 102. 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 CONCURRENTDBCOORDACTIVITIES database threshold that was applied to the activity.

concurrentdbcoordactivities_subclass_threshold_id - Concurrent database coordinator activities service subclass threshold ID monitor element

This monitor element returns the ID of the CONCURRENTDBCOORDACTIVITIES service subclass threshold that was applied to the activity.

Table 103. 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 CONCURRENTDBCOORDACTIVITIES service subclass threshold, if any, was applied to the activity.

concurrentdbcoordactivities_subclass_threshold_queued - Concurrent database coordinator activities service subclass threshold queued monitor element

This monitor element returns '1' (Yes) to indicate that the activity was queued by the CONCURRENTDBCOORDACTIVITIES service subclass threshold. A value of '0' (No) indicates that the activity was not queued.

Table 104. 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 if the activity was queued by the CONCURRENTDBCOORDACTIVITIES service subclass threshold applied to the activity.

concurrentdbcoordactivities_subclass_threshold_value - Concurrent database coordinator activities service subclass threshold value monitor element

This monitor element returns the upper bound of the CONCURRENTDBCOORDACTIVITIES service subclass 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 the value of the CONCURRENTDBCOORDACTIVITIES service subclass threshold applied to the activity, if any.

concurrentdbcoordactivities_subclass_threshold_violated - Concurrent database coordinator activities service subclass threshold violated monitor element

This monitor element returns '1' (Yes) to indicate that the activity violated the CONCURRENTDBCOORDACTIVITIES service subclass threshold. A value of '0' (No) indicates that the activity has not yet violated the threshold.

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 determine if the activity violated the CONCURRENTDBCOORDACTIVITIES service subclass threshold that was applied to the activity.

concurrentdbcoordactivities_superclass_threshold_id - Concurrent database coordinator activities service superclass threshold ID monitor element

The ID of the CONCURRENTDBCOORDACTIVITIES_SUPERCLASS threshold that was applied to the activity.

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 understand which CONCURRENTDBCOORDACTIVITIES service superclass threshold, if any, was applied to the activity.

concurrentdbcoordactivities_superclass_threshold_queued - Concurrent database coordinator activities service superclass threshold queued monitor element

This monitor element returns '1’ (Yes) to indicate that the activity was queued by the CONCURRENTDBCOORDACTIVITIES service superclass threshold. A value of '0' (No) indicates that the activity was not queued.

Table 108. 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 if the activity was queued by the CONCURRENTDBCOORDACTIVITIES service superclass threshold applied to the activity.

concurrentdbcoordactivities_superclass_threshold_value - Concurrent database coordinator activities service superclass threshold value monitor element

The upper bound of the CONCURRENTDBCOORDACTIVITIES service superclass threshold that was applied to the activity.

Table 109. 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 CONCURRENTDBCOORDACTIVITIES service superclass threshold applied to the activity, if any.

concurrentdbcoordactivities_superclass_threshold_violated - Concurrent database coordinator activities service superclass threshold violated monitor element

This monitor element returns '1' (Yes) to indicate that the activity violated the CONCURRENTDBCOORDACTIVITIES service superclass threshold. A value of '0' (No) indicates that the activity has not yet violated the threshold.

Table 110. 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 CONCURRENTDBCOORDACTIVITIES service superclass threshold that was applied to the activity.

concurrentdbcoordactivities_wl_was_threshold_id - Concurrent database coordinator activities workload work action set threshold ID monitor element

The identifier of the CONCURRENTDBCOORDACTIVITIES workload work action set threshold that was applied to the activity.

Table 111. 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 CONCURRENTDBCOORDACTIVITIES workload work action set threshold, if any, was applied to the activity.

concurrentdbcoordactivities_wl_was_threshold_queued - Concurrent database coordinator activities workload work action set threshold queued monitor element

This monitor element returns '1’ (Yes) to indicate that the activity was queued by the CONCURRENTDBCOORDACTIVITIES workload work action set threshold. A value of ’0’ (No) indicates that the activity was not queued.

Table 112. 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 if the activity was queued by the CONCURRENTDBCOORDACTIVITIES workload work action set threshold applied to the activity.

concurrentdbcoordactivities_wl_was_threshold_value - Concurrent database coordinator activities workload work action set threshold value monitor element

The upper bound of the CONCURRENTDBCOORDACTIVITIES workload work action set threshold that was applied to the activity.

Table 113. 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 CONCURRENTDBCOORDACTIVITIES workload work action set threshold applied to the activity.

concurrentdbcoordactivities_wl_was_threshold_violated - Concurrent database coordinator activities workload work action set threshold violated monitor element

This monitor element returns '1’ (Yes) to indicate that the activity violated the CONCURRENTDBCOORDACTIVITIES workload work action set threshold. A value of ’0’ (No) indicates that the activity has not yet violated the threshold.

Table 114. 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 CONCURRENTDBCOORDACTIVITIES workload work action set threshold that was applied to the activity.

concurrentdbcoordactivities_work_action_set_threshold_id - Concurrent database coordinator activities work action set threshold ID monitor element

The identifier of the CONCURRENTDBCOORDACTIVITIES database work action set threshold that was applied to the activity.

Table 115. 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 CONCURRENTDBCOORDACTIVITIES work action set threshold, if any, was applied to the activity.

concurrentdbcoordactivities_work_action_set_threshold_queued - Concurrent database coordinator activities work action set threshold queued monitor element

This monitor element returns '1' (Yes) to indicate that the activity was queued by the CONCURRENTDBCOORDACTIVITIES database work action set threshold. A value of '0' (No) indicates that the activity was not queued.

Table 116. 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 if the activity was queued by the CONCURRENTDBCOORDACTIVITIES database work action set threshold applied to the activity.

concurrentdbcoordactivities_work_action_set_threshold_value - Concurrent database coordinator activities work action set threshold value monitor element

The upper bound of the CONCURRENTDBCOORDACTIVITIES database work action set threshold that was applied to the activity.

Table 117. 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 CONCURRENTDBCOORDACTIVITIES database work action set threshold applied to the activity, if any.

concurrentdbcoordactivities_work_action_set_threshold_violated - Concurrent database coordinator activities work action set threshold violated monitor element

This monitor element returns '1' (Yes) to indicate that the activity violated the CONCURRENTDBCOORDACTIVITIES database work action set threshold. A value of '0' (No) indicates that the activity has not yet violated the threshold.

Table 118. 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 CONCURRENTDBCOORDACTIVITIES database work action set threshold that was applied to the activity.

concurrentworkloadactivities_threshold_id - Concurrent workload activities threshold ID monitor element

The ID of the CONCURRENTWORKLOADACTIVITIES workload threshold that was applied to the activity.

Table 119. 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 Always collected
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities Always collected

concurrentworkloadactivities_threshold_value - Concurrent workload activities threshold value monitor element

This monitor element returns the upper bound of the CONCURRENTWORKLOADACTIVITIES workload threshold that was applied to the activity.

Table 120. 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 Always collected
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities Always collected

concurrentworkloadactivities_threshold_violated - Concurrent workload activities threshold violated monitor element

This monitor element returns '1' (Yes) to indicate that the activity violated the CONCURRENTWORKLOADACTIVITIES workload threshold. A value of ’0’ (No) indicates that the activity has not yet violated the threshold.

Table 121. 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 Always collected
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities Always collected

configured_cf_gbp_size - Configured cluster caching facility group buffer pool size monitor element

The allocated and reserved group buffer pool memory specified using the cf_gbp_sz configuration parameter, in pages with a page size of 4 KB.

Table 122. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CF table function - Get CF metrics Always collected

configured_cf_lock_size - Configured cluster caching facility lock size monitor element

Global lock memory configured, in pages with a page size of 4 KB. This value is specified using the cf_lock_sz configuration parameter.

Table 123. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CF table function - Get CF metrics Always collected

configured_cf_sca_size - Configured cluster caching facility shared communications area size monitor element

Shared communications area memory currently allocated and reserved, in pages with a page size of 4 KB. This value is specified using the cf_sca_sz configuration parameter.

Table 124. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CF table function - Get CF metrics Always collected

configured_cf_mem_size - Configured cluster caching facility memory size monitor element

Configured total memory size for the cluster caching facility, in pages with a page size of 4 KB. This value is specified using the cf_mem_sz configuration parameter.

Table 125. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CF table function - Get CF metrics Always collected

conn_complete_time - Connection Request Completion Timestamp monitor element

The date and time that a connection request was granted.

Element identifier
conn_complete_time
Element type
timestamp
Table 126. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Timestamp
Usage
Use this element to determine when a connection request to the database was granted.

conn_time - Time of database connection monitor element

The date and time of the connection to the database (at the database level, this is the first connection to the database), or when the activate database was issued.

Table 127. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Unit of work - Always collected
Database event_dbheader Always collected
Connections event_connheader Always collected

Usage

Use this element with the disconn_time monitor element to calculate the elapsed time since:
  • The database was active (for information at the database level).
  • The connection was active (for information at the connection level).

connection_start_time - Connection start time monitor element

The time at which the connection was established with the database server. The connection_time monitor element is an alias of the connection_start_time monitor element.

Table 128. 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 (reported in DETAILS XML document) Always collected
Table 129. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Threshold violations event_thresholdviolations Always collected

connection_status - Connection Status monitor element

For snapshot monitor, this monitor element reports the status of the communication connection between the node issuing the GET SNAPSHOT command and other nodes listed in the db2nodes.cfg file. For table function monitor, this monitor element reports the text identifier indicating the FCM connection status.

Table 130. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM_CONNECTION_LIST - Get details for all FCM connections Always collected
Table 131. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager fcm_node Basic

Usage

For the snapshot monitor, the connection values are :
SQLM_FCM_CONNECT_INACTIVE
No current connection
SQLM_FCM_CONNECT_ACTIVE
Connection is active
For table function monitoring, the available values are:
Active
No current connection
Inactive
Connection is active

Two members can be active, but the communication connection between them remains inactive until there is some communication between the members.

connections_top - Maximum Number of Concurrent Connections monitor element

The highest number of simultaneous connections to the database since the database was activated.

Table 132. 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 133. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 134. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
You may use this element to evaluate the setting of the maxappls configuration parameter.

If the value of this element is the same as the maxappls parameter, it is likely that some database connection requests were rejected, since maxappls limits the number of database connections allowed.

The current number of connections at the time the snapshot was taken can be calculated using the following formula:
 rem_cons_in + local_cons 

consistency_token - Package consistency token monitor element

For a given package name and creator, there can exist (starting in DB2 Version 8) multiple versions. The package consistency token helps to identify the version of the package that contains the SQL currently executing.

Table 135. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
Table 136. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Statements event_stmt Always collected

Usage

You can use this element to help identify the package and the SQL statement that is executing.

container_accessible - Accessibility of container monitor element

This element indicates whether a container is accessible. A value of 1 means "Yes"; a value of 0 means "No".

Table 137. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONTAINER table function - Get table space container metrics Always collected
Table 138. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_container Basic
Usage
This element can be used in conjunction with the elements container_id, container_name, container_type, container_total_pages, container_usable_pages, and container_stripe_set to describe the container.

container_id - Container identification monitor element

An integer that uniquely defines a container within a table space.

Table 139. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_LOCK_NAME table function - Format the internal lock name and return details Always collected
MON_GET_CONTAINER table function - Get container metrics Always collected
Table 140. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_container Basic
Usage
This element can be used in conjunction with the elements container_name, container_type, container_total_pages, container_usable_pages, container_stripe_set, and container_accessible to describe the container.

container_name - Container name monitor element

The name of a container.

Table 141. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONTAINER table function - Get container metrics Always collected
Table 142. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_container Basic
Usage
This element can be used in conjunction with the elements container_id, container_type, container_total_pages, container_usable_pages, container_stripe_set, and container_accessible to describe the container.

container_stripe_set - Container stripe set monitor element

The stripe set that a container belongs to.

Table 143. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_CONTAINER table function - Get table space container metrics Always collected
Table 144. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_container Basic

Usage

Use this monitor element in conjunction with the elements container_id, container_name, container_type, container_total_pages, container_usable_pages, and container_accessible to describe the container. This is only applicable to a DMS table space.

container_total_pages - Total pages in container monitor element

The total number of pages occupied by the container.

Table 145. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_CONTAINER table function - Get table space container metrics Always collected
Table 146. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_container Basic (DMS table spaces)

Buffer Pool (SMS table spaces)

Usage
This element can be used in conjunction with the elements container_id, container_name, container_type, container_usable_pages, container_stripe_set, and container_accessible to describe the container.

container_type - Container type monitor element

The type of the container.

Table 147. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONTAINER table function - Get container metrics Always collected
Table 148. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_container Basic

Usage

This element returns the type of the container, which can be a directory path (for SMS only), file (for DMS) or a raw device (for DMS). This element can be used in conjunction with the elements container_id, container_name, container_total_pages, container_usable_pages, container_stripe_set, and container_accessible to describe the container.

The valid values for this monitor element are defined in the sqlutil.h file.

container_usable_pages - Usable pages in container monitor element

The total number of usable pages in a container.

Table 149. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_CONTAINER table function - Get table space container metrics Always collected
Table 150. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table Space tablespace_container Basic (DMS table spaces)

Buffer Pool (SMS table spaces)

Usage
This element can be used in conjunction with the elements container_id, container_name, container_type, container_total_pages, container_stripe_set, and container_accessible to describe the container. For SMS table spaces, this value is the same as container_total_pages.

coord_act_aborted_total - Coordinator activities aborted total monitor element

The total number of coordinator activities at any nesting level that completed with errors since the last reset. For service classes, the value is updated when the activity completes. For workloads, the value is updated by each workload occurrence at the end of its unit of work.

For service classes, if you remap an activity to a different subclass with a REMAP ACTIVITY action before it aborts, then this activity counts only toward the total of the subclass it aborts in.

Table 152. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats Always collected
Statistics event_wlstats Always collected

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.

coord_act_completed_total - Coordinator activities completed total monitor element

The total number of coordinator activities at any nesting level that completed successfully since the last reset. For service classes, the value is updated when the activity completes. For workloads, the value is updated by each workload occurrence at the end of its unit of work.

For service classes, if you remap an activity 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 154. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_wlstats Always collected
Statistics event_scstats Always collected

Usage

This element can be used to determine the throughput of activities in the system or to aid in calculating average activity lifetime across multiple members.

coord_act_est_cost_avg - Coordinator activity estimated cost average monitor element

Arithmetic mean of the estimated costs for coordinator DML activities at nesting level 0 associated with this service subclass or work class since the last reset. If the internally tracked average has overflowed, the value -2 is returned. For service subclasses, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the service subclass is set to NONE or BASE. For work classes, this monitor elements returns -1 if no COLLECT AGGREGATE ACTIVITY DATA EXTENDED work action is specified for the work class. For workloads, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the workload is set to NONE or BASE. It is measured in units of timerons.

For service classes, the estimated cost of an activity is counted only toward the service subclass in which the activity enters the system. When you remap activities between service subclasses with a REMAP ACTIVITY action, the coord_act_est_cost_avg mean of the service subclass you remap an activity to is unaffected.

Table 155. 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 statistic to determine the arithmetic mean of the estimated costs of coordinator DML activities at nesting level 0 that are associated this service subclass, workload, or work class that completed or aborted since the last statistics reset.

This average can also be used to determine whether or not the histogram template used for the activity estimated cost histogram is appropriate. Compute the average activity estimated cost from the activity estimated cost histogram. Compare the computed average with this monitor element. If the computed average deviates from the true average reported by this monitor element, consider altering the histogram template for the activity estimated cost histogram, using a set of bin values that are more appropriate for your data.

coord_act_exec_time_avg - Coordinator activities execution time average monitor element

Arithmetic mean of execution times for coordinator activities at nesting level 0 associated with this service subclass or work class since the last reset. If the internally tracked average has overflowed, the value -2 is returned. For service subclasses, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the service subclass is set to NONE. For work classes, this monitor elements returns -1 if no COLLECT AGGREGATE ACTIVITY DATA work action is specified for the work class. For workloads, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the workload is set to NONE. Units are milliseconds.

For service classes, when you remap activities between service subclasses with a REMAP ACTIVITY action, the coord_act_exec_time_avg mean of service subclasses an activity is mapped to but does not complete in is unaffected.

Table 156. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses ACTIVITY METRICS BASE
MON_GET_WORKLOAD_STATS table function - Return workload statistics ACTIVITY METRICS BASE
WLM_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses COLLECT AGGREGATE ACTIVITY DATA
WLM_GET_WORKLOAD_STATS table function - Return workload statistics COLLECT AGGREGATE ACTIVITY DATA
Table 157. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats -
Statistics event_wcstats -
Statistics event_wlstats -

Usage

Use this statistic to determine the arithmetic mean of execution time for coordinator activities associated with a service subclass, workload, or work class that completed or aborted.

This average can also be used to determine whether or not the histogram template used for the activity execution time histogram is appropriate. Compute the average activity execution time from the activity execution time histogram. Compare the computed average with this monitor element. If the computed average deviates from the true average reported by this monitor element, consider altering the histogram template for the activity execution time histogram, using a set of bin values that are more appropriate for your data.

coord_act_interarrival_time_avg - Coordinator activity arrival time average monitor element

Arithmetic mean of the time between arrivals of coordinator activities at nesting level 0 associated with this service subclass or work class since the last reset. If the internally tracked average has overflowed, the value -2 is returned. For service subclasses, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the service subclass is set to NONE or BASE. For work classes, this monitor elements returns -1 if no COLLECT AGGREGATE ACTIVITY DATA EXTENDED work action is specified for the work class. For workloads, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the workload is set to NONE or BASE. It is measured in milliseconds.

For service classes, the inter-arrival time mean is calculated for service subclasses through which activities enter the system. When you remap activities between service subclasses with a REMAP ACTIVITY action, the coord_act_interarrival_time_avg of the service subclass you remap an activity to is unaffected.

Table 158. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats -
Statistics event_wcstats -
Statistics event_wlstats -

Usage

Use this statistic to determine the arithmetic mean between arrivals of coordinator activities at nesting level 0 associated with this service subclass, workload, or work class.

The inter-arrival time can be used to determine arrival rate, which is the inverse of inter-arrival time. This average can also be used to determine whether or not the histogram template used for the activity inter-arrival time histogram is appropriate. Compute the average activity inter-arrival time from the activity inter-arrival time histogram. Compare the computed average with this monitor element. If the computed average deviates from the true average reported by this monitor element, consider altering the histogram template for the activity inter-arrival time histogram, using a set of bin values that are more appropriate for your data.

coord_act_lifetime_avg - Coordinator activity lifetime average monitor element

Arithmetic mean of lifetime for coordinator activities at nesting level 0 associated with this service subclass, workload, or work class since the last reset. If the internally tracked average has overflowed, the value -2 is returned. For service subclasses, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the service subclass is set to NONE. For work classes, this monitor elements returns -1 if no COLLECT AGGREGATE ACTIVITY DATA work action is specified for the work class. For workloads, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the workload is set to NONE. It is measured in milliseconds.

For service classes, when you remap activities between service subclasses with a REMAP ACTIVITY action, only the the coord_act_lifetime_avg mean of the final service class where the activity completes is affected.

Table 159. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses ACTIVITY METRICS BASE
MON_GET_WORKLOAD_STATS table function - Return workload statistics ACTIVITY METRICS BASE
WLM_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses COLLECT AGGREGATE ACTIVITY DATA
WLM_GET_WORKLOAD_STATS table function - Return workload statistics COLLECT AGGREGATE ACTIVITY DATA
Table 160. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats -
Statistics event_wcstats -
Statistics event_wlstats -

Usage

Use this statistic to determine the arithmetic mean of the lifetime for coordinator activities associated with a service subclass, workload, or work class that completed or aborted.

This statistic can also be used to determine whether or not the histogram template used for the activity lifetime histogram is appropriate. Compute the average activity lifetime from the activity lifetime histogram. Compare the computed average with this monitor element. If the computed average deviates from the true average reported by this monitor element, consider altering the histogram template for the activity lifetime histogram, using a set of bin values that are more appropriate for your data.

coord_act_lifetime_top - Coordinator activity lifetime top monitor element

The coord_act_lifetime_top element is a high watermark for coordinator activity lifetime, counted at all nesting levels. The stored information is represented in milliseconds. For service classes, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the service class is set to NONE. For work classes, this monitor element returns -1 if no COLLECT AGGREGATE ACTIVITY DATA work action is specified for the work class. For workloads, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the workload is set to NONE.

To effectively use this statistic with service classes when you also remap activities between service subclasses with a REMAP ACTIVITY action, you must aggregate the coord_act_lifetime_top high watermark of any given service subclass with that of other subclasses affected by the same remapping threshold or thresholds. This is because an activity will complete after it has been remapped to a different service subclass by a remapping threshold, and the time the activity spends in other service subclasses before being remapped is counted only toward the service class in which it completes.

Table 161. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses ACTIVITY METRICS BASE
MON_GET_WORKLOAD_STATS table function - Return workload statistics ACTIVITY METRICS BASE
WLM_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses COLLECT AGGREGATE ACTIVITY DATA
WLM_GET_WORKLOAD_STATS table function - Return workload statistics COLLECT AGGREGATE ACTIVITY DATA
Table 162. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_wcstats -
Statistics event_scstats -
Statistics event_wlstats -

Usage

This element can be used to help determine whether or not thresholds on activity lifetime are being effective and can also help to determine how to configure such thresholds.

coord_act_queue_time_avg - Coordinator activity queue time average monitor element

Arithmetic mean of queue time for coordinator activities at nesting level 0 associated with this service subclass or work class since the last reset. If the internally tracked average has overflowed, the value -2 is returned. For service subclasses, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the service subclass is set to NONE. For work classes, this monitor elements returns -1 if no COLLECT AGGREGATE ACTIVITY DATA work action is specified for the work class. For workloads, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the workload is set to NONE. It is measured in milliseconds.

For service classes, the queue time counts only toward the service subclass in which the activity completes or is aborted. When you remap activities between service subclasses with a REMAP ACTIVITY action, the coord_act_queue_time_avg mean of service subclasses an activity is mapped to but does not complete in is unaffected.

Table 163. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses ACTIVITY METRICS BASE
MON_GET_WORKLOAD_STATS table function - Return workload statistics ACTIVITY METRICS BASE
WLM_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses COLLECT AGGREGATE ACTIVITY DATA
WLM_GET_WORKLOAD_STATS table function - Return workload statistics COLLECT AGGREGATE ACTIVITY DATA
Table 164. 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 statistic to determine the arithmetic mean of the queue time for coordinator activities associated with a service subclass, workload, or work class that completed or aborted.

This statistic can also be used to determine whether or not the histogram template used for the activity queue time histogram is appropriate. Compute the average activity queue time from the activity queue time histogram. Compare the computed average with this monitor element. If the computed average deviates from the true average reported by this monitor element, consider altering the histogram template for the activity queue time histogram, using a set of bin values that are more appropriate for your data.

coord_act_rejected_total - Coordinator activities rejected total monitor element

The coord_act_rejected_total stores the total number of coordinator activities at any nesting level that were rejected instead of being allowed to execute since the last reset. This counter is updated when an activity is prevented from executing by either a predictive threshold or a prevent execution work action. For service classes, the value is updated when the activity completes. For workloads, the value is updated by each workload occurrence at the end of its unit of work.
Table 166. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats Always collected
Statistics event_wlstats Always collected

Usage

This element can be used to help determine whether or not predictive thresholds and work actions that prevent execution are being effective and whether or not they are too restrictive.

coord_agent_pid - Coordinator agent identifier monitor element

The engine dispatchable unit (EDU) identifier of the coordinator agent for the application. 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 DB2 generated unique identifier.

Table 167. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
Table 168. 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.

coord_agent_tid - Coordinator agent engine dispatchable unit ID monitor element

The engine dispatchable unit (EDU) identifier of the coordinator agent for the application.

Table 169. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking lock_participants  

coord_agents_top - Maximum Number of Coordinating Agents monitor element

The maximum number of coordinating agents working at one time.

Table 170. 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
MON_GET_INSTANCE table function - Get instance level information Always collected
Table 171. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Basic
Database dbase Basic

Usage

If the peak number of coordinating agents represents too high a workload for this node, you can reduce this upper boundary by changing the max_coordagents configuration parameter.

coord_member - Coordinator member monitor element

Coordinating member for an application.

Table 173. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Unit of work - Always collected
Change history changesummary Always collected

coord_node - Coordinating Node monitor element

In a multi-node system, the node number of the node where the application connected or attached to the instance.

Table 174. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Table 175. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Connection event_conn Always collected
Usage
Each connected application is served by one coordinator node.

coord_partition_num - Coordinator partition number monitor element

The coordinator partition of the unit of work or activity. In a multi-partition system, the coordinator partition is the partition where the application connected to the database.

Table 177. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Unit of work - Always collected
Activities event_activity Always collected
Threshold violations event_thresholdviolations Always collected

Usage

This element allows the coordinator partition to be identified for activities or units of work that have records on partitions other than the coordinator.

coord_stmt_exec_time - Execution time for statement by coordinator agent monitor element

The total time spent executing this statement by coordinator agents on this member. The value is given in milliseconds.

Table 179. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitymetrics ACTIVITY METRICS BASE
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

corr_token - DRDA Correlation Token monitor element

The DRDA AS correlation token.

Table 180. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
Application appl Basic
Table 181. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Connections event_connheader Always collected
Usage
The DRDA correlation token is used for correlating the processing between the application server and the application requester. It is an identifier dumped into logs when errors arise, that you can use to identify the conversation that is in error. In some cases, it will be the LUWID of the conversation.

If communications are not using DRDA, this element returns the appl_id (see appl_id).

If you are using the database system monitor APIs, note that the API constant SQLM_APPLID_SZ is used to define the length of this element.

cost_estimate_top - Cost estimate top monitor element

The cost_estimate_top monitor element is a high watermark for the estimated cost of DML activities at all nesting levels in a service subclass or work class. For service subclasses, this monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for the service subclass is set to NONE. For work classes, this monitor elements returns -1 if no COLLECT AGGREGATE ACTIVITY DATA work action is specified for the work class.

For service classes, the estimated cost of DML activities is counted only toward the service subclass in which the activity enters the system. When you remap activities between service subclasses with a REMAP ACTIVITY action, the cost_estimate_top of the service subclass you remap an activity to is unaffected.

Table 182. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats -
Statistics event_wcstats -
Statistics event_wlstats -

Usage

Use this element to determine the highest DML activity estimated cost reached on a member for a service class, workload, or work class in the time interval collected.

count - Number of Event Monitor Overflows monitor element

The number of consecutive overflows that have occurred.

Element identifier
count
Element type
counter
Table 183. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Overflow Record event_overflow Always collected
Usage
You may use this element to get an indication of how much monitor data has been lost.

The event monitor sends one overflow record for a set of consecutive overflows.

cpu_configured - Number of configured CPUs monitor element

The number of processors on this host that the operating system is aware of.

Table 184. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 185. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This metric reports point in time information at statistics event monitor record generation.

cpu_cores_per_socket - Number of CPU cores per socket monitor element

The number of CPU cores per socket. On single core systems, this value is 1.

Table 186. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 187. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This metric reports point in time information at statistics event monitor record generation.

cpu_hmt_degree - Number of logical CPUs monitor element

On systems that support hardware multithreading, the number of logical processors that appear to be present as a result of multithreading. On systems that do not support multithreading, this value is 1.

Table 188. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 189. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This metric reports point in time information at statistics event monitor record generation.

cpu_idle - Processor idle time monitor element

Processor idle time, expressed in processor ticks. Reported for Windows, AIX® and Linux systems only. This measurement represents the aggregate for all processors on the system.

Table 190. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 191. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This measurement represents the aggregate for all processors on the system. For the statistics event monitor, this value is a delta from the previously collected value.

On AIX, this metric is reported for the workload partition (WPAR) and the logical partition (LPAR) on which the DB2 server is running.

You can use this monitor element along with the related processor timer elements to calculate processor utilization for a specific time interval on the host system. To calculate processor utilization as a percentage, perform the following steps:
  1. Use the ENV_GET_SYSTEM_RESOURCES function at the beginning to the time interval to retrieve the values for the following metrics:
    • cpu_usert1 = cpu_user
    • cpu_systemt1 = cpu_system
    • cpu_idlet1 = cpu_idle
    • cpu_waitt1 = cpu_wait
  2. Repeat the preceding step to determine the timestamps for the same metrics at the end of the time interval for which you want to calculate processor utilization:
    • cpu_usert2 = cpu_user
    • cpu_systemt2 = cpu_system
    • cpu_idlet2 = cpu_idle
    • cpu_iowaitt2 = cpu_iowait
  3. Calculate processor utilization using the following formula:
          (cpu_systemt2 - cpu_systemt1) + (cpu_usert2 - cpu_usert1)
    100 × ----------------------------------------------------------
          (cpu_systemt2 - cpu_systemt1) + (cpu_usert2 - cpu_usert1) +
          (cpu_idlet2 - cpu_idlet1) + (cpu_iowaitt2 - cpu_iowaitt1)

cpu_iowait - IO Wait time monitor element

Time spent waiting for IO (Linux, UNIX); time spent receiving and servicing hardware interrupts (Windows), expressed in processor ticks. Reported for Windows, AIX and Linux systems only. This measurement represents the aggregate for all processors on the system.

Table 192. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 193. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This measurement represents the aggregate for all processors on the system. For the statistics event monitor, this value is a delta from the previously collected value.

On AIX, this metric is reported for the workload partition (WPAR) and the logical partition (LPAR) on which the DB2 server is running.

You can use this monitor element along with the related processor timer elements to calculate processor utilization for a specific time interval on the host system. To calculate processor utilization as a percentage, perform the following steps:
  1. Use the ENV_GET_SYSTEM_RESOURCES function at the beginning to the time interval to retrieve the values for the following metrics:
    • cpu_usert1 = cpu_user
    • cpu_systemt1 = cpu_system
    • cpu_idlet1 = cpu_idle
    • cpu_waitt1 = cpu_wait
  2. Repeat the preceding step to determine the timestamps for the same metrics at the end of the time interval for which you want to calculate processor utilization:
    • cpu_usert2 = cpu_user
    • cpu_systemt2 = cpu_system
    • cpu_idlet2 = cpu_idle
    • cpu_iowaitt2 = cpu_iowait
  3. Calculate processor utilization using the following formula:
          (cpu_systemt2 - cpu_systemt1) + (cpu_usert2 - cpu_usert1)
    100 × ----------------------------------------------------------
          (cpu_systemt2 - cpu_systemt1) + (cpu_usert2 - cpu_usert1) +
          (cpu_idlet2 - cpu_idlet1) + (cpu_iowaitt2 - cpu_iowaitt1)

cpu_load_long - Processor load (long timeframe) monitor element

Processor load over the longer term, as defined by the system. For example, the average processor load over the past 10 or 15 minutes. Reported for all platforms except Windows.

Table 195. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 196. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This metric reports point in time information at statistics event monitor record generation.

cpu_load_medium - Processor load (medium timeframe) monitor element

Processor load over the medium term, as defined by the system. For example, the average processor load over the past 5 or 10 minutes. Reported for all platforms except Windows.

Table 197. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 198. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This metric reports point in time information at statistics event monitor record generation.

cpu_load_short - Processor load (short timeframe) monitor element

Processor load over the short term, as defined by the system. For example, the average processor load over the past 1 or 5 minutes. Reported for all platforms except Windows.

Table 199. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 200. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This metric reports point in time information at statistics event monitor record generation.

cpu_online - Number of CPUs online monitor element

The number of processors on this host that are currently online.

Table 201. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 202. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This metric reports point in time information at statistics event monitor record generation.

cpu_share_type - WLM dispatcher CPU share type monitor element

The type of WLM dispatcher CPU shares configured for the service class. Possible values are soft and hard.

Table 203. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_SAMPLE_SERVICE_CLASS_METRICS - Get sample service class metrics Always collected

cpu_speed - CPU clock speed monitor element

The clock speed of the processors on this host, in MHz. The speed reported reflects the current CPU clock speed and not the maximum speed.
Table 205. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 206. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This metric reports point in time information at statistics event monitor record generation.

cpu_system - Kernel time monitor element

Time spent running kernel code, expressed in processor ticks. Reported for Windows, AIX and Linux systems only. This measurement represents the aggregate for all processors on the system.

Table 207. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
ENV_GET_DB2_SYSTEM_RESOURCES table function - Return DB2(r) system information Always collected
Table 208. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This measurement represents the aggregate for all processors on the system. For the statistics event monitor, this value is a delta from the previously collected value.

On AIX, this metric is reported for the workload partition (WPAR) and the logical partition (LPAR) on which the DB2 server is running.

You can use this monitor element along with the related processor timer elements to calculate processor utilization for a specific time interval on the host system. To calculate processor utilization as a percentage, perform the following steps:
  1. Use the ENV_GET_SYSTEM_RESOURCES function at the beginning to the time interval to retrieve the values for the following metrics:
    • cpu_usert1 = cpu_user
    • cpu_systemt1 = cpu_system
    • cpu_idlet1 = cpu_idle
    • cpu_waitt1 = cpu_wait
  2. Repeat the preceding step to determine the timestamps for the same metrics at the end of the time interval for which you want to calculate processor utilization:
    • cpu_usert2 = cpu_user
    • cpu_systemt2 = cpu_system
    • cpu_idlet2 = cpu_idle
    • cpu_iowaitt2 = cpu_iowait
  3. Calculate processor utilization using the following formula:
          (cpu_systemt2 - cpu_systemt1) + (cpu_usert2 - cpu_usert1)
    100 × ----------------------------------------------------------
          (cpu_systemt2 - cpu_systemt1) + (cpu_usert2 - cpu_usert1) +
          (cpu_idlet2 - cpu_idlet1) + (cpu_iowaitt2 - cpu_iowaitt1)

cpu_timebase - Frequency of timebase register increment monitor element

The frequency, in Hz, at which the timebase register is incremented. For Linux and PowerPC® systems only

Table 209. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 210. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This metric reports point in time information at statistics event monitor record generation.

cpu_total - Number of CPUs monitor element

The number of processors on this host.

Table 211. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 212. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This metric reports point in time information at statistics event monitor record generation.

The number reported for this monitor element means different things in different operating environments. For example, when returned from a Windows, system, cpu_total refers to the total number of processors installed; on AIX, it represents the number of configured processors.

cpu_usage_total - Processor usage monitor element

The overall processor usage on this host including kernel processing time, expressed as a percentage. Reported for AIX, Linux and Windows systems only.

Table 213. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
Table 214. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This metric reports point in time information at statistics event monitor record generation.

cpu_user - Non-kernel processing time monitor element

Time spent running user (non-kernel) code, expressed in processor ticks. Reported for Windows, AIX, and Linux systems only. This measurement represents the aggregate for all processors on the system.

Table 215. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected
ENV_GET_DB2_SYSTEM_RESOURCES table function - Return DB2(r) system information Always collected
Table 216. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_osmetrics Always collected

Usage

This measurement represents the aggregate for all processors on the system. For the statistics event monitor, this value is a delta from the previously collected value.

On AIX, this metric is reported for the workload partition (WPAR) and the logical partition (LPAR) on which the DB2 server is running.

You can use this monitor element along with the related processor timer elements to calculate processor utilization for a specific time interval on the host system. To calculate processor utilization as a percentage, perform the following steps:
  1. Use the ENV_GET_SYSTEM_RESOURCES function at the beginning to the time interval to retrieve the values for the following metrics:
    • cpu_usert1 = cpu_user
    • cpu_systemt1 = cpu_system
    • cpu_idlet1 = cpu_idle
    • cpu_waitt1 = cpu_wait
  2. Repeat the preceding step to determine the timestamps for the same metrics at the end of the time interval for which you want to calculate processor utilization:
    • cpu_usert2 = cpu_user
    • cpu_systemt2 = cpu_system
    • cpu_idlet2 = cpu_idle
    • cpu_iowaitt2 = cpu_iowait
  3. Calculate processor utilization using the following formula:
          (cpu_systemt2 - cpu_systemt1) + (cpu_usert2 - cpu_usert1)
    100 × ----------------------------------------------------------
          (cpu_systemt2 - cpu_systemt1) + (cpu_usert2 - cpu_usert1) +
          (cpu_idlet2 - cpu_idlet1) + (cpu_iowaitt2 - cpu_iowaitt1)

cpu_utilization - CPU utilization monitor element

The total CPU time consumed by the service class or workload on a particular logical partition divided by the amount of CPU time available on the host or the LPAR in a given period of time.

Table 218. 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

When returned by the WLM_GET_WORKLOAD_STATS or the WLM_GET_SERVICE_SUBCLASS_STATS function, this monitor element represents the CPU utilization 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 CPU utilization since the function was executed.

cpu_velocity - CPU velocity monitor element

A measure of the amount of contention for the CPU resources, measured on a scale from 0 to 1, with lower numbers meaning greater contention.

CPU velocity is computed by measuring the amount of time that work in a service class has access to the CPU divided by the total time spent accessing the CPU or waiting to access the CPU. It gives a measure of how efficiently the work is being executed relative to how efficiently it could be executed if such work never had to wait for the CPU. The formula is as follows:
cpu_velocity = total_cpu_time / (total_cpu_time + total_disp_run_queue_time)

The wlm_dispatcher database manager configuration parameter must be set to ON for cpu_velocity to be collected.

Table 219. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_SAMPLE_SERVICE_CLASS_METRICS - Sample service class metrics REQUEST METRICS BASE
MON_SAMPLE_WORKLOAD_METRICS - Sample workload metrics REQUEST METRICS BASE

Usage

The dispatcher is effective at prioritizing a service class or workload when that service class or workload demands more CPU resources at a given instant than can be supplied. In such instances, the work executing in the service class or workload spends time queuing to access the CPU resources. It is when this occurs that the dispatcher can give more of the CPU resources to such a service class or workload by reducing how much of the CPU resources it gives to another. A high CPU velocity indicates that the dispatcher can have little effect on improving response times or throughput for this service class at its current level of CPU demand because this demand is already being met. A low CPU velocity indicates that the dispatcher can potentially have a significant effect on improving response times or throughput for this service class or workload at its current level of CPU demand.

Use this element to determine whether the work executing in a service class or workload is spending a relatively large proportion of its time queuing to use the CPU resources. If the CPU velocity for a service class is low and you want to increase it, you can adjust the WLM dispatcher control of the CPU resources by increasing the number of CPU shares or increasing the CPU limit assigned to the service class that is exhibiting a low CPU velocity.

cputime_threshold_id - CPU time threshold ID monitor element

The ID of the CPUTIME threshold that was applied to the activity.

Table 220. 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 CPUTIME threshold, if any, was applied to the activity.

cputime_threshold_value - CPU time threshold value monitor element

The upper bound of the CPUTIME threshold that was applied to the activity.

Table 221. 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 CPUTIME threshold applied to the activity, if any.

cputime_threshold_violated - CPU time threshold violated monitor element

This monitor element returns '1’ (Yes) to indicate that the activity violated the CPUTIME threshold. '0’ (No) indicates that the activity has not yet violated the threshold.

Table 222. 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 CPUTIME threshold that was applied to the activity.

cputimeinsc_threshold_id - CPU time in service class threshold ID monitor element

The ID of the CPUTIMEINSC threshold that was applied to the activity.

Table 223. 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 CPUTIMEINSC threshold, if any, was applied to the activity.

cputimeinsc_threshold_value - CPU time in service class threshold value monitor element

The upper bound of the CPUTIMEINSC threshold that was applied to the activity.

Table 224. 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 CPUTIMEINSC threshold applied to the activity, if any.

cputimeinsc_threshold_violated - CPU time in service class threshold violated monitor element

This monitor element returns '1’ (Yes) to indicate that the activity violated the CPUTIMEINSC threshold. '0’ (No) indicates that the activity has not yet violated the threshold.

Table 225. 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 CPUTIMEINSC threshold that was applied to the activity.

create_nickname - Create Nicknames monitor element

This element contains a count of the total number of times the federated server has created a nickname over an object residing on this data source on behalf of any application since the start of the federated server instance or the last reset of the database monitor counters. The monitor stores the most recent of the values.
Table 226. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase_remote Basic
Application appl_remote Basic
For snapshot monitoring, this counter can be reset.

Usage

Use this element to determine the amount of CREATE NICKNAME activity against this data source by this federated server instance or an application. CREATE NICKNAME processing results in multiple queries running against the data source catalogs; therefore, if the value of this element is high, you should determine the cause and perhaps restrict this activity.

create_nickname_time - Create Nickname Response Time monitor element

This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to process CREATE NICKNAME statements from all applications or a single application running on this federated server instance. The response time is measured since the start of the federated server instance, or the last reset of the database monitor counter, whichever is the latest. The response time is measured as the difference between the time the federated server started retrieving information from the data source to process the CREATE NICKNAME statement, and the time it took to retrieve all the required data from the data source.
Table 227. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase_remote Timestamp
Application appl_remote Timestamp
For snapshot monitoring, this counter can be reset.
Usage
Use this element to determine how much actual time was used to create nicknames for this data source.

creator - Application Creator monitor element

The authorization ID of the user that pre-compiled the application.

Table 228. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
DCS Statement dcs_stmt Statement
Table 229. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks event_dlconn -
Statements event_stmt -
Activities event_activitystmt -
Usage
Use this element to help identify the SQL statement that is processing, in conjunction with the CREATOR column of the package section information in the catalogs.

If the CURRENT PACKAGE PATH special register is set, the creator value may reflect different values over the lifetime of the SQL statement. If a snapshot or event monitor record is taken before PACKAGE PATH resolution, the creator value will reflect the value flowed in from the client request. If a snapshot or event monitor record is taken after PACKAGE PATH resolution, the creator value will reflect the creator of the resolved package. The resolved package will be the package whose creator value appears earliest in the CURRENT PACKAGE PATH SPECIAL REGISTER and whose package name and unique ID matches that of the client request.

current_active_log - Current Active Log File Number monitor element

The file number of the active log file the DB2 database system is currently writing.

Table 230. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 231. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database detail_log Basic
Table 232. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
Use this element in conjunction with the first_active_log and last_active_log elements to determine the range of active log files. Knowing the range of active log files helps you determine the disk space required for log files.

You can also use this element to determine which log files have data to help you identify log files needed for split mirror support.

current_archive_log - Current Archive Log File Number monitor element

The file number of the log file the DB2 database system is currently archiving. If the DB2 database system is not archiving a log file, the value for this element is SQLM_LOGFILE_NUM_UNKNOWN.

Table 233. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 234. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database detail_log Basic
Table 235. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
Use this element to determine if there is a problem archiving log files. Such problems include:
  • Slow archive media
  • Archive media that is not available

current_cf_gbp_size - Current cluster caching facility group buffer pool size monitor element

Group buffer pool memory currently in use at the cluster caching facility, in pages with a page size of 4 KB.

Table 236. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CF table function - Get CF metrics Always collected

current_cf_lock_size - Current cluster caching facility lock size monitor element

Global lock memory currently in use, in pages with a page size of 4 KB.

Table 237. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CF table function - Get CF metrics Always collected

current_cf_mem_size - Current cluster caching facility memory size monitor element

Total memory currently in use, in pages with a page size of 4 KB.

Table 238. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CF table function - Get CF metrics Always collected

current_cf_sca_size - Current cluster caching facility shared communications area size monitor element

Shared communications area memory currently in use, in pages with a page size of 4 KB.

Table 239. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CF table function - Get CF metrics Always collected

current_isolation - Current isolation level monitor element

Identifies the isolation level for any dynamic SQL statements issued within the current session.

Table 241. 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 (reported in DETAILS XML document) 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

current_request - Current operation request monitor element

The operation currently being processed or most recently processed by the agent.

Table 242. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking lock_participants  

cursor_name - Cursor Name monitor element

The name of the cursor corresponding to this SQL statement.

Element identifier
cursor_name
Element type
information
Table 243. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
Table 244. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks with Details event_detailed_dlconn Always collected
Statements event_stmt Always collected
Usage
You may use this element to identify the SQL statement that is processing. This name will be used on an OPEN, FETCH, CLOSE, and PREPARE of an SQL SELECT statement. If a cursor is not used, this field will be blank.