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 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.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Table | table | Basic |
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 Function | Monitor Element Collection Level |
---|---|
ADMINTABINFO administrative view and ADMIN_GET_TAB_INFO table function - retrieve table size and state information | Always collected |
MON_FORMAT_LOCK_NAME table function - Format the internal lock name and return details | Always collected |
MON_GET_INDEX table function - Get index metrics | Always collected |
MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list | Always collected |
MON_GET_PAGE_ACCESS_INFO table function - Get buffer pool page waiting information | Always collected |
MON_GET_TABLE table function - Get table metrics | Always collected |
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list | Always collected |
MON_GET_USAGE_LIST_STATUS table function - Returns the status on a usage list | Always collected |
Snapshot level | Logical data grouping | Monitor switch |
---|---|---|
Table | table | Basic |
Lock | lock | Lock |
Lock | lock_wait | Lock |
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 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 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 function | Monitor element collection level |
---|---|
MON_GET_TABLE - Get table metrics | Always collected |
Usage
- 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
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
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 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 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 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 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 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 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 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
- 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 function | Monitor element collection level |
---|---|
MON_GET_DATABASE table function - Get database level information | Always collected |
MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Timestamp |
Table Space | tablespace_list | Buffer Pool, Timestamp |
Table | table_list | Timestamp |
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.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
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.
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 Function | Monitor Element Collection Level |
---|---|
MON_GET_AUTO_MAINT_QUEUE table function - Get information about automatic maintenance jobs | Always collected |
MON_GET_CF table function - Get CF metrics | Always collected |
MON_GET_MEMORY_POOL table function - Get memory pool information | Always collected |
MON_GET_MEMORY_SET table function - Get memory set information | Always collected |
MON_SAMPLE_SERVICE_CLASS_METRICS - Get sample service class metrics | Always collected |
MON_SAMPLE_WORKLOAD_METRICS - Get sample workload metrics | Always collected |
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 |
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 function | Monitor element collection level |
---|---|
MON_GET_DATABASE table function - Get database level information | Always collected |
MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
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 |
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 function | Monitor element collection level |
---|---|
MON_GET_DATABASE table function - Get database level information | Always collected |
MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
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. |
- 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 Function | Monitor Element Collection Level |
---|---|
ADMIN_GET_STORAGE_PATHS table function - Get storage path information for storage groups | Always collected |
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_id - Storage path identifier monitor element
Unique identifier for each occurrence of a storage path in a storage group.
Table Function | Monitor Element Collection Level |
---|---|
ADMIN_GET_STORAGE_PATHS table function - Get storage path information for storage groups | Always collected |
MON_GET_CONTAINER table function - Get table space container metrics | Always collected |
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 Function | Monitor Element Collection Level |
---|---|
ADMIN_GET_STORAGE_PATHS table function - Get storage path information for storage groups | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | db_sto_path_info | Basic |
Usage
- 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 Function | Monitor Element Collection Level |
---|---|
ADMIN_GET_STORAGE_PATHS table function - Get storage path information for storage groups | Always collected |
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 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 |
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 Function | Monitor Element Collection Level |
---|---|
MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | Always collected |
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Activities | event_activity | Always collected |
Usage
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_process_id - DB2 process ID monitor element
Numeric identifier of the DB2 process running on the reported member.
Table Function | Monitor Element Collection Level |
---|---|
ENV_GET_DB2_EDU_SYSTEM_RESOURCES table function - Return DB2 engine dispatchable units system information | Always collected |
ENV_GET_DB2_SYSTEM_RESOURCES table function - Return DB2(r) system information | Always collected |
db2_process_name - DB2 process name monitor element
Name of the DB2 process running on the reported member.
Table Function | Monitor Element Collection Level |
---|---|
ENV_GET_DB2_SYSTEM_RESOURCES table function - Return DB2(r) system information | Always collected |
db2_status - Status of DB2 instance monitor element
The current status of the instance of the database manager.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_INSTANCE table function - Get instance level information | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database Manager | db2 | Basic |
Usage
You can use this element to determine the state of your database manager instance.
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. |
- 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 Function | Monitor Element Collection Level |
---|---|
MON_GET_INSTANCE table function - Get instance level information | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database Manager | db2 | Basic |
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.
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.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
DCS Application | dcs_appl_info | Basic |
Usage
- 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.
dcs_db_name - DCS Database Name monitor element
The name of the DCS database as cataloged in the DCS directory.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
DCS Database | dcs_dbase | Basic |
DCS Application | dcs_appl_info | Basic |
- Usage
- Use this element for problem determination on DCS applications.
ddl_classification - DDL classification monitor element
Classification describing type of DDL executed.
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.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
Application | appl | Basic |
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
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.
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
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.
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Locking | lock |
deadlocks - Deadlocks detected monitor element
The total number of deadlocks that have occurred.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
Application | appl | Lock |
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
- 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.
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
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 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.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase_remote | Basic |
Application | appl_remote | Basic |
- 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.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase_remote | Timestamp |
Application | appl_remote | Timestamp |
- 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.
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.
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.
device_type - Device type monitor element
This element is an identifier for the device type associated with a UTILSTART event.
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.
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.
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.
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 |
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
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.
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 |
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
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.
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 |
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.
- 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.
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 |
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
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.
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 |
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
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.
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 |
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
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.
- 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.
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
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.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase_remote | Basic |
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.
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.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_ROUTINE table function - get aggregated execution metrics for routines | Always collected |
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines | Always collected |
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine | Always collected |
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.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
Application | appl | Basic |
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. - 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