N
nesting_level - Nesting level monitor element
This element shows the level of nesting or recursion in effect when the statement was being run; each level of nesting corresponds to nested or recursive invocation of a stored procedure or user-defined function (UDF). This monitor element is an alias for the nesting_level element.
The nesting_level monitor element is an alias of the stmt_nest_level monitor element.
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 |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Locking | - | Always collected |
Deadlocks with Details History Values1 | event_stmt_history | Always collected |
Deadlocks with Details History1 | event_stmt_history | Always collected |
Activities | event_activitystmt | Always collected |
Unit of work | Reported in the package list. | 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
You can use this element, along with stmt_invocation_id monitor element, to uniquely identify the invocation in which a particular SQL statement has been executed. You can also use this element in conjunction with other statement history entries to see the sequence of SQL statements that caused the deadlock.
network_interface_bound - Network interface hostname or IP address for remote client and server communications monitor element
The hostname or IP address corresponding to one or more network interfaces for communications between the member and remote clients and servers. The value is stored in the DB2 instance configuration file nicbinding.cfg. This value is NULL if no network interface is specified for the member in the configuration file.
Table function | Monitor element collection level |
---|---|
MON_GET_INSTANCE table function - Get instance level information | Always collected |
network_time_bottom - Minimum Network Time for Statement monitor element
This element represents the shortest network time for a statement executed against this DCS database or in this DCS application, or having used this many data transmissions. (Network time is the difference between host response time and elapsed execution time for a statement.)
- Element identifier
- network_time_bottom
- Element type
- watermark
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
DCS Database | dcs_dbase | Statement, Timestamp |
DCS Application | dcs_appl | Statement, Timestamp |
Data Transmission | stmt_transmissions | Statement, Timestamp |
Usage
Use this element to get a better idea of the database activity and network traffic at the database or application levels.
This element is composed of two subelements that report time spent as seconds and microseconds (one millionth of a second). The names of the subelements can be derived by adding "_s" and "_ms" to the name of this monitor element. To retrieve the total time spent for this monitor element, the values of the two subelements must be added together. For example, if the "_s" subelement value is 3 and the "_ms" subelement value is 20, then the total time spent for the monitor element is 3.00002 seconds.
network_time_top - Maximum Network Time for Statement monitor element
This element represents the longest network time for a statement executed against this DCS database or in this DCS application, or having used this many data transmissions. (Network time is the difference between host response time and elapsed execution time for a statement.)
- Element identifier
- network_time_top
- Element type
- watermark
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
DCS Database | dcs_dbase | Statement, Timestamp |
DCS Application | dcs_appl | Statement, Timestamp |
Data Transmission | stmt_transmissions | Statement, Timestamp |
Usage
Use this element to get a better idea of the database activity and network traffic at the database or application levels. Note that this element is not collected when the timestamp switch is off.
This element is composed of two subelements that report time spent as seconds and microseconds (one millionth of a second). The names of the subelements can be derived by adding "_s" and "_ms" to the name of this monitor element. To retrieve the total time spent for this monitor element, the values of the two subelements must be added together. For example, if the "_s" subelement value is 3 and the "_ms" subelement value is 20, then the total time spent for the monitor element is 3.00002 seconds.
nleaf - Number of leaf pages monitor element
The approximate number of leaf pages.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_INDEX table function - Get index metrics | Always collected |
nlevels - Number of index levels monitor element
Number of index levels. This is an approximation.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_INDEX table function - Get index metrics | Always collected |
no_change_updates - Number of no change row updates monitor element
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_TABLE table function - get table metrics | Always collected |
node_number - Node Number monitor element
The number assigned to the node in the db2nodes.cfg file.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database Manager | collected | Basic |
Database Manager | memory_pool | Basic |
Database Manager | fcm | Basic |
Database Manager | fcm_node | Basic |
Database Manager | utility_info | Basic |
Database | detail_log | Basic |
Buffer Pool | bufferpool_nodeinfo | Buffer Pool |
Table Space | rollforward | Basic |
Lock | lock | Basic |
Lock | lock_wait | Basic |
Database | db_sto_path_info | Buffer Pool |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Connections | event_connheader | Always collected |
Deadlocks | lock | Always collected |
Overflow Record | event_overflow | Always collected |
Database | event_dbmemuse | Always collected |
Connection | event_connmemuse | Always collected |
- Usage
- This value identifies the current node number, which can be used when monitoring multiple nodes.
nonboundary_leaf_node_splits - Non-boundary leaf node splits monitor element
A non-boundary leaf node split is when a leaf node split is triggered by the insertion of a key that is not a new highest or new lowest key in an index. The nonboundary_leaf_node_splits monitor element returns the number of times a non-boundary leaf node was split during an insert operation.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_INDEX table function - Get index metrics | Always collected |
num_agents - Number of Agents Working on a Statement monitor element
Number of concurrent agents currently executing a statement or subsection.
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 |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Application | stmt | Statement |
Application | subsection | Statement |
- Usage
- An indicator how well the query is parallelized. This is useful for tracking the progress of query execution, by taking successive snapshots.
num_assoc_agents - Number of Associated Agents monitor element
At the application level, this is the number of subagents associated with an application. At the database level, it is the number of subagents for all applications.
Table function | Monitor element collection level |
---|---|
MON_GET_CONNECTION table function - Get connection metrics | Always collected |
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics | Always collected |
MON_GET_DATABASE table function - Get database level information | Always collected |
MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics | Always collected |
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
Application | appl_info | Basic |
- Usage
- You can use this element to help evaluate your settings for your agent configuration parameters.
num_columns_referenced - Number of columns referenced monitor element
This element counts the number of columns referenced during the execution of a section for an SQL statement.
Table function | Monitor element collection level |
---|---|
MON_GET_TABLE table function - get table metrics | DATA OBJECT METRICS EXTENDED |
Usage
SELECT C1, C2 FROM T1
This
element applies to both column-organized and row-organized tables.
Columns referenced in synopsis tables and temporary tables are not
included in this counter.num_compilations - Statement Compilations monitor element
The number of different compilations for a specific SQL statement.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Dynamic SQL | dynsql | Basic |
- Usage
- Some SQL statements issued on different schemas, such as SELECT t1 FROM test will appear to be the same statement in the DB2® cache even though they refer to different access plans. Use this value in conjunction with num_executions to determine whether a bad compilation environment may be skewing the results of dynamic SQL snapshot statistics.
num_coord_agents - Number of coordinator agents monitor element
Current number of active coordinating agents.
Table function | Monitor element collection level |
---|---|
MON_GET_DATABASE table function - Get database level information | Always collected |
MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
MON_GET_INSTANCE table function - Get instance level information | Always collected |
num_coord_exec - Number of executions by coordinator agent monitor element
The number of times this section was executed by a coordinator agent.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache | Always collected |
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries | Always collected |
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine | Always collected |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Package cache | - | Always collected |
num_coord_exec_with_metrics - Number of executions by coordinator agent with metrics monitor element
The number of times this section was executed by a coordinator agent and monitoring metrics were being captured
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache | Always collected |
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries | Always collected |
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine | Always collected |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Package cache | - | Always collected |
num_db_storage_paths - Number of automatic storage paths monitor element
The number of automatic storage paths associated with a database.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
- Usage note
- You can use this element with the db_storage_path monitor element to identify the storage paths that are associated with this database.
num_exec_with_metrics - Number of executions with metrics collected monitor element
The number of times that this SQL statement section has been executed with the metrics collected. This element can be used to calculate the per execution value for monitor elements for statements in the package cache.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache | Always collected |
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries | Always collected |
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine | Always collected |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Package cache | - | Always collected |
num_executions - Statement executions monitor element
The number of times that an SQL statement has been executed.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache | Always collected |
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries | Always collected |
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Dynamic SQL | dynsql | Basic |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Package cache | - | Always collected |
Usage
You can use this element to identify the most frequently executed SQL statements in your system.
total_cpu_time / num_exec_with_metrics
Use the num_exec_with_metrics monitor element instead of the num_executions monitor element when computing averages, since the num_executions monitor element counts all executions of a statement, regardless of whether or not the execution of the statement contributed to the activity metrics that are reported.
num_extents_left - Number of extents left to process monitor element
The number of extents left to move during this table rebalancing process.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_EXTENT_MOVEMENT_STATUS - Get extent movement progress status metrics | Always collected |
num_extents_moved - Number of extents moved monitor element
The number of extents moved so far during this extent movement operation.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_EXTENT_MOVEMENT_STATUS - Get extent movement progress status metrics | Always collected |
num_gw_conn_switches - Connection Switches monitor element
The number of times that an agent from the agents pool was primed with a connection and was reassigned for use with a different DRDA database.
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
For most users, the default setting of the num_poolagents configuration parameter ensures optimal performance. The default setting for this configuration parameter automatically manages agent pooling and avoids reassigning agents.
To reduce the value of this monitor element, adjust the value of the num_poolagents configuration parameter.
The GET SNAPSHOT command displays the num_gw_conn_switches monitor element as "Gateway connection pool agents stolen".
num_indoubt_trans - Number of Indoubt Transactions monitor element
The number of outstanding indoubt transactions in the database.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_TRANSACTION_LOG table function - Get log information | ACTIVITY METRICS BASE |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
- Usage
- Indoubt transactions hold log space for uncommitted transactions, which can cause the logs to become full. When the logs are full, further transactions cannot be completed. The resolution of this problem involves a manual process of heuristically resolving the indoubt transactions. This monitor element provides a count of the number of currently outstanding indoubt transactions that must be heuristically resolved.
num_log_buffer_full - Number of times full log buffer caused agents to wait monitor element
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
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 determine if the logbufsz database configuration parameter needs to be increased.
num_log_data_found_in_buffer - Number of Log Data Found In Buffer monitor element
The number of times an agent reads log data from the buffer. Reading log data from the buffer is preferable to reading from the disk because the latter is slower.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | event_db | Always collected |
- Usage
- Use this element in conjunction with the num_log_read_io element to determine if the LOGBUFSZ database configuration parameter needs to be increased.
num_log_part_page_io - Number of Partial Log Page Writes monitor element
The number of I/O requests issued by the logger for writing partial log data to the disk.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | event_db | Always collected |
- Usage
- Use this element in conjunction with the log_writes, log_write_time, and num_log_write_io elements to determine if the current disk is adequate for logging.
num_log_read_io - Number of Log Reads monitor element
The number of I/O requests issued by the logger for reading log data from the disk.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | event_db | Always collected |
- Usage
- Use this element in conjunction with the log_reads and log_read_time elements to determine if the current disk is adequate for logging.
num_log_write_io - Number of Log Writes monitor element
The number of I/O requests issued by the logger for writing log data to the disk.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | event_db | Always collected |
- Usage
- Use this element in conjunction with the log_writes and log_write_time elements to determine if the current disk is adequate for logging.
num_lw_thresh_exceeded - Number of lock wait thresholds exceeded monitor element
This monitor element reports the number of times the lock wait threshold (set using mon_lw_thresh configuration parameter) was exceeded and a lock wait event was captured by the locking event monitor. If no lock wait event is generated, the monitor element is not incremented.
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 |
Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
num_nodes_in_db2_instance - Number of Nodes in Partition monitor element
The number of nodes on the instance where the snapshot was taken.
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database Manager | db2 | Basic |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Event Log Header | event_log_header | Always collected |
- Usage
- Use this element to determine the number of nodes for an instance. For non-partitioned system databases, this value will be 1.
num_page_dict_built - Number of page-level compression dictionaries created or recreated
The number of page-level compression dictionaries created or recreated for a table since the database was last activated.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_TABLE table function - get table metrics | Always collected |
num_pooled_agents - Number of pooled agents monitor element
Identifies the number of pooled agents.
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 |
num_ref_with_metrics - Number of references with metrics monitor element
The total number of times that a section referenced the database object. The usage list for the data object must be created and active; in addition the collection of object metrics must be enabled for the section.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list | Always collected |
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list | Always collected |
num_references - Number of references monitor element
The number of times that this section has referenced this object since it was added to the list.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list | Always collected |
MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list | Always collected |
num_remaps - Number of remaps monitor element
Count of the number of times this activity has been remapped. If num_remaps is greater than zero, the service_class_id of this activity record is the ID of the last service class to which the activity was remapped.
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Activities | event_activity | Always collected |
Usage
Use this information to verify whether the activity was remapped the expected number of times.
num_routines - Number of routines monitor element
Number of procedures, external functions, compiled functions, and compiled triggers that might be invoked during section execution.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_ACTIVITY table function - Return a list of activities | Always collected |
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details | Always collected |
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache | Always collected |
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries | Always collected |
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine | Always collected |
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | Always collected |
Event Type | Logical Data Grouping | Monitor Element Collection Level |
---|---|---|
Activities | event_activitystmt | Always collected |
Package Cache | event_pkgcache | Always collected |
Usage
Use MON_GET_SECTION_ROUTINE table function to list the routines and triggers. This list can be compared to the output from the MON_GET_ROUTINE and MON_GET_ROUTINE_EXEC_LIST table functions.
num_tbsps - Number of table spaces monitor element
The number of table spaces associated with a logged event.
Table Function | Monitor Element Collection Level |
---|---|
MON_GET_UTILITY table function - Get utilities running on the database | Always collected |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Change history | utilstart | Always collected |
num_threshold_violations - Number of threshold violations monitor element
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | dbase | Basic |
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Database | event_db | Always collected |
Usage
This element can be used to help determine whether or not thresholds are effective for this particular application or whether the threshold violations are excessive.
num_transmissions - Number of Transmissions monitor element
Number of data transmissions between the DB2 Connect gateway and the host that was used to process this DCS statement. (One data transmission consists of either one send or one receive.)
This is a legacy monitor element that is not relevant for DB2 UDB Version 8.1.2 or higher. If you are using DB2 UDB Version 8.1.2 or higher, refer to the num_transmissions_group monitor element.
- Element identifier
- num_transmissions
- Element type
- counter
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
DCS Statement | dcs_stmt | Statement |
- Usage
- Use this element to get a better understanding of the reasons why a particular statement took longer to execute. For example, a query returning a large result set may need many data transmissions to complete.
num_transmissions_group - Number of Transmissions Group monitor element
The range of data transmissions between the DB2 Connect gateway and the host that was used to process this DCS statement. (One data transmission consists of either one send or one receive.)
Snapshot Level | Logical Data Grouping | Monitor Switch |
---|---|---|
DCS Statement | dcs_stmt | Statement |
- Usage
- Use this element to get a better understanding of the reasons
why a particular statement took longer to execute. For example, a
query returning a large result set may need many data transmissions
to complete. The constants representing the ranges of transmissions are described as follows and are defined in sqlmon.h.
API Constant Description SQLM_DCS_TRANS_GROUP_2 2 transmissions SQLM_DCS_TRANS_GROUP_3TO7 3 to 7 transmissions SQLM_DCS_TRANS_GROUP_8TO15 8 to 15 transmissions SQLM_DCS_TRANS_GROUP_16TO64 16 to 64 transmissions SQLM_DCS_TRANS_GROUP_GT64 Greater than 64 transmissions
number_in_bin - Number in bin monitor element
This element holds the count of the number of activities or requests that fall within the histogram bin.
Event Type | Logical Data Grouping | Monitor Switch |
---|---|---|
Statistics | event_histogrambin | - |
Usage
Use this element to represent the height of a bin in the histogram.