S

savepoint_id - Savepoint ID monitor element

The ID of the savepoint set within a unit of work.

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

sc_work_action_set_id - Service class work action set ID monitor element

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

Table 2. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
Table 3. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity Always collected

Usage

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

sc_work_class_id - Service class work class ID monitor element

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

Table 4. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details ACTIVITY METRICS BASE
Table 5. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity Always collected

Usage

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

sec_log_used_top - Maximum Secondary Log Space Used monitor element

The maximum amount of secondary log space used (in bytes).

Table 6. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 7. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 8. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
You may use this element in conjunction with sec_logs_allocated and tot_log_used_top to show your current dependency on secondary logs. If this value is high, you may need larger log files, or more primary log files, or more frequent COMMIT statements within your application.
As a result, you may need to adjust the following configuration parameters:
  • logfilsiz
  • logprimary
  • logsecond
  • logarchmeth1

The value will be zero if the database does not have any secondary log files. This would be the case if there were none defined.

Note: While the database system monitor information is given in bytes, the configuration parameters are set in pages, which are each 4K bytes.

sec_logs_allocated - Secondary Logs Allocated Currently monitor element

The total number of secondary log files that are currently being used for the database.

Table 9. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_TRANSACTION_LOG table function - Get log information Always collected
Table 10. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Usage
You may use this element in conjunction with sec_log_used_top and tot_log_used_top to show your current dependency on secondary logs. If this value is consistently high, you may need larger log files, or more primary log files, or more frequent COMMIT statements within your application.
As a result, you may need to adjust the following configuration parameters:
  • logfilsiz
  • logprimary
  • logsecond
  • logarchmeth1

section_actuals - Section actuals monitor element

A binary string generated at the data server containing runtime statistics for a section that was executed. If section capture or actuals collection are not enabled, the value is a 0 length string. For non-SQL activities (for example, LOAD) the value is a 0 length string.

Table 11. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activity Always collected

Usage

The data collected in the section_actuals monitor element or per connection using WLM_SET_CONN_ENV is used when a section explain is performed using the EXPLAIN_FROM_ACTIVITY stored procedure. This data is used during EXPLAIN processing to populate the EXPLAIN_ACTUALS explain table and represents the runtime statistics for the operators in the access plan.
Note:
  • Section actuals are only available if they have been enabled (set to BASE) using the section_actuals database configuration parameter or if they have been enabled for a particular application using the WLM_SET_CONN_ENV stored procedure. For more information describing the stored procedure, see WLM_SET_CONN_ENV.
  • The collection of section actuals can be controlled by specifying the INCLUDE ACTUALS BASE clause of workload management DDL statements.
  • The section_actuals setting specified by the WLM_SET_CONN_ENV procedure for an application takes effect immediately.

section_env - Section environment monitor element

A blob that contains the section for an SQL statement. It is the actual section contents, that is the executable form of the query plan.

Table 12. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitystmt Always collected
Package cache pkgcache COLLECT DETAILED DATA

Usage

Use this element with the section explain procedures to explain the statement and view the access plan for the statement.

section_number - Section number monitor element

The internal section number in the package for a static SQL statement.

Table 13. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected
MON_GET_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
WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - return a list of activities Always collected
Table 14. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
DCS Statement dcs_stmt Statement
Table 15. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details1 event_detailed_dlconn -
Statements event_stmt -
Activities event_activitystmt -
Package cache - COLLECT BASE DATA
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

For static SQL statements, you can use this element along with creator, package_version_id, and package_name monitor elements to query the SYSCAT.STATEMENTS system catalog table and obtain the static SQL statement text, using the sample query as follows:

 
    SELECT SEQNO, SUBSTR(TEXT,1,120)
           FROM SYSCAT.STATEMENTS
           WHERE PKGNAME   = 'package_name' AND
                 PKGSCHEMA = 'creator'      AND
                 VERSION = 'package_version_id' AND
                 SECTNO    = section_number
           ORDER BY SEQNO
Note: Exercise caution in obtaining static statement text, because this query against the system catalog table could cause lock contention. Whenever possible, only use this query when there is little other activity against the database.

section_type - Section type indicator monitor element

Indicates whether the SQL statement section is dynamic or static.

Table 16. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache Always collected
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries Always collected
MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine Always collected
Table 17. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Package cache - COLLECT BASE DATA

Usage

The possible values for this monitor element are:
  • D: dynamic
  • S: static

select_sql_stmts - Select SQL Statements Executed monitor element

The number of SQL SELECT statements that were executed.

Table 18. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Database dbase_remote Basic
Table Space tablespace Basic
Application appl Basic
Application appl_remote Basic
For snapshot monitoring, this counter can be reset.
Table 19. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
Usage
You can use this element to determine the level of database activity at the application or database level.
You can also use the following formula to determine the ratio of SELECT statements to the total statements: 
 
      select_sql_stmts
    / ( static_sql_stmts
      + dynamic_sql_stmts )

This information can be useful for analyzing application activity and throughput.

select_time - Query Response Time monitor element

This element contains the aggregate amount of time, in milliseconds, that it has taken this data source to respond to queries 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. The monitor stores the most recent of the values.
Note: Due to query blocking, not all attempts by the federated server to retrieve a row result in communication processing; the request to get the next row can potentially be satisfied from a block of returned rows. As a result, the aggregate query response time does not always indicate processing at the data source, but it usually indicates processing at either the data source or client.
Table 20. 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 is spent waiting for data from this data source. This can be useful in capacity planning and tuning the CPU speed and communication rates in SYSCAT.SERVERS. Modifying these parameters can impact whether the optimizer does or does not send requests to the data source.

The response time is measured as the difference in time between the time the federated server requests a row from the data source, and the time the row is available for the federated server to use.

sequence_no - Sequence number monitor element

This identifier is incremented whenever a unit of work ends (that is, when a COMMIT or ROLLBACK terminates a unit of work). Together, the appl_id and sequence_no uniquely identify a transaction.

Table 21. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_id_info Basic
DCS Application dcs_appl_info Basic
Table 22. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Connection event_conn -
Connections event_connheader -
Statements event_stmt -
Transactions event_xact -
Deadlocks event_dlconn -
Deadlocks with Details event_detailed_dlconn -
Deadlocks with Details History event_detailed_dlconn -
Deadlocks with Details History event_stmt_history -
Deadlocks with Details History Values event_detailed_dlconn -
Deadlocks with Details History Values event_stmt_history -

sequence_no_holding_lk - Sequence Number Holding Lock monitor element

The sequence number of the application that is holding a lock on the object that this application is waiting to obtain.

Element identifier
sequence_no_holding_lk
Element type
information
Table 23. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Lock appl_lock_list Basic
Table 24. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks event_dlconn Always collected
Deadlocks with Details event_detailed_dlconn Always collected
Usage
This identifier is used in tandem with appl_id to uniquely identify a transaction that is holding a lock on the object that this application is waiting to obtain.

server_db2_type - Database Manager Type at Monitored (Server) Node monitor element

Identifies the type of database manager being monitored.

Table 25. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager collected Basic
Usage
It contains one of the following types of configurations for the database manager:
API Symbolic Constant
Command Line Processor Output
sqlf_nt_server
Database server with local and remote clients
sqlf_nt_stand_req
Database server with local clients
The API symbolic constants are defined in the include file sqlutil.h.

server_instance_name - Server Instance Name monitor element

The name of the database manager instance for which the snapshot was taken.

Element identifier
server_instance_name
Element type
information
Table 26. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager collected Basic
Table 27. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Event Log Header event_log_header Always collected
Usage
If more than one instance of the database manager is present on the same system, this data item is used to uniquely identify the instance for which the snapshot call was issued. This information can be useful if you are saving your monitor output in a file or database for later analysis, and you need to differentiate the data from different instances of the database manager.

server_platform - Server Operating System monitor element

The operating system running the database server.

Element identifier
server_platform
Element type
information
Table 28. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Table 29. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Usage
This element can be used for problem determination for remote applications. Values for this field can be found in the header file sqlmon.h.

server_prdid - Server Product/Version ID monitor element

The product and version that is running on the server.

Table 30. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager collected Basic
Table 31. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Event Log Header event_log_header -
Usage
It is in the form PPPVVRRM, where:
PPP
is SQL
VV
identifies a 2-digit version number (with high-order 0 in the case of a 1-digit version)
RR
identifies a 2-digit release number (with high-order 0 in the case of a 1-digit release)
M
identifies a 1-character modification level (0-9 or A-Z)

server_version - Server Version monitor element

The version of the server returning the information.

Table 32. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager collected Basic

Usage

This field identifies the level of the database server collecting database system monitor information. This allows applications to interpret the data based on the level of the server returning the data. Valid values are:
SQLM_DBMON_VERSION1
Data was returned by DB2® Version 1
SQLM_DBMON_VERSION2
Data was returned by DB2 Version 2
SQLM_DBMON_VERSION5
Data was returned by DB2 Universal Database Version 5
SQLM_DBMON_VERSION5_2
Data was returned by DB2 Universal Database Version 5.2
SQLM_DBMON_VERSION6
Data was returned by DB2 Universal Database Version 6
SQLM_DBMON_VERSION7
Data was returned by DB2 Universal Database Version 7
SQLM_DBMON_VERSION8
Data was returned by DB2 Universal Database Version 8
SQLM_DBMON_VERSION9
Data was returned by DB2 for Linux®, UNIX, and Windows Version 9
SQLM_DBMON_VERSION9_5
Data was returned by DB2 for Linux, UNIX, and Windows Version 9.5

service_class_id - Service class ID monitor element

Unique ID of service subclass. For a unit of work, this ID represents the service subclass ID of the workload with which the connection issuing the unit of work is associated.

Table 33. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics Always collected
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass 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
MON_SAMPLE_SERVICE_CLASS_METRICS - Get sample service class metrics Always collected
WLM_GET_WORKLOAD_OCCURRENCE _ACTIVITIES table function - Return a list of activities Always collected
Table 34. 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
Locking - Always collected
Unit of work - Always collected
Statistics event_histogrambin Always collected
Statistics event_scstats Always collected

Usage

The value of this element matches a value from column SERVICECLASSID of view SYSCAT.SERVICECLASSES. Use this element to look up the service subclass name, or link information about a service subclass from different sources. For example, join service class statistics with histogram bin records.

The value of this element is 0 when the following conditions are met:
  • The element is reported in an event_histogrambin logical data group.
  • The histogram data is collected for an object that is not a service class.

service_level - Service Level monitor element

This is the current corrective service level of the DB2 instance.

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

service_subclass_name - Service subclass name monitor element

The name of a service subclass.

Table 36. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics Always collected
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) ACTIVITY METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics Always collected
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) Always collected
MON_SAMPLE_SERVICE_CLASS_METRICS - Get sample service class metrics 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
Table 37. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml document) REQUEST METRICS BASE
Locking - Always collected
Unit of work - Always collected
Activities event_activity Always collected
Statistics event_scstats Always collected
Statistics event_qstats Always collected

Usage

Use this element in conjunction with other activity elements for analysis of the behavior of an activity or with other statistics elements for analysis of a service class or threshold queue.

service_superclass_name - Service superclass name monitor element

The name of a service superclass.

Table 38. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics Always collected
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) Always collected
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics Always collected
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) Always collected
MON_SAMPLE_SERVICE_CLASS_METRICS - Get sample service class metrics 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
Table 39. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_scstats (reported in the details_xml document) REQUEST METRICS BASE
Unit of work - Always collected
Activities event_activity Always collected
Statistics event_scstats Always collected
Statistics event_qstats Always collected

Usage

Use this element in conjunction with other activity elements for analysis of the behavior of an activity or with other statistics elements for analysis of a service class or threshold queue.

session_auth_id - Session authorization ID monitor element

The current authorization ID for the session being used by this application. For monitoring workload management activities, this monitor element describes the session authorization ID under which the activity was injected into the system.

Table 40. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONNECTION table function - Get connection metrics Always collected
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) Always collected
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics Always collected
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) Always collected
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
Table 41. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_info Basic
Lock appl_lock_list Basic
Table 42. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Unit of work - Always collected
Activities event_activity Always collected
Threshold violations event_activity Always collected
Change history changesummary Always collected

Usage

You can use this element to determine what authorization ID is being used to prepare SQL statements, execute SQL statements, or both. This monitor element does not report any session authorization ID values set within executing stored procedures.

shr_workspace_num_overflows - Shared Workspace Overflows monitor element

The number of times that shared workspaces overflowed the bounds of their allocated memory.

Note: This monitor element has been deprecated. 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 43. 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 44. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
Usage
Use this element with shr_workspace_size_top to determine whether the size of the Shared Workspaces need to be increased to avoid overflowing. Overflows of Shared Workspaces may cause performance degradation as well as out of memory errors from the other heaps allocated out of application shared memory.

At the database level, the element reported will be from the same shared workspace as that which was reported as having the Maximum Shared Workspace Size. At the application level, it is the number of overflows for the workspace used by the current application.

shr_workspace_section_inserts - Shared Workspace Section Inserts monitor element

Number of inserts of SQL sections by applications into shared workspaces.

Note: This monitor element has been deprecated. 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 45. 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 46. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
Usage
The working copy of executable sections are stored in shared workspaces. This counter indicates when a copy was not available and had to be inserted.

At the database level, it is the cumulative total of all inserts for every application across all shared workspaces in the database. At the application level, it is the cumulative total of all inserts for all sections in the shared workspace for this application.

shr_workspace_section_lookups - Shared Workspace Section Lookups monitor element

Lookups of SQL sections by applications in shared workspaces.

Note: This monitor element has been deprecated. 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 47. 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 48. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
Usage
Each application has access to a shared workspace where the working copy of executable sections are kept.

This counter indicates how many times shared workspaces were accessed in order to locate a specific section for an application. At the database level, it is the cumulative total of all lookups for every application across all Shared Workspaces in the database. At the application level, it is the cumulative total of all lookups for all sections in the shared workspace for this application.

You can use this element in conjunction with Shared Workspace Section Inserts to tune the size of shared workspaces. The size of the shared workspace is controlled by the app_ctl_heap_sz configuration parameter.

shr_workspace_size_top - Maximum Shared Workspace Size monitor element

The largest size reached by shared workspaces.

Note: This monitor element has been deprecated. 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 49. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl Basic
Table 50. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
Usage
This element indicates the maximum number of bytes the shared workspaces required for the workload run against the database since it was activated. At the database level, it is the maximum size reached by all of the shared workspaces. At the application level, it is the maximum size of the shared workspace used by the current application.

If a shared workspace overflowed, then this element contains the largest size reached by that shared workspace during the overflow. Check Shared Workspace Overflows to determine if such a condition occurred.

When the shared workspace overflows, memory is temporarily borrowed from other entities in application shared memory. This can result in memory shortage errors from these entities or possibly performance degradation. You can reduce the chance of overflow by increasing APP_CTL_HEAP_SZ.

skipped_prefetch_data_p_reads - Skipped prefetch data physical reads monitor element

The number of data pages that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool.

Table 51. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

This monitor element, along with the other skipped_prefetch_*_p_reads elements tells you the number of times a page that was scheduled for retrieval by a prefetcher was not prefetched because it was already in a buffer pool. Pages that are already in a buffer pool might be there for a number of reasons:
  • The page is a new page, and is not yet created on disk.
  • Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
  • An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

The skipped_prefetch_*_p_reads elements tell you about all skipped read requests, regardless of the reason the read was skipped. To see how many requests were skipped because an agent from the same unit of work performed a read before the prefetcher was able to retrieve the page, examine the skipped_prefetch_uow_*_p_reads monitor elements.

skipped_prefetch_index_p_reads - Skipped prefetch index physical reads monitor element

The number of index pages that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool.

Table 52. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

This monitor element, along with the other skipped_prefetch_*_p_reads elements tells you the number of times a page that was scheduled for retrieval by a prefetcher was not prefetched because it was already in a buffer pool. Pages that are already in a buffer pool might be there for a number of reasons:
  • The page is a new page, and is not yet created on disk.
  • Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
  • An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

The skipped_prefetch_*_p_reads elements tell you about all skipped read requests, regardless of the reason the read was skipped. To see how many requests were skipped because an agent from the same unit of work performed a read before the prefetcher was able to retrieve the page, examine the skipped_prefetch_uow_*_p_reads monitor elements.

skipped_prefetch_temp_data_p_reads - Skipped prefetch temporary data physical reads monitor element

The number of data pages for temporary table spaces that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool.

Table 53. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

This monitor element, along with the other skipped_prefetch_*_p_reads elements tells you the number of times a page that was scheduled for retrieval by a prefetcher was not prefetched because it was already in a buffer pool. Pages that are already in a buffer pool might be there for a number of reasons:
  • The page is a new page, and is not yet created on disk.
  • Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
  • An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

The skipped_prefetch_*_p_reads elements tell you about all skipped read requests, regardless of the reason the read was skipped. To see how many requests were skipped because an agent from the same unit of work performed a read before the prefetcher was able to retrieve the page, examine the skipped_prefetch_uow_*_p_reads monitor elements.

skipped_prefetch_temp_index_p_reads - Skipped prefetch temporary index physical reads monitor element

The number of index pages for temporary table spaces that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool.

Table 54. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

This monitor element, along with the other skipped_prefetch_*_p_reads elements tells you the number of times a page that was scheduled for retrieval by a prefetcher was not prefetched because it was already in a buffer pool. Pages that are already in a buffer pool might be there for a number of reasons:
  • The page is a new page, and is not yet created on disk.
  • Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
  • An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

The skipped_prefetch_*_p_reads elements tell you about all skipped read requests, regardless of the reason the read was skipped. To see how many requests were skipped because an agent from the same unit of work performed a read before the prefetcher was able to retrieve the page, examine the skipped_prefetch_uow_*_p_reads monitor elements.

skipped_prefetch_temp_xda_p_reads - Skipped prefetch temporary XDA data physical reads monitor element

The number of XML storage object (XDA) data pages for temporary table spaces that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool.

Table 55. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

This monitor element, along with the other skipped_prefetch_*_p_reads elements tells you the number of times a page that was scheduled for retrieval by a prefetcher was not prefetched because it was already in a buffer pool. Pages that are already in a buffer pool might be there for a number of reasons:
  • The page is a new page, and is not yet created on disk.
  • Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
  • An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

The skipped_prefetch_*_p_reads elements tell you about all skipped read requests, regardless of the reason the read was skipped. To see how many requests were skipped because an agent from the same unit of work performed a read before the prefetcher was able to retrieve the page, examine the skipped_prefetch_uow_*_p_reads monitor elements.

skipped_prefetch_uow_data_p_reads - Skipped prefetch unit of work data physical reads monitor element

The number of data pages that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool by an agent in the same unit of work..

Table 56. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

This monitor element, along with the other skipped_prefetch_uow_*_p_reads elements tells you the number of pages that were in a prefetch request that were read directly by an agent in the same unit of work that caused the prefetch request to be created. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

skipped_prefetch_uow_index_p_reads - Skipped prefetch unit of work index physical reads monitor element

The number of index pages that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool by an agent in the same unit of work.

Table 57. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

This monitor element, along with the other skipped_prefetch_uow_*_p_reads elements tells you the number of pages that were in a prefetch request that were read directly by an agent in the same unit of work that caused the prefetch request to be created. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

skipped_prefetch_uow_temp_data_p_reads - Skipped prefetch unit of work temporary data physical reads monitor element

The number of data pages for temporary table spaces that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool by an agent in the same unit of work.

Table 58. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

This monitor element, along with the other skipped_prefetch_uow_*_p_reads elements tells you the number of pages that were in a prefetch request that were read directly by an agent in the same unit of work that caused the prefetch request to be created. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

skipped_prefetch_uow_temp_index_p_reads - Skipped prefetch unit of work temporary index physical reads monitor element

The number of index pages for temporary table spaces that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool by the synchronous transaction.

Table 59. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

skipped_prefetch_uow_temp_xda_p_reads - Skipped prefetch unit of work temporary XDA data physical reads monitor element

The number of XML storage object (XDA) data pages for temporary table spaces that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool by the synchronous transaction.

Table 60. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

skipped_prefetch_uow_xda_p_reads - Skipped prefetch unit of work XDA data physical reads monitor element

The number of XML storage object (XDA) data pages that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool by an agent in the same unit of work.

Table 61. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

This monitor element, along with the other skipped_prefetch_uow_*_p_reads elements tells you the number of pages that were in a prefetch request that were read directly by an agent in the same unit of work that caused the prefetch request to be created. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

skipped_prefetch_xda_p_reads - Skipped prefetch XDA physical reads monitor element

The number of XML storage object (XDA) data pages that an I/O server (prefetcher) skipped because the pages were already loaded into the buffer pool.

Table 62. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics DATA OBJECT METRICS BASE
MON_GET_TABLESPACE table function - Get table space metrics DATA OBJECT METRICS BASE

Usage

This monitor element, along with the other skipped_prefetch_*_p_reads elements tells you the number of times a page that was scheduled for retrieval by a prefetcher was not prefetched because it was already in a buffer pool. Pages that are already in a buffer pool might be there for a number of reasons:
  • The page is a new page, and is not yet created on disk.
  • Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
  • An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

The skipped_prefetch_*_p_reads elements tell you about all skipped read requests, regardless of the reason the read was skipped. To see how many requests were skipped because an agent from the same unit of work performed a read before the prefetcher was able to retrieve the page, examine the skipped_prefetch_uow_*_p_reads monitor elements.

smallest_log_avail_node - Node with Least Available Log Space monitor element

This element is only returned for global snapshots and indicates the node with the least amount (in bytes) of available log space.

Element identifier
smallest_log_avail_node
Element type
information
Table 63. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Usage
Use this element, in conjunction with appl_id_oldest_xact, to ensure that adequate log space is available for the database. In a global snapshot, appl_id_oldest_xact, total_log_used, and total_log_available correspond to the values on this node.

snapshot_timestamp - Snapshot timestamp monitor element

The date and time that the snapshot was taken.

sort_heap_allocated - Total Sort Heap Allocated monitor element

The total number of allocated pages of sort heap space for all sorts at the level chosen and at the time the snapshot was taken.

Table 68. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Basic
Database dbase Basic
Usage
The amount of memory allocated for each sort may be some or all of the available sort heap size. Sort heap size is the amount of memory available for each sort as defined in the sortheap database configuration parameter.

It is possible for a single application to have concurrent sorts active. For example, in some cases a SELECT statement with a subquery can cause concurrent sorts.

Information may be collected at two levels:
  • At the database manager level, it represents the sum of sort heap space allocated for all sorts in all active databases in the database manager
  • At the database level, it represents the sum of the sort heap space allocated for all sorts in a database.

Normal memory estimates do not include sort heap space. If excessive sorting is occurring, the extra memory used for the sort heap should be added to the base memory requirements for running the database manager. Generally, the larger the sort heap, the more efficient the sort. Appropriate use of indexes can reduce the amount of sorting required.

You may use the information returned at the database manager level to help you tune the sheapthres configuration parameter. If the element value is greater than or equal to sheapthres, it means that the sorts are not getting the full sort heap as defined by the sortheap parameter.

sort_heap_top - Sort private heap high watermark monitor element

The private sort memory high watermark, in 4 KB pages, across the database manager.

Table 69. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager db2 Basic
Usage
This element can be used to determine if the SHEAPTHRES configuration parameter is set to an optimal value. For example, if this watermark approaches or exceeds SHEAPTHRES, it is likely that SHEAPTHRES should be increased. This is because private sorts are given less memory whenever SHEAPTHRES is exceeded, and this can adversely affect system performance.

sort_overflows - Sort overflows monitor element

The total number of sorts that ran out of sort heap and may have required disk space for temporary storage.

Table 70. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_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_ROUTINE_EXEC_LIST table function - get list of statements executed by routine ACTIVITY 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
Table 71. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl Basic
Application stmt Basic
Dynamic SQL dynsql Basic
For snapshot monitoring, this counter can be reset.
Table 72. 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
Statements event_stmt Always collected
Activities event_activity Statement, Sort
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

Usage

At a database or application level, use this element in conjunction with total_sorts to calculate the percentage of sorts that had to overflow to disk. If this percentage is high, you may want adjust the database configuration by increasing the value of sortheap.

At a statement level, use this element to identify statements that require large sorts. These statements may benefit from additional tuning to reduce the amount of sorting required.

When a sort overflows, additional processing time is required because the sort will require a merge phase and can potentially require more I/O, if data needs to be written to disk.

This element provides information for one statement, one application, or all applications accessing one database.

sort_shrheap_allocated - Sort Share Heap Currently Allocated monitor element

Total amount of shared sort memory allocated in the database.

Table 73. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Usage
This element can be used to assess the threshold for shared sort memory. If this value is frequently much higher or lower than the current shared sort memory threshold, it is likely that the threshold should be adjusted.
Note: The "shared sort memory threshold" is determined by the value of the SHEAPTHRES database manager configuration parameter if the SHEAPTHRES_SHR database configuration parameter is 0. Otherwise, it is determined by the value of SHEAPTHRES_SHR.

sort_shrheap_top - Sort share heap high watermark monitor element

Database-wide shared sort memory high watermark in 4 KB pages.

Table 74. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Usage
This element can be used to assess whether or not SHEAPTHRES (or SHEAPTHRES_SHR) is set to an optimal value. For example, if this high watermark is persistently much lower than the shared sort memory threshold, it is likely that this threshold needs to be decreased, thus freeing memory for other database functions. Conversely, if this high watermark begins to approach the shared sort memory threshold, then this might indicate that this threshold needs to be increased. This is important because the shared sort memory threshold is a hard limit. When the total amount of sort memory reaches this threshold, no more shared sorts can be initiated.
This element, along with the high watermark for private sort memory, can also help users determine if the threshold for shared and private sorts need to be set independently of each other. Normally, if the SHEAPTHRES_SHR database configuration option has a value of 0, then the shared sort memory threshold is determined by the value of the SHEAPTHRES database manager configuration option. However, if there is a large discrepancy between the private and shared sort memory high watermarks, this might be an indication that the user needs to override SHEAPTHRES and set SHEAPTHRES_SHR to a more appropriate value that is based on the shared sort memory high watermark.
Note: This element reports the high watermark of sort reservation requests granted by the sort memory controller. Requests that are granted do not always result in a similar level of memory allocation, since they only permit consumers of sort heap to allocate memory as necessary, up to the granted amount, during the processing of an SQL request. It is normal for there to be a discrepancy between the value for this element and the high water mark of the shared sort memory pool (pool_watermark).

source_service_class_id - Source service class ID monitor element

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

Table 75. 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. It can also be used to compute aggregates of how many activities were mapped out of a given service subclass.

sp_rows_selected - Rows Returned by Stored Procedures monitor element

This element contains the number of rows sent from the data source to the federated server at the start of the federated server instance, or the last reset of the database monitor counters as a result of stored procedure operations for this application.

Table 76. 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
This element has several uses. You can use it to compute the average number of rows sent to the federated server from the data source, per stored procedure, with the following formula: 
    rows per stored procedure 
  = rows returned 
  / # of stored procedures invoked
You can also compute the average time to return a row to the federated server from the data source for this application: 
  average time = aggregate stored proc. response time / rows returned

specific_name - Specific name monitor element

Name of the routine instance.

This element might be system generated.
Table 77. Table Function Monitoring Information
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
MON_GET_SECTION_ROUTINE table function - get list of routines for input section Always collected

sql_chains - Number of SQL Chains Attempted monitor element

Represents the number of SQL statements taking n data transmissions between the DB2 Connect gateway and the host during statement processing. The range n is specified by the num_transmissions_group element.

Table 78. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Data Transmission stmt_transmissions Basic
For snapshot monitoring, this counter can be reset.

For example, if chaining is on, and if PREP and OPEN statements are chained together and the chain takes a total of two transmissions, sql_chains is reported as "1" and sql_stmts is reported as "2".

If chaining is off, then the sql_chains count equals the sql_stmts count.

Usage
Use this element to get statistics on how many statements used 2, 3, 4 (and so on) data transmissions during their processing. (At least two data transmissions are necessary to process a statement: a send and a receive.) These statistics can give you a better idea of the database or application activity and network traffic at the database or application levels.
Note: The sql_stmts monitor element represents the number of attempts made to send an SQL statement to the server. At the transmission level, all statements within the same cursor count as a single SQL statement.

sql_req_id - Request Identifier for SQL Statement monitor element

The request identifier for an operation in an SQL statement.

Table 79. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statements event_stmt -
Usage
This identifier increments with each successive SQL operation processed by the database manager since the first application has connected to the database. Its value is unique across the database and uniquely identifies a statement operation.

sql_reqs_since_commit - SQL Requests Since Last Commit monitor element

Number of SQL requests that have been submitted since the last commit.

Table 80. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Basic
Usage
You can use this element to monitor the progress of a transaction.

sql_stmts - Number of SQL Statements Attempted monitor element

For data transmission snapshots, this element represents the number of SQL statements taking n data transmissions between the DB2 Connect gateway and the host during statement processing. The range n is specified by the num_transmissions_group element.

Table 81. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
DCS Database dcs_dbase Basic
DCS Application dcs_appl Basic
Data Transmission stmt_transmissions Basic
For snapshot monitoring, this counter can be reset.

For DCS DATABASE snapshots, this statement count is the number of statements since the database was activated.

For DCS APPLICATION snapshots, this statement count is the number of statements since the connection to the database was established by this application.

Usage
Use this element to measure database activity at the database or application level. To calculate the SQL statement throughput for a given period, you can divide this element by the elapsed time between two snapshots.
For the data transmission level: Use this element to get statistics on how many statements used 2, 3, 4 (and so on) data transmissions during their processing. (At least 2 data transmissions are necessary to process a statement: a send and a receive.) These statistics can give you a better idea of the database or application activity and network traffic at the database or application levels.
Note:
  1. The sql_stmts monitor element represents the number of attempts made to send an SQL statement to the server:
    • At the application level and database level, each SQL statement within a cursor is counted separately.
    • At the transmission level, all statements within the same cursor count as a single SQL statement.

sqlca - SQL Communications Area (SQLCA) monitor element

The SQLCA data structure that was returned to the application at statement completion.

Table 82. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statements event_stmt -
Activities event_activity -

Usage

The SQLCA data structure can be used to determined if the statement completed successfully. For information about the content of the SQLCA, see SQLCA (SQL communications area) or SQLCA data structure.

sqlrowsread_threshold_id - SQL rows read threshold ID monitor element

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

Table 83. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to understand which SQLROWSREAD threshold, if any, was applied to the activity.

sqlrowsread_threshold_value - SQL rows read threshold value monitor element

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

Table 84. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to understand the value of the SQLROWSREAD threshold applied to the activity, if any.

sqlrowsread_threshold_violated - SQL rows read threshold violated monitor element

This monitor element returns 'Yes' to indicate that the activity violated the SQLROWSREAD threshold. 'No' indicates that the activity has not yet violated the threshold.

Table 85. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to determine if the activity violated the SQLROWSREAD threshold that was applied to the activity.

sqlrowsreadinsc_threshold_id - SQL rows read in service class threshold ID monitor element

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

Table 86. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to understand which SQLROWSREADINSC threshold, if any, was applied to the activity.

sqlrowsreadinsc_threshold_value - SQL rows read in service class threshold value monitor element

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

Table 87. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to understand the value of the SQLROWSREADINSC threshold applied to the activity, if any.

sqlrowsreadinsc_threshold_violated - SQL rows read in service class threshold violated monitor element

This monitor element returns 'Yes' to indicate that the activity violated the SQLROWSREADINSC threshold. 'No' indicates that the activity has not yet violated the threshold.

Table 88. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to determine if the activity violated the SQLROWSREADINSC threshold that was applied to the activity.

sqlrowsreturned_threshold_id - SQL rows read returned threshold ID monitor element

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

Table 89. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to understand which SQLROWSRETURNED threshold, if any, was applied to the activity.

sqlrowsreturned_threshold_value - SQL rows read returned threshold value monitor element

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

Table 90. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to understand the value of the SQLROWSRETURNED threshold applied to the activity, if any.

sqlrowsreturned_threshold_violated - SQL rows read returned threshold violated monitor element

This monitor element returns 'Yes' to indicate that the activity violated the SQLROWSRETURNED threshold. 'No' indicates that the activity has not yet violated the threshold.

Table 91. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to determine if the activity violated the SQLROWSRETURNED threshold that was applied to the activity.

sqltempspace_threshold_id - SQL temporary space threshold ID monitor element

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

Table 92. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to understand which SQLTEMPSPACE threshold, if any, was applied to the activity.

sqltempspace_threshold_value - SQL temporary space threshold value monitor element

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

Table 93. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to understand the value of the SQLTEMPSPACE threshold applied to the activity, if any.

sqltempspace_threshold_violated - SQL temporary space threshold violated monitor element

This monitor element returns 'Yes' to indicate that the activity violated the SQLTEMPSPACE threshold. 'No' indicates that the activity has not yet violated the threshold.

Table 94. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected

Usage

Use this element to determine if the activity violated the SQLTEMPSPACE threshold that was applied to the activity.

spacemappage_page_reclaims_x - Space map page reclaims exclusive access monitor element

The number of times a page related to a space map page was reclaimed by another member in the DB2 pureScale® instance before its planned release. The member that reclaimed the page required exclusive access to the space map page.

Table 95. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PAGE_ACCESS_INFO table function - Get buffer pool page waiting information Always collected

Usage

This value is only reported for object-relative table spaces, that is table spaces that have been enabled for reclaimable storage. Use the reclaimable_space_enabled monitor element to determine if the table space has been enabled for reclaimable storage.

Since Extent Map Pages (EMPs) are metadata, EMPs are included in the value of this monitor element.

Data space map pages contain user data, therefore they are included in the value of the page_reclaims_x monitor element, in addition to being included the value of the spacemappage_page_reclaims_x monitor element. Index space map pages do not contain user data, therefore they are included only in the value of the spacemappage_page_reclaims_x monitor element.

spacemappage_page_reclaims_s - Space map page reclaims shared access monitor element

The number of times a page related to a space map page was reclaimed by another member in the DB2 pureScale instance before its planned release. The member that reclaimed the page required shared access to the space map page.

Table 96. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PAGE_ACCESS_INFO table function - Get buffer pool page waiting information Always collected

Usage

This value is only reported for object-relative table spaces, that is table spaces that have been enabled for reclaimable storage. Use the reclaimable_space_enabled monitor element to determine if the table space has been enabled for reclaimable storage.

Since Extent Map Pages (EMPs) are metadata, EMPs are included in the value of this monitor element.

Data space map pages contain user data, therefore they are included in the value of the page_reclaims_s monitor element, in addition to being included the value of the spacemappage_page_reclaims_s monitor element. Index space map pages do not contain user data, therefore they are included only in the value of the spacemappage_page_reclaims_s monitor element.

spacemappage_page_reclaims_initiated_x - Space map page reclaims initiated exclusive access monitor element

The number of times a page accessed in exclusive mode for a space map page caused the page to be reclaimed from another member.

Table 97. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PAGE_ACCESS_INFO table function - Get buffer pool page waiting information Always collected

Usage

This value is only reported for object-relative table spaces, that is table spaces that have been enabled for reclaimable storage. Use the reclaimable_space_enabled monitor element to determine if the table space has been enabled for reclaimable storage.

Since Extent Map Pages (EMPs) are metadata, EMPs are included in the value of this monitor element.

Data space map pages contain user data, therefore they are included in the value of the page_reclaims_initiated_x monitor element, in addition to being included the value of the spacemappage_page_reclaims_initiated_x monitor element. Index space map pages do not contain user data, therefore they are included only in the value of the spacemappage_page_reclaims_initiated_x monitor element.

spacemappage_page_reclaims_initiated_s - Space map page reclaims initiated shared access monitor element

The number of times a page accessed in shared mode of a space map page caused the page to be reclaimed from another member.

Table 98. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PAGE_ACCESS_INFO table function - Get buffer pool page waiting information Always collected

Usage

This value is only reported for object-relative table spaces, that is table spaces that have been enabled for reclaimable storage. Use the reclaimable_space_enabled monitor element to determine if the table space has been enabled for reclaimable storage.

Since Extent Map Pages (EMPs) are metadata, EMPs are included in the value of this monitor element.

Data space map pages contain user data, therefore they are included in the value of the page_reclaims_initiated_s monitor element, in addition to being included the value of the spacemappage_page_reclaims_initiated_s monitor element. Index space map pages do not contain user data, therefore they are included only in the value of the spacemappage_page_reclaims_initiated_s monitor element.

spacemappage_reclaim_wait_time - Space map page reclaim wait time monitor element

In a DB2 pureScale environment, this element represents the amount of time spent waiting on page locks for pages related to internally maintained object space management where the lock request caused a reclaim from another member. The unit of measurement for time is in milliseconds.

Table 99. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_TIMES_BY_ROW - Get formatted row-based combined hierarchy wait and processing times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_WAIT_TIMES_BY_ROW - Get formatted row-based output for wait times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
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_PAGE_ACCESS_INFO table function - Get buffer pool page waiting information Always collected
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache 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
Table 100. 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 - Always collected

ss_exec_time - Subsection Execution Elapsed Time monitor element

The time in seconds that it took a subsection to execute.

Table 101. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement
Table 102. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statements event_subsection -
Usage
Allows you to track the progress of a subsection.

ss_node_number - Subsection Node Number monitor element

Node where the subsection was executed.

Table 103. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement
Table 104. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statements event_subsection -
Usage
Use to correlate each subsection with the database partition where it was executed.

ss_number - Subsection number monitor element

Identifies the subsection associated with the returned information.

Table 105. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_APPL_LOCKWAIT table function - get information about locks for which an application is waiting Always collected
Table 106. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement
Table 107. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statements event_subsection Always collected

Usage

This number relates to the subsection number in the access plan that can be obtained with db2expln command.

ss_status - Subsection status monitor element

The current status of an executing subsection.

Table 108. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Statement

Usage

The current status values can be:
  • executing (SQLM_SSEXEC in sqlmon.h)
  • waiting for a lock
  • waiting to receive data on a table queue
  • waiting to send data on a table queue

ss_sys_cpu_time - System CPU Time used by Subsection monitor element

The total system CPU time (in seconds and microseconds) used by the currently executing statement subsection. For event monitors that write to tables, the value of this element is given in microseconds by using the BIGINT data type.

Element identifier
ss_sys_cpu_time
Element type
time
Table 109. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Timestamp
Table 110. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statements event_subsection Timestamp
Usage
This element along with the other related CPU-time elements can help you understand the level of activity within an application, and can help you identify applications that could benefit from additional tuning.

System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.

ss_usr_cpu_time - User CPU Time used by Subsection monitor element

The total user CPU time (in seconds and microseconds) used by the currently executing statement subsection. For event monitors that write to tables, the value of this element is given in microseconds by using the BIGINT data type.

Element identifier
ss_usr_cpu_time
Element type
time
Table 111. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application subsection Timestamp
Table 112. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statements event_subsection Timestamp
Usage
This element along with the other related CPU-time elements can help you understand the level of activity within an application, and can help you identify applications that could benefit from additional tuning.

System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.

standby_error_time - Standby error time monitor element

The most recent time when the standby database encountered a major error.

Table 114. Table function monitoring information
Table function Monitor element collection level
MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information Always collected

Usage

Check the administration notification log and db2diag.log for error messages that have occurred since the last time you checked for errors. Check the logs fully, not just until the value reported by the standby_error_time value. There might be multiple errors. Log entries might include, but are not limited to the following errors:
  • Replay errors taking a table space to an abnormal state
  • Load replay errors taking a table to an invalid state
The standby_error_time value is reset to NULL when a database changes its role from primary or standard to standby. It is not reset when a standby database is deactivated and reactivated.

standby_id - Standby id monitor element

Standby ID used to differentiate between the standbys.

Table 115. Table function monitoring information
Table function Monitor element collection level
MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information Always collected

Usage

This ID is used to differentiate the standbys. This ID is system generated. The mapping from ID to standby might change from query to query. However, the ID "1" is always assigned to the principal standby (or the only standby in single standby systems). Other standbys are not visible when the query is issued on a standby database; in such cases, 0 is always returned.

standby_log_file - Standby log file monitor element

Note: The hadr_standby_log_file and standby_log_file monitor elements are aliases that represent the same information in different monitoring interfaces. hadr_standby_log_file is returned by the snapshot monitor interfaces, and standby_log_file is returned by the MON_GET_HADR table function and db2pd interfaces.

The name of the log file corresponding to the standby received log position on this log stream.

Table 117. Table function monitoring information
Table function Monitor element collection level
MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information Always collected

standby_log_page - Standby log page monitor element

Note: The hadr_standby_log_page and standby_log_page monitor elements are aliases that represent the same information in different monitoring interfaces. hadr_standby_log_page is returned by the snapshot monitor interfaces, and standby_log_page is returned by the MON_GET_HADR table function and db2pd interfaces.

The page number in standby_log_file corresponding to standby receive log position. The page number is relative to the log file. For example, page zero is the beginning of the file.

Table 118. Table function monitoring information
Table function Monitor element collection level
MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information Always collected

standby_log_pos - Standby log position monitor element

Note: The hadr_standby_log_lsn and standby_log_pos monitor elements are aliases that represent the same information in different monitoring interfaces. hadr_standby_log_lsn is returned by the snapshot monitor interfaces, and standby_log_pos is returned by the MON_GET_HADR table function and db2pd interfaces.

Standby receive log position on this log stream. This is a byte offset.

Table 119. Table function monitoring information
Table function Monitor element collection level
MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information Always collected

Usage

The receive and replay positions are reported separately for more detailed standby status. Spooling allows receive and replay positions to differ greatly. standby_log_pos shows receive position. When compared with primary_log_pos, the standby_log_pos indicates risk of data loss in case of failover. standby_replay_log_pos affects how long a takeover (forced and not forced) would take, since the takeover has to complete the replay of all received logs. The standby_replay_log_pos also indicates how up-to-date data read on standby will be. In Version 9.7 and earlier, the reported standby log position is the replay position.

standby_recv_replay_gap - Standby receive replay gap monitor element

The recent average of the gap between the standby log receive position and the standby log replay position.

Table 127. Table function monitoring information
Table function Monitor element collection level
MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information Always collected

Usage

The gap is measured in number of bytes. It generally will not exceed sum of standby_recv_buf_size and standby_spool_limit. A small amount over the sum is possible due to flexibility in buffer and spool management. When the gap reaches the combined buffer and spool limit, standby will stop receiving logs which will block primary in peer state. Standby may also run out of buffer and spool space when reported receive-replay gap is smaller than sum of buffer and spool, because a partial page can be sent multiple times and occupy multiple pages of space in buffer (always one page in spool though). However, the log gap calculation does not take multiple sends into account.

standby_spool_percent - Standby spool percentage monitor element

The percentage of spool space used, relative to the configured spool limit.

Table 135. Table function monitoring information
Table function Monitor element collection level
MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information Always collected

Usage

If the spool limit is 0 (spooling disabled) or -1 (unlimited spooling), NULL is returned. When the spool percentage reaches 100%, the standby database will stop receiving logs until space is released as replay proceeds. Spooling can stop before the limit is reached if the spool device (standby log path) is full.

start_event_id - Start event ID monitor element

Unique identifier of the corresponding UTILSTART or UTILSTARTPROC event.

Table 136. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History UTILSTOP Always collected

Usage

For the change history event monitor, unique identifier of the corresponding starting of a utility event (UTILSTART or UTILSTARTPROC). Use this element with the START_EVENT_TIMESTAMP and member elements to associate the stop record with the corresponding start record.

start_event_timestamp - Start event timestamp monitor element

Time of the corresponding UTILSTART or UTILSTARTPROC event.

Table 137. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Change History UTILSTOP Always collected

Usage

For the change history event monitor, use with the START_EVENT_ID and member elements to associate the stop record with the corresponding start record.

start_time - Event Start Time monitor element

The date and time of unit of work start, statement start, or deadlock detection. This element, in the event_start API structure indicates the start of the event monitor.

Table 138. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_start Timestamp
Statements event_stmt Timestamp
Deadlocks event_deadlock Timestamp
Deadlocks event_dlconn Timestamp
Deadlocks with Details event_detailed_dlconn Timestamp
Usage
You can use this element to correlate the deadlock connection records to the deadlock event record, and in conjunction with stop_time to calculate the elapsed statement or transaction execution time.
Note: When the Timestamp switch is OFF, this element reports "0".

static_sql_stmts - Static SQL Statements Attempted monitor element

The number of static SQL statements that were attempted.

Table 139. 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 140. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Connection event_conn Always collected
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

statistics_timestamp - Statistics timestamp monitor element

The time at which this statistics record was generated.

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

Usage

Use this element to determine when this statistics record was generated.

Use this element along with the last_wlm_reset element to identify the time interval over which the statistics in this statistics record were generated.

This monitor element can also be used to group together all statistics records that were generated for the same collection interval.

stats_cache_size - Size of statistics cache monitor element

The current size of the statistics cache, in bytes, which is used in a catalog partition to cache statistics information generated by real-time statistics gathering.

Note: Since the statistics cache resides in the catalog partition, only the snapshot taken at the catalog partition will report the statistics cache size. Snapshots taken at other partitions will report the value of zero instead. When taking a global snapshot, the values reported by all the database partitions are aggregated together.
Table 142. Table Function Monitoring Information
Table Function Monitor Element Collection Level
SNAP_GET_DB_V97 table function - Retrieve snapshot information from the dbase logical group  
SNAPDB administrative view and SNAP_GET_DB table function - Retrieve snapshot information from the dbase logical group  
Table 143. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase -
Table 144. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected

Usage

Use this element to determine the size of the current statistics cache. This value changes frequently. In order to evaluate system usage, take the snapshot at specific intervals over an extended period of time. Use this element to adjust the value of the catalogcache_sz configuration parameter.

stats_fabricate_time - Total time spent on statistics fabrication activities monitor element

The stats_fabricate_time monitor element stores the total time spent on statistics fabrications by real-time statistics gathering, in milliseconds. Statistics fabrication is the statistics collection activity needed to generate statistics during query compilation. If this monitor element is collected at the database level, it represents the total time spent on real-time statistics gathering activities for all the applications running on the database. If it is collected at the statement level, it represents the time spent on the latest real-time statistics gathering activities for the statement. The times reported by all the database partitions are aggregated together.
Table 145. Table Function Monitoring Information
Table Function Monitor Element Collection Level
SNAP_GET_DB_V97 table function - Retrieve snapshot information from the dbase logical group  
SNAPDB administrative view and SNAP_GET_DB table function - Retrieve snapshot information from the dbase logical group  
Table 146. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Statement
Dynamic SQL dynsql Statement
For snapshot monitoring, this element can be reset.
Table 147. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Statement event_stmt Always collected

Usage

Use this element along with stats_fabrications to evaluate the performance impact of real-time statistics gathering at the database level. For snapshot monitor for dynamic SQL, you can use this element along with total_exec_time and num_executions to evaluate the impact of statistics fabrications. For the statement event monitor, you can combine this element with stmt_start and stmt_stop for further evaluation of real-time statistics gathering impact.

stats_fabrications - Total number of statistics fabrications monitor elements

The stats_fabrications monitor elements are the total number of statistics fabrications performed by real-time statistics during query compilation for all the database applications. Rather than obtaining statistics by scanning data stored in a table or an index, statistics are fabricated based on metadata maintained by the index and data manager. Values reported by all the database partitions are aggregated together.
Table 148. Table Function Monitoring Information
Table Function Monitor Element Collection Level
SNAP_GET_DB_V97 table function - Retrieve snapshot information from the dbase logical group  
SNAPDB administrative view and SNAP_GET_DB table function - Retrieve snapshot information from the dbase logical group  
Table 149. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Statement
For snapshot monitoring, this counter can be reset.
Table 150. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected

Usage

Use this element to determine the frequency of statistics fabrications in the database. This value changes frequently. In order to get a better overview of the system usage, take the snapshot at specific intervals over an extended period of time. When used in conjunction with stats_fabricate_time, this element can help you evaluate the impact of statistics fabrications.

status_change_time - Application Status Change Time monitor element

The date and time the application entered its current status.

Element identifier
status_change_time
Element type
timestamp
Table 151. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl_id_info Unit of Work, Timestamp
Lock appl_lock_list Unit of Work, Timestamp
DCS Application dcs_appl_info Unit of Work, Timestamp
Usage
This element allows you to determine how long an application has been in its current status. If it has been in the same status for a long period of time, this may indicate that it has a problem.

stmt_elapsed_time - Most Recent Statement Elapsed Time monitor element

The elapsed execution time of the most recently completed statement.

Table 152. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement, Timestamp
DCS Statement dcs_stmt Statement, Timestamp

Usage

Use this element as an indicator of the time it takes for a statement to complete.

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.

stmt_exec_time - Statement execution time monitor element

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

Table 153. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW - Get formatted row-based component times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_METRICS_BY_ROW - Get formatted row-based output for all metrics Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_FORMAT_XML_TIMES_BY_ROW - Get formatted row-based combined hierarchy wait and processing times Not applicable; reports whichever elements are contained in the XML document provided as input to the formatting function.
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) ACTIVITY 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
Table 154. 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
Package cache Reported in the activity_metrics document. ACTIVITY METRICS BASE

stmt_first_use_time - Statement first use timestamp monitor element

This element shows the first time the statement entry was processed. For cursor operations, stmt_first_use_time shows when the cursor was opened. At application coordination nodes, this value reflects the application requests; at non-coordinator nodes, this value reflects when requests were received from the originating node.

Table 155. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details History Values 1 event_stmt_history timestamp
Deadlocks with Details History 1 event_stmt_history timestamp
Activities event_activitystmt timestamp
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 conjunction with other statement history entries to see the sequence of SQL statements that caused the deadlock.

stmt_history_id - Statement history identifier monitor element

This numeric element shows the position in which the statement was run within the unit of work indicated by the sequence_no element, relative to other statement history elements. The earliest statement run in the unit of work will have the lowest value. If the same statement is run twice in the same unit of work, two different occurrences of the statement will be shown with two different stmt_history_id values.
Table 156. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks with Details History Values event_stmt_history -
Deadlocks with Details History Values event_data_value -
Deadlocks with Details History event_stmt_history -
Usage
You can use this information to see the sequence of SQL statements that caused the deadlock.

stmt_invocation_id - Statement invocation identifier monitor element

An identifier that distinguishes one invocation of a routine from others at the same nesting level within a unit of work. It is unique within a unit of work for a specific nesting level.

Table 157. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected
Table 158. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Activities event_activitystmt -
Locking - -
Deadlocks with Details History Values1 event_stmt_history -
Deadlocks with Details History1 event_stmt_history -
Unit of work Reported in the package list. -
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 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.

stmt_isolation - Statement isolation monitor element

This element shows the isolation value in effect for the statement while it was being run.

Table 159. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks with Details History Values event_stmt_history -
Deadlocks with Details History event_stmt_history -
Activities event_activitystmt -

The possible isolation level values are:

  • SQLM_ISOLATION_LEVEL_NONE 0 (no isolation level specified)
  • SQLM_ISOLATION_LEVEL_UR 1 (uncommitted read)
  • SQLM_ISOLATION_LEVEL_CS 2 (cursor stability)
  • SQLM_ISOLATION_LEVEL_RS 3 (read stability)
  • SQLM_ISOLATION_LEVEL_RR 4 (repeatable read)
Usage
You can use this element in conjunction with other statement history entries to understand the cause of the deadlock and the execution behavior of a particular SQL statement.

stmt_last_use_time - Statement last use timestamp monitor element

This element shows the last time the statement entry was processed. For cursor operations, stmt_last_use_time shows the time of the last action on the cursor where that action could be an open, fetch, or close. At application coordination nodes, this value reflects the application requests; at non-coordinator nodes, this value reflects when requests were received from the originating node.
Table 160. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details History Values 1 event_stmt_history timestamp
Deadlocks with Details History 1 event_stmt_history timestamp
Activities event_activitystmt timestamp
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 conjunction with other statement history entries to see the sequence of SQL statements that caused the deadlock.

stmt_lock_timeout - Statement lock timeout monitor element

This element shows the lock timeout value in effect for the statement while it was being run.

Table 161. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details History Values1 event_stmt_history -
Deadlocks with Details History1 event_stmt_history -
Activities event_activitystmt -
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 in conjunction with other statement history entries to understand the cause of the deadlock and the execution behavior of a particular SQL statement.

stmt_nest_level - Statement 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).

Table 162. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected
Table 163. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details History Values1 event_stmt_history -
Deadlocks with Details History1 event_stmt_history -
Activities event_activitystmt -
Unit of work Reported in the package list. -
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.

stmt_node_number - Statement Node monitor element

Node where the statement was executed.

Table 164. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
Usage
Used to correlate each statement with the node where it was executed.

stmt_operation/operation - Statement operation monitor element

The statement operation currently being processed or most recently processed (if none currently running).

Table 165. Table Function Monitoring Information
Table Function Monitor Element Collection Level
SNAPSHOT_STATEMENT table function ACTIVITY METRICS BASE
SNAPSTMT administrative view and SNAP_GET_STMT table function - Retrieve statement snapshot information ACTIVITY METRICS BASE
Table 166. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
DCS Statement dcs_stmt Statement
Table 167. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks with Details1 event_detailed_dlconn Always collected
Statements event_stmt 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 to determine the operation that is executing or recently finished.

It can be one of the following.

For SQL operations:
Value Constant Description
1 SQLM_PREPARE SQL Prepare
2 SQLM_EXECUTE SQL Execute
3 SQLM_EXECUTE_IMMEDIATE SQL Execute Immediate
4 SQLM_OPEN SQL Open
5 SQLM_FETCH SQL Fetch
6 SQLM_CLOSE SQL Close
7 SQLM_DESCRIBE SQL Describe
8 SQLM_STATIC_COMMIT SQL Static Commit
9 SQLM_STATIC_ROLLBACK SQL Static Rollback
10 FREE LOCATOR SQL Free Locator
11 SQLM_PREP_COMMIT Prepare to commit (2-phase commit)
12 SQLM_CALL Call a stored procedure
15 SQLM_SELECT SELECT statement
16 SQLM_PREP_OPEN Prep. and open (DB2 Connect only)
17 SQLM_PREP_EXEC Prep. and execute (DB2 Connect)
18 SQLM_COMPILE Compile (DB2 Connect only)
19 SQLM_SET SET statement
For non-SQL operations:
Value Constant Description
20 SQLM_RUNSTATS Runstats
21 SQLM_REORG Reorg.
22 SQLM_REBIND Rebind package
23 SQLM_REDIST Redistribute
24 SQLM_GETTA Get Table Authorization
25 SQLM_GETAA Get Administrative Authorization
27 SQLM_DRPPKG Drop Package
Note: API users should refer to the sqlmon.h header file containing definitions of database system monitor constants.

stmt_pkg_cache_id - Statement package cache identifier monitor element

This element shows the internal package cache identifier (ID) for a dynamic SQL statement.The element name stmt_pkgcache_id is used as a synonym for this element in some monitoring interfaces.

Table 168. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache Always collected
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries Always collected
Table 169. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Dynamic SQL dynsql Basic
Table 170. Event Monitoring Information
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
Package cache - COLLECT BASE DATA
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

In a multi-partitioned environment, each partition has a unique statement ID for a cached statement. A given statement may not have the same ID across partitions.

In a global dynamic SQL snapshot, only the first statement ID is returned.

stmt_query_id - Statement query identifier monitor element

This element shows the internal query identifier (ID) given to any SQL statement used as a cursor.

Table 171. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details History Values1 event_stmt_history -
Deadlocks with Details History1 event_stmt_history -
Activities event_activitystmt -

Usage

You can use this element, along with the stmt_nest_level monitor element, to uniquely identify an invocation of a particular SQL statement. You can also use this element in conjunction with other statement history entries to understand the cause of the deadlock.

stmt_sorts - Statement Sorts monitor element

The total number of times that a set of data was sorted in order to process the stmt_operation.

Table 172. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Statement
Application stmt Statement
Dynamic SQL dynsql Statement
Usage
You can use this element to help identify the need for an index, since indexes can reduce the need for sorting of data. Using the related elements in the previously shown table you can identify the SQL statement for which this element is providing sort information, and then analyze this statement to determine index candidates by looking at columns that are being sorted (for example, columns used in ORDER BY and GROUP BY clauses and join columns). See explain in the Administration Guide for information on checking whether your indexes are used to optimize sort performance.

This count includes sorts of temporary tables that were generated internally by the database manager to execute the statement. The number of sorts is associated with the first FETCH operation of the SQL statement. This information is returned to you when the operation for the statement is the first FETCH. You should note that for blocked cursors several fetches may be performed when the cursor is opened. In these cases it can be difficult to use the snapshot monitor to obtain the number of sorts, since a snapshot would need to be taken while DB2 was internally issuing the first FETCH.

A more reliable way to determine the number of sorts performed when using a blocked cursor would be with an event monitor declared for statements. The total_sorts counter, in the statement event for the CLOSE cursor, contains the total number of sorts that were performed while executing the statement for which the cursor was defined.

stmt_source_id - Statement source identifier monitor element

This element shows the internal identifier (ID) given to the source of the SQL statement that was run.

Table 173. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details History Values1 event_stmt_history -
Deadlocks with Details History1 event_stmt_history -
Activities event_activitystmt -
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 appl_id monitor element, to uniquely identify the origin of a request to run a particular SQL statement. You can also use this element in conjunction with other statement history entries to understand the cause of the deadlock.

stmt_start - Statement Operation Start Timestamp monitor element

The date and time when the stmt_operation started executing.

Element identifier
stmt_start
Element type
timestamp
Table 174. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement, Timestamp
DCS Statement dcs_stmt Statement, Timestamp
Usage
You can use this element with stmt_stop to calculate the elapsed statement operation execution time.

stmt_stop - Statement Operation Stop Timestamp monitor element

The date and time when the stmt_operation stopped executing.

Element identifier
stmt_stop
Element type
Timestamp
Table 175. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement, Timestamp
DCS Statement dcs_stmt Statement, Timestamp
Usage
You can use this element with stmt_start to calculate the elapsed statement operation execution time.

stmt_sys_cpu_time - System CPU Time used by Statement monitor element

The total system CPU time (in seconds and microseconds) used by the currently executing statement.

Element identifier
stmt_sys_cpu_time
Element type
time
Table 176. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Statement, Timestamp
Application stmt Statement, Timestamp
Usage
This element along with the other related CPU-time elements can help you understand the level of activity within an application, and can help you identify applications that could benefit from additional tuning.

This counter includes time spent on both SQL and non-SQL statements, as well as any unfenced user defined functions (UDF) or stored procedures executed by the application.

System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.

Note: If this information is not available for your operating system, this element will be set to 0.

stmt_text - SQL statement text monitor element

The text of the SQL statement.

Table 177. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) Always collected
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache Always collected
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries Always collected
Table 178. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
Dynamic SQL dynsql Basic
DCS Statement dcs_stmt Statement
Table 179. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks with Details1 event_detailed_dlconn Always collected
Deadlocks with Details History1 event_stmt_history Always collected
Statements event_stmt Always collected
Activities event_activitystmt Always collected
Package cache - COLLECT BASE DATA
Change history ddlstmtexec 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

For application snapshots, this statement text helps you identify what the application was executing when the snapshot was taken, or most recently processed if no statement was being processed right at the time the snapshot was taken.

The information returned by this element is taken from the SQL statement cache and it might not be available if the cache has overflowed. The only guaranteed way to capture the SQL text of a statement is to use an event monitor for statements.

For dynamic SQL statements, this element identifies the SQL text associated with a package.

For statement event monitors, this element is returned only for dynamic statements. If a statement event monitor record cannot fit into the size of the buffer specified by the BUFFERSIZE option of a statement event monitor, the value of the stmt_text monitor may be truncated so that the record can fit.

For the EVENT_STMT_HISTORY event monitor, this element is returned only for dynamic statements. For remaining event monitors, stmt_text is returned for dynamic and static statements only if it is available in the SQL statement cache.

For information about how to query the system catalog tables to obtain static SQL statement text that is not provided due to performance considerations, see the section_number monitor element.

stmt_type - Statement type monitor element

The type of statement processed.

Table 180. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
Table 181. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - Always collected
Deadlocks with Details1 event_detailed_dlconn Always collected
Statements event_stmt Always collected
Activities event_activitystmt 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 to determine the type of statement that is executing. It can be one of the following statements:
  • A static SQL statement
  • A dynamic SQL statement
  • An operation other than an SQL statement; for example, a bind or pre-compile operation.
For the snapshot monitor, this element describes the statement that is currently being processed or was most recently processed.
Note: API users should refer to the sqlmon.h header file containing definitions of database system monitor constants.

stmt_type_id - Statement type identifier monitor element

Statement type identifier.

Table 182. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache Always collected
MON_GET_PKG_CACHE_STMT_DETAILS table function - get detailed metrics for package cache entries Always collected
Table 183. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Package cache - COLLECT BASE DATA

Usage

The stmt_type_id monitor element has the following possible values:
  • Statement not prepared
  • DDL, (not Set Constraints)
  • DDL, Set Constraints
  • DML, Select
  • DML, Insert/Update/Delete
  • Authorization
  • DML, Select (blockable)
  • DML, Lock Table
  • DML, Commit/Rollback
  • Set environment
  • DDL, Savepoint
  • DDL, (declared user temp)
  • Passthru support
  • CALL
  • Free locator
  • DML, Select with IUD
  • DML, Select with IUD (blockable)
  • Top-level SET, no SQL
  • Top-level SET, reads SQL
  • DDL, (issues internal commit)
  • Top-level SET, modifies SQL
  • Unknown

stmt_unicode - Statement unicode flag monitor element

The SQL statement unicode flag. Possible values: Yes or No.

Table 184. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking lock_participant_activities  

stmt_usr_cpu_time - User CPU Time used by Statement monitor element

The total user CPU time (in seconds and microseconds) used by the currently executing statement.

Element identifier
stmt_usr_cpu_time
Element type
time
Table 185. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application appl Statement, Timestamp
Application stmt Statement, Timestamp
Usage
This element along with the other related CPU-time elements can help you understand the level of activity within an application, and can help you identify applications that could benefit from additional tuning.

This counter includes time spent on both SQL and non-SQL statements, as well as any unfenced user-defined functions (UDFs) or stored procedures executed by the application.

System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.

Note: If this information is not available for your operating system, this element will be set to 0.

stmt_value_data - Value data monitor element

This element contains a string representation of a data value to an SQL statement. LOB, LONG, and structured type parameters appear as empty strings. Date, time, and timestamp fields are recorded in ISO format.

Table 186. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries Always collected
Table 187. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details History Values1 stmt_value_data -
Activities event_activityvals -
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 in conjunction with other statement history entries to understand the cause of the deadlock.

stmt_value_index - Value index monitor element

This element represents the position of the input parameter marker or host variable used in the SQL statement.

Table 188. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries Always collected
Table 189. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details History Values1 stmt_value_data -
Activities event_activityvals -
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 in conjunction with other statement history entries to understand the cause of the deadlock.

stmt_value_isnull - Value has null value monitor element

This element shows whether a data value that is associated with an SQL statement is the NULL value; whether an extended indicator has been used to specify the default value; or that this statement value is unassigned.

Possible values are:
  • 0 or "no" if the value is not NULL
  • 1 or "yes" if the value is NULL
  • 2 or "default" if the extended indicator value of default (-5) was specified for this statement value
  • 3 or "unassigned" if the extended indicator value of unassigned (-7) was specified for this statement value
Table 190. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries Always collected
Table 191. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details History Values1 stmt_value_isnull -
Activities event_activityvals -
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 in conjunction with other statement history entries to understand the cause of the deadlock.

stmt_value_isreopt - Variable used for statement reoptimization monitor element

This element shows whether the provided value was a value used during statement reoptimization. It returns a value of True if the statement was reoptimized (for example, due to the setting of the REOPT bind option) and if the value was used as input to the SQL compiler during this reoptimization.
Table 192. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries ACTIVITY METRICS BASE
Table 193. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details History Values1 event_data_value -
Activities event_activityvals -
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 in conjunction with the provided compilation environment to allow for full analysis of the SQL compiler's treatment of the SQL statement.

stmt_value_type - Value type monitor element

This element contains a string representation of the type of a data value associated with an SQL statement.

Table 194. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_PKG_CACHE_STMT_DETAILS - Get detailed metrics for package cache entries Always collected
Table 195. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Locking - -
Deadlocks with Details History Values1 stmt_value_type -
Activities event_activityvals -
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 in conjunction with other statement history entries to understand the cause of the deadlock.

stmtno - Statement number monitor element

Statement number within a package for a static SQL statement.

This element is set to '1' for dynamic SQL statements, or '-1' if the statement number is unavailable. Statement numbers are not available for DDL statements, or dynamically compiled compound SQL statements.

Table 196. Table Function Monitoring Information
Table Function Monitor Element Collection Level
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
Table 197. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Activities event_activitystmt Always collected
Package CacheActivities event_pkgcache Always collected

Usage

For static SQL statements, this value is the same as the value used for the SYSCAT.STATEMENTS catalog view.

sto_path_free_size - Automatic storage path free space monitor element

This element shows the amount of free space (in bytes) available on a file system pointed to by a storage path. If multiple storage paths point to the same file system, the free size is not divided between the separate storage groups. The free size is divided between the multiple paths that point to the same file system within one storage group.

Table 198. 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 199. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database db_sto_path_info Buffer Pool

Usage

You can use this element together with the following elements to gather per-node data on space utilization for the database:
  • db_storage_path
  • fs_used_size
  • fs_total_size
  • fs_id

stop_time - Event Stop Time monitor element

The date and time when the statement stopped executing.

Table 200. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statements event_stmt Timestamp
Usage
You can use this element with start_time to calculate the elapsed statement execution time.

For a FETCH statement event, this is the time of the last successful fetch.

Note: When the Timestamp switch is OFF, this element reports "0".

storage_group_id - Storage group identifier monitor element

An integer that uniquely represents a storage group used by the current database.

Table 201. 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
MON_GET_TABLESPACE table function - get table space metrics Always collected

Usage notes

  • If using the ADMIN_GET_STORAGE_PATHS table function, the storage group identifier indicates the storage group to which a storage path is defined.
  • If using the MON_GET_TABLESPACES table function, the storage group identifier indicates which storage group the table space is defined in.

storage_group_name - Storage group name monitor element

Name of a storage group.

Table 202. 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
MON_GET_TABLESPACE table function - get table space metrics Always collected

Usage notes

  • If using the ADMIN_GET_STORAGE_PATHS table function, this monitor element indicates the storage group to which a storage path is defined.
  • If using the MON_GET_TABLESPACES table function, this monitor element indicates which storage group the table space is defined in.

stored_proc_time - Stored Procedure Time monitor element

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

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

The response time is measured as the difference between the time the federated server submits a stored procedure to the data source, and the time it takes the data source to respond, indicating that the stored procedure has been processed.

Usage
Use this element to determine how much actual time is spent at this data source processing stored procedures.

stored_procs - Stored Procedures monitor element

This element contains a count of the total number of stored procedures from the start of the federated server instance, or the last reset of the database monitor counters, that the federated server has called at this data source on behalf of any application.

Table 204. 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 how many stored procedure calls were made locally at the federated database or by an application against the federated database.

subroutine_id - Subroutine identifier monitor element

A unique subroutine identifier.

This element returns NULL when the object is not a subroutine.
Table 205. Table Function Monitoring Information
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
MON_GET_SECTION_ROUTINE table function - get list of routines for input section Always collected

Usage

Declared procedures have the same external ROUTINE_ID value as their parent, use this element to differentiate between them.

swap_page_size - Swap page size monitor element

The page size used for swap space, in bytes. Reported for AIX and Linux systems only.

Table 208. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ENV_GET_SYSTEM_RESOURCES table function - Return system information Always collected

sync_runstats - Total number of synchronous RUNSTATS activities monitor element

The total number of synchronous RUNSTATS activities triggered by real-time statistics gathering for all the applications in the database. This value includes both successful and unsuccessful synchronous RUNSTATS commands. Values reported by all the database partitions are aggregated together.

Table 209. Table Function Monitoring Information
Table Function Monitor Element Collection Level
SNAP_GET_DB_V97 table function - Retrieve snapshot information from the dbase logical group  
SNAPDB administrative view and SNAP_GET_DB table function - Retrieve snapshot information from the dbase logical group  
Table 210. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Statement
For snapshot monitoring, this counter can be reset.
Table 211. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected

Usage

Use this monitor element to determine how many synchronous RUNSTATS activities have been triggered by real-time statistics gathering in the database. This value changes frequently. In order to get a better view of the system usage, take a snapshot at specific intervals over an extended period of time. When used in conjunction with sync_runstats_time, this element can help you evaluate the performance impact of synchronous RUNSTATS activities triggered by real-time statistics gathering.

sync_runstats_time - Total time spent on synchronous RUNSTATS activities monitor element

The sync_runstats_time monitor element stores the total time spent on synchronous RUNSTATS activities triggered by real-time statistics gathering, in milliseconds. The synchronous RUNSTATS activities occur during query compilation. At the database level, this monitor element represents the total time spent on synchronous RUNSTATS activities for all the applications running on the database, triggered by real-time statistics gathering. At the statement level, it represents the time spent on the latest synchronous RUNSTATS activities for a particular statement, triggered by real-time statistics gathering. Values reported by all the database partitions are aggregated together.
Table 212. Table Function Monitoring Information
Table Function Monitor Element Collection Level
SNAP_GET_DB_V97 table function - Retrieve snapshot information from the dbase logical group  
SNAPDB administrative view and SNAP_GET_DB table function - Retrieve snapshot information from the dbase logical group  
Table 213. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Statement
Dynamic SQL dynsql Statement
For snapshot monitoring, this element can be reset.
Table 214. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Database event_db Always collected
Statement event_stmt Always collected

Usage

Use this element along with sync_runstats to evaluate the performance impact of synchronous RUNSTATS activities triggered by real-time statistics gathering, at the database level,

For dynamic SQL snapshot monitor, use this element along with total_exec_time and num_executions to evaluate the impact of synchronous RUNSTATS on query performance.

For the statement event monitor, use this element along with stmt_start and stmt_stop for further evaluation of the impact of real-time statistics gathering.

system_auth_id - System authorization identifier monitor element

The system authorization id for the connection.

Table 215. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_CONNECTION table function - Get connection metrics Always collected
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) Always collected
WLM_GET_SERVICE_CLASS_WORKLOAD _OCCURRENCES table function - list workload occurrences Always collected
Table 216. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Threshold violations event_thresholdviolations Always collected
Change history changesummary Always collected

system_cpu_time - System CPU time monitor element

The total system CPU time (in seconds and microseconds) used by the database manager agent process, the unit of work, or the statement. For event monitors that write to tables, the value of this element is given in microseconds by using the BIGINT data type.

When either the statement monitor switch or the timestamp switch is not turned on, this element is not collected. In that case, the monitor element displays -1 instead.

Table 217. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Connection event_conn Always collected
Transactions event_xact Always collected
Statements event_stmt Always collected
Activities event_activity Always collected

Usage

This element, along with the other related CPU-time elements, can help you understand the level of activity within an application, and can help you identify applications that could benefit from additional tuning.

Note: If this information is not available for your operating system, this element will be set to 0.
Note: Due to the differences in granularity with which the DB2 system collects statistics, the value of the total_exec_time monitor element might not equal the sum of values of system_cpu_time and user_cpu_time monitor elements. In this case, the sum of system_cpu_time and user_cpu_time monitor elements more accurately reflects the actual total execution time.