tab_organization - Data organization in table monitor element
This element reports the organization of data in the table.
| Table function | Monitor element collection level |
|---|---|
| ADMIN_GET_TEMP_TABLES table function - Retrieve information for temporary tables | Table collection level |
| MON_GET_TABLE table function - get table metrics | Always collected |
Usage
Possible returned values are:
- C
- Indicates that data is column-organized.
- R
- Indicates that data is row-organized.
table_file_id - Table file ID monitor element
The file ID (FID) for the table.
| Table Function | Monitor Element Collection Level |
|---|---|
| ADMINTEMPTABLES administrative view and ADMIN_GET_TEMP_TABLES table function - Retrieve information for temporary tables | Always collected |
| MON_GET_APPL_LOCKWAIT table function - get information about locks for which an application is waiting | Always collected |
| MON_GET_LOCKS table function - list all locks in the currently connected database | Always collected |
| MON_GET_TABLE table function - Get table metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl | Lock |
| Table | table | Basic |
| Lock | appl_lock_list | Lock |
| Lock | lock | Lock |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Deadlocks | lock | Always collected |
Usage
For snapshot monitoring, this element is provided for information purposes only. It is returned for compatibility with previous versions of the database system monitor, and it may not uniquely identify the table. Use table_name and table_schema monitor elements to identify the table.
In MON_GET_LOCKS and MON_GET_APPL_LOCKWAIT table functions, this element represents the file ID (FID) for the table that the lock references.
table_name - Table name monitor element
The name of the table.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table | table | Basic |
| Application | appl | Lock |
| Lock | appl_lock_list | Lock |
| Lock | lock | Lock |
| Lock | lock_wait | Lock |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Locking | - | Always collected |
| Tables | event_table | Always collected |
| Deadlocks1 | lock | Always collected |
| Deadlocks1 | event_dlconn | Always collected |
| Deadlocks with Details1 | event_detailed_dlconn | 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
Along with table_schema, this element can help you determine the source of contention for resources.
At the application-level, application-lock level,
and deadlock-monitoring-level, this is the table that the application
is waiting to lock, because it is currently locked by another application.
For snapshot monitoring, this item is only valid when the lock
monitor group information is set to ON, and when lock_object_type indicates that the application is waiting to obtain a table lock.
For snapshot monitoring at the object-lock level, this item is returned for table-level and row-level locks. The table reported at this level is the table against which this application holds these locks.
For snapshot and event monitoring at the table level,
this is the table for which information has been collected. For temporary
tables, the format for table_name is
TEMP (n, m), where:
- n is the table space ID
- m is the table_file_id element
table_scans - Table scans monitor element
The number of scans on this table.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLE table function - Get table metrics | Always collected |
table_schema - Table schema name monitor element
The schema of the table.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table | table | Basic |
| Application | appl | Lock |
| Lock | appl_lock_list | Lock |
| Lock | lock | Lock |
| Lock | lock_wait | Lock |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Locking | - | Always collected |
| Tables | event_table | Always collected |
| Deadlocks1 | lock | Always collected |
| Deadlocks1 | event_dlconn | Always collected |
| Deadlocks with Details1 | event_detailed_dlconn | 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
Along with table_name, this element can help you determine the source of contention for resources.
For application-level, application-lock-level,
deadlock-monitoring-level, this is the schema of the table that the
application is waiting to lock, because it is currently locked by
another application. This element is only set if lock_object_type indicates that the application is waiting to obtain a table lock.
For snapshot monitoring at the application-level and application-lock
levels, this item is only valid when the lock
monitor group
information is set to ON.
For snapshot monitoring at the object-lock level, this item is returned for table and row level locks. The table reported at this level is the table against which this application holds these locks.
For snapshot and event monitoring at the
table level, this element identifies the schema of the table for
which information has been collected. For temporary tables, the format
for table_schema is
<agent_id><auth_id>, where:
- agent_id is the Application Handle of the application creating the temporary table
- auth_id is the authorization ID used by the application to connect to the database
table_type - Table type monitor element
The type of table for which information is returned.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLE table function - Get table metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table | table | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Tables | event_table | Always collected |
Usage
Use this element to help identify the table for which information is returned. If the table is a user table or a system catalog table, you can use table_name and table_schema to identify the table.
The type of table may
be one of the following values. The possible values are text strings
based on defines in the sqlmon.h file.
- CATALOG_TABLE
- System catalog table.
- SYNOPSIS_TABLE
- Synopsis table.
- TEMP_TABLE
- Temporary table. Information about temporary tables is returned although temporary tables are not kept in the database after being used. Information about this type of tables might be useful even if they are no longer in the database.
- USER_TABLE
- User table.
tablespace_auto_resize_enabled - Table space automatic resizing enabled monitor element
This element describes whether automatic resizing is enabled for the table space. A value of 1 means "Yes"; a value of 0 means "No".
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace | Basic |
Usage
This element is only applicable
to DMS table spaces and non-temporary automatic storage table spaces.
If this element is set to 1, then automatic resizing is enabled. See
the following monitor elements for information about the rate of increase
and the maximum size for the table space.
- tablespace_max_size
- tablespace_increase_size
- tablespace_increase_size_percent
tablespace_content_type - Table space content type monitor element
The type of content in a table space.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace | Basic |
Usage
The type of content in the table
space (defined in sqlmon.h) can be one of the following values:
- All types of permanent data.
- Regular table space: SQLM_TABLESPACE_CONTENT_ANY
- Large table space: SQLM_TABLESPACE_CONTENT_LARGE
- System temporary data: SQLM_TABLESPACE_CONTENT_SYSTEMP
- User temporary data: SQLM_TABLESPACE_CONTENT_USRTEMP
tablespace_cur_pool_id - Buffer pool currently being used monitor element
The buffer pool identifier for a buffer pool that a table space is currently using.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace | Basic |
- Usage
- Each buffer pool is identified by a unique integer. The value of this element matches a value from column BUFFERPOOLID of view SYSCAT.BUFFERPOOLS.
tablespace_current_size - Current table space size monitor element
This element shows the current size of the table space in bytes.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
- Usage
- For DMS and automatic storage table spaces, this element represents
the total size of all table space containers in bytes. This value
is equal to the total pages for the table space (tablespace_total_pages)
multiplied by the table space's page size (tablespace_page_size).
This element is not applicable for SMS table spaces, or for temporary
automatic storage table spaces.
On table space creation for an automatic storage table space, the current size might not match the initial size. The value of current size will be within page size multiplied by extent size multiplied by the number of storage paths of the initial size on creation (usually greater, but sometimes smaller). It will always be less than or equal to tablespace_max_size (if set). This is because containers can only grow by full extents, and must be grown as a set.
tablespace_extent_size - Table space extent size monitor element
The extent size used by a table space.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace | Basic |
tablespace_free_pages - Free pages in table space monitor element
The total number of pages that are currently free in a table space.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
Usage
This is applicable only to a DMS table space.
tablespace_id - Table space identification monitor element
An integer that uniquely represents a table space used by the current database.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace | Basic |
| Table | table | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Tables | event_table | Always collected |
Usage
The value of this element matches a value from column TBSPACEID of view SYSCAT.TABLESPACES.
tablespace_increase_size - Increase size in bytes monitor element
This element shows the size that an auto-resize table space will increase by in bytes when the table space becomes full and more space is required.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
- Usage
- This represents the amount of space that will be added to a table
space that can be automatically resized when it becomes full, more
space is being requested, and the maximum table space size has not
been reached. If the value of this element is -1 (or
AUTOMATIC
in the snapshot output), then the database automatically determines the value when space needs to be added. This element is only applicable to table spaces that are enabled to be automatically resized.
tablespace_increase_size_percent - Increase size by percent monitor element
This element shows the amount by which an auto-resize table space will increase when the table space becomes full and more space is required. The actual number of bytes is determined at the time the table space is resized based on the size of the table space at that time.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
- Usage
- This represents the amount of space that will be added to a table space that can be automatically resized when it becomes full, more space is being requested, and the maximum table space size has not been reached. The growth rate is based on a percentage of the current table space size (tablespace_current_size) at the time the table space is resized. This element is only applicable to table spaces that are enabled to be automatically resized.
tablespace_initial_size - Initial table space size monitor element
The initial size of the automatic storage table space in bytes.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
- Usage
- For non-temporary automatic storage table spaces, this monitor element represents the initial size in bytes for the table space when it was created.
tablespace_last_resize_failed - Last resize attempt failed monitor element
This element describes whether or not the last attempt to automatically increase the size of the table space failed. A value of 1 means yes, 0 means no.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
- Usage
- For an automatic storage table space, this element may show that there is no space left on any of the database's storage paths. For a non-automatic storage table space, a failure means that one of the containers could not be extended because its filesystem was full. Another reason for failure is that the maximum size of the table space has been reached. This element is only applicable to table spaces that are enabled to be automatically resized.
tablespace_last_resize_time - Time of last successful resize monitor element
This element shows a timestamp representing the last time that the size of the table space was successfully increased.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
- Usage
- For table spaces that can be automatically resized, this element represents the last time that space was automatically added to the table space when it became full, more space was being requested, and the maximum table space size had not been reached. This element is only applicable to table spaces that are enabled to be automatically resized.
tablespace_max_size - Maximum table space size monitor element
This element shows the maximum size in bytes to which the table space can automatically resize or increase.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
- Usage
- This represents the maximum size in bytes to which a table space
that can be automatically resized can automatically increase. If this
value is equal to the tablespace_current_size element, then there
is no room for the table space to grow. If the value of this element
is -1, then the maximum size is considered to be
unlimited
and the table space can automatically resize until the file systems are full or the architectural size limit of the table space is reached. (This limit is described in the SQL Limits appendix of the SQL Reference ). This element is only applicable to table spaces that are enabled for automatic resizing.
tablespace_min_recovery_time - Minimum recovery time for rollforward monitor element
A timestamp showing the earliest point in time to which a table space can be rolled forward. The timestamp reflects local time.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
Usage
Displayed only if non zero.
tablespace_name - Table space name monitor element
The name of a table space.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_FORMAT_LOCK_NAME table function - Format the internal lock name and return details | Always collected |
| MON_GET_CONTAINER table function - Get table space container metrics | Always collected |
| MON_GET_EXTENT_MOVEMENT_STATUS - Get extent movement progress status metrics | Always collected |
| MON_GET_REBALANCE_STATUS - Get rebalance progress for a table space | Always collected |
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| MON_GET_TABLESPACE_QUIESCER table function - Get information about quiesced table spaces | Always collected |
| MON_GET_TABLESPACE_RANGE table function - Get information about table space ranges | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace | Basic |
| Lock | appl_lock_list | Basic |
| Lock | lock | Lock |
| Lock | lock_wait | Lock |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Locking | - | - |
| Deadlocks1 | lock | - |
| Deadlocks1 | event_dlconn | - |
| Deadlocks with Details1 | event_detailed_dlconn | - |
| Table Space | tablespace_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
This element can help you determine the source of contention for resources.
It is equivalent to the TBSPACE column in the database catalog table SYSCAT.TABLESPACES. At the application level, application-lock level, and deadlock monitoring level, this is the name of the table space that the application is waiting to lock. Another application currently holds a lock on this table space.
At the lock level, this is the name of the table space against which the application currently holds a lock.
At the table space level (when the buffer pool monitor group is ON), this is the name of the table space for which information is returned.
This element will not be returned for a table lock held on a partitioned table.
tablespace_next_pool_id - Buffer pool that will be used at next startup monitor element
The buffer pool identifier for a buffer pool that a table space will use at the next database startup.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace | Basic |
- Usage
- Each buffer pool is identified by a unique integer. The value of this element matches a value from column BUFFERPOOLID of view SYSCAT.BUFFERPOOLS
tablespace_num_containers - Number of Containers in Table Space monitor element
Total number of containers in the table space.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
tablespace_num_quiescers - Number of Quiescers monitor element
The number of users quiescing the table space (can be in the range of 0 to 5).
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
- Usage
- This value represents the number of agents that have quiesced
the table space (either in "SHARE", "UPDATE", or "EXCLUSIVE" mode).
For each quiescer, the following information is returned in a tablespace_quiescer
logical data group:
- User authorization ID of the quiescer
- Agent ID of the quiescer
- Table space ID of the object that was quiesced that resulted in this table space being quiesced
- Object ID of the object that was quiesced that resulted in this table space being quiesced
- Quiesce state
tablespace_num_ranges - Number of Ranges in the Table Space Map monitor element
The number of ranges (entries) in the table space map. This can be in the range of 1 to 100's (but is usually less than a dozen). The table space map only exists for DMS table spaces.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
tablespace_page_size - Table space page size monitor element
Page size used by a table space in bytes.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace | Basic |
tablespace_page_top - Table space high watermark monitor element
The page in a table space that is holding the high watermark.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
Usage
For DMS, this element represents the page number of the first free extent following the last allocated extent of a table space. Note that this is not really a "high watermark", but rather a "current watermark", since the value can decrease. For SMS, this is not applicable.
tablespace_paths_dropped - Table space using dropped path monitor element
Indicates that the table space is using a storage path that has been dropped.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
Usage
For table spaces using automatic storage, use this monitor element to determine whether any of the table space containers reside on a storage path that has been dropped. Before storage paths are physically dropped from the database, all table spaces must stop using them. To stop using a dropped storage path, either drop the table space or rebalance the table space using the REBALANCE clause of the ALTER TABLESPACE statement.
tablespace_pending_free_pages - Pending free pages in table space monitor element
The number of pages in a table space which would become free if all pending transactions are committed or rolled back and new space is requested for an object.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
Usage
This is applicable only to a DMS table space.
tablespace_prefetch_size - Table space prefetch size monitor element
The maximum number of pages the prefetcher gets from the disk at a time.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace | Basic |
| Table Space | tablespace_nodeinfo | Basic |
Usage
- For table function monitoring, this element always reports the actual value for the table space prefetch size.
- For snapshot monitoring, if automatic prefetch size is enabled, this element reports the value "-1" in the tablespace Logical Data Grouping, and the actual value is reported in the tablespace_nodeinfo Logical Data Grouping.
- For snapshot monitoring, if automatic prefetch size is not enabled, this element reports the actual value in the tablespace Logical Data Grouping, and the element does not appear in the tablespace_nodeinfo Logical Data Grouping.
tablespace_state - Table space state monitor element
This element describes the current state of a table space.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
Usage
In administrative views and table
functions, this monitor element returns a text identifier based on
defines in sqlutil.h, and is combination of the
following values separated by a '+' sign:
- BACKUP_IN_PROGRESS
- BACKUP_PENDING
- DELETE_PENDING
- DISABLE_PENDING
- DROP_PENDING
- LOAD_IN_PROGRESS
- LOAD_PENDING
- MOVE_IN_PROGRESS
- EM_STARTED
- EM_TERMINATING
- NORMAL
- OFFLINE
- PSTAT_CREATION
- PSTAT_DELETION
- QUIESCED_EXCLUSIVE
- QUIESCED_SHARE
- QUIESCED_UPDATE
- REBAL_IN_PROGRESS
- REDIST_IN_PROGRESS
- REORG_IN_PROGRESS
- RESTORE_IN_PROGRESS
- RESTORE_PENDING
- ROLLFORWARD_IN_PROGRESS
- ROLLFORWARD_PENDING
- STORDEF_ALLOWED
- STORDEF_CHANGED
- STORDEF_FINAL_VERSION
- STORDEF_PENDING
- SUSPEND_WRITE
This element contains a hexadecimal
value indicating the current table space state. The externally visible
state of a table space is composed of the hexadecimal sum of certain
state values. For example, if the state is "quiesced: EXCLUSIVE" and
"Load pending", the value is 0x0004 + 0x0008, which is 0x000c. Use
the db2tbst command to obtain the table space state
associated with a given hexadecimal value.
| Hexadecimal Value | Decimal Value | State |
|---|---|---|
| 0x0 | 0 | Normal (see the definition SQLB_NORMAL in sqlutil.h) |
| 0x1 | 1 | Quiesced: SHARE |
| 0x2 | 2 | Quiesced: UPDATE |
| 0x4 | 4 | Quiesced: EXCLUSIVE |
| 0x8 | 8 | Load pending |
| 0x10 | 16 | Delete pending |
| 0x20 | 32 | Backup pending |
| 0x40 | 64 | Roll forward in progress |
| 0x80 | 128 | Roll forward pending |
| 0x100 | 256 | Restore pending |
| 0x100 | 256 | Recovery pending (not used) |
| 0x200 | 512 | Disable pending |
| 0x400 | 1024 | Reorg in progress |
| 0x800 | 2048 | Backup in progress |
| 0x1000 | 4096 | Storage must be defined |
| 0x2000 | 8192 | Restore in progress |
| 0x4000 | 16384 | Offline and not accessible |
| 0x8000 | 32768 | Drop pending |
| 0x10000 | 65536 | No write is allowed |
| 0x20000 | 131072 | Load in progress |
| 0x40000 | 262144 | Redistribute in progress |
| 0x80000 | 524288 | Move in progress |
| 0x100000 | 1048576 | Move has started |
| 0x200000 | 2097152 | Move is terminating |
| 0x2000000 | 33554432 | Storage may be defined |
| 0x4000000 | 67108864 | Storage Definition is in 'final' state |
| 0x8000000 | 134217728 | Storage Definition was changed before rollforward |
| 0x10000000 | 268435456 | DMS rebalancer is active |
| 0x20000000 | 536870912 | TBS deletion in progress |
| 0x40000000 | 1073741824 | TBS creation in progress |
Note: LOAD does not set the table space state to
Load
pending or Delete pending.tablespace_state_change_object_id - State Change Object Identification monitor element
The object that caused the table space state to be set to "Load pending" or "Delete pending".
- Element identifier
- tablespace_state_change_object_id
- Element type
- information
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
- Usage
- This element is meaningful only if the table space state is
"Load pending" or "Delete pending". If nonzero, the value of this
element matches a value from column TABLEID of view SYSCAT.TABLES.
Note: LOAD does not set the table space state to
Load pendingorDelete pending.
tablespace_state_change_ts_id - State Change Table Space Identification monitor element
If the table space state is "Load pending" or "Delete pending", this shows the table space ID of the object that caused the table space state to be set.
- Element identifier
- tablespace_state_change_ts_id
- Element type
- information
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic |
- Usage
- This element is meaningful only if the table space state is
"Load pending" or "Delete pending". If nonzero, the value of this
element matches a value from column TABLESPACEID of view SYSCAT.TABLES.
Note: LOAD does not set the table space state to
Load pendingorDelete pending.
tablespace_total_pages - Total pages in table space monitor element
Total number of pages in a table space.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic (DMS table spaces) Buffer Pool (SMS table spaces) |
Usage
Total operating system space occupied by a table space. For DMS, this is the sum of the container sizes. For SMS, this is the sum of all file space used for the tables stored in this table space (and is only collected if the buffer pool switch is on).
tablespace_type - Table space type monitor element
The type of a table space.
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace | Basic |
Usage
This element shows whether this table space is a database managed table space (DMS), or system managed table space (SMS).
The values for tablespace_type (defined in sqlmon.h)
are as follows:
- For DMS: SQLM_TABLESPACE_TYP_DMS
- For SMS: SQLM_TABLESPACE_TYP_SMS
tablespace_usable_pages - Usable pages in table space monitor element
The total number of pages in a table space minus overhead pages.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic (DMS table spaces) Buffer Pool (SMS table spaces) |
Usage
This element is applicable to DMS table spaces only. For SMS table spaces, this element will have the same value as the tablespace_total_pages monitor element.
During a table space rebalance, the number of usable pages will include pages for the newly added container, but these new pages may not be reflected in the number of free pages until the rebalance is complete. When a table space rebalance is not taking place, the number of used pages plus the number of free pages, plus the number of pending free pages will equal the number of usable pages.
tablespace_used_pages - Used pages in table space monitor element
The total number of pages that are currently used (not free) in a table space.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace_nodeinfo | Basic (DMS table spaces) Buffer Pool (SMS table spaces) |
Usage
This is the total number of pages in use for a DMS table space. For an SMS table space it is equal to the value of tablespace_total_pages monitor element.
tablespace_using_auto_storage - Table space enabled for automatic storage monitor element
This element describes whether the table space was created as an automatic storage table space. A value of 1 means "Yes"; a value of 0 means "No".
| Table Function | Monitor Element Collection Command and Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | tablespace | Basic |
Usage
You can use this element to determine whether the given table space was created using automatic storage (that is, created with the MANAGED BY AUTOMATIC STORAGE clause), rather than with containers that are explicitly provided. The table space can have containers that exist on some or all of the storage paths associated with the database.
takeover_app_remaining_primary - Applications remaining on primary monitor element
During non forced takeover, the number of applications still to be forced off on primary. Return NULL if not in takeover.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information | Always collected |
takeover_app_remaining_standby - Applications remaining on standby monitor element
The number of reads on standby applications still to be forced off on the standby during takeover (forced and non forced). Return NULL if not in takeover.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information | Always collected |
target_cf_gbp_size - Target cluster caching facility group buffer pool size monitor element
During a dynamic resize, this monitor element shows the group buffer pool memory target value, in pages with a page size of 4 KB. A resize is complete when the target value matches the configured value.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_CF table function - Get CF metrics | Always collected |
target_cf_lock_size - Target cluster caching facility lock size monitor element
During a dynamic resize, this monitor element shows the global lock memory target value, in pages with a page size of 4 KB. A resize is complete when the target value matches the configured value.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_CF table function - Get CF metrics | Always collected |
target_cf_sca_size - Target cluster caching facility shared communications area size monitor element
During a dynamic resize, this monitor element shows the shared communications area memory target value, in pages with a page size of 4 KB. A resize is complete when the target value matches the configured value.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_CF table function - Get CF metrics | Always collected |
tbsp_datatag - Table space data tag monitor element
This element identifies the effective
data tag value for a table space. The effective data tag is the data
tag value that was specified explicitly for the table space or inherited
from the table space storage group.
Valid
user-specified range is 1 through 9; 0 indicates no data tag specified.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - get table space metrics | Always collected |
Usage note
A data tag is used to identify and group data that can be referenced within WLM configurations. The WLM configurations determine the effect of the tagging which may affect the processing priority of user work.
tbsp_last_consec_page - Last consecutive object table page monitor element
Object relative page number of the last contiguous meta-data page for the table space. This value is only valid for DMS table spaces. It is 0 otherwise.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
tbsp_max_page_top - Maximum table space page high watermark monitor element
The highest allocated page number for a DMS table space since the database was activated.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always collected |
Usage
This value changes whenever the value of the tablespace_page_top monitor element increases.
tbsp_names - Table space names monitor element
This element lists the table space names that the utility acts on.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_UTILITY table function - Get utilities running on the database | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Change History | UTILSTART | Always collected |
Usage
For the change history event monitor , if theobject_type element is DATABASE or TABLESPACE,
this is a comma delimited list of table space names that the utility
acts on.tbsp_trackmod_state - Table space trackmod state monitor element
The modification state that a table space is in with respect to the last or next backup.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TABLESPACE table function - Get table space metrics | Always |
Usage
You can use this monitor element to
determine the modification status of a table space. The status of
a table space can be in one of the following states:
- CLEAN
- No modifications occurred in the table space since the previous backup. If an incremental or delta backup is executed at this time, no data pages from this table space would be backed up.
- DIRTY
- Table space contains data that needs to be picked up by the next backup.
- ININCREMENTAL
- Table space contains modifications that were copied into an incremental backup. This state is in a DIRTY state relative to a full backup such that a future incremental backup needs to include some pages from this pool. This state is also in a CLEAN state such that a future delta backup does not need to include any pages from this pool.
- READFULL
- The latest table space modification state change was caused by a dirty table space that is being read by a full backup that might not have completed successfully, or is currently in progress.
- READINCREMENTAL
- The latest table space modification state change was caused by a dirty table space that is being read by an incremental backup that might not have completed successfully, or is currently in progress.
- UNAVAILABLE
- The trackmod configuration parameter is set to No. Therefore, no table space modification status information is available.
tcpip_recv_volume - TCP/IP received volume monitor element
The amount of data received by the data server from clients over TCP/IP. This value is reported in bytes.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
tcpip_recv_wait_time - TCP/IP received wait time monitor element
The time spent waiting for an incoming client request over TCP/IP excluding idle time. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
tcpip_recvs_total - TCP/IP receives total monitor element
The number of times data was received by the database server from the client application over TCP/IP.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
tcpip_send_volume - TCP/IP send volume monitor element
The amount of data sent by data server to client. This value is reported in bytes.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
tcpip_send_wait_time - TCP/IP send wait time monitor element
Time spent blocking on a TCP/IP send to the client. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
tcpip_sends_total - TCP/IP sends total monitor element
The number of times data was sent from the database server to the client application over TCP/IP.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
temp_tablespace_top - Temporary table space top monitor element
The temp_tablespace_top
monitor element is a high watermark, in KB, for the temporary table
space usage of DML activities at all nesting levels in a service class
or work class.
For service classes, this
monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for
the service class is set to NONE. For work classes, this monitor elements
returns -1 if no COLLECT AGGREGATE ACTIVITY DATA work action is specified
for the work class. For workloads, this
monitor element returns -1 when COLLECT AGGREGATE ACTIVITY DATA for
the workload is set to NONE.
For service classes, when you remap activities between service subclasses with a REMAP ACTIVITY action, only the temp_tablespace_top high watermark of the service subclass where an activity completes is changed. High watermarks of service subclasses an activity is mapped to but does not complete in are unaffected.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats | - |
| Statistics | event_wcstats | - |
| Statistics | event_wlstats | - |
Usage
Use this element to determine the highest DML activity system temporary table space usage reached on a member for a service class, workload, or work class in the time interval collected.
This element is only updated by activities that have a temporary table space threshold applied to them. If no temporary table space threshold is applied to an activity, a value of 0 is returned.
territory_code - Database Territory Code monitor element
The territory code of the database for which the monitor data is collected. This monitor element was formerly known as country_code.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl_info | Basic |
| Application | appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Event Log Header | event_log_header | Always collected |
| Connections | event_connheader | Always collected |
- Usage
- Territory code information is recorded in the database configuration
file.
For DRDA AS connections, this element will be set to 0.
thresh_violations - Number of threshold violations monitor element
Number of times
a threshold was violated.
This
monitor element is an alias of the num_threshold_violations - Number of threshold violations monitor element monitor
element, which is returned by snapshot monitoring routines and the
Database event monitor.
| 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 details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
Use this element to quickly determine if there have been any WLM thresholds that have been violated. If thresholds have been violated you can then use the threshold violations event monitor (if created and active) to obtain details about the threshold violations.
For example, to obtain details which threshold was violated.
threshold_action - Threshold action monitor element
The action of the threshold to which this threshold violation record applies. Possible values include Stop, Continue and Remap.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Threshold violations | event_thresholdviolations | - |
Usage
Use this element to determine whether the activity that violated the threshold was stopped when the violation occurred, was allowed to continue executing, or was remapped to another service subclass. If the activity was stopped, the application that submitted the activity will have received an SQL4712N error. If the activity was remapped to another service subclass, agents working for the activity on the member will be moving to the target service subclass of the threshold.
threshold_domain - Threshold domain monitor element
The domain of the threshold responsible for this queue.
Possible values are
- Database
- Work Action Set
- Service Superclass
- Service Subclass
- Workload
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_QUEUE_STATS table function - Return threshold queue statistics | Always collected |
| WLM_GET_QUEUE_STATS table function - Return threshold queue statistics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_qstats | Always collected |
Usage
This element can be used for distinguishing the queue statistics of thresholds that have the same predicate but different domains.
threshold_maxvalue - Threshold maximum value monitor element
For non-queuing thresholds, this
monitor element represents the value that was exceeded to cause this
threshold violation. For queuing thresholds, this monitor element
represents the level of concurrency that caused the queuing.
The level of concurrency that caused the violation of the
queuing threshold is the sum of threshold_maxvalue and threshold_queuesize monitor
elements.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Threshold violations | event_thresholdviolations | Always collected |
Usage
For activity thresholds, this element provides a historical record of what the threshold's maximum value was at the time the threshold was violated. This is useful when the threshold's maximum value has changed since the time of the violation and the old value is no longer available from the SYSCAT.THRESHOLDS view. For the DATATAGINSC IN and DATATAGINSC NOT IN thresholds, this element contains the value of the data tag that violated the threshold.
threshold_name - Threshold name monitor element
The unique name of the threshold responsible for this queue.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_QUEUE_STATS table function - Return threshold queue statistics | Always collected |
| WLM_GET_QUEUE_STATS table function - Return threshold queue statistics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_qstats | Always collected |
Usage
Use this element to uniquely identify the queuing threshold whose statistics this record represents.
threshold_predicate - Threshold predicate monitor element
Identifies the type of threshold that was violated or for which statistics were collected.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_QUEUE_STATS table function - Return threshold queue statistics | Always collected |
| WLM_GET_QUEUE_STATS table function - Return threshold queue statistics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Threshold violations | event_thresholdviolations | Always collected |
| Statistics | event_qstats | Always collected |
Usage
Use this element in conjunction with other statistics or threshold violation monitor elements for analysis of a threshold violation.
The valid values for this element when reported in the
event_thresholdviolations logical group are:
- ActivityRunTime
- ActivityRunTimeInAllSC
- ActivityTotalTime
- AggSQLTempSpace
- ConcurrentDBCoordActivities
- ConcurrentWorkloadActivities
- ConcurrentWorkloadOccurrences
- ConnectionIdleTime
- CPUTime
- CPUTimeInSC
- DataTagInSC
- DataTagNotInSC
- EstimatedSQLCost
- SQLRowsRead
- SQLRowsReadInSC
- SQLRowsReturned
- SQLTempSpace
- TotalMemberConnections
- TotalSCMemberConnections
- UowTotalTime
The valid values for this element when reported in the event_qstats
logical group are:
- ConcurrentDBCoordActivities
- TotalMemberConnections
- TotalSCMemberConnections
threshold_queuesize - Threshold queue size monitor element
The size of the queue for a queuing threshold. An attempt to exceed this size causes a threshold violation. For a non-queuing threshold, this value is 0.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Threshold violations | event_thresholdviolations | - |
Usage
Use this element to determine the number of activities or connections in the queue for this threshold at the time the threshold was violated.
thresholdid - Threshold ID monitor element
Identifies the threshold to which a threshold violation record applies or for which queue statistics were collected.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_QUEUE_STATS table function - Return threshold queue statistics | Always collected |
| WLM_GET_QUEUE_STATS table function - Return threshold queue statistics | Always collected |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Threshold violations | event_thresholdviolations | Always collected |
| Statistics | event_qstats | Always collected |
Usage
Use this element in conjunction with other activity history monitor elements to analyze a threshold queue or the activity that violated a threshold.
time_completed - Time completed monitor element
The time at which the activity described by this activity record finished executing. This element is a local timestamp.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | Always collected |
Usage
Use this element in conjunction with other activity history elements for analysis of the behavior of an activity.
This field has a value of "0000-00-00-00.00.00.000000" when a full activity record could not be written to a table event monitor due to memory limitations. If the activity was captured while it was in progress, then this field represents the time that activity was collected.
time_created - Time created monitor element
The time at which a user submitted the activity described by this activity record. This element is a local timestamp.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | - |
Usage
Use this element in conjunction with other activity history elements for analysis of the behavior of an activity.
time_of_violation - Time of violation monitor element
The time at which the threshold violation described in this threshold violation record occurred. This element is a local timestamp.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Threshold violations | event_thresholdviolations | - |
Usage
Use this element in conjunction with other threshold violations monitor elements for analysis of a threshold violation.
time_since_last_recv - Time since last message received monitor element
The time since the last message was received. Normally, this number is no more than heartbeat_interval because an HADR database sends out a heartbeat message on heartbeat interval when the channel is idle. A larger number indicates a delay in message delivery. When this number reaches hadr_timeout, the connection is closed. Units are milliseconds.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_HADR table function - Returns high availability disaster recovery (HADR) monitoring information | Always collected |
time_stamp - Snapshot Time monitor element
The date and time when the database system monitor information was collected.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database Manager | collected | Basic |
- Usage
- You can use this element to help relate data chronologically if you are saving the results in a file or database for ongoing analysis.
time_started - Time started monitor element
The time at which the activity described by this activity record began executing. This element is a local timestamp.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | Always collected |
Usage
Use this element in conjunction with other activity history elements for analysis of the behavior of an activity.
If the activity got rejected, then the value of act_exec_time monitor element is 0. In this case, the value of time_started monitor element equals the value of time_completed monitor element.
time_zone_disp - Time Zone Displacement monitor element
Number of seconds that the local time zone is displaced from Greenwich Mean Time (GMT).
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database Manager | collected | Basic |
- Usage
- All time reported by reported by the database system monitor is GMT, this displacement calculates the local time.
timezoneid - Time zone identifier monitor element
Identifies the time zone (for example, US/Eastern).
| Table function | Monitor element collection level |
|---|---|
| MON_GET_INSTANCE table function - Get instance level information | Always collected |
timezoneoffset - Time difference from UCT monitor element
Time difference (in milliseconds) from Universal Coordinated Time (UCT).
| Table function | Monitor element collection level |
|---|---|
| MON_GET_INSTANCE table function - Get instance level information | Always collected |
top - Histogram bin top monitor element
The inclusive top end of the range of a histogram bin. The value of this monitor element is also the bottom exclusive end of the range of the next histogram bin.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_histogrambin | - |
Usage
Use this element with the corresponding bottom element to determine the range of a bin within a histogram.
tot_log_used_top - Maximum Total Log Space Used monitor element
The maximum amount of total log space used (in bytes).
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | event_db | Always collected |
- Usage
- You can use this element to help evaluate the amount
of primary log space
that you have allocated. Comparing the value of this element with
the amount
of primary log space you have allocated can help you to evaluate your
configuration
parameter settings. Your primary log space allocation can be calculated
using
the following formula:
logprimary x logfilsiz x 4096 (see note below)You can use this element in conjunction with sec_log_used_top and sec_logs_allocated to show your current dependency on secondary logs.
This value includes space used in both primary and secondary log files.
You may need to adjust the following configuration parameters:- logfilsiz
- logprimary
- logsecond
Note: While the database system monitor information is given in bytes, the configuration parameters are set in pages, which are each 4K bytes.
total_act_time - Total activity time monitor element
The total amount of time spent executing activities. This value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics event_wlmetrics |
REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
During
its life cycle, an activity can spend time in various states, which
are reported by the activity_state event monitor
element. Some of the states for an activity include:
- EXECUTING - This state indicates that the coordinator agent is working on the activity. An activity that encounters a lock wait situation is reported as executing.
- IDLE - This state indicates that the coordinator agent is waiting for the next request from a client.
- QUEUED - Some thresholds include a built-in queue. This state indicates that the activity is waiting in the queue for its turn to begin executing.
Use this monitor
element along with the total_act_wait_time monitor
element to determine the percentage of time the data server spent
working on the activity.
(total_act_time - total_act_wait_time)/(total_act_time) = % of time data server is actively working on activitytotal_act_wait_time - Total activity wait time monitor element
Total time spent waiting within the database server, while processing an activity. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics event_wlmetrics |
REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
Use this monitor element along with
the total_act_time monitor element to determine
the percentage of time the data server spent working on the activity.
(total_act_time - total_act_wait_time)/(total_act_time) = % of time data server is actively working on activitytotal_app_commits - Total application commits monitor elements
Total number of commit statements issued by the client application.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_app_rollbacks - Total application rollbacks monitor element
Total number of rollback statements issued by the client application.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
If you issue a CONNECT RESET or TERMINATE command, you also initiate a rollback operation. This operation increments the total_app_rollbacks counter by 1.
total_app_rqst_time - Total application request time monitor element
The total elapsed time spent on application requests; this is the total time spent by coordinator agents on the server executing application requests. This value is reported in milliseconds.
Usage
Use this monitor element to determine the time that the application request spent in the data server. This value can be used to help determine if the data server is the source of an observed performance problem.
For example, if a user reports that there is a problem with an application and it has taken 20 minutes to return, and if you determine that total application request time is 1 minute and there are currently no application requests in progress for the connection, then the performance problem might lie outside of the data server.
total_app_section_executions - Total application section executions monitor element
Number of section executions performed by an application.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_async_runstats - Total number of asynchronous RUNSTATS requests monitor element
The total number of successful asynchronous RUNSTATS activities performed by real-time statistics gathering for all the applications in the database. Values reported by all the database partitions are aggregated together.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_DATABASE table function - Get database level information | Always collected |
| MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Statement |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | event_db | Always collected |
Usage
Use this element to determine how many successful asynchronous RUNSTATS activities have been performed by real-time statistics gathering. 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 and stats_fabrications monitor elements, this element can help you to track the different types of statistics collection activities related to real-time statistics gathering and analyze their performance impact.
total_backup_proc_time - Total non-wait time for online backups monitor element
The total amount of processing (non-wait) time that was spent doing online backups. The value is in milliseconds.
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of Work | uow_metrics | REQUEST METRICS BASE |
Usage
Use the total_backup_proc_time monitor element to determine the portion of the elapsed time that was spent in online backups that is spent only processing requests. The total_backup_time monitor element indicates the total amount of elapsed time that is spent in online backups.
total_backup_time - Total elapsed time for doing online backups monitor element
The total elapsed time that was spent doing online backups. The value is in milliseconds.
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of Work | uow_metrics | REQUEST METRICS BASE |
Usage
Use the total_backup_time monitor element to determine the portion of the total amount of time that was spent on database requests that was used for backup operations. The total_rqst_time monitor element determines the total amount of time that is spent on database requests.
total_backups - Total online backups monitor element
The total number of online backups.
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of Work | uow_metrics | REQUEST METRICS BASE |
total_buffers_rcvd - Total FCM Buffers Received monitor element
For snapshot monitor, this monitor element reports the total number of FCM buffers received by the node issuing the GET SNAPSHOT command from the node identified by the node_number monitor element. For table function monitor, this monitor element reports the total number of FCM buffers received from a remote database member.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_FCM_CONNECTION_LIST - Get details for all FCM connections | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database Manager | fcm_node | Basic |
Usage
Use this element to measure the level of traffic between the current member and the remote member. If the total number of FCM buffers received from this member is high, consider redistributing the database or moving tables to reduce the traffic between members.
total_buffers_sent - Total FCM Buffers Sent monitor element
For snapshot monitor, this monitor element reports the total number of FCM buffers that have been sent from the node issuing the GET SNAPSHOT command to the node identified by the node_number monitor element. For table function monitor, this monitor element reports the total number of FCM buffers sent from the current database member to a remote database member.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_FCM_CONNECTION_LIST - Get details for all FCM connections | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database Manager | fcm_node | Basic |
Usage
Use this element to measure the level of traffic between the current member and the remote member. If the total number of FCM buffers sent to this member is high, consider redistributing the database or moving tables to reduce the traffic between members.
total_bytes_received - Bytes received monitor element
Total number of bytes received since the network adapter started.
| Table Function | Monitor Element Collection Level |
|---|---|
| ENV_GET_NETWORK_RESOURCES table function - Return network adapter information | Always collected |
total_bytes_sent - Bytes sent monitor element
Total number of bytes sent s since the network adapter started.
| Table Function | Monitor Element Collection Level |
|---|---|
| ENV_GET_NETWORK_RESOURCES table function - Return network adapter information | Always collected |
total_col_executions - Total column-organized executions monitor element
Total number of times that data in column-organized tables was accessed.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
This element
is incremented when the following operators are processed in a query
plan:
- Top level CTQ operator
- INSERT, UPDATE, and DELETE operators on a column-organized table.
total_col_proc_time - Total column-organized processing time monitor element
The total non-wait processing time spent accessing columnar data in a query oncolumn-organized tables. The value is given in milliseconds. This element is a subset of the elapsed time returned by the monitor element total_col_time, and represents the time in which the column-organized processing subagents were not idle on a measured wait time (for example, lock wait or IO).
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
total_col_synopsis_executions - Total column-organized synopsis table executions monitor element
Total number of times that column-organized synopsis tables were accessed.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
total_col_synopsis_proc_time - Total column-organized synopsis table processing time monitor element
The total non-wait processing time spent accessing column-organized synopsis tables.
The value is given in milliseconds.
This element is a subset of the elapsed time returned by the monitor element total_col_synopsis_time. This element represents the time in which the column-organized processing subagents were not idle on a measured wait time (for example, lock wait or I/O).
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
total_col_synopsis_time - Total column-organized synopsis table time monitor element
The total elapsed time spent accessing column-organized synopsis tables.
The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
total_col_time - Total column-organized time monitor element
The total elapsed time spent accessing columnar data in a query oncolumn-organized tables. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_col_vector_consumers - Total columnar vector memory consumers monitor element
The total number of consumers of columnar vector memory that ran.
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activity Metrics | event_activitymetrics | ACTIVITY METRICS BASE |
| Package Cache Metrics | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics Metrics | event_scmetrics | REQUEST METRICS BASE |
| Statistics Metrics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of Work Metrics | uow_metrics | REQUEST METRICS BASE |
total_commit_proc_time - Total commits processing time monitor element
The total amount of processing (non-wait) time spent performing commit processing on the database server. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_commit_time - Total commit time monitor element
The total amount of time spent performing commit processing on the database server. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_compilations - Total compilations monitor element
The total number of explicit compiles on the database server. Explicit compiles are compilations directly initiated by a user request such as a bind, rebind, prepare or execute immediate.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_compile_proc_time - Total compile processing time monitor element
The total amount of processing (non-wait) time spent performing explicit compiles on the database server. Explicit compiles are compilations directly initiated by a user request such as a bind, rebind, prepare or execute immediate. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_compile_time - Total compile time monitor element
The total amount of time spent performing explicit compiles on the database server. Explicit compiles are compilations directly initiated by a user request such as a bind, rebind, prepare or execute immediate. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_connect_authentication_proc_time - Total connection authentication processing time monitor element
The amount of processing (non-wait) time spent performing connection or switch user authentication, in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of Work | Reported in the system_metrics document. | REQUEST METRICS BASE |
total_connect_authentications - Connections or switch user authentications performed monitor element
The number of connection or switch user authentications performed.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of Work | uow (reported in the metrics.xml
document) uow_metrics |
REQUEST METRICS BASE |
total_connect_authentication_time - Total connection or switch user authentication request time monitor element
The amount of time spent performing connection or switch user authentication, in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of Work | uow (reported in the metrics.xml
document) uow_metrics |
REQUEST METRICS BASE |
total_connect_request_proc_time - Total connection or switch user request processing time monitor element
The amount of processing (non-wait) time spent processing a connection or switch user request, in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of Work | uow (reported in the metrics.xml
document) uow_metrics |
REQUEST METRICS BASE |
total_connect_requests - Connection or switch user requests monitor element
The total number of connection or switch user requests.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of Work | uow (reported in the metrics.xml
document) uow_metrics |
REQUEST METRICS BASE |
total_connect_request_time - Total connection or switch user request time monitor element
The amount of time spent performing a connection or switch user request, in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of Work | uow (reported in the metrics.xml
document) uow_metrics |
REQUEST METRICS BASE |
total_connections - Total connections monitor element
Current number of connections.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_INSTANCE table function - Get instance level information | Always collected |
Usage
This element can be used to help determine the appropriate setting for the max_connections configuration parameter.
This element combines local and remote connections.
total_cons - Connects Since Database Activation monitor element
Indicates the number of connections to the database since the first connect, activate, or last reset (coordinator agents).
| Table function | Monitor element collection level |
|---|---|
| MON_GET_DATABASE table function - Get database level information | Always collected |
| MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Database | dbase_remote | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | event_db | Always collected |
- Usage
- You can use this element in conjunction with the db_conn_time
and the db2start_time monitor elements to calculate the frequency
at which applications have connected to the database.
If the frequency of connects is low, you may want to explicitly activate the database using the ACTIVATE DATABASE command before connecting any other application, because of the extra processing time that is associated with the first connect to a database (for example, initial buffer pool allocation). This will result in subsequent connects being processed at a higher rate.
Note: When you reset this element, its value is set to the number of applications that are currently connected, not to zero.
total_cpu_time - Total CPU time monitor element
The total amount of CPU time used while within the database system. Represents total of both user and system CPU time. This value is in microseconds.
When returned by the WLM_GET_SERVICE_SUBCLASS_STATS or the WLM_GET_WORKLOAD_STATS table function, it represents the total CPU time since the last reset of statistics. When returned by the MON_SAMPLE_SERVICE_CLASS_METRICS or the MON_SAMPLE_WORKLOAD_METRICS table function, it represents the total CPU time since the function was executed.
When returned by the MON_GET_ROUTINE or MON_GET_ROUTINE_DETAILS table function, this element represents the total CPU time spent in agents and subagents of the current member for this routine. CPU time spent in fenced processes is not included.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
Usage
Together
with the total_disp_run_queue_time monitor element, total_cpu_time can
be used to compute a measure of the amount of contention for the CPU
resource, measured on a scale from 0 to 1, with lower numbers meaning
greater contention for the CPU resource. This measure, called CPU
velocity, is computed by measuring the amount of time that work in
the service class has access to the CPU divided by the total time
spent accessing the CPU or waiting to access the CPU. CPU velocity
gives a measure of how efficiently the work executing in the service
class is being executed relative to how efficiently it could be executed
if such work never had to wait for the CPU. The formula is as follows:
CPU velocity = total_cpu_time / (total_cpu_time + total_disp_run_queue_time)total_disp_run_queue_time - Total dispatcher run queue time monitor element
The total time that requests, that were run in this service class, spent waiting to access the CPU. This value is given in microseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Package cache | Reported in the activity_metrics document | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document | REQUEST METRICS BASE |
Usage
Together with the total_cpu_time,
the total_disp_run_queue_time monitor element
can be used to compute a measure of the amount of contention for the
CPU resource, measured on a scale from 0 to 1, with lower numbers
meaning greater contention for the CPU resource. This measure, called
CPU velocity, is computed by measuring the amount of time that work
in a service class has access to the CPU divided by the total time
spent accessing the CPU or waiting to access the CPU. It gives a measure
of how efficiently the work is being executed relative to how efficiently
it could be executed if such work never had to wait for the CPU. The
formula is as follows:
CPU velocity = total_cpu_time / (total_cpu_time + total_disp_run_queue_time)When returned by the WLM_GET_SERVICE_SUBCLASS_STATS or the WLM_GET_WORKLOAD_STATS function, this monitor element represents the total dispatcher run queue wait time since the last reset of statistics.
When returned by the MON_SAMPLE_SERVICE_CLASS_METRICS or the MON_SAMPLE_WORKLOAD_METRICS function, this monitor element represents the total dispatcher run queue wait time since the function was executed.
total_exec_time - Elapsed statement execution time monitor element
The total time in seconds and microseconds that was spent executing a particular statement in the SQL cache.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Dynamic SQL | dynsql | Statement |
Usage
Use this element with num_executions monitor element determine the average elapsed time for the statement and identify the SQL statements that would most benefit from a tuning of their SQL. The num_compilation monitor element must be considered when evaluating the contents of this element.
Note: Due to the differences in granularity
with which the database 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.
total_extended_latch_wait_time - Total extended latch wait time monitor element
The amount of time, in milliseconds, spent in extended latch waits.
| 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 | uow (reported in the metrics.xml
document) uow_metrics |
REQUEST METRICS BASE |
| Package cache | pkgcache (reported in
the metrics.xml document) pkgcache_metrics |
ACTIVITY METRICS BASE |
Usage
- Use the following formula to determine extended latch wait time
as a percentage of total wait time. This formula can be used to determine
if the time spent waiting on extended latches is high relative to
the total wait time.
(TOTAL_EXTENDED_LATCH_WAIT_TIME / TOTAL_WAIT_TIME) * 100 - Use the following formula to determine the average length of time
in milliseconds of an extended latch wait.
TOTAL_EXTENDED_LATCH_WAIT_TIME / TOTAL_EXTENDED_LATCH_WAITS
total_extended_latch_waits - Total extended latch waits monitor element
The number of extended latch waits.
| 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 | uow (reported in the metrics.xml
document) uow_metrics |
REQUEST METRICS BASE |
| Package cache | pkgcache (reported in
the metrics.xml document) pkgcache_metrics |
ACTIVITY METRICS BASE |
Usage
Use
the following formula to determine the average length of time in milliseconds
of an extended latch wait.
TOTAL_EXTENDED_LATCH_WAIT_TIME / TOTAL_EXTENDED_LATCH_WAITStotal_hash_grpbys - Total hash GROUP BY operations monitor element
The total number of hashed GROUP BY operations.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package Cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of work | event_wlmetrics | REQUEST METRICS BASE |
Usage
Use this element along with the hash_grpby_overflows element to determine if a large number of hashed GROUP BY operations are overflowing to disk. If the overflow value is high and the performance of applications using hashed GROUP BY operations needs improvement, then consider increasing the size of the sort heap.
total_hash_joins - Total Hash Joins monitor element
The total number of hash joins executed.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Application | appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Connection | event_conn | Always collected |
| Database | event_db | Always collected |
| Package cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmmetrics | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
- Usage
- At the database or application level, use this value in conjunction with hash_join_overflows and hash_join_small_overflows to determine if a significant percentage of hash joins would benefit from modest increases in the sort heap size.
total_hash_loops - Total Hash Loops monitor element
The total number of times that a single partition of a hash join was larger than the available sort heap space.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Application | appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Connection | event_conn | Always collected |
| Database | event_db | Always collected |
| Package cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmmetrics | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
- Usage
- Values for this element indicate inefficient execution of hash joins. This might indicate that the sort heap size is too small or the sort heap threshold is too small. Use this value in conjunction with the other hash join variables to tune the sort heap size (sortheap) and sort heap threshold (sheapthres) configuration parameters.
total_implicit_compilations - Total implicit compilations monitor element
The total_implicit_compilations
monitor element stores the total number of implicit compiles on the
database server. Implicit compiles are compilations that are not directly
requested by the user.
That is, they are not a result
of a bind, rebind, prepare or execute immediate request. For example,
an implicit compilation may occur when executing a statement that
was bound using the VALIDATE RUN option if the statement needs to
be compiled at execution time.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_implicit_compile_proc_time - Total implicit compile processing time monitor element
The total_implicit_compile_proc_time
monitor element stores the total amount of processing (non-wait) time
spent performing implicit compiles on the database server. Implicit
compiles are compilations that are not directly requested by the user.
That
is, they are not a result of a bind, rebind, prepare or execute immediate
request. For example, an implicit compilation may occur when executing
a statement that was bound using the VALIDATE RUN option if the statement
needs to be compiled at execution time. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_implicit_compile_time - Total implicit compile time monitor element
The total_implicit_compile_time
monitor element stores the total amount of time spent performing implicit
compiles on the database server. Implicit compiles are compilations
that are not directly requested by the user.
That is,
they are not a result of a bind, rebind, prepare or execute immediate
request. For example, an implicit compilation may occur when executing
a statement that was bound using the VALIDATE RUN option if the statement
needs to be compiled at execution time. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_index_build_proc_time - Total non-wait time spent building indexes due to index creation or re-creation monitor element
The total amount of processing (non-wait) time that is spent building indexes due to index creation or re-creation. This time includes the time that is spent by subagents when the index creation or re-creation operation is parallelized. The value is in milliseconds.
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package Cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of Work | uow_metrics | REQUEST METRICS BASE |
Usage
This element reports the numbers of
both explicit and implicit index builds. A CREATE INDEX statement
is an example of a way to explicitly build an index. Implicit index
builds can include the following builds:
- Creation of an index to enforce primary key or unique key constraints
- Creation of an index for a system temporary table that is created internally to help optimize query performance
- Re-creation of an index, which is considered implicit because it occurs only as necessary and can be caused by any access to a table, depending on the value of the INDEXREC configuration parameter
The value of this element does not include time that is spent building or rebuilding indexes during the execution of utilities, including the LOAD, REORG, and REDISTRIBUTE utilities. That time is counted as part of the time for the corresponding utility's time monitor element when one exists. For example, time for an index rebuild that occurs during a LOAD operation is counted as part of the time for the total_load_time monitor element. The value of the total_index_build_proc_time monitor element also does not include time for indexes that are built during log replay (for HADR or ROLLFORWARD operations, for example) that can occur when index builds are fully logged. For more information, see the logindexbuild configuration parameter.
total_index_build_time - Total time spent building indexes due to index creation or re-creation monitor element
The total time that is spent building indexes due to index creation or re-creation, including the time that is spent by subagents when the index creation or re-creation operation is parallelized. The value is in milliseconds.
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package Cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of Work | uow_metrics | REQUEST METRICS BASE |
Usage
This value of this element includes
both explicit and implicit index builds. A CREATE INDEX statement
is an example of a way to explicitly build an index. Implicit index
builds can include the following builds:
- Creation of an index to enforce primary key or unique key constraints
- Creation of an index for a system temporary table that is created internally to help optimize query performance
- Re-creation of an index, which is considered implicit because it occurs only as necessary and can be caused by any access to a table, depending on the value of the INDEXREC configuration parameter
The value of this element does not include time that is spent building or rebuilding indexes during the execution of utilities, including the LOAD, REORG, and REDISTRIBUTE utilities. That time is counted as part of the time for the corresponding utility's time monitor element when one exists. For example, time for an index rebuild that occurs during a LOAD operation is counted as part of the time for the total_load_time monitor element. The value of the total_index_build_time element also does not include time for indexes that are built during log replay (for HADR or ROLLFORWARD operations, for example) that can occur when index builds are fully logged. For more information, see the logindexbuild configuration parameter.
total_indexes_built - Total number of indexes built monitor element
The total number of indexes that were built.
| Event type | Logical data grouping | Monitor switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Package Cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmetrics | REQUEST METRICS BASE |
| Unit of Work | uow_metrics | REQUEST METRICS BASE |
Usage
This element reports the numbers of
both explicit and implicit index builds. A CREATE INDEX statement
is an example of a way to explicitly build an index. Implicit index
builds can include the following builds:
- Creation of an index to enforce primary key or unique key constraints
- Creation of an index for a system temporary table that is created internally to help optimize query performance
- Re-creation of an index, which is considered implicit because it occurs only as necessary and can be caused by any access to a table, depending on the value of the INDEXREC configuration parameter
The value of this element does not include time for indexes that are built during the execution of utilities, including the LOAD, REORG, and REDISTRIBUTE utilities. The value of this element also does not include time for indexes that are built during log replay (for HADR or ROLLFORWARD operations, for example) that can occur when index builds are fully logged. For more information, see the logindexbuild configuration parameter.
total_load_proc_time - Total load processing time monitor element
Total amount of processing (non-wait) time spent performing load processing on the database server. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_load_time - Total load time monitor element
The total amount of time spent performing loads on the database server. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_loads - Total loads monitor element
The total number of loads performed on the database server.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the details_xml document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_log_available - Total Log Available monitor element
The amount of active log space in the database that is not being used by uncommitted transactions (in bytes).
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
Usage
Use
this element in conjunction with total_log_used to determine whether
you may need to adjust the following configuration parameters to avoid
running out of log space:
- logfilsiz
- logprimary
- logsecond
If total_log_available goes down to 0, SQL0964C will be returned. You may need to increase the above configuration parameters, or end the oldest transaction by COMMIT, ROLLBACK or FORCE APPLICATION.
If logsecond is set to -1 this element will contain SQLM_LOGSPACE_INFINITE.
Note: While
the database system monitor information
is given in bytes, the configuration parameters are set in pages,
which are each 4K bytes.
total_log_used - Total Log Space Used monitor element
The total amount of active log space currently used (in bytes) in the database.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_TRANSACTION_LOG table function - Get log information | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
- Usage
- Use this element in conjunction with
total_log_available to determine
whether you may need to adjust the following configuration parameters
to avoid
running out of log space:
- logfilsiz
- logprimary
- logsecond
Note: While the database system monitor information is given in bytes, the configuration parameters are set in pages, which are each 4K bytes.
total_metadata_update_time - Total time for metadata update for extent movement monitor element
During extent movement, this monitor element shows the overall time taken to update cached page locations for moved extents, including overhead.
The value is given in milliseconds.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_EXTENT_MOVEMENT_STATUS - get extent movement progress | Always collected |
total_move_time - Total extent move time monitor element
In milliseconds, the total move time for all extents moved during the table space rebalance process.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_EXTENT_MOVEMENT_STATUS - Get extent movement progress status metrics | Always collected |
total_nested_invocations - Total nested invocations monitor element
Number of times a routine is invoked at a nesting level > 1.
An example is when a routine or trigger is invoked under
the context of another routine or trigger.
| 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 |
total_olap_funcs - Total OLAP Functions monitor element
The total number of OLAP functions executed.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Application | appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Connection | event_conn | Always collected |
| Database | event_db | Always collected |
| Package cache | pkgcache_metrics | ACTIVITY METRICS BASE |
| Statistics | event_scmetrics | REQUEST METRICS BASE |
| Statistics | event_wlmmetrics | REQUEST METRICS BASE |
| Unit of work | uow_metrics | REQUEST METRICS BASE |
Usage
At the database or application level, use this value in conjunction with olap_func_overflows to determine if a significant percentage of OLAP functions would benefit from modest increases in the sort heap size.
total_peas - Total partial early aggregations monitor element
The total number of times that partial early aggregation operations have been executed.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Connection | event_conn | - |
| Statements | event_stmt | - |
| Transactions | event_xact | - |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
Usage
At the database or application level, use this value in conjunction with post_threshold_peas to determine if a significant number of partial early aggregation operations would benefit from an increase in either sort heap size or sort heap threshold. If the ratio of post_threshold_peas to total_peas is high, increasing the sort heap size or the sort heap threshold, or both, may improve database or application performance.
total_peds - Total partial early distincts monitor element
The total number of times that partial early distinct operations have been executed.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Connection | event_conn | - |
| Statements | event_stmt | - |
| Transactions | event_xact | - |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
Usage
At the database or application level, use this value in conjunction with the disabled_peds monitor element and the post_threshold_peds monitor element to determine if a significant number of partial early distinct operations would benefit from an increase in either sort heap size or sort heap threshold. If the ratio of the disabled_peds monitor element and the post_threshold_peds monitor element to the total_peds monitor element is high, increasing the sort heap size or the sort heap threshold, or both, may improve database or application performance.
total_reorg_proc_time - Total reorganization processing time monitor element
The total amount of processing (non-wait) time spent performing reorg operations on the database server. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_reorg_time - Total reorganization time monitor element
The total amount of time spent performing reorg operations on the database server. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_reorgs - Total reorganizations monitor element
The number of reorg operations issued against the database server.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_rollback_proc_time - Total rollback processing time monitor element
The total amount of processing (non-wait) time spent performing rollback operations on the database server. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_rollback_time - Total rollback time monitor element
The total amount of time spent performing rollback operations on the database server. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_routine_coord_time - Total routine coordinator time monitor element
Total amount of time the coordinator agent spent executing the routine.
For procedures, compiled SQL functions, compiled triggers,
and dynamically prepared compound SQL statements, this element represents
the total elapsed time spent in the routine. If request metrics are
disabled, this value is zero.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_ROUTINE table function - get aggregated execution metrics for routines | REQUEST METRICS BASE |
| MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines | REQUEST METRICS BASE |
total_routine_invocations - Total routine invocations monitor elements
The total number of times a routine was invoked.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_routine_non_sect_proc_time - Non-section processing time monitor element
The total amount of processing time this statement spent performing non-section execution within routines. This value includes both the time spent executing user-code within routines and time spent performing non-section operations like commit or rollback. Processing time does not include wait time. The value is given in milliseconds.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | ACTIVITY METRICS BASE |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | ACTIVITY METRICS BASE |
| MON_GET_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 |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | ACTIVITY METRICS BASE |
| 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 |
total_routine_non_sect_time - Non-section routine execution time monitor elements
The total amount of time this statement spent performing non-section execution within routines. This value includes both the time spent executing user-code within routines and the time spent performing non-section operations like commit or rollback. The value is given in milliseconds.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_ACTIVITY table function - Return a list of activities | ACTIVITY METRICS BASE |
| MON_GET_ACTIVITY_DETAILS table function - Get complete activity details (reported in DETAILS XML document) | ACTIVITY METRICS BASE |
| MON_GET_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 |
| WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities | ACTIVITY METRICS BASE |
| 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 |
total_routine_time - Total routine time monitor element
The total time spent executing routines. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
If the collection level is set to BASE, then the value of total_routine_time monitor element does not include any time spent executing functions that were defined using the NO SQL clause.
If the collection level is set to EXTENDED, then the value of total_routine_time monitor element includes the time spent in all routines.
total_routine_user_code_proc_time - Total routine user code processing time monitor element
The total amount of time spent processing user code in routines. The value is in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
If the collection level is set to BASE, then this monitor element does not include any processing time spent executing functions that were defined using the NO SQL clause. Instead, this time is included in the value of the total_section_proc_time monitor element.
If the collection level is set to EXTENDED, then the value of this monitor element includes the processing time spent executing all routines.
total_routine_user_code_time - Total routine user code time monitor element
The total amount of time spent executing user code in routines. The value is in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
If the collection level is set to BASE, then the value of this monitor element does not include the time spent executing functions that were defined using the NO SQL clause. Instead, this time is included in the value of the total_section_time monitor element.
If the collection level is set to EXTENDED, then the value of this monitor element includes the time spent executing all routines.
total_rqst_mapped_in - Total request mapped-in monitor element
The total number of requests that were mapped into this service subclass via a remap threshold or a work action set.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_DATABASE table function - Get database level information | Always collected |
| MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
| MON_GET_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 |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the details_xml document) | REQUEST METRICS BASE |
total_rqst_mapped_out - Total request mapped-out monitor element
The total number of requests that were mapped out of this service subclass via a remap threshold or a work action set.
| Table Function | Monitor Element Collection Level |
|---|---|
| MON_GET_DATABASE table function - Get database level information | Always collected |
| MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
| MON_GET_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 |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
total_rqst_time - Total request time monitor element
The total amount of time spent working on requests. This value is reported in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
total_runstats - Total runtime statistics monitor element
The total number of runstats operations performed on the database server.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_runstats_proc_time - Total runtime statistics processing time monitor element
The total amount of processing (non-wait) time spent performing runstats operations on the database server. The value is given in milliseconds. Any time the runstats utility spends throttled does not count towards the runstats processing time.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_runstats_time - Total runtime statistics time monitor element
The total amount of time spent performing runstats operations on the database server. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
total_sec_cons - Secondary Connections monitor element
The number of connections made by a subagent to the database at the current node.
| Table function | Monitor element collection level |
|---|---|
| MON_GET_DATABASE table function - Get database level information | Always collected |
| MON_GET_DATABASE_DETAILS table function - Get database information metrics | Always collected |
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
- Usage
- You can use this element in conjunction with the total_cons, db_conn_time, and the db2start_time monitor elements to calculate the frequency at which applications have connected to the database.
total_section_proc_time - Total section processing time monitor element
The total amount of processing time agents spent performing section execution. Processing time does not include wait time. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
If the collection level is set to BASE, then the value of the total_section_proc_time monitor element includes processing time spent executing functions that were defined using the NO SQL clause.
If the collection level is set to EXTENDED, then the processing time spent executing these functions is not included in the value of the total_section_proc_time monitor element. It is included in the value of the total_routine_user_code_proc_time monitor element.
total_section_sort_proc_time - Total section sort processing time monitor element
Total amount of processing (non-wait) time spent performing sorts while executing a section, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
At the system level, use this element with the total_section_sorts monitor element to calculate the average sort processing time (does not include waits) during section execution, which can indicate whether or not sorting is an issue as far as performance is concerned.
At the activity level, use this element to identify statements that spend a large amount of time sorting. These statements may benefit from additional tuning to reduce the sort time.
total_section_sort_time - Total section sort time monitor element
Total amount of time spent performing sorts while executing a section, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the details_xml) document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
At the system level, use this element with the total_section_sorts monitor element to calculate the average sort time during section execution, which can indicate whether or not sorting is an issue as far as statement performance is concerned.
The total_section_sort_time element
includes both wait and processing time. If the value of (total_section_sort_time
- total_section_sort_proc_time) is high, sorts are spending
a lot of time waiting. For example, if sorts are frequently spilling
to disk, the value of the total_section_sort_time monitor
element will increase due to I/O waits. This time will not be included
in the total_section_sort_proc_time monitor element
value, which only counts the time actively processing a sort. In this
case, you may consider tuning sort memory to improve performance.
At the activity level, use this element to identify statements that spend a large amount of time sorting. These statements may benefit from additional tuning to reduce the sort time.
total_section_sorts - Total section sorts monitor element
Total number of sorts performed during section execution, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
Use this element with thetotal_section_sort_time monitor element to calculate the average amount of time spent performing sorts during section execution.
At the activity and package cache levels, use this element to identify statements which are performing large numbers of sorts. These statements may benefit from additional tuning to reduce the number of sorts. You can also use the EXPLAIN statement to identify the number of sorts a statement performs.
total_section_time - Total section time monitor element
The total time agents spent performing section execution. The value is given in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
| Unit of work |
Reported in the system_metrics document. |
REQUEST METRICS BASE |
Usage
If the collection level is set to BASE, then the value of total_section_time monitor element includes time spent executing functions that were defined using the NO SQL clause.
If the collection level is set to EXTENDED, then the time spent executing these functions is not included in the value of the total_section_time monitor element. It is included in the value of the total_routine_user_code_time monitor element instead.
total_sort_time - Total sort time monitor element
The total elapsed time for all sorts that have been executed. This value is reported in milliseconds.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Sort |
| Application | appl | Sort |
| Application | stmt | Sort |
| Dynamic SQL | dynsql | Sort |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | event_db | Always collected |
| Connection | event_conn | Always collected |
| Statements | event_stmt | Always collected |
| Activities | event_activity | Statement, Sort |
Usage
At a database or application level, use this element with total_sorts to calculate the average sort time, which can indicate whether or not sorting is an issue as far as performance is concerned.
At a statement level, use this element to identify statements that spend a lot of time sorting. These statements may benefit from additional tuning to reduce the sort time.
This count also includes sort time of temporary tables created during related operations. It provides information for one statement, one application, or all applications accessing one database.
When using monitor elements providing
elapsed
times, you should consider:
- Elapsed times are affected by system load, so the more processes you have running, the higher this elapsed time value.
- To calculate
this monitor element at a database level, the database system monitor sums
the application-level times. This can result in double counting
elapsed times at a database level, since more than one application
process
can be running at the same time. To provide meaningful data from the database level, you should normalize the data to a lower level. For example:
total_sort_time / total_sortsprovides information about the average elapsed time for each sort.
total_sorts - Total sorts monitor element
The total number of sorts that have been executed.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Database | dbase | Basic |
| Application | appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Activities | event_activitymetrics | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| 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 value with sort_overflows to calculate the percentage of sorts that need more heap space. You can also use it with total_sort_time to calculate the average sort time.
If the number of sort overflows is small with respect to the total sorts, then increasing the sort heap size may have little impact on performance, unless this buffer size is increased substantially.
At a statement level, use this element to identify statements which are performing large numbers of sorts. These statements may benefit from additional tuning to reduce the number of sorts. You can also use the SQL EXPLAIN statement to identify the number of sorts a statement performs.
total_stats_fabrication_proc_time - Total statistics fabrication processing time monitor element
The total non-wait 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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
total_stats_fabrication_time - Total statistics fabrication time monitor element
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.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | 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 | uow_metrics | REQUEST METRICS BASE |
| Package cache | pkgcache | ACTIVITY METRICS BASE |
total_stats_fabrications - Total statistics fabrications monitor elements
The total number of statistics fabrications performed by real-time statistics gathering. Statistics fabrication is the statistics collection activity needed to generate statistics during query compilation.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
total_sync_runstats_time - Total synchronous RUNSTATS time monitor elements
The total time spent on synchronous RUNSTATS activities triggered by real-time statistics gathering, in milliseconds. The synchronous RUNSTATS activities occur during query compilation.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | Always collected |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Package cache | Reported in the activity_metrics document. | Always collected |
total_sync_runstats_proc_time - Total synchronous RUNSTATS processing time monitor element
The non-wait time spent on synchronous RUNSTATS activities triggered by real-time statistics gathering, in milliseconds. The synchronous RUNSTATS activities occur during query compilation.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
total_sync_runstats - Total synchronous RUNSTATS activities monitor element
The total number of synchronous RUNSTATS activities triggered by real-time statistics gathering. The synchronous RUNSTATS activities occur during query compilation.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
total_sys_cpu_time - Total system CPU time for a statement monitor element
The total system CPU time for an SQL statement.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Dynamic SQL | dynsql | Statement |
Usage
Use this element with Elapsed Statement Execution Time and Total User CPU for a Statement to evaluate which statements are the most expensive.
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.
total_times_routine_invoked - Total routine invoked occurrences monitor element
Number of times the routine executed on a member.
| 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 |
total_usr_cpu_time - Total user CPU time for a statement monitor element
The total user CPU time for an SQL statement.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Dynamic SQL | dynsql | Statement |
Usage
Use this element with Elapsed Statement Execution Time and to evaluate the longest running statements.
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.
total_wait_time - Total wait time monitor element
The total time spent waiting within the database server. The value is in milliseconds.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
Usage
To understand the percentage of
time the database server spends actively working on requests, use
the following ratio:
(total_rqst_time - total_wait_time) / total_rqst_timetpmon_acc_str - TP monitor client accounting string monitor element
The data passed to the target database for logging and diagnostic purposes, if the sqleseti API was issued in this connection. The current value of the CLIENT_ACCTNG special register for this connection, unit of work, or activity.
This monitor element is synonymous to the client_acctng monitor element. The client_acctng monitor
element is used for monitoring table functions and event monitors
that write to unformatted tables. The tpmon_acc_str monitor element is used for snapshot monitors and event monitors
that write to tables, files, and pipes.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl_info | Basic |
| DCS Application | dcs_appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | - |
| Deadlock | event_dlconn | - |
| Transaction | event_xact | - |
Usage
Use this element for problem determination and accounting purposes.
tpmon_client_app - TP monitor client application name monitor element
Identifies the server transaction program performing the transaction, if the sqleseti API was issued in this connection. The current value of the CLIENT_APPLNAME special register for this connection, unit of work, or activity.
This monitor element is synonymous to the client_applname monitor element. The client_applname monitor
element is used for monitoring table functions and event monitors
that write to unformatted tables. The tpmon_client_app monitor element is used for snapshot monitors and event monitors
that write to tables, files, and pipes.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl_info | Basic |
| DCS Application | dcs_appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | - |
| Deadlock | event_dlconn | - |
| Transaction | event_xact | - |
Usage
Use this element for problem determination and accounting purposes.
tpmon_client_userid - TP monitor client user ID monitor element
The client user ID generated by a transaction manager and provided to the server, if the sqleseti API is used. The current value of the CLIENT_USERID special register for this connection, unit of work, or activity.
This monitor element is synonymous to the client_userid monitor element. The client_userid monitor
element is used for monitoring table functions and event monitors
that write to unformatted tables. The tpmon_client_userid monitor element is used for snapshot monitors and event monitors
that write to tables, files, and pipes.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl_info | Basic |
| DCS Application | dcs_appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | - |
| Deadlock | event_dlconn | - |
| Transaction | event_xact | - |
Usage
Use this element in application server or Transaction Processing monitor environments to identify the end-user for whom the transaction is being executed.
tpmon_client_wkstn - TP monitor client workstation name monitor element
Identifies the client's system or workstation (for example CICS® EITERMID), if the sqleseti API was issued in this connection. The current value of the CLIENT_WRKSTNNAME special register for this connection, unit of work, or activity.
This monitor element is synonymous to the client_wrkstnname monitor element. The client_wrkstnname monitor
element is used for monitoring table functions and event monitors
that write to unformatted tables. The tpmon_client_wkstn monitor element is used for snapshot monitors and event monitors
that write to tables, files, and pipes.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | appl_info | Basic |
| DCS Application | dcs_appl | Basic |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity | - |
| Deadlock | event_dlconn | - |
| Transaction | event_xact | - |
Usage
Use this element to identify the user's machine by node ID, terminal ID, or similar identifiers.
tq_cur_send_spills - Current number of table queue buffers overflowed monitor element
The current number of table queue buffers residing in a temporary table.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | subsection | Statement |
- Usage
- An agent writing to a table queue may be sending rows to several
readers. The writing agent will overflow buffers to a temporary table
when the agent that it is currently sending rows to is not accepting
rows and another agent requires rows in order to proceed. Overflowing
to temporary table allows both the writer and the other readers to
continue processing.
Rows that have been overflowed will be sent to the reading agent when it is ready to accept more rows.
If this number is high, and queries fail with sqlcode -968, and there are messages in db2diad.log indicating that you ran out of temporary space in the TEMP table space, then table queue overflows may be the cause. This could indicate a problem on another node (such as locking). You would investigate by taking snapshots on all the partitions for this query.
There are also cases, perhaps because of the way data is partitioned, where many buffers need to be overflowed for the query. In these cases you will need to add more disk to the temporary table space.
tq_id_waiting_on - Waited on node on a table queue monitor element
The identifier of the table queue that is waiting to send or receive data.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | subsection | Statement |
Usage
This can be used for troubleshooting.
tq_max_send_spills - Maximum number of table queue buffers overflows monitor element
Maximum number of table queue buffers overflowed to a temporary table.
- Element identifier
- tq_max_send_spills
- Element type
- watermark
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | subsection | Statement |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statements | event_subsection | Always collected |
- Usage
- Indicates the maximum number of table queue buffers that have been written to a temporary table.
tq_node_waited_for - Waited for node on a table queue monitor element
If the subsection status ss_status is waiting to receive or waiting to send and tq_wait_for_any is FALSE, then this is the number of the node that this agent is waiting for.
- Element identifier
- tq_node_waited_for
- Element type
- information
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | subsection | Statement |
- Usage
- This can be used for troubleshooting. You may want to take an application snapshot on the node that the subsection is waiting for. For example, the application could be in a lock wait on that node.
tq_rows_read - Number of Rows Read from table queues monitor element
Total number of rows read from table queues.
- Element identifier
- tq_rows_read
- Element type
- counter
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | subsection | Statement |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statements | event_subsection | Always collected |
- Usage
- If monitoring does not indicate that
this number is increasing, then
processing progress is not taking place.
If there is significant differences in this number between nodes, then some nodes may be over utilized while others are being under utilized.
If this number is large, then there is a lot of data being shipped between nodes, suggest that optimization might improve the access plan.
tq_rows_written - Number of rows written to table queues monitor element
Total number of rows written to table queues.
- Element identifier
- tq_rows_written
- Element type
- counter
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | subsection | Statement |
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Statements | event_subsection | Always collected |
- Usage
- If monitoring does not indicate that
this number is increasing, then
processing progress is not taking place.
If there is significant differences in this number between nodes, then some nodes may be over utilized while others are being under utilized.
If this number is large, then there is a lot of data being shipped between nodes, suggest that optimization might improve the access plan.
tq_sort_heap_rejections - Table queue sort heap rejections monitor element
The number of times that table queues requested for more sort heap memory and were rejected due to sort heap threshold being exceeded.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Connection | event_conn | - |
| Statements | event_stmt | - |
| Transactions | event_xact | - |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
Usage
Use this element in conjunction with the tq_sort_heap_requests monitor element to determine if table queues are getting sufficient sort heap memory most of the time. If the ratio of the tq_sort_heap_rejections monitor element to the tq_sort_heap_requests monitor element is high, database performance may be sub-optimal. Consider increasing the sort heap size.
tq_sort_heap_requests - Table queue sort heap requests monitor element
The number of times that table queues requested for more sort heap memory to store data.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Activities | event_activity (reported in the details_xml document) | ACTIVITY METRICS BASE |
| Statistics | event_scstats (reported in the metrics document) | REQUEST METRICS BASE |
| Statistics | event_wlstats (reported in the metrics document) | REQUEST METRICS BASE |
| Unit of work | Reported in the system_metrics document. | REQUEST METRICS BASE |
| Connection | event_conn | - |
| Statements | event_stmt | - |
| Transactions | event_xact | - |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
Usage
Use this element in conjunction with the tq_sort_heap_rejections monitor element to determine if table queues are getting sufficient sort heap memory most of the time. If the ratio of the tq_sort_heap_rejections monitor element to the tq_sort_heap_requests monitor element is high, database performance may be sub-optimal. Consider increasing the sort heap size.
tq_tot_send_spills - Total number of table queue buffers overflowed monitor element
Total number of table queue buffers overflowed to a temporary table.
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | subsection | Statement |
| 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 |
| Statements | event_subsection | Always collected |
| Package cache | Reported in the activity_metrics document. | ACTIVITY METRICS BASE |
Usage
Indicates the total number of table queue buffers that have been written to a temporary table. See the tq_cur_send_spills monitor element for more information.
tq_wait_for_any - Waiting for any node to send on a table queue monitor element
This flag is used to indicate that the subsection is blocked because it is waiting to receive rows from any node.
- Element identifier
- tq_wait_for_any
- Element type
- information
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Application | subsection | Statement |
- Usage
- If ss_status indicates waiting to receive data on a table queue and this flag is TRUE, then the subsection is waiting to receive rows from any node. This generally indicates that the SQL statement has not processed to the point it can pass data to the waiting agent. For example, the writing agent may be performing a sort and will not write rows until the sort has completed. From the db2expln output, determine the subsection number associated with the tablequeue that the agent is waiting to receive rows from. You can then examine the status of that subsection by taking a snapshot on each node where it is executing.
ts_name - Table space being rolled forward monitor element
The name of the table space currently rolled forward.
- Element identifier
- ts_name
- Element type
- information
| Snapshot Level | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Table Space | rollforward | Basic |
- Usage
- If a rollforward is in progress, this element identifies the table spaces involved.
txn_completion_status - Transaction completion status monitor element
This element indicates the status of the transaction.
| Event Type | Logical Data Grouping | Monitor Switch |
|---|---|---|
| Change History | TXNCOMPLETION | Always collected |
Usage
For the change history event monitor, the status of the transaction is one of:- C
- Commit
- R
- Rollback
- S
- Rollback to savepoint