D

data_object_l_pages - Table data logical pages monitor element

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

Table 1. 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.
  • This value might be 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.

    To retrieve an accurate measure of the logical or physical size of an object, use the ADMIN_GET_TAB_INFO_V97 function. This function provides more accurate information about the size of objects than you can obtain by multiplying the number of pages reported for this monitor element by the page size.

data_object_pages - Data Object Pages monitor element

The number of disk pages consumed by a table. This size represents the base table size only. Space consumed by index objects are reported by index_object_pages, LOB data is reported by lob_object_pages, and long data is reported by long_object_pages.

Table 2. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Table table Basic
Table 3. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Tables event_table Always collected
Usage
This element provides a mechanism for viewing the actual amount of space consumed by a particular table. This element can be used in conjunction with a table event monitor to track the rate of table growth over time.

data_partition_id - Data partition identifier monitor element

The identifier of the data partition for which information is returned.

Table 5. Snapshot monitoring information
Snapshot level Logical data grouping Monitor switch
Table table Basic
Lock lock Lock
Lock lock_wait Lock
Table 6. Event monitoring information
Event type Logical data grouping Monitor switch
Table event_table -
Deadlocks event_dlconn -
Deadlocks with Details event_detailed_dlconn -
Deadlocks lock -

Usage

This element is only applicable to partitioned tables and partitioned indexes. Otherwise, the value of this monitor element is NULL.

When returning lock level information, a value of -1 represents a lock which controls access to the whole table.

data_sharing_remote_lockwait_count - Data sharing remote lock wait count monitor element

The number of times that the table exits the NOT_SHARED data sharing state.

Table 7. Table function monitoring information
Table function Monitor element collection level
MON_GET_DATABASE - Get database level information Always collected
MON_GET_DATABASE_DETAILS table function - Get database information metrics Always collected
MON_GET_TABLE - Get table metrics Always collected

Usage

In a DB2® pureScale® environment, when an application connected to a remote member requests a lock on a table that is in a NOT_SHARED state on the specified member, the application must wait for the table to convert to a SHARED state before it can acquire a lock on the table.

This monitor element counts the number of times that remote applications waited for the specified table to transition out of the NOT_SHARED state. At the table level, this number indicates the number of times that the specified table moved out of the NOT_SHARED state during the current activation of the database on the specified member. At the database level, this number indicates the aggregate number of times that tables moved out of the NOT_SHARED state during the current activation of the database on the specified member.

If the opt_direct_wrkld database configuration parameter is set to OFF, then the NULL value is returned.

data_sharing_remote_lockwait_time - Data sharing remote lock wait time monitor element

The number of milliseconds that remote applications waited while the table transitions out of the NOT_SHARED data sharing state.

Table 8. Table function monitoring information
Table function Monitor element collection level
MON_GET_DATABASE - Get database level information Always collected
MON_GET_DATABASE_DETAILS table function - Get database information metrics Always collected
MON_GET_TABLE - Get table metrics Always collected

Usage

In a DB2 pureScale environment, when an application connected to a remote member requests a lock on a table that is in a NOT_SHARED state on the specified member, the application must wait for the table to convert to a SHARED state before it can acquire a lock on the table.

At the table level, this element measures the amount of time that the specified table spent exiting out of the NOT_SHARED state during the current activation of the database on the specified member. At the database level, this element measures the aggregate amount of time that tables spent moving out of the NOT_SHARED state during the current activation of the database on the specified member.

If the opt_direct_wrkld database configuration parameter is set to OFF, then the 'NULL' is returned.

data_sharing_state - Data sharing state monitor element

Indicates the current data sharing state of the table.

Table 9. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLE - Get table metrics Always collected

Usage

Use this monitor element to determine the current data sharing state of the table. The following string values are returned by the query:
SHARED
The table is fully shared across all members.
BECOMING_NOT_SHARED
The table is moving from SHARED to NOT_SHARED.
NOT_SHARED
All access to the table in this EHL state is done on this member.
BECOMING_SHARED
The table is moving from NOT_SHARED to SHARED.
NULL
The opt_direct_wrkld database configuration parameter is set to OFF or the database is not running the configuration for aDB2 pureScale instance. 'NULL' is used to indicate that no value is being returned.

This monitor element indicates the data sharing state for the specified member. A SHARED state means that table is shared according to the information available to that member, however it is possible that the table has a NOT_SHARED state on other members. To determine the actual data sharing state of a table, you must determine its data sharing state on all members and see whether any are in a non-SHARED state.

data_sharing_state_change_time - Data sharing state change time monitor element

The timestamp for the last explicit hierarchical locking (EHL) state change for this table.
If the table never exits the SHARED state during the current database activation on the specified member, then this element returns NULL. If the opt_direct_wrkld database configuration parameter is set to OFF, then the NULL value is returned.
Table 10. Table function monitoring information
Table function Monitor element collection level
MON_GET_TABLE - Get table metrics Always collected

datasource_name - Data Source Name monitor element

This element contains the name of the data source whose remote access information is being displayed by the federated server. This element corresponds to the 'SERVER' column in SYSCAT.SERVERS.

Element identifier
datasource_name
Element type
information
Table 11. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase_remote Basic
Application appl_remote Basic
Usage
Use this element to identify the data source whose access information has been collected and is being returned.

datataginsc_threshold_id - Data tag in service class threshold (IN condition) ID monitor element

The ID of the DATATAGINSC IN threshold that was applied to the activity.

Table 12. 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 notes

  • Use this element to understand which DATATAGINSC IN threshold was applied to the activity, if any.

datataginsc_threshold_value - Data tag in service class threshold (IN condition) value monitor element

Comma separated list of data tags in the DATATAGINSC IN threshold that was applied to the activity.

Table 13. 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 notes

  • Use this element to understand the value of the DATATAGINSC IN threshold applied to the activity, if any.

datataginsc_threshold_violated - Data tag in service class threshold (IN condition) violated monitor element

Indicates if the activity has violated the DATATAGINSC IN threshold. Returns 1 if the activity violated the DATATAGINSC IN threshold. Returns 0 if the activity has not violated the threshold.

Table 14. 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 notes

  • Use this element to determine if the activity violated the DATATAGINSC IN threshold that was applied to the activity.

datatagnotinsc_threshold_id - Data tag in service class threshold (NOT IN condition) IDmonitor element

The ID of the DATATAGINSC NOT IN threshold that was applied to the activity.

Table 15. 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 notes

  • Use this element to understand which DATATAGINSC NOT IN threshold was applied to the activity, if any.

datatagnotinsc_threshold_value - Data tag in service class threshold (NOT IN condition) value monitor element

Comma separated list of data tags in the DATATAGINSC NOT IN threshold that was applied to the activity.

Table 16. 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 notes

  • Use this element to understand the value of the DATATAGINSC NOT IN threshold applied to the activity, if any.

datatagnotinsc_threshold_violated - Data tag in service class threshold (NOT IN condition) violated monitor element

Indicates if the activity has violated the DATATAGINSC NOT IN threshold. Returns 1 if the activity violated the DATATAGINSC NOT IN threshold. Returns 0 if the activity did not violate the threshold.

Table 17. 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 notes

  • Use this element to determine if the activity violated the DATATAGINSC NOT IN threshold that was applied to the activity.

db_activation_state - Database activation state monitor element

Current activation state of the database.

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

Usage

Possible database activation states include:
NONE
Not activated and shuts down on the last connection.
IMPLICIT
Implicitly activated during connection processing and does not shutdown on last connection.
EXPLICIT
Explicitly activated by a command and does not shutdown on last connection.

db_conn_time - Database activation timestamp 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 19. 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 20. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Timestamp
Table Space tablespace_list Buffer Pool, Timestamp
Table table_list Timestamp
Table 21. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Unit of work - -
Change history evmonstart Always collected

Usage

Use this element with the disconn_time monitor element to calculate the total connection time.

For the change history event monitor, this element can be used to track when deferred database configuration parameter updates took effect.

db_heap_top - Maximum Database Heap Allocated monitor element

This element is being maintained for DB2 version compatibility. It now measures memory usage, but not exclusively usage by the database heap.

Note: The db_heap_top monitor element is deprecated starting with DB2 Version 9.5. Using this monitor element will not generate an error. However, it does not return a valid value. This monitor element is no longer recommended and might be removed in a future release.
Table 22. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 23. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected

db_location - Database Location monitor element

The location of the database in relation to the application.

Table 24. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Usage
Determine the relative location of the database server with respect to the application taking the snapshot. Values are:
  • SQLM_LOCAL
  • SQLM_REMOTE

db_name - Database name monitor element

The real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Table 26. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Database dbase_remote Basic
Application appl_id_info Basic
Application appl_remote Basic
Table Space tablespace_list Buffer Pool
Buffer Pool bufferpool Buffer Pool
Table table_list Table
Lock db_lock_list Basic
Dynamic SQL dynsql_list Basic
DCS Database dcs_dbase Basic
DCS Application dcs_appl_info Basic
Table 27. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_dbheader Always collected

Usage

You may use this element to identify the specific database to which the data applies.

For applications that are not using DB2 Connect to connect to a host or System i® database server, you can use this element in conjunction with the db_path monitor element to uniquely identify the database and help relate the different levels of information provided by the monitor.

db_path - Database Path monitor element

The full path of the location where the database is stored on the monitored system.

Table 28. 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 29. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl_id_info Basic
Table Space tablespace_list Buffer Pool
Buffer Pool bufferpool Buffer Pool
Table table_list Table
Lock db_lock_list Basic
Dynamic SQL dynsql_list Basic
Table 30. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_dbheader Always collected
Usage
This element can be used with the db_name monitor element to identify the specific database to which the data applies.

db_status - Status of database monitor element

The current status of the database.

Table 31. 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 32. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic

Usage

You can use this element to determine the state of your database.

The snapshot values for this element are:

API Constant Value Description
SQLM_DB_ACTIVE 0 The database is active.
SQLM_DB_QUIESCE_PEND 1 The database is in quiesce-pending state. New connections to the database are not permitted and new units of work cannot be started. Depending on the quiesce request, active units of work will be allowed to complete or will be rolled back immediately.
SQLM_DB_QUIESCED 2 The database has been quiesced. New connections to the database are not permitted and new units of work cannot be started.
SQLM_DB_ROLLFWD 3 A rollforward is in progress on the database.
SQLM_DB_ACTIVE_STANDBY 4 The database is a read-enabled HADR standby database.
SQLM_DB_STANDBY 5 The database is an HADR standby database.
The table function can return the following string values:
  • ACTIVE
  • QUIESCE_PEND
  • QUIESCED
  • ROLLFWD
  • ACTIVE_STANDBY
  • STANDBY

db_storage_path - Automatic storage path monitor element

This element shows the full path of a location that is used by the database for placing automatic storage table spaces. There can be 0 or more storage paths associated with a database.

Table 33. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ADMIN_GET_STORAGE_PATHS table function - Get storage path information for storage groups Always collected
Table 34. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database db_sto_path_info Basic

Usage

Use this element with the num_db_storage_paths monitor element to identify the storage paths that are associated with this database.

db_storage_path_state - Storage path state monitor element

The automatic storage path state indicates whether the storage path is in use by the database.

Table 36. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ADMIN_GET_STORAGE_PATHS table function - Get storage path information for storage groups Always collected
Table 37. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database db_sto_path_info Basic

Usage

Use this monitor element to determine whether the storage path is in use by the database. The following values are possible:
NOT_IN_USE
There are no table spaces using this storage path on the specified database partition.
IN_USE
There are table spaces using this storage path on the specified database partition.
DROP_PENDING
This storage path has been dropped, but some table spaces are still using it. Before storage paths are physically dropped from the database, all table spaces must stop using them. To stop using a dropped storage path, either drop the table space or rebalance the table space using the REBALANCE clause of the ALTER TABLESPACE statement.

db_storage_path_with_dpe - Storage path including database partition expression monitor element

Automatic storage path that includes the unevaluated database partition expression.

Table 38. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ADMIN_GET_STORAGE_PATHS table function - Get storage path information for storage groups Always collected
Table 39. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database db_sto_path_info Basic

Usage

Use this monitor element to determine the storage path that was specified for the database as part of the CREATE DATABASE command or the ALTER DATABASE statement, if the storage path contains a database partition expression.

If the storage path does not contain a database partition expression, then this monitor element returns a null value.

db_work_action_set_id - Database work action set ID monitor element

If this activity has been categorized into a work class of database scope, this monitor element shows the ID of the work action set associated with the work class set to which the work class belongs. Otherwise, this monitor element shows the value of 0.

Table 40. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_ACTIVITY table function - Return a list of activities Always collected
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details Always collected
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities Always collected
Table 41. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity Always collected

Usage

This element can be used with the db_work_class_id element to uniquely identify the database work class of the activity, if one exists.

db_work_class_id - Database work class ID monitor element

If this activity has been categorized into a work class of database scope, this monitor element displays the ID of the work class. Otherwise, this monitor element displays the value of 0.

Table 42. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities Always collected
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities Always collected
Table 43. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity Always collected

Usage

This element can be used with the db_work_action_set_id element to uniquely identify the database work class of the activity, if one exists.

db2_status - Status of DB2 instance monitor element

The current status of the instance of the database manager.

Table 46. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INSTANCE table function - Get instance level information Always collected
Table 47. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Basic

Usage

You can use this element to determine the state of your database manager instance.

The snapshot values for this element are:
API Constant Value Description
SQLM_DB2_ACTIVE 0 The database manager instance is active.
SQLM_DB2_QUIESCE_PEND 1 The instance and the databases in the instance are in quiesce-pending state. New connections to any instance database are not permitted and new units of work cannot be started. Depending on the quiesce request, active units of work will be allowed to complete or will be rolled back immediately.
SQLM_DB2_QUIESCED 2 The instance and the databases in the instance has been quiesced. New connections to any instance database are not permitted and new units of work cannot be started.
The table function can return the following string values:
  • ACTIVE
  • QUIESCE_PEND
  • QUIESCED

db2start_time - Start Database Manager Timestamp monitor element

The date and time that the database manager was started using the db2start command.

Table 48. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INSTANCE table function - Get instance level information Always collected
Table 49. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Basic
Table 50. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change history evmonstart Always collected

Usage

This element can be used with the time_stamp monitor element to calculate the elapsed time since the database manager was started up until the snapshot was taken.

For the change history event monitor, this element can be used to track when deferred database manager configuration parameter updates took effect.

dbpartitionnum - Database partition number monitor element

In a partitioned database environment, this is the numeric identifier for the database member. For DB2 Enterprise Server Edition and in a DB2 pureScale environment, this value is 0.

Table 51. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ADMIN_GET_INDEX_COMPRESS_INFO table function - returns compressed index information Always collected
ADMIN_GET_INDEX_INFO table function - returns index information Always collected
ADMIN_GET_MSGS table function - Retrieve messages generated by a data movement utility that is executed through the ADMIN_CMD procedure Always collected
ADMIN_GET_STORAGE_PATHS table function - Get storage path information for storage groups Always collected
ADMIN_GET_TAB_COMPRESS_INFO table function - estimate compression savings Always collected
ADMIN_GET_TAB_DICTIONARY_INFO table function - report properties of existing table dictionaries Always collected
ADMINTABINFO administrative view and ADMIN_GET_TAB_INFO table function - retrieve table size and state information Always collected
AUDIT_ARCHIVE procedure and table function - Archive audit log file Always collected
DBCFG administrative view and DB_GET_CFG table function - Retrieve database configuration parameter information Always collected
DBPATHS administrative view and ADMIN_LIST_DB_PATHS table function - Retrieve database paths Always collected
MON_GET_ACTIVITY table function - Return a list of activities Always collected
MON_GET_AGENT table function - List agents, fenced mode processes, and system entities for the database Always collected
MON_GET_CONTAINER table function - Get table space container metrics Always collected
MON_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
MON_GET_REBALANCE_STATUS table function - Get rebalance progress for a table space Always collected
MON_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses Always collected
MON_GET_SERVICE_SUPERCLASS_STATS table function - Return statistics of service superclasses Always collected
MON_GET_TABLE table function - get table metrics Always collected
MON_GET_TABLESPACE table function - Get table space metrics Always collected
MON_GET_TABLESPACE_QUIESCER table function - Get information about quiesced table spaces Always collected
MON_GET_TABLESPACE_RANGE table function - Get information about table space ranges Always collected
MON_GET_WORKLOAD_STATS table function - Return workload statistics Always collected
MON_GET_WORK_ACTION_SET_STATS table function - Return work action set statistics Always collected
PD_GET_DIAG_HIST table function - Return records from a given facility Always collected
PDLOGMSGS_LAST24HOURS administrative view and PD_GET_LOG_MSGS table function - Retrieve problem determination messages Always collected
WLM_GET_QUEUE_STATS table function - Return threshold queue statistics Always collected
WLM_GET_SERVICE_CLASS_AGENTS table function - list agents running in a service class Always collected
WLM_GET_SERVICE_CLASS_WORKLOAD _OCCURRENCES table function - list workload occurrences Always collected
WLM_GET_SERVICE_SUBCLASS_STATS table function - Return statistics of service subclasses Always collected
WLM_GET_SERVICE_SUPERCLASS_STATS table function - Return statistics of service superclasses Always collected
WLM_GET_WORK_ACTION_SET_STATS table function - Return work action set statistics Always collected
WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities Always collected
WLM_GET_WORKLOAD_STATS table function - Return workload statistics Always collected

Usage

In a DB2 pureScale environment, multiple members operate on a single partition. When running in such a configuration, physical attributes of storage, such as the number of free pages in a table space, are duplicated across all members in the system. Each member reports the total accurate size for the system. In a multiple partition configuration, the values from each partition must be correlated by the user in order to understand the overall value for the system.

The dbpartitionnum monitor element is different to the data_partition_id monitor element, which is used to identify a data partition that was created by subdividing data in a table based on a value.

dcs_appl_status - DCS application status monitor element

The status of a DCS application at the DB2 Connect gateway.

Table 52. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Application dcs_appl_info Basic

Usage

Use this element for problem determination on DCS applications. Values are:
  • SQLM_DCS_CONNECTPEND_OUTBOUND

    The application has initiated a database connection from the DB2 Connect gateway to the host database, but the request has not completed yet.

  • SQLM_DCS_UOWWAIT_OUTBOUND

    The DB2 Connect gateway is waiting for the host database to reply to the application's request.

  • SQLM_DCS_UOWWAIT_INBOUND

    The connection from the DB2 Connect gateway to the host database has been established and the gateway is waiting for SQL requests from the application. Or the DB2 Connect gateway is waiting on behalf of the unit of work in the application. This usually means that the application's code is being executed.

ddl_classification - DDL classification monitor element

Classification describing type of DDL executed.

Table 54. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History DDLSTMTEXEC Always collected

Usage

DDL can be classified as one of:
STORAGE
The execution of alter database, buffer pool, partition group, storage group, and table space DDL.
WLM
The execution of histogram, service class, threshold, work action set, work class set, and workload DDL.
MONITOR
The execution of event monitor, and usage list DDL.
SECURITY
The execution of audit policy, grant, mask, permission role, revoke, security label, security label component, security policy, and trusted context DDL.
SQL
The execution of alias, function, method, module, package, procedure, schema, synonym, transform, trigger, type, variable, and view DDL.
DATA
The execution of index, sequence, table, and temporary table DDL.
XML
The execution of XSROBJECT DDL.
FEDERATED
The execution of nickname/server, type/user mapping, and wrapper DDL.

ddl_sql_stmts - Data Definition Language (DDL) SQL Statements monitor element

This element indicates the number of SQL Data Definition Language (DDL) statements that were executed.

Table 56. 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 57. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Connection event_conn Always collected
Database event_db Always collected
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_wlmmetrics REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE
Usage
You can use this element to determine the level of database activity at the application or database level. DDL statements are expensive to run due to their impact on the system catalog tables. As a result, if the value of this element is high, you should determine the cause, and possibly restrict this activity from being performed.
You can also use this element to determine the percentage of DDL activity using the following formula:
 
 ddl_sql_stmts / total number of statements
This information can be useful for analyzing application activity and throughput. DDL statements can also impact:
  • the catalog cache, by invalidating table descriptor information and authorization information that are stored there and causing additional system usage to retrieve the information from the system catalogs
  • the package cache, by invalidating sections that are stored there and causing additional processing time due to section recompilation.

Examples of DDL statements are CREATE TABLE, CREATE VIEW, ALTER TABLE, and DROP INDEX.

deadlock_id - Deadlock Event Identifier monitor element

The deadlock identifier for a deadlock.

Element identifier
deadlock_id
Element type
information
Table 58. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks event_deadlock Always collected
Deadlocks event_dlconn Always collected
Deadlocks with Details event_detailed_dlconn Always collected
Deadlocks with Details History event_detailed_dlconn Always collected
Deadlocks with Details History event_stmt_history Always collected
Deadlocks with Details History Values event_data_value Always collected
Deadlocks with Details History Values event_detailed_dlconn Always collected
Deadlocks with Details History Values event_stmt_history Always collected
Usage
Use this element in your monitoring application to correlate deadlock connection and statement history event records with deadlock event records.

deadlock_member - Deadlock member monitor element

The member were the participant is requesting the lock.

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

deadlock_node - Partition Number Where Deadlock Occurred monitor element

Partition number where the deadlock occurred.

Element identifier
deadlock_node
Element type
information
Table 60. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks event_deadlock Always collected
Deadlocks event_dlconn Always collected
Deadlocks with Details event_detailed_dlconn Always collected
Usage
This element is relevant only for partitioned databases. Use this in your monitoring application to correlate deadlock connection event records with deadlock event records.

deadlock_type - Deadlock type monitor element

The type of deadlock that has occurred. The value can be either LOCAL or GLOBAL. In a local deadlock, all participants run on the same member. In a global deadlock, at least one deadlock participant is running on a remote member.

Table 61. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking lock  

deadlocks - Deadlocks detected monitor element

The total number of deadlocks that have occurred.

Table 62. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 63. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl Lock
For snapshot monitoring, this counter can be reset.
Table 64. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Connection event_conn Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

This element can indicate that applications are experiencing contention problems. These problems could be caused by the following situations:
  • Lock escalations are occurring for the database
  • An application may be locking tables explicitly when system-generated row locks may be sufficient
  • An application may be using an inappropriate isolation level when binding
  • Catalog tables are locked for repeatable read
  • Applications are getting the same locks in different orders, resulting in deadlock.

You may be able to resolve the problem by determining in which applications (or application processes) the deadlocks are occurring. You may then be able to modify the application to better enable it to run concurrently. Some applications, however, may not be capable of running concurrently.

You can use the connection timestamp monitor elements (last_reset, db_conn_time, and appl_con_time) to determine the severity of the deadlocks. For example, 10 deadlocks in 5 minutes is much more severe than 10 deadlocks in 5 hours.

The descriptions for the previously listed related elements may also provide additional tuning suggestions.

deferred - Deferred monitor element

Indicates if a change to a configuration parameter value is deferred.

Table 65. 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:
Y
Change deferred until next database activation
N
Change takes effect immediately

degree_parallelism - Degree of Parallelism monitor element

The degree of parallelism requested when the query was bound.

Element identifier
degree_parallelism
Element type
information
Table 66. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
Usage
Use with agents_top, to determine if the query achieved maximum level of parallelism.

del_keys_cleaned - Pseudo deleted keys cleaned monitor element

Number of pseudo deleted keys that have been cleaned.

Table 67. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INDEX table function - Get index metrics Always collected

delete_sql_stmts - Deletes monitor element

This element contains a count of the total number of times the federated server has issued a DELETE statement to 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, whichever is latest.

Table 68. 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 level of database activity directed against this data source by the federated server or an application.
You can also use this element to determine the percentage of write activity against this data source by the federated server or an application, with the following formula:
   write_activity =
     (INSERT statements + UPDATE statements + DELETE statements ) /
     (SELECT statements + INSERT statements + UPDATE statements +
     DELETE statements)

delete_time - Delete Response Time monitor element

This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to respond to DELETEs from all applications or a single application running on this federated server instance since the start of the federated server instance or the last reset of the database monitor counters, whichever is latest.

The response time is measured as the difference in time between the time the federated server submits a DELETE statement to the data source, and the time the data source responds to the federated server, indicating the DELETE has been processed.

Table 69. 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 transpires while waiting for DELETEs to this data source to be processed. This information can be useful for capacity planning and tuning.

destination_service_class_id - Destination service class ID monitor element

The ID of the service subclass to which an activity was remapped when the threshold violation record to which this element belongs was generated. This element has a value of zero for any threshold action other than REMAP ACTIVITY.

Table 70. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Threshold violations event_thresholdviolations -

Usage

Use this element to trace the path of an activity through the service classes to which it was remapped. This element can also be used to compute aggregates of how many activities were mapped into a given service subclass.

details_xml - Details XML monitor element

An XML document containing some of the system monitor elements collected by the statistics event monitor.

Table 71. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Statistics EVENTS_SCSTATS Always collected
Statistics EVENT_WLSTATS Always collected

Usage

The schema for the XML documents returned is available in the file sqllib/misc/DB2MonCommon.xsd The top level element is system_metrics.

The XML documents associated with this monitor element and the metrics monitor element contain the same system metrics, but there is an important difference. The metrics in this monitor element generally start at 0 and continue to accumulate until the next database activation, while the metrics in metrics are calculated to show the change in value for the metric since the last time statistics were collected.
Important: Starting with Version 10.1 Fix Pack 1, this monitor element is deprecated for the statistics event monitor and might be removed in a future release. If you use the XML metrics data returned in this element, start using the metrics document instead.

device_type - Device type monitor element

This element is an identifier for the device type associated with a UTILSTART event.

Table 72. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History UTILLOCATION Always collected

Usage

For the change history event monitor, this field determines the interpretation for the LOCATION field:
A
TSM
C
Client
D
Disk
F
Snapshot backup
L
Local
N
Internally generated by DB2
O
Other vendor device support
P
Pipe
Q
Cursor
R
Remove fetch data
S
Server
T
Tape
U
User exit
X
X/Open XBSA interface

diaglog_write_wait_time - Diagnostic log file write wait time monitor element

The time spent waiting on a write to the db2diag log file. The value is given in milliseconds.

Table 73. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 74. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Use this element to understand the amount of time spent writing to the db2diag log file. In a partitioned database environment, a high value for this time may indicate contention for the db2diag log file if shared storage is being used for the diagnostic directory path (diagpath). A high value may also indicate excessive logging, for example if diaglevel has been set to log all informational messages.

diaglog_writes_total - Total diagnostic log file writes monitor element

The number of times agents have written to the db2diag log file.

Table 75. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 76. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Use this element with the diaglog_write_wait_time monitor element to understand the average amount of time spent writing to the db2diag log file.

direct_read_reqs - Direct read requests monitor element

The number of requests to perform a direct read of one or more sectors of data.

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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 78. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 79. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Connection event_conn Always collected
Tablespaces event_tablespace Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Use the following formula to calculate the average number of sectors that are read by a direct read:
 
 direct_reads / direct_read_reqs

direct_read_time - Direct read time monitor element

The elapsed time required to perform the direct reads. This value is given in milliseconds.

Table 80. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_CONTAINER table function - Get table space container metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 81. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 82. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Connection event_conn Always collected
Tablespaces event_tablespace Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Use the following formula to calculate the average direct read time per sector:

direct_read_time / direct_reads

A high average time may indicate an I/O conflict.

direct_reads - Direct reads from database monitor element

The number of read operations that do not use the buffer pool.

Table 83. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_CONTAINER table function - Get table space container metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 84. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 85. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Connection event_conn Always collected
Tablespaces event_tablespace Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Use the following formula to calculate the average number of sectors that are read by a direct read:

 
 direct_reads / direct_read_reqs

When using system monitors to track I/O, this element helps you distinguish database I/O from non-database I/O on the device.

Direct reads are performed in units, the smallest being a 512-byte sector. They are used when:
  • Reading LONG VARCHAR columns
  • Reading LOB (large object) columns
  • Performing a backup

direct_write_reqs - Direct write requests monitor element

The number of requests to perform a direct write of one or more sectors of data.

Table 86. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 87. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 88. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Connection event_conn Always collected
Tablespaces event_tablespace Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Use the following formula to calculate the average number of sectors that are written by a direct write:
 
 direct_writes / direct_write_reqs

direct_write_time - Direct write time monitor element

The elapsed time required to perform the direct writes. This value is reported in milliseconds.

Table 89. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_CONTAINER table function - Get table space container metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 90. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 91. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Connection event_conn Always collected
Tablespaces event_tablespace Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Use the following formula to calculate the average direct write time per sector:
 
 direct_write_time / direct_writes

A high average time may indicate an I/O conflict.

direct_writes - Direct writes to database monitor element

The number of write operations that do not use the buffer pool.

Table 92. 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_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_CONTAINER table function - Get table space container metrics DATA OBJECT METRICS BASE
MON_GET_DATABASE table function - Get database level information DATA OBJECT METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics DATA OBJECT METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries ACTIVITY METRICS BASE
MON_GET_ROUTINE - get aggregated execution metrics for routines table function REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS - get aggregated execution metric details for routines table function REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_TABLE table function - get table metrics DATA OBJECT METRICS EXTENDED
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list DATA OBJECT METRICS EXTENDED
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 93. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Buffer Pool
Table Space tablespace Buffer Pool
Buffer Pool bufferpool Buffer Pool
Application appl Buffer Pool
For snapshot monitoring, this counter can be reset.
Table 94. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity (reported in the details_xml document) ACTIVITY METRICS BASE
Activities event_activitymetrics ACTIVITY METRICS BASE
Statistics event_scstats (reported in the metrics document) REQUEST METRICS BASE
Statistics event_wlstats (reported in the metrics document) REQUEST METRICS BASE
Unit of work Reported in the system_metrics document. REQUEST METRICS BASE
Database event_db Always collected
Connection event_conn Always collected
Tablespaces event_tablespace Always collected
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Use the following formula to calculate the average number of sectors that are written by a direct write.
 
 direct_writes / direct_write_reqs

When using system monitors to track I/O, this element helps you distinguish database I/O from non-database I/O on the device.

Direct writes are performed in units, the smallest being a 512-byte sector. They are used when:
  • Writing LONG VARCHAR columns
  • Writing LOB (large object) columns
  • Performing a restore
  • Performing a load
  • Allocating new extents for SMS table space if MPFA is enabled (which is the default)

disabled_peds - Disabled partial early distincts monitor element

The number of times that partial early distinct operations were disabled because insufficient sort heap was available.

Table 95. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY table function - Return a list of activities ACTIVITY METRICS BASE
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache ACTIVITY METRICS BASE
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries (reported in DETAILS 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 (reported in DETAILS XML document) REQUEST METRICS BASE
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities ACTIVITY METRICS BASE
Table 96. 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 Reported in the system_metrics document. REQUEST METRICS BASE
Connection event_conn -
Statements event_stmt -
Transactions event_xact -
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

Use this element in conjunction with the total_peds monitor element to determine if partial early distinct operations are getting sufficient sort heap memory most of the time. If the ratio of the disabled_peds monitor element to the total_peds monitor element is high, your database performance may be sub-optimal. You should consider increasing the sort heap size or the sort heap threshold, or both.

disconn_time - Database Deactivation Timestamp monitor element

The date and time that the application disconnected from the database (at the database level, this is the time the last application disconnected).

Element identifier
disconn_time
Element type
timestamp
Table 97. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
Usage
Use this 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).

disconnects - Disconnects monitor element

This element contains a count of the total number of times the federated server has disconnected from 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, whichever is latest.

Table 98. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase_remote Basic
For snapshot monitoring, this counter can be reset.

Usage

Use this element to determine the total number of times the federated server has disconnected from this data source on behalf of any application. Together with the CONNECT count, this element provides a mechanism by which you can determine the number of applications this instance of the federated server believes is currently connected to a data source.

dl_conns - Connections involved in deadlock monitor element

The number of connections that are involved in the deadlock.

Table 99. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks1 event_deadlock Always collected
1
This event monitor has been deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR FOR LOCKING statement to monitor lock-related events, such as lock timeouts, lock waits, and deadlocks.

Usage

Use this element in your monitoring application to identify how many deadlock connection event records will follow in the event monitor data stream.

dyn_compound_exec_id - Dynamic compound statement executable identifier monitor element

Executable ID identifying the dynamically prepared compound SQL statement or anonymous block in PL/SQL.

Usage

Use this value to retrieve the statement text of the routine using the MON_GET_PKG_CACHE_STMT table function.

dynamic_sql_stmts - Dynamic SQL Statements Attempted monitor element

The number of dynamic SQL statements that were attempted.

Table 102. 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 103. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Connection event_conn Always collected
Database event_db Always collected
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_wlmmetrics REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE
An occurrence of each of the following operations increments the value of the dynamic_sql_stmts monitor element by one.
  • OPEN
  • EXECUTE
  • EXECUTE IMMEDIATE
Usage
You can use this element to calculate the total number of successful SQL statements at the database or application level:
 
      dynamic_sql_stmts
    + static_sql_stmts
    - failed_sql_stmts
    = throughput during monitoring period