System Monitor Guide and Reference

Database and Application Activity

The following sections provide information on database and application activity.

Locks and Deadlocks

The following elements provide information about locks and deadlocks:

Locks Held
Total Lock List Memory In Use
Deadlocks Detected
Number of Lock Escalations
Exclusive Lock Escalations
Lock Mode
Lock Status
Lock Object Type Waited On
Lock Object Name
Number of Lock Timeouts
Maximum Number of Locks Held
Connections Involved in Deadlock
Lock Escalation
Lock Mode Requested

Locks Held

Snapshot Level Database Application Lock	Logical Data Grouping dbase appl dbase_lock appl_lock	Monitor Switch Basic Basic Basic Basic
Resettable	No
Element Name Element Type	locks_held gauge
Related Information	"Number of Lock Escalations" "Exclusive Lock Escalations" "Maximum Number of Locks Held"

Description: The number of locks currently held.

Usage: If the monitor information is at the database level, this is the total number of locks currently held by all applications in the database.

If it is at the application level, this is the total number of locks currently held by all agents for the application. How you use this element depends on the level of information being returned from the database system monitor.

At the database level, you can use it in one of two ways:
- This element can provide summary information about locking. For example, you can calculate the average number of locks per application by dividing the value of this element by "Applications Connected Currently". If the resulting number is high, it may indicate that you can tune one of your applications to improve performance.
- You can also compare the value of this element against the results of the following formula to determine the number of additional locks that may be requested. This comparison can help you determine if the configuration parameters need adjusting or your applications need tuning.
```
 
  (locklist * 4096 / 36 ) - locks held = # remaining
```
  where:
  - locklist is the configuration parameter as described in the Administration Guide
  - 4096 is the number of bytes in one 4K page
  - 36 is the number of bytes required for each lock.
  Note: You may also use Total Lock List Memory In Use in a similar fashion.
At the application level, you can use this counter to determine if the application is approaching the maximum number of locks available to it, as defined by the maxlocks configuration parameter. This parameter indicates the percentage of the lock list that each application can use before lock escalations occur. Lock escalations can result in a decrease in concurrency between applications connected to a database. (See the Administration Guide for more information about this parameter.)
Since the maxlocks parameter is specified as a percentage and this element is a counter, you can compare the count provided by this element against the total number of locks that can be held by an application, as calculated using the following formula:
```
 
  (locklist * 4096 / 36 ) * (maxlocks / 100)
```
If you have a large number of locks, you may need to perform more commits within your application so that some of the locks can be released.

Total Lock List Memory In Use

Snapshot Level Database	Logical Data Grouping dbase	Monitor Switch Basic
Resettable	No
Element Name Element Type	lock_list_in_use gauge
Related Information	None

Description: The total amount of lock list memory (in bytes) that is in use.

Usage: This element may be used in conjunction with the locklist configuration parameter to calculate the lock list utilization. If the lock list utilization is high, you may want to consider increasing the size of that parameter. See the Administration Guide for more information.
Note: When calculating utilization, it is important to note that the locklist configuration parameter is allocated in pages of 4K bytes each, while this monitor element provides results in bytes.

Deadlocks Detected

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Lock
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	deadlocks counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Database Activation Timestamp" "Connection Request Start Timestamp" "Number of Lock Escalations" "Exclusive Lock Escalations" "Application ID Holding Lock"

Description: The total number of deadlocks that have occurred.

Usage: This element can indicate that applications are experiencing contention problems. These problems could be caused by the following situations:

Lock escalations are occurring for the database
An application may be locking tables explicitly when system-generated row locks may be sufficient
An application may be using an inappropriate isolation level when binding
Catalog tables are locked for repeatable read
Applications are getting the same locks in different orders, resulting in deadlock.

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

You can use the connection timestamp monitor elements (Last Reset Timestamp, Database Activation Timestamp, and Connection Request Start Timestamp) to determine the severity of the deadlocks. For example, 10 deadlocks in 5 minutes is much more severe than 10 deadlocks in 5 hours.

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

Number of Lock Escalations

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection Transaction	Logical Data Grouping db_event conn_event xaction_event
Element Name Element Type	lock_escals counter
Related Information	"When Counters are Initialized" "Database Activation Timestamp" "Exclusive Lock Escalations" "Maximum Number of Locks Held"

Description: The number of times that locks have been escalated from several row locks to a table lock.

Usage: A lock is escalated when the total number of locks held by an application reaches the maximum amount of lock list space available to the application, or the lock list space consumed by all applications is approaching the total lock list space. The amount of lock list space available is determined by the maxlocks and locklist configuration parameters.

When an application reaches the maximum number of locks allowed and there are no more locks to escalate, it will then use space in the lock list allocated for other applications. When the entire lock list is full, an error occurs.

This data item includes a count of all lock escalations, including exclusive lock escalations.

There are several possible causes for excessive lock escalations:

The lock list size (locklist) may be too small for the number of concurrent applications
The percent of the lock list usable by each application (maxlocks) may be too small
One or more applications may be using an excessive number of locks.

To resolve these problems, you may be able to:

Increase the locklist configuration parameter value. See the Administration Guide for a description of this configuration parameter.
Increase the maxlocks configuration parameter value. See the Administration Guide for a description of this configuration parameter.
Identify the applications with large numbers of locks (see "Maximum Number of Locks Held"), or those that are holding too much of the lock list, using the following formula:
```
  (((locks held * 36) / (locklist * 4096)) * 100)
```
and comparing the value to maxlocks. These applications can also cause lock escalations in other applications by using too large a portion of the lock list. These applications may need to resort to using table locks instead of row locks, although table locks may cause an increase in Lock Waits and Time Waited On Locks.

Exclusive Lock Escalations

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection Transaction	Logical Data Grouping db_event conn_event xaction_event
Element Name Element Type	x_lock_escals counter
Related Information	"When Counters are Initialized" "Database Activation Timestamp" "Number of Lock Escalations" "Connection Request Start Timestamp" "Maximum Number of Locks Held"

Description: The number of times that locks have been escalated from several row locks to one exclusive table lock, or the number of times an exclusive lock on a row caused the table lock to become an exclusive lock.

Usage: Other applications cannot access data held by an exclusive lock; therefore it is important to track exclusive locks since they can impact the concurrency of your data.

A lock is escalated when the total number of locks held by an application reaches the maximum amount of lock list space available to the application. The amount of lock list space available is determined by the locklist and maxlocks configuration parameters.

See Number of Lock Escalations for possible causes and resolutions to excessive exclusive lock escalations.

An application may be using exclusive locks when share locks are sufficient. Although share locks may not reduce the total number of lock escalations share lock escalations may be preferable to exclusive lock escalations.

Lock Mode

Snapshot Level Application Lock	Logical Data Grouping appl lock lock_wait	Monitor Switch Lock Lock Lock
Resettable	No
Event Type Deadlock	Logical Data Grouping dlconn_event
Element Name Element Type	lock_mode information
Related Information	"Resetting Monitor Data" Other lock information

Description: The type of lock being held.

Usage: This mode can help you determine the source of contention for resources.

This element indicates one of the following, depending on the type of monitor information being examined:

The type of lock another application holds on the object that this application is waiting to lock (for application-monitoring and deadlock-monitoring levels)
The type of lock held on the object by this application (for object-lock levels).

The values for this field are:

Mode Type of Lock API Constant

No Lock SQLM_LNON
IS Intention Share Lock SQLM_LOIS
IX Intention Exclusive Lock SQLM_LOIX
S Share Lock SQLM_LOOS
SIX Share with Intention Exclusive Lock SQLM_LSIX
X Exclusive Lock SQLM_LOOX
IN Intent None SQLM_LOIN
Z Super Exclusive Lock SQLM_LOOZ
U Update Lock SQLM_LOOU
NS Next Key Share Lock SQLM_LONS
NX Next Key Exclusive Lock SQLM_LONX
W Weak Exclusive Lock SQLM_LOOW
NW Next Key Weak Exclusive Lock SQLM_LONW

Mode	Type of Lock	API Constant
	No Lock	SQLM_LNON
IS	Intention Share Lock	SQLM_LOIS
IX	Intention Exclusive Lock	SQLM_LOIX
S	Share Lock	SQLM_LOOS
SIX	Share with Intention Exclusive Lock	SQLM_LSIX
X	Exclusive Lock	SQLM_LOOX
IN	Intent None	SQLM_LOIN
Z	Super Exclusive Lock	SQLM_LOOZ
U	Update Lock	SQLM_LOOU
NS	Next Key Share Lock	SQLM_LONS
NX	Next Key Exclusive Lock	SQLM_LONX
W	Weak Exclusive Lock	SQLM_LOOW
NW	Next Key Weak Exclusive Lock	SQLM_LONW

Lock Status

Snapshot Level Lock	Logical Data Grouping lock	Monitor Switch Basic
Resettable	No
Element Name Element Type	lock_status information
Related Information	"Lock Mode" "Lock Object Type Waited On" "Table File ID"

Description: Indicates the internal status of the lock.

Usage: This element can help explain what is happening when an application is waiting to obtain a lock on an object. While it may appear that the application already has a lock on the object it needs, it may have to wait to obtain a different type of lock on the same object.

The lock can be in one of the following statuses:

Granted state: indicates that the application has the lock in the state specified by Lock Mode.
Converting state: indicates that the application is trying to change the lock held to a different type; for example, changing from a share lock to an exclusive lock.

Note:

API users should refer to the sqlmon.h header file containing definitions of database system monitor constants.

Lock Object Type Waited On

Snapshot Level Application Lock	Logical Data Grouping appl appl_lock lock lock_wait	Monitor Switch Lock Lock Basic Lock
Resettable	No
Event Type Deadlock	Logical Data Grouping dlconn_event
Element Name Element Type	lock_object_type information
Related Information	"Resetting Monitor Data"

Description: The type of object against which the application holds a lock (for object-lock-level information), or the type of object for which the application is waiting to obtain a lock (for application-level and deadlock-level information).

Usage: This element can help you determine the source of contention for resources.

The objects may be one of the following types:

Table space
Table
Record (or row)
Internal (another type of lock held internally by the database manager).

Lock Object Name

Snapshot Level Application Lock	Logical Data Grouping appl appl_lock lock	Monitor Switch Lock Lock Basic
Resettable	No
Event Type Deadlock	Logical Data Grouping dlconn_event
Element Name Element Type	lock_object_name information
Related Information	"Resetting Monitor Data" "Lock Object Type Waited On" "Table Space Name" "Table Name" "Table Schema Name"

Description: This element is provided for informational purposes only. It is the name of the object for which the application holds a lock (for object-lock-level information), or the name of the object for which the application is waiting to obtain a lock (for application-level and deadlock-level information).

Usage: It is the name of the object for table-level locks is the file ID (FID) for SMS and DMS table spaces. For row-level locks, the object name is the row ID (RID). For table space locks, the object name is blank.

To determine the table holding the lock, use "Table Name" and "Table Schema Name" instead of the file ID, since the file ID may not be unique.

To determine the table space holding the lock, use "Table Space Name".

Lock Node

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Element Name Element Type	lock_node information
Related Information	None

Description: The node involved in a lock.

Usage: This can be used for troubleshooting.

Number of Lock Timeouts

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	lock_timeouts counter
Related Information	"When Counters are Initialized" Other elements in "Locks and Deadlocks"

Description: The number of times that a request to lock an object timed-out instead of being granted.

Usage: This element can help you adjust the setting for the locktimeout database configuration parameter. If the number of lock time-outs becomes excessive when compared to normal operating levels, you may have an application that is holding locks for long durations. In this case, this element may indicate that you should analyze some of the other elements related to Locks and Deadlocks to determine if you have an application problem.

You could also have too few lock time-outs if your locktimeout database configuration parameter is set too high. In this case, your applications may wait excessively to obtain a lock. See the Administration Guide for more information.

Maximum Number of Locks Held

Event Type Transaction	Logical Data Grouping xaction_event
Element Name Element Type	locks_held_top counter
Related Information	"Locks Held" "Number of Lock Escalations" "Exclusive Lock Escalations"

Description: The maximum number of locks held during this transaction.

Usage: You can use this element to determine if your application is approaching the maximum number of locks available to it, as defined by the maxlocks configuration parameter. This parameter indicates the percentage of the lock list that each application can use before lock escalations occur. Lock escalations can result in a decrease in concurrency between applications connected to a database. (See the Administration Guide for more information about this parameter.)

Since the maxlocks parameter is specified as a percentage and this element is a counter, you can compare the count provided by this element against the total number of locks that can be held by an application, as calculated using the following formula:

 
  (locklist * 4096 / 36 ) * (maxlocks / 100)

If you have a large number of locks, you may need to perform more commits within your application so that some of the locks can be released.

Connections Involved in Deadlock

Event Type Deadlock	Logical Data Grouping deadlock_event
Element Name Element Type	dl_conns gauge
Related Information	None

Description: The number of connections that are involved in the deadlock.

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

Lock Escalation

Snapshot Level Lock	Logical Data Grouping lock lock_wait	Monitor Switch Lock Lock
Resettable	No
Event Type Deadlock	Logical Data Grouping dlconn_event
Element Name Element Type	lock_escalation information
Related Information	other lock data elements

Description: Indicates whether a lock request was made as part of a lock escalation.

Usage: Use this element to better understand the cause of deadlocks. If you experience a deadlock that involves applications doing lock escalation, you may want to increase the amount of lock memory or change the percentage of locks that any one application can request.

Lock Mode Requested

Snapshot Level Lock	Logical Data Grouping lock_wait	Monitor Switch Lock
Resettable	No
Event Type Deadlock	Logical Data Grouping dlconn_event
Element Name Element Type	lock_mode_requested information
Related Information	"Resetting Monitor Data" Other lock information

Description: The lock mode being requested by the application.

Usage: The mode in which the lock was requested by the application. This value can help you determine the source of contention for resources.

Lock Wait Information

The following elements provide information is returned when a DB2 agent working on behalf of an application is waiting to obtain a lock:

Lock Waits
Time Waited On Locks
Table Space Name
Current Agents Waiting On Locks
Total Time Unit of Work Waited on Locks
Lock Wait Start Timestamp
Agent ID Holding Lock
Application ID Holding Lock
Sequence Number Holding Lock
Rolled Back Application
Rolled Back Sequence Number

Lock Waits

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Lock Lock
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	lock_waits counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Connection Request Start Timestamp" "Time Waited On Locks"

Description: The total number of times that applications or connections waited for locks.

Usage: At the database level, this is the total number of times that applications have had to wait for locks within this database.

At the application-connection level, this is the total number of times that this connection requested a lock but had to wait because another connection was already holding a lock on the data.

This element may be used with "Time Waited On Locks" to calculate, at the database level, the average wait time for a lock. This calculation can be done at either the database or the application-connection level.

If the average lock wait time is high, you should look for applications that hold many locks, or have lock escalations, with a focus on tuning your applications to improve concurrency, if appropriate. If escalations are the reason for a high average lock wait time, then the values of one or both of the locklist and maxlocks configuration parameters may be too low. See the Administration Guide for more information.

Time Waited On Locks

Snapshot Level Database Application	Logical Data Grouping dbase appl appl_lock	Monitor Switch Lock Lock
Resettable	Yes
Event Type Database Connection Transaction	Logical Data Grouping db_event conn_event xaction_event
Element Name Element Type	lock_wait_time counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Current Agents Waiting On Locks" "Lock Waits"

Description: The total elapsed time waited for a lock.

Usage: At the database level, this is the total amount of elapsed time that all applications were waiting for a lock within this database.

At the application-connection and transaction levels, this is the total amount of elapsed time that this connection or transaction has waited for a lock to be granted to it.

This element may be used in conjunction with the "Lock Waits" monitor element to calculate the average wait time for a lock. This calculation can be performed at either the database or the application-connection level.

When using data 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 data element at the 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, you can calculate the average wait time for a lock, as described above.

Table Space Name

Snapshot Level Table Space Application Lock	Logical Data Grouping tablespace appl_lock lock lock_wait	Monitor Switch Buffer Pool Basic Lock Lock
Resettable	No
Event Type Deadlock Table Space	Logical Data Grouping dlconn_event tablespace_header
Element Name Element Type	tablespace_name information
Related Information	"Resetting Monitor Data" "Lock Object Type Waited On"

Description: The name of a table space.

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.TABLESPACE. 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.

Current Agents Waiting On Locks

Snapshot Level Database Application Lock	Logical Data Grouping dbase appl dbase_lock	Monitor Switch Basic Basic Basic
Resettable	No
Element Name Element Type	locks_waiting gauge
Related Information	"Applications Connected Currently"

Description: Indicates the number of agents waiting on a lock.

Usage: When used in conjunction with "Applications Connected Currently", this element indicates the percentage of applications waiting on locks. If this number is high, the applications may have concurrency problems, and you should identify applications that are holding locks or exclusive locks for long periods of time.

Total Time Unit of Work Waited on Locks

Snapshot Level Application	Logical Data Grouping appl	Monitor Switch Unit of Work
Resettable	No
Element Name Element Type	uow_lock_wait_time counter
Related Information	"Resetting Monitor Data" Application-level information on locks

Description: The total amount of elapsed time this unit of work has spent waiting for locks.

Usage: This element can help you determine the severity of the resource contention problem.

Lock Wait Start Timestamp

Snapshot Level Application Lock	Logical Data Grouping appl lock_wait	Monitor Switch Lock Lock
Resettable	No
Event Type Deadlock	Logical Data Grouping dlconn_event
Element Name Element Type	lock_wait_start_time timestamp
Related Information	"Resetting Monitor Data" "Agent ID Holding Lock"

Description: The date and time that this application started waiting to obtain a lock on the object that is currently locked by another application.

Usage: This element can help you determine the severity of resource contention.

Agent ID Holding Lock

Snapshot Level Application Lock	Logical Data Grouping appl appl_lock lock_wait	Monitor Switch Lock Lock Lock
Resettable	No
Element Name Element Type	agent_id_holding_lock information
Related Information	"Resetting Monitor Data" "Lock Wait Start Timestamp" "Application ID Holding Lock"

Description: The application handle of the agent holding a lock for which this application is waiting. The lock monitor group must be turned on to obtain this information.

Usage: This element can help you determine which applications are in contention for resources.

If this element is 0 (zero) and the application is waiting for a lock, this indicates that the lock is held by an indoubt transaction. You can use either Application ID Holding Lock or the command line processor LIST INDOUBT TRANSACTIONS command (which displays the application ID of the CICS agent that was processing the transaction when it became indoubt) to determine the indoubt transaction, and then either commit it or roll it back.

Note that more than one application can hold a shared lock on an object for which this application is waiting. See Lock Mode for information about the type of lock that the application holds. If you are taking an application snapshot, only one of the agent IDs holding a lock on the object will be returned. If you are taking a lock snapshot, all of the agent IDs holding a lock on the object will be identified.

Application ID Holding Lock

Snapshot Level Application Lock	Logical Data Grouping appl appl_lock lock_wait	Monitor Switch Lock Lock Lock
Resettable	No
Event Type Deadlock	Logical Data Grouping dlconn_event
Element Name Element Type	appl_id_holding_lk information
Related Information	"Resetting Monitor Data" "Agent ID Holding Lock" "Deadlocks Detected"

Description: The application ID of the application that is holding a lock on the object that this application is waiting to obtain.

Usage: This element can help you determine which applications are in contention for resources. Specifically, it can help you identify the application handle (agent ID) and table ID that are holding the lock. Note that you may use the LIST APPLICATIONS command to obtain information to relate the application ID with an agent ID. However, it is a good idea to collect this type of information when you take the snapshot, as it could be unavailable if the application ends before you run the LIST APPLICATIONS command.

Note that more than one application can hold a shared lock on an object for which this application is waiting to obtain a lock. See Lock Mode for information about the type of lock that the application holds. If you are taking an application snapshot, only one of the application IDs holding a lock on the object will be returned. If you are taking a lock snapshot, all of the application IDs holding a lock on the object will be returned.

Sequence Number Holding Lock

Snapshot Level Application	Logical Data Grouping appl appl_lock	Monitor Switch Basic Basic
Resettable	No
Event Type Deadlock	Logical Data Grouping dlconn_event
Element Name Element Type	sequence_no_holding_lk information
Related Information	None

Description: This element is reserved for future use. In this release, its value will always be "0001". In future releases of the product, it may contain different values.

Rolled Back Application

Event Type Deadlock	Logical Data Grouping deadlock_event
Element Name Element Type	rolled_back_appl_id information
Related Information	"Service Level" "Maximum Number of Coordinating Agents"

Description: Application id that was rolled back when a deadlock occurred.

Usage: A system administrator can use this information to determine which application did not complete its updates, and determine which applications should be restarted

Rolled Back Agent

Event Type Deadlock	Logical Data Grouping deadlock_event
Element Name Element Type	rolled_back_agent_id information
Related Information	"Service Level" "Maximum Number of Coordinating Agents"

Description: Agent that was rolled back when a deadlock occurred.

Usage: A system administrator can use this information to determine which application did not complete its updates, and determine which applications should be restarted

Rolled Back Sequence Number

Event Type Deadlock	Logical Data Grouping deadlock_event
Element Name Element Type	rolled_back_sequence_no information
Related Information	None

Description: The sequence number of the application that was rolled back when a deadlock occurred.

Usage: A system administrator can use this information to determine which application did not complete its updates, and determine which applications should be restarted

Rollforward Monitoring

Recovering database changes can be a time consuming process. You can use the database system monitor to monitor the progression of a recovery. The following elements provide information about rollforward status:

Rollforward Timestamp
Tablespace Being Rolled Forward
Rollforward Type
Log Being Rolled Forward
Log Phase
Number of Rollforward Table Spaces

Rollforward Timestamp

Snapshot Level Table Space	Logical Data Grouping rollfwd_info	Monitor Switch Basic
Resettable	No
Element Name Element Type	rf_timestamp timestamp
Related Information	"Tablespace Being Rolled Forward"

Description: The timestamp of the log being processed.

Usage: If a rollforward is in progress, this is the timestamp of the log record being processed. This is an indicator of the data changes that will be recovered.

Tablespace Being Rolled Forward

Snapshot Level Table Space	Logical Data Grouping rollfwd_ts_info	Monitor Switch Basic
Resettable	No
Element Name Element Type	ts_name information
Related Information	"Rollforward Timestamp"

Description: The name of the table space currently rolled forward.

Usage: If a rollforward is in progress, this element identifies the table spaces involved.

Rollforward Type

Snapshot Level Table Space	Logical Data Grouping rollfwd_info	Monitor Switch Basic
Resettable	No
Element Name Element Type	rf_type information
Related Information	None

Description: The type of rollforward in progress.

Usage: An indicator of whether recovery is happening at a database or table space level. For more information on rollforward recovery at the database or table space level see the Administration Guide.

Log Being Rolled Forward

Snapshot Level Table Space	Logical Data Grouping rollfwd_info	Monitor Switch Basic
Resettable	No
Element Name Element Type	rf_log_num information
Related Information	None

Description: The log being processed.

Usage: If a rollforward is in progress, this element identifies the log involved.

Log Phase

Snapshot Level Table Space	Logical Data Grouping rollfwd_info	Monitor Switch Basic
Resettable	No
Element Name Element Type	rf_status information
Related Information	None

Description: The status of the recovery.

Usage: This element indicates the progression of a recovery. It indicates if the recovery is in an undo (rollback) or redo (rollforward) phase.

Number of Rollforward Table Spaces

Snapshot Level Table Space	Logical Data Grouping rollfwd_info	Monitor Switch Basic
Resettable	No
Element Name Element Type	rf_num_tspaces counter
Related Information	None

Description: The number of table spaces involved in a rollforward.

Usage: This is a counter of the table spaces involved in recovery.

Table Activity

The following elements provide information about the tables:

Table Type
Table Name
Table Schema Name
Rows Deleted
Rows Inserted
Rows Updated
Rows Selected
Rows Written
Rows Read
Accesses to Overflowed Records
Internal Rows Deleted
Internal Rows Updated
Internal Rows Inserted
Table File ID
Page Reorganizations

Table Type

Snapshot Level Table	Logical Data Grouping table	Monitor Switch Table
Resettable	No
Event Type Table	Logical Data Grouping table_event
Element Name Element Type	table_type information
Related Information	"Resetting Monitor Data" "Table File ID"

Description: The type of table for which information is returned.

Usage: You can 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 Name" to identify the table.

The type of table may be one of the following:

User table.
User table that has been dropped. The table type will only be updated after the changes are committed (either explicitly or implicitly).
Temporary table. Information regarding temporary tables is returned, even though the tables are not kept in the database after being used. You may still find information about this type of table useful.
System catalog table.
Reorganization table. A table created and used by the database manager while performing a reorganization of another table.

Table Name

Snapshot Level Table Application Lock	Logical Data Grouping table appl sqml_appl_lock lock lock_wait	Monitor Switch Table Lock Lock Lock Lock
Resettable	No
Event Type Table Deadlock	Logical Data Grouping table_event dlconn_event
Element Name Element Type	table_name information
Related Information	"Resetting Monitor Data" "Table Schema Name" "Lock Object Type Waited On"

Description: The name of the table.

Usage: Along with "Table Schema Name", 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 turned on, and when "Lock Object Type Waited On" 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. This element is blank for temporary tables, reorganization tables, and tables that were dropped. Table names are only provided for catalog and user tables. For snapshot monitoring, this element is only valid when the "table" monitor group information is turned on.

Table Schema Name

Snapshot Level Table Application Lock	Logical Data Grouping table appl appl_lock lock lock_wait	Monitor Switch Table Lock Lock Lock Lock
Resettable	No
Event Type Table Deadlock	Logical Data Grouping table_event dlconn_event
Element Name Element Type	table_schema information
Related Information	"Resetting Monitor Data" "Table Name" "Lock Object Type Waited On"

Description: The schema of the table.

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 Waited On" 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 turned 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. This element is blank for temporary tables, reorganization tables, and tables that were dropped. Schema names are provided only for catalog and user tables. For snapshot monitoring, this element is valid only when the "table" monitor group information is turned on.

Rows Deleted

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	rows_deleted counter
Related Information	"When Counters are Initialized" "Internal Rows Deleted"

Description: This is the number of row deletions attempted.

Usage: You can use this element to gain insight into the current level of activity within the database manager.

This count does not include the attempts counted in "Internal Rows Deleted".

Rows Inserted

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	rows_inserted counter
Related Information	"When Counters are Initialized"

Description: This is the number of row insertions attempted.

Usage: You can use this element to gain insight into the current level of activity within the database manager.

Rows Updated

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	rows_updated counter
Related Information	"When Counters are Initialized" "Internal Rows Updated"

Description: This is the number of row updates attempted.

Usage: You can use this element to gain insight into the current level of activity within the database manager.

This value does not include updates counted in "Internal Rows Updated". However, rows that are updated by more than one update statement are counted for each update.

Rows Selected

Snapshot Level Database Application DCS Database DCS Application	Logical Data Grouping dbase appl dcs_dbase dcs_appl	Monitor Switch Basic Basic Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	rows_selected counter
Related Information	"When Counters are Initialized" "Select SQL Statements Executed"

Description: This is the number of rows that have been selected and returned to the application.

Usage: You can use this element to gain insight into the current level of activity within the database manager.

This element does not include a count of rows read for actions such as COUNT(*) or joins.

Rows Written

Snapshot Level Table Application Dynamic SQL	Logical Data Grouping table appl stmt subsection dynsql	Monitor Switch Table Basic Basic Statement Statement
Resettable	Yes
Event Type Connection Table Statement Transaction	Logical Data Grouping conn_event table_event stmt_event xaction_event
Element Name Element Type	rows_written counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Rows Read" "Internal Rows Inserted" "Internal Rows Deleted" "Internal Rows Updated"

Description: This is the number of rows changed (inserted, deleted or updated) in the table.

Usage: A high value for table-level information indicates there is heavy usage of the table and you may want to use the Run Statistics (RUNSTATS) utility to maintain efficiency of the packages used for this table.

For application-connections and statements, this element includes the number of rows inserted, updated, and deleted in temporary tables.

At the application, transaction, and statement levels, this element can be useful for analyzing the relative activity levels, and for identifying candidates for tuning.

Rows Read

Snapshot Level Table Application Dynamic SQL	Logical Data Grouping table appl stmt subsection dynsql	Monitor Switch Table Basic Basic Statement Statement
Resettable	Yes
Event Type Connection Table Statement Transaction	Logical Data Grouping conn_event table_event stmt_event xaction_event
Element Name Element Type	rows_read counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Rows Written" "Accesses to Overflowed Records"

Description: This is the number of rows read from the table.

Usage: This element helps you identify tables with heavy usage for which you may want to create additional indexes. To avoid the maintenance of unnecessary indexes, you may use the SQL EXPLAIN statement, described in the Administration Guide to determine if the package uses an index.

This count is not the number of row that were returned to the calling application. Rather, it is the number of rows that had to be read in order to return the result set. For example, the following statement returns one row to the application, but many rows are read to determine the average salary:

 
     SELECT AVG(SALARY) FROM USERID.EMPLOYEE

This count includes the value in "Accesses to Overflowed Records".

Accesses to Overflowed Records

Snapshot Level Table	Logical Data Grouping table	Monitor Switch Table
Resettable	Yes
Event Type Table	Logical Data Grouping table_event
Element Name Element Type	overflow_accesses counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Rows Read" "Rows Written"

Description: The number of accesses (reads and writes) to overflowed rows of this table.

Usage: Overflowed rows indicate that data fragmentation has occurred. If this number is high, you may be able to improve table performance by reorganizing the table using the REORG utility, which cleans up this fragmentation.

A row overflows if it is updated and no longer fits in the data page where it was originally written. This usually happens as a result of an update of a VARCHAR or an ALTER TABLE statement.

Internal Rows Deleted

Snapshot Level Database Application Dynamic SQL	Logical Data Grouping dbase appl stmt dynsql	Monitor Switch Basic Basic Basic Statement
Resettable	Yes
Event Type Database Connection Statement	Logical Data Grouping db_event conn_event stmt_event
Element Name Element Type	int_rows_deleted counter
Related Information	"When Counters are Initialized" "Rows Deleted"

Description: This is the number of rows deleted from the database as a result of internal activity.

Usage: This element can help you gain insight into internal activity within the database manager of which you might not be aware. If this activity is high, you may want to evaluate your table design to determine if the referential constraints or triggers that you have defined on your database are necessary.

Internal delete activity can be a result of:

A cascading delete enforcing an ON CASCADE DELETE referential constraint
A trigger being fired.

Internal Rows Updated

Snapshot Level Database Application Dynamic SQL	Logical Data Grouping dbase appl stmt dynsql	Monitor Switch Basic Basic Basic Statement
Resettable	Yes
Event Type Database Connection Statement	Logical Data Grouping db_event conn_event stmt_event
Element Name Element Type	int_rows_updated counter
Related Information	"When Counters are Initialized" "Rows Updated"

Description: This is the number of rows updated from the database as a result of internal activity.

Usage: This element can help you gain insight into internal activity within the database manager of which you might not be aware. If this activity is high, you may want to evaluate your table design to determine if the referential constraints that you have defined on your database are necessary.

Internal update activity can be a result of:

A set null row update enforcing a referential constraint defined with the ON DELETE SET NULL rule
A trigger being fired.

Internal Rows Inserted

Snapshot Level Database Application Dynamic SQL	Logical Data Grouping dbase appl stmt dynsql	Monitor Switch Basic Basic Basic Statement
Resettable	Yes
Event Type Database Connection Statement	Logical Data Grouping db_event conn_event stmt_event
Element Name Element Type	int_rows_inserted counter
Related Information	"When Counters are Initialized" "Rows Inserted"

Description: The number of rows inserted into the database as a result of internal activity caused by triggers.

Usage: This element can help you gain insight into the internal activity within the database manager. If this activity is high, you may want to evaluate your design to determine if you can alter it to reduce this activity.

Table File ID

Snapshot Level Application Table Lock	Logical Data Grouping appl table appl_lock lock	Monitor Switch Lock Table Lock Lock
Resettable	No
Element Name Element Type	table_file_id information
Related Information	"Resetting Monitor Data" "Table Name" "Table Schema Name" "Table Type"

Description: This is the file ID (FID) for the table.

Usage: 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 Name" to identify the table.

Page Reorganizations

Snapshot Level Table	Logical Data Grouping table	Monitor Switch Table
Resettable	Yes
Event Type Table	Logical Data Grouping table_event
Element Name Element Type	page_reorgs counter
Related Information	Rows Inserted Rows Updated

Description: The number of page reorganizations executed for a table.

Usage: Too many page reorganizations can result in less than optimal insert performance. You can use the REORG TABLE utility to reorganize a table and eliminate fragmentation. You can also use the APPEND parameter for the ALTER TABLE statement to indicate that all inserts are appended at the end of a table and so avoid page reorgs.

In situations where updates to rows causes the row length to increase, the page may have enough space to accommodate the new row, but a page reorg may be required to defragment that space. Or if the page does not have enough space for the new larger row, an overflow record is created being created causing Accesses to Overflowed Records during reads. You can avoid both situations by using fixed length columns instead of varying length columns.

SQL Cursors

The following elements provide information about the SQL cursors:

Open Remote Cursors
Open Remote Cursors with Blocking
Rejected Block Cursor Requests
Accepted Block Cursor Requests
Open Local Cursors
Open Local Cursors with Blocking

Open Remote Cursors

Snapshot Level Application	Logical Data Grouping appl	Monitor Switch Basic
Resettable	No
Element Name Element Type	open_rem_curs gauge
Related Information	"Open Remote Cursors with Blocking" "Open Local Cursors"

Description: The number of remote cursors currently open for this application, including those cursors counted by "Open Remote Cursors with Blocking".

Usage: You may use this element in conjunction with "Open Remote Cursors with Blocking" to calculate the percentage of remote cursors that are blocking cursors. If the percentage is low, you may be able to improve performance by improving the row blocking in the application. See "Open Remote Cursors with Blocking" for more information.

For the number of open cursors used by applications connected to a local database, see "Open Local Cursors".

Open Remote Cursors with Blocking

Snapshot Level Application	Logical Data Grouping appl	Monitor Switch Basic
Resettable	No
Element Name Element Type	open_rem_curs_blk gauge
Related Information	"Open Remote Cursors" "Rejected Block Cursor Requests" "Accepted Block Cursor Requests" "Open Local Cursors" "Open Local Cursors with Blocking"

Description: The number of remote blocking cursors currently open for this application.

Usage: You can use this element in conjunction with "Open Remote Cursors" to calculate the percentage of remote cursors that are blocking cursors. If the percentage is low, you may be able to improve performance by improving the row blocking in the application:

Check the pre-compile options for record blocking for treatment of ambiguous cursors
Redefine cursors to allow for blocking (for example, if possible, specify FOR FETCH ONLY on your cursors).

"Rejected Block Cursor Requests" and "Accepted Block Cursor Requests" provide additional information that may help you tune your configuration parameters to improve row blocking in your application.

For the number of open blocking cursors used by applications connected to a local database see "Open Local Cursors with Blocking".

Rejected Block Cursor Requests

Snapshot Level Application	Logical Data Grouping appl	Monitor Switch Basic
Resettable	No
Event Type Connection	Logical Data Grouping conn_event
Element Name Element Type	rej_curs_blk counter
Related Information	"Accepted Block Cursor Requests" "Open Local Cursors" "Open Local Cursors with Blocking" "Open Remote Cursors" "Open Remote Cursors with Blocking"

Description: The number of times that a request for an I/O block at server was rejected and the request was converted to non-blocked I/O.

Usage: If there are many cursors blocking data, the communication heap may become full. When this heap is full, an error is not returned. Instead, no more I/O blocks are allocated for blocking cursors. If cursors are unable to block data, performance can be affected.

If a large number of cursors were unable to perform data blocking, you may be able to improve performance by:

Increasing the size of the query_heap database manager configuration parameter. For more information see the Administration Guide.

Accepted Block Cursor Requests

Snapshot Level Application	Logical Data Grouping appl	Monitor Switch Basic
Resettable	No
Event Type Connection	Logical Data Grouping conn_event
Element Name Element Type	acc_curs_blk counter
Related Information	"Rejected Block Cursor Requests" "Open Local Cursors" "Open Local Cursors with Blocking" "Open Remote Cursors" "Open Remote Cursors with Blocking"

Description: The number of times that a request for an I/O block was accepted.

Usage: You can use this element in conjunction with "Rejected Block Cursor Requests" to calculate the percentage of blocking requests that are accepted and/or rejected.

See "Rejected Block Cursor Requests" for suggestions on how to use this information to tune your configuration parameters.

Open Local Cursors

Snapshot Level Application	Logical Data Grouping appl	Monitor Switch Basic
Resettable	No
Element Name Element Type	open_loc_curs gauge
Related Information	"Open Local Cursors with Blocking" "Open Remote Cursors" "Open Remote Cursors with Blocking" "Rejected Block Cursor Requests" "Accepted Block Cursor Requests"

Description: The number of local cursors currently open for this application, including those cursors counted by "Open Local Cursors with Blocking".

Usage: You may use this element in conjunction with "Open Local Cursors with Blocking" to calculate the percentage of local cursors that are blocking cursors. If the percentage is low, you may be able to improve performance by improving the row blocking in the application.

For cursors used by remote applications, see "Open Remote Cursors".

Open Local Cursors with Blocking

Snapshot Level Application	Logical Data Grouping appl	Monitor Switch Basic
Resettable	No
Element Name Element Type	open_loc_curs_blk gauge
Related Information	"Open Local Cursors" "Open Remote Cursors" "Open Remote Cursors with Blocking" "Rejected Block Cursor Requests" "Accepted Block Cursor Requests"

Description: The number of local blocking cursors currently open for this application.

Usage: You may use this element in conjunction with "Open Local Cursors" to calculate the percentage of local cursors that are blocking cursors. If the percentage is low, you may be able to improve performance by improving the row blocking in the application:

Check the pre-compile options for record blocking for treatment of ambiguous cursors
Redefine cursors to allow for blocking (for example, if possible, specify FOR FETCH ONLY on your cursors).

"Rejected Block Cursor Requests" and "Accepted Block Cursor Requests" provide additional information that may help you tune your configuration parameters to improve row blocking in your application.

For blocking cursors used by remote applications, see "Open Remote Cursors with Blocking".

SQL Statement Activity

The following elements provide information about SQL statement activity:

Static SQL Statements Attempted
Dynamic SQL Statements Attempted
Failed Statement Operations
Commit Statements Attempted
Rollback Statements Attempted
Select SQL Statements Executed
Update/Insert/Delete SQL Statements Executed
Data Definition Language (DDL) SQL Statements
Internal Automatic Rebinds
Internal Commits
Internal Rollbacks
Internal Rollbacks Due To Deadlock
SQL Requests Since Last Commit
Statement Node
Binds/Precompiles Attempted

Static SQL Statements Attempted

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	static_sql_stmts counter
Related Information	"When Counters are Initialized" "Failed Statement Operations"

Description: The number of static SQL statements that were attempted.

Usage: You can use this element to calculate the total number of successful SQL statements at the database or application level:

 
      Dynamic SQL Statements Attempted
    + Static SQL Statements Attempted
    - Failed Statement Operations
    = throughput during monitoring period

Dynamic SQL Statements Attempted

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	dynamic_sql_stmts counter
Related Information	"When Counters are Initialized" "Failed Statement Operations"

Description: The number of dynamic SQL statements that were attempted.

Usage: You can use this element to calculate the total number of successful SQL statements at the database or application level:

 
      Dynamic SQL Statements Attempted
    + Static SQL Statements Attempted
    - Failed Statement Operations
    = throughput during monitoring period

Failed Statement Operations

Snapshot Level Database Application DCS Database DCS Application	Logical Data Grouping dbase appl dcs_dbase dcs_appl	Monitor Switch Basic Basic Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	failed_sql_stmts counter
Related Information	"When Counters are Initialized" "Dynamic SQL Statements Attempted" "Static SQL Statements Attempted"

Description: The number of SQL statements that were attempted, but failed.

Usage: You can use this element to calculate the total number of successful SQL statements at the database or application level:

 
      Dynamic SQL Statements Attempted
    + Static SQL Statements Attempted
    - Failed Statement Operations
    = throughput during monitoring period

This count includes all SQL statements that received a negative SQLCODE.

This element may also help you in determining reasons for poor performance, since failed statements mean time wasted by the database manager and as a result, lower throughput for the database.

Commit Statements Attempted

Snapshot Level Database Application DCS Database DCS Application	Logical Data Grouping dbase appl dcs_dbase dcs_appl	Monitor Switch Basic Basic Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	commit_sql_stmts counter
Related Information	"When Counters are Initialized" "Internal Commits" "Rollback Statements Attempted" "Internal Rollbacks" "Internal Rollbacks Due To Deadlock"

Description: The total number of SQL COMMIT statements that have been attempted.

Usage: A small rate of change in this counter during the monitor period may indicate that applications are not doing frequent commits, which may lead to problems with logging and data concurrency.

You can also use this element to calculate the total number of units of work by calculating the sum of the following:

 
   commit statements attempted
 + internal commits
 + rollback statements attempted
 + internal rollbacks

Note:

The units of work calculated will only include those since the later of:

The connection to the database (for database-level information, this is the time of the first connection)
The last reset of the database monitor counters.

This calculation can be done at a database or application level.

Rollback Statements Attempted

Snapshot Level Database Application DCS Database DCS Application	Logical Data Grouping dbase appl dcs_dbase dcs_appl	Monitor Switch Basic Basic Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	rollback_sql_stmts counter
Related Information	"When Counters are Initialized" "Statement Type" "Commit Statements Attempted" "Internal Commits" "Internal Rollbacks" "Internal Rollbacks Due To Deadlock"

Description: The total number of SQL ROLLBACK statements that have been attempted.

Usage: A rollback can result from an application request, a deadlock, or an error situation. This element only counts the number of rollback statements issued from applications.

At the application level, this element can help you determine the level of database activity for the application and the amount of conflict with other applications. At the database level, it can help you determine the amount of activity in the database and the amount of conflict between applications on the database.
Note: You should try to minimize the number of rollbacks, since higher rollback activity results in lower throughput for the database.

It may also be used to calculate the total number of units of work, by calculating the sum of the following:

 
   commit statements attempted
 + internal commits
 + rollback statements attempted
 + internal rollbacks

Select SQL Statements Executed

Snapshot Level Database Table Space Application	Logical Data Grouping dbase tablespace appl	Monitor Switch Basic Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	select_sql_stmts counter
Related Information	"When Counters are Initialized" "Static SQL Statements Attempted" "Dynamic SQL Statements Attempted"

Description: The number of SQL SELECT statements that were executed.

Usage: You can use this element to determine the level of database activity at the application or database level.

You can also use the following formula to determine the ratio of SELECT statements to the total statements:

 
      select SQL statements executed
    / ( static SQL statements attempted
      + dynamic SQL statements attempted )

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

Update/Insert/Delete SQL Statements Executed

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	uid_sql_stmts counter
Related Information	"When Counters are Initialized" "Static SQL Statements Attempted" "Dynamic SQL Statements Attempted"

Description: The number of SQL UPDATE, INSERT, and DELETE statements that were executed.

Usage: You can use this element to determine the level of database activity at the application or database level.

You can also use the following formula to determine the ratio of UPDATE, INSERT and DELETE statements to the total number of statements:

 
   update/insert/delete SQL statements executed
 / (static SQL statements attempted + dynamic SQL statements attempted )

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

Data Definition Language (DDL) SQL Statements

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	ddl_sql_stmts counter
Related Information	"When Counters are Initialized"

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

Usage: You can use this element to determine the level of database activity at the application or database level. DDL statements are expensive to run due to their impact on the system catalog tables. As a result, if the value of this element is high, you should determine the cause, and possibly restrict this activity from being performed.

You can also use this element to determine the percentage of DDL activity using the following formula:

 
 data definition language (DDL) SQL statements / total number of statements

This information can be useful for analyzing application activity and throughput. DDL statements can also impact the package cache, by invalidating sections that are stored there and causing additional system overhead due to section recompilation.

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

Internal Automatic Rebinds

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	int_auto_rebinds counter
Related Information	"When Counters are Initialized" "Binds/Precompiles Attempted"

Description: The number of automatic rebinds (or recompiles) that have been attempted.

Usage: Automatic rebinds are the internal binds the system performs when an package has been invalidated. The rebind is performed the first time that the database manager needs to execute an SQL statement from the package. For example, packages are invalidated when you:

Drop an object, such as a table, view, or index, on which the plan is dependent
Add or drop a foreign key
Revoke object privileges on which the plan is dependent.

You can use this element to determine the level of database activity at the application or database level. Since internal automatic rebinds can have a significant impact on performance, they should be minimized where possible.

You can also use this element to determine the percentage of rebind activity using the following formula:

 
 internal automatic rebinds / total number of statements

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

Internal Commits

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	int_commits counter
Related Information	"When Counters are Initialized" "Commit Statements Attempted" "Rollback Statements Attempted" "Internal Rollbacks"

Description: The total number of commits initiated internally by the database manager.

Usage: An internal commit may occur during any of the following:

A reorganization
An import
A bind or pre-compile
An application ends without executing an explicit SQL COMMIT statement (on UNIX).

This value, which does not include explicit SQL COMMIT statements, represents the number of these internal commits since the later of:

The connection to the database (for database-level information, this is the time of the first connection)
The last reset of the database monitor counters.

You can use this element to calculate the total number of units of work by calculating the sum of the following:

 
   commit statements attempted
 + internal commits
 + rollback statements attempted
 + internal rollbacks

Note:

The units of work calculated will only include those since the later of:

The connection to the database (for database-level information, this is the time of the first connection)
The last reset of the database monitor counters.

This calculation can be done at the application or the database level.

Internal Rollbacks

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	int_rollbacks counter
Related Information	"When Counters are Initialized" "Commit Statements Attempted" "Internal Commits" "Rollback Statements Attempted" "Internal Rollbacks Due To Deadlock"

Description: The total number of rollbacks initiated internally by the database manager.

Usage: An internal rollback occurs when any of the following cannot complete successfully:

A reorganization
An import
A bind or pre-compile
An application ends as a result of a deadlock situation or lock timeout situation
An application ends without executing an explicit commit or rollback statement (on Windows).

This value represents the number of these internal rollbacks since the later of:

The connection to the database (for database-level information, this is the time of the first connection)
The last reset of the database monitor counters.

While this value does not include explicit SQL ROLLBACK statements, the count from "Internal Rollbacks Due To Deadlock" is included.

You can use this element to calculate the total number of units of work by calculating the sum of the following:

 
   commit statements attempted
 + internal commits
 + rollback statements attempted
 + internal rollbacks

Note:

The units of work calculated will include those since the later of:

The connection to the database (for database-level information, this is the time of the first connection)
The last reset of the database monitor counters.

This calculation can be done at the application or the database level.

Internal Rollbacks Due To Deadlock

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Connection	Logical Data Grouping conn_event
Element Name Element Type	int_deadlock_rollbacks counter
Related Information	"When Counters are Initialized" "Deadlocks Detected" "Rollback Statements Attempted" "Internal Rollbacks"

Description: The total number of forced rollbacks initiated by the database manager due to a deadlock. A rollback is performed on the current unit of work in an application selected by the database manager to resolve the deadlock.

Usage: This element shows the number of deadlocks that have been broken and can be used as an indicator of concurrency problems. It is important, since internal rollbacks due to deadlocks lower the throughput of the database.

This value is included in the value given by "Internal Rollbacks".

SQL Requests Since Last Commit

Snapshot Level Application	Logical Data Grouping appl	Monitor Switch Basic
Resettable	No
Element Name Element Type	sql_reqs_since_commit information
Related Information	None

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

Usage: You can use this element to monitor the progress of a transaction.
Note: This element is similar to the cur_reqs field in the sqlestat output. See Appendix D. DB2 Version 1 sqlestat Users for more information on sqlestat equivalent data elements.

Statement Node

Snapshot Level Application	Logical Data Grouping stmt	Monitor Switch Statement
Resettable	No
Element Name Element Type	stmt_node_number information
Related Information	None

Description: Node where the statement was executed.

Usage: Used to correlate each statement with the node where it was executed.

Binds/Precompiles Attempted

Snapshot Level Database Application	Logical Data Grouping dbase appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Logical Data Grouping db_event conn_event
Element Name Element Type	binds_precompiles counter
Related Information	"When Counters are Initialized" "Internal Automatic Rebinds"

Description: The number of binds and pre-compiles attempted.

Usage: You can use this element to gain insight into the current level of activity within the database manager.

This value does not include the count of "Internal Automatic Rebinds", but it does include binds that occur as a result of the REBIND PACKAGE command.

SQL Statement Details

The following elements provide details about the SQL statements:

Statement Type
Statement Operation
Package Name
Section Number
Cursor Name
Application Creator
Statement Operation Start Timestamp
Statement Operation Stop Timestamp
Event Stop Time
Event Start Time
Most Recent Statement Elapsed Time
SQL Dynamic Statement Text
Statement Sorts
Number of Successful Fetches
SQL Communications Area (SQLCA)
Query Number of Rows Estimate
Query Cost Estimate

Statement Type

Snapshot Level Application	Logical Data Grouping appl stmt	Monitor Switch Statement Statement
Resettable	No
Event Type Statement	Logical Data Grouping stmt_event
Element Name Element Type	stmt_type information
Related Information	"Resetting Monitor Data" "SQL Dynamic Statement Text" "Application Creator" "Section Number" "Package Name"

Description: The type of statement processed.

Usage: You can use this element to determine the type of statement that is executing. It can be one of the following:

A static SQL statement
A dynamic SQL statement
An operation other than an SQL statement; for example, a bind or pre-compile operation.

For the snapshot monitor, this element describes the statement that is currently being processed or was most recently processed.
Note: API users should refer to the sqlmon.h header file containing definitions of database system monitor constants.

Statement Operation

Snapshot Level Application DCS Application DCS Statement	Logical Data Grouping appl stmt dcs_appl dcs_stmt	Monitor Switch Basic Statement Basic Statement
Resettable	No
Event Type Statement	Logical Data Grouping stmt_event
Element Name Element Type	stmt_operation (Snapshot) operation (Event) information
Related Information	"Resetting Monitor Data" "Statement Type" "SQL Dynamic Statement Text" "Application Creator" "Section Number" "Package Name" "Number of Successful Fetches"

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

Usage: You can use this element to determine the operation that is executing or recently finished.

It can be one of the following.

For SQL operations:

SELECT
PREPARE
EXECUTE
EXECUTE IMMEDIATE
OPEN
FETCH
CLOSE
DESCRIBE
STATIC COMMIT
STATIC ROLLBACK
FREE LOCATOR
PREP_COMMIT
CALL
PREP_OPEN
PREP_EXEC
COMPILE

For non-SQL operations:

RUN STATISTICS
REORG
REBIND
REDISTRIBUTE
GET TABLE AUTHORIZATION
GET ADMINISTRATIVE AUTHORIZATION

Note:

API users should refer to the sqlmon.h header file containing definitions of database system monitor constants.

Package Name

Snapshot Level Application DCS Application DCS Statement	Logical Data Grouping appl stmt dcs_appl dcs_stnt	Monitor Switch Statement Statement Statement Statement
Resettable	No
Event Type Statement	Logical Data Grouping stmt_event
Element Name Element Type	package_name information
Related Information	"Resetting Monitor Data" "Application Creator" "Section Number" "SQL Dynamic Statement Text"

Description: The name of the package that contains the SQL statement currently executing.

Usage: You may use this element to help identify the application program and the SQL statement that is executing.

Section Number

Snapshot Level Application DCS Application DCS Statement	Logical Data Grouping appl stmt dcs_appl dcs_stmt	Monitor Switch Statement Statement Statement Statement
Resettable	No
Event Type Statement	Logical Data Grouping stmt_event
Element Name Element Type	section_number information
Related Information	"Resetting Monitor Data" "SQL Dynamic Statement Text" "Application Creator" "Package Name"

Description: The internal section number in the package for the SQL statement currently processing or most recently processed.

Usage: For static SQL, you can use this element along with "Application Creator" and "Package Name" to query the SYSCAT.STATEMENTS system catalog table and obtain the static SQL statement text, using the sample query as follows:

 
    SELECT SEQNO, SUBSTR(TEXT,1,120)
           FROM SYSCAT.STATEMENTS
           WHERE PKGNAME   = 'package_name' AND
                 PKGSCHEMA = 'creator'      AND
                 SECTNO    = section_number
           ORDER BY SEQNO

Note:

Exercise caution in obtaining static statement text, because this query against the system catalog table could cause lock contentions. Whenever possible, only use this query when there is little other activity against the database.

Cursor Name

Snapshot Level Application	Logical Data Grouping appl stmt	Monitor Switch Statement Statement
Resettable	No
Event Type Statement	Logical Data Grouping stmt_event
Element Name Element Type	cursor_name information
Related Information	"Resetting Monitor Data" "SQL Dynamic Statement Text" "Statement Type" "Number of Successful Fetches"

Description: The name of the cursor corresponding to this SQL statement.

Usage: You may use this element to identify the SQL statement that is processing. This name will be used on an OPEN, FETCH, CLOSE, and PREPARE of an SQL SELECT statement. If a cursor is not used, this field will be blank.

Application Creator

Snapshot Level Application DCS Application DCS Statement	Logical Data Grouping appl stmt dcs_appl dcs_stmt	Monitor Switch Statement Statement Statement Statement
Resettable	No
Event Type Statement	Logical Data Grouping stmt_event
Element Name Element Type	creator information
Related Information	"Resetting Monitor Data" "Package Name" "Section Number"

Description: The authorization ID of the user that pre-compiled the application.

Usage: You may use this element to help identify the SQL statement that is processing, in conjunction with the CREATOR column of the package section information in the catalogs.

Statement Operation Start Timestamp

Snapshot Level Application DCS Application DCS Statement	Logical Data Grouping appl stmt dcs_appl dcs_stmt	Monitor Switch Statement Statement Statement Statement
Resettable	No
Element Name Element Type	stmt_start timestamp
Related Information	"Resetting Monitor Data" "Statement Operation Stop Timestamp" "Statement Operation"

Description: The date and time when the "Statement Operation" started executing.

Usage: You can use this element with "Statement Operation Stop Timestamp" to calculate the elapsed statement operation execution time.

Statement Operation Stop Timestamp

Snapshot Level Application DCS Application	Logical Data Grouping stmt dcs_stmt	Monitor Switch Statement Statement
Resettable	No
Element Name Element Type	stmt_stop Timestamp
Related Information	"Resetting Monitor Data" "Statement Operation Start Timestamp" "Statement Operation" "Event Stop Time"

Description: The date and time when the "Statement Operation" stopped executing.

Usage: You can use this element with "Statement Operation Start Timestamp" to calculate the elapsed statement operation execution time.

Event Stop Time

Event Type Statement	Logical Data Grouping stmt_event
Element Name Element Type	stop_time timestamp
Related Information	"Previous Transaction Stop Time" "Statement Operation Stop Timestamp"

Description: The date and time when the statement stopped executing.

Usage: You can use this element with "Event Start Time" to calculate the elapsed statement execution time.

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

Event Start Time

Event Type Database Transaction Statement Deadlock	Logical Data Grouping evmon_start_event xaction_event stmt_event deadlock_event dlconn_event
Element Name Element Type	start_time timestamp
Related Information	"Previous Transaction Stop Time" "Statement Operation"

Description: The date and time of unit of work start, statement start, or deadlock detection.

This element, in the evmon_start_event API structure indicates the start of the event monitor.

Usage: You can use this element to correlate the deadlock connection records to the deadlock event record, and in conjunction with "Event Stop Time" to calculate the elapsed statement or transaction execution time.

Most Recent Statement Elapsed Time

Snapshot Level Statement DCS Statement	Logical Data Grouping stmt dcs_stmt	Monitor Switch Statement Statement
Resettable	No
Element Name Element Type	stmt_elapsed_time time
Related Information	"Communication Errors" "Communication Error Time"

Description: The elapsed execution time of the most recently completed statement.

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

SQL Dynamic Statement Text

Snapshot Level Application Dynamic SQL DCS Statement	Logical Data Grouping stmt dynsql dcs_stmt	Monitor Switch Statement Basic Statement
Resettable	No
Event Type Statement	Logical Data Grouping stmt_event
Element Name Element Type	stmt_text information
Related Information	"Resetting Monitor Data" "Statement Operation" "Cursor Name" "Input Database Alias" "Application Creator" "Package Name" "Section Number"

Description: This is the text of the dynamic SQL statement.

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

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

For event monitors, it is returned in the Statement event record for all dynamic statements.

See "Section Number" for information on how to query the system catalog tables to obtain static SQL statement text that is not provided due to performance considerations.

Statement Sorts

Snapshot Level Application Dynamic SQL	Logical Data Grouping appl stmt dynsql	Monitor Switch Statement Statement Statement
Resettable	No
Element Name Element Type	stmt_sorts counter
Related Information	"Resetting Monitor Data" "Total Sorts"

Description: The total number of times that a set of data was sorted in order to process the statement operation.

Usage: You can use this element to help identify the need for an index, since indexes can reduce the need for sorting of data. Using the related elements in the above table you can identify the SQL statement for which this element is providing sort information, and then analyze this statement to determine index candidates by looking at columns that are being sorted (for example, columns used in ORDER BY and GROUP BY clauses and join columns). See explain in the Administration Guide for information on checking whether your indexes are used to optimize sort performance.

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

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

Number of Successful Fetches

Snapshot Level Application DCS Statement	Logical Data Grouping stmt dcs_stmt	Monitor Switch Statement Statement
Resettable	No
Event Type Statement	Logical Data Grouping stmt_event
Element Name Element Type	fetch_count counter
Related Information	"Statement Type" "Statement Operation" "Cursor Name" "Statement Operation Start Timestamp" "Statement Operation Stop Timestamp"

Description: The number of successful fetches performed on a specific cursor.

Usage: You can use this element to gain insight into the current level of activity within the database manager.

For performance reasons, a statement event monitor does not generated a statement event record for every FETCH statement. A record event is only generated when a FETCH returns a non-zero SQLCODE.

SQL Communications Area (SQLCA)

Event Type Statement	Logical Data Grouping stmt_event
Element Name Element Type	sqlca information
Related Information	"Statement Operation"

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

Usage: The SQLCA data structure can be used to determined if the statement completed successfully. See the SQL Reference or Administrative API Reference for information about the content of the SQLCA.

Query Number of Rows Estimate

Snapshot Level Application DCS Statement	Logical Data Grouping stmt dcs_stmt	Monitor Switch Statement Statement
Resettable	No
Element Name Element Type	query_card_estimate information
Related Information	"Resetting Monitor Data" "Query Cost Estimate"

Description: An estimate of the number of rows that will be returned by a query.

Usage: This estimate by the SQL compiler can be compared with the run time actuals.

This data element also returns information for the following SQL statements when you are monitoring DB2 Connect.

INSERT, UPDATE, and DELETE
Indicates the number of rows affected.
PREPARE
Estimate of the number of rows that will be returned. Only collected if the DRDA server is DB2 Universal Database, DB2 for VM and VSE, or DB2 for OS/400.
FETCH
Set to the number of rows fetched. Only collected if the DRDA server is DB2 for OS/400.

If information is not collected for a DRDA server, then the data element is set to zero.

Query Cost Estimate

Snapshot Level Application DCS Statement	Logical Data Grouping stmt dcs_stmt	Monitor Switch Statement Statement
Resettable	No
Element Name Element Type	query_cost_estimate information
Related Information	None

Description: Estimated cost, in timerons, for a query, as determined by the SQL compiler.

Usage: This allows correlation of actual run-time with the compile-time estimates.

This data element also returns information for the following SQL statements when you are monitoring DB2 Connect.

PREPARE
Represents the relative cost of the prepared SQL statement.
FETCH
Contains the length of the row retrieved. Only collected if the DRDA server is DB2 for OS/400.

If information is not collected for a DRDA server, then the data element is set to zero.
Note: If the DRDA server is DB2 for OS/390, this estimate could be higher than 2**32 - 1 (the maximum integer number that can be expressed through an unsigned long variable). In that case, the value returned by the System Monitor for this data element will be 2**32 - 1.

Subsection Details

When a statement is executed against a partitioned database, it is divided into subsections that may be executed on different nodes. An application may have several subsections simultaneously executing on a node. See Monitoring Subsections and the Administration Guide for more information on subsections.

For problem determination, you may have to locate the problem subsection. For example, a subsection may be waiting on a tablequeue, because one of the writers to this tablequeue is in lock wait on another node. To get the overall picture for an application, you may have to issue an application snapshot on each node where the application is running.

The following database system monitor elements provide information about Subsections:

Subsection Number
Subsection Node Number
Subsection Status
Execution Elapsed Time
Number of Agents Working on a Subsection
Waiting for Any Node to Send on a Tablequeue
Waited for Node on a Tablequeue
Total Number of Tablequeue Buffers Overflowed
Current Number of Tablequeue Buffers Overflowed
Number of Rows Read from Tablequeues
Number of Rows Written to Tablequeues

Subsection Number

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Logical Data Grouping subsection_event
Element Name Element Type	ss_number information
Related Information	None

Description: Identifies the subsection associated with the returned information.

Usage: This number relates to the subsection number in the access plan that can be obtained with db2expln (see Administration Guide).

Subsection Node Number

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Logical Data Grouping subsection_event
Element Name Element Type	ss_node_number information
Related Information	None

Description: Node where the subsection was executed.

Usage: Use to correlate each subsection with the database partition where it was executed.

Subsection Status

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Element Name Element Type	ss_status information
Related Information	"Waited for Node on a Tablequeue" "Waiting for Any Node to Send on a Tablequeue"

Description: The current status of an executing subsection.

Usage: The current status values can be:

executing
waiting for a lock
waiting to receive data on a tablequeue
waiting to send data on a tablequeue

Execution Elapsed Time

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Logical Data Grouping subsection_event
Element Name Element Type	ss_exec_time counter
Related Information	None

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

Usage: Allows you to track the progress of a subsection.

Number of Agents Working on a Subsection

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Logical Data Grouping subsection_event
Element Name Element Type	num_subagents gauge
Related Information	None

Description: Total number of subagents currently working on a subsection.

Usage: Indicates the current degree of parallelism. Helps you track how execution is progressing.

Waiting for Any Node to Send on a Tablequeue

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Element Name Element Type	tq_wait_for_any information
Related Information	"Subsection Status" "Waited for Node on a Tablequeue"

Description: This flag is used to indicate that the subsection is blocked because it is waiting to receive rows from any node.

Usage: If "Subsection Status" indicates waiting to receive data on a tablequeue 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.

Waited for Node on a Tablequeue

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Element Name Element Type	tq_node_waited_for information
Related Information	"Subsection Status" "Waiting for Any Node to Send on a Tablequeue"

Description: If the subsection status "Subsection Status" is waiting to receive or waiting to send and "Waiting for Any Node to Send on a Tablequeue" is FALSE, then this is the number of the node that this agent is waiting for.

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.

Total Number of Tablequeue Buffers Overflowed

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Logical Data Grouping subsection_event
Element Name Element Type	tq_tot_send_spills counter
Related Information	"Subsection Status" "Current Number of Tablequeue Buffers Overflowed"

Description: Total number of tablequeue buffers overflowed to a temporary table.

Usage: Indicates the total number of tablequeue buffers that have been written to a temporary table. See Current Number of Tablequeue Buffers Overflowed for more information.

Current Number of Tablequeue Buffers Overflowed

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Element Name Element Type	tq_cur_send_spills gauge
Related Information	"Subsection Status" "Total Number of Tablequeue Buffers Overflowed"

Description: Current number of tablequeue buffers residing in a temporary table.

Usage: An agent writing to a tablequeue 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 your ran out of temporary space in the TEMP table space, then tablequeue 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.

Number of Rows Read from Tablequeues

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Logical Data Grouping subsection_event
Element Name Element Type	tq_rows_read counter
Related Information	None

Description: Total number of rows read from tablequeues.

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.

Number of Rows Written to Tablequeues

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Logical Data Grouping subsection_event
Element Name Element Type	tq_rows_written counter
Related Information	None

Description: Total number of rows written to tablequeues.

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.

Maximum Number of Tablequeue Buffers Overflows

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Logical Data Grouping subsection_event
Element Name Element Type	tq_max_send_spills water mark
Related Information	"Total Number of Tablequeue Buffers Overflowed" "Current Number of Tablequeue Buffers Overflowed"

Description: Maximum number of tablequeue buffers overflowed to a temporary table.

Usage: Indicates the maximum number of tablequeue buffers that have been written to a temporary table.

Waited on Node on a Tablequeue

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Statement
Resettable	No
Element Name Element Type	tq_id_waiting_on information
Related Information	"Subsection Status" "Waited for Node on a Tablequeue"

Description: The agent that is waiting.

Usage: This can be used for troubleshooting.

Dynamic SQL

The DB2 statement cache stores packages and statistics for frequently used SQL statements. By examining the contents of this cache, you can identify the dynamic SQL statements that are most frequently executed, and the queries that consume the most resource. Using this information, you can examine the most commonly executed and most expensive SQL operations, to determine if SQL tuning could result in better database performance.

Statement Executions
Statement Compilations
Statement Worst Preparation Time
Statement Best Preparation Time
Elapsed Statement Execution Time

Statement Executions

Snapshot Level Dynamic SQL	Logical Data Grouping dynsql	Monitor Switch Basic
Resettable	Yes
Element Name Element Type	num_executions counter
Related Information	"Statement Compilations"

Description: The number of times that an SQL statement has been executed.

Usage: You can use this element to identify the most frequently executed SQL statements in your system.

Statement Compilations

Snapshot Level Dynamic SQL	Logical Data Grouping dynsql	Monitor Switch Basic
Resettable	Yes
Element Name Element Type	num_compilations counter
Related Information	"Statement Executions"

Description: The number of different compilations for a specific SQL statement.

Usage: Some SQL statements issued on different schemas, such as "select t1 from foo" will appear to be the same statement in the DB2 cache even though they refer to different access plans. Use this value in conjunction with "Statement Executions" to determine whether a bad compilation environment may be skewing the results of dynamic SQL snapshot statistics.

Statement Worst Preparation Time

Snapshot Level Dynamic SQL	Logical Data Grouping dynsql	Monitor Switch Basic
Resettable	No
Element Name Element Type	prep_time_worst water mark
Related Information	"Statement Best Preparation Time"

Description: The longest amount of time in microseconds that was required to prepare a specific SQL statement.

Usage: Use this value in conjunction with "Statement Best Preparation Time" to identify SQL statements that are expensive to compile.

Statement Best Preparation Time

Snapshot Level Dynamic SQL	Logical Data Grouping dynsql	Monitor Switch Basic
Resettable	No
Element Name Element Type	prep_time_best water mark
Related Information	"Statement Worst Preparation Time"

Description: The shortest amount of time that was required to prepare a specific SQL statement.

Usage: Use this value in conjunction with "Statement Worst Preparation Time" to identify SQL statements that are expensive to compile.

Elapsed Statement Execution Time

Snapshot Level Dynamic SQL	Logical Data Grouping dynsql	Monitor Switch Statement
Resettable	Yes
Element Name Element Type	total_exec_time time
Related Information	"Statement Executions" "Statement Compilations" "Total System CPU for a Statement" "Total User CPU for a Statement"

Description: The total time in seconds and microseconds that was spent executing a particular statement in the SQL cache.

Usage: Use this element with Statement Executions determine the average elapsed time for the statement and identify the SQL statements that would most benefit from a tuning of their SQL. The Statement Compilations must be considered when evaluating the contents of this data element.

Intra-query Parallelism

The following database system monitor elements provide information about queries for which the degree of parallelism is greater than 1:

Number of Agents Working on a Statement
Number of Agents Created
Degree of Parallelism

Number of Agents Working on a Statement

Snapshot Level Statement	Logical Data Grouping stmt subsection	Monitor Switch Statement Statement
Resettable	No
Element Name Element Type	num_agents gauge
Related Information	"Number of Agents Created" "Degree of Parallelism"

Description: Number of concurrent agents currently executing a statement or subsection.

Usage: An indicator how well the query is parallelized. This is useful for tracking the progress of query execution, by taking successive snapshots.

Number of Agents Created

Snapshot Level Database Application	Logical Data Grouping dbase stmt	Monitor Switch Statement Statement
Resettable	No
Element Name Element Type	agents_top water mark
Related Information	"Number of Agents Working on a Statement" "Degree of Parallelism"

Description: At the application level, this is the maximum number of agents that were used when executing the statement. At the database level, it is the maximum number of agents for all applications.

Usage: An indicator how well intra-query parallelism was realized.

Degree of Parallelism

Snapshot Level Statement	Logical Data Grouping stmt	Monitor Switch Statement
Resettable	No
Element Name Element Type	degree_parallelism information
Related Information	"Number of Agents Working on a Statement" "Number of Agents Created"

Description: The degree of parallelism requested when the query was bound.

Usage: Use with Number of Agents Created, to determine if the query achieved maximum level of parallelism.

CPU Usage

The CPU usage for an application is broken down into user CPU, which is the CPU consumed while executing application code, and system CPU, which is the CPU consumed executing system calls.

CPU consumption is available at the application, transaction, statement, and subsection levels.

User CPU Time used by Agent
System CPU Time used by Agent
User CPU Time used by Statement
System CPU Time used by Statement
User CPU Time
System CPU Time
User CPU Time used by Subsection
System CPU Time used by Subsection
Total System CPU for a Statement
Total User CPU for a Statement

User CPU Time used by Agent

Snapshot Level Application	Logical Data Grouping appl	Monitor Switch Basic
Resettable	Yes
Element Name Element Type	agent_usr_cpu_time time
Related Elements	"System CPU Time used by Agent" "User CPU Time used by Statement" "System CPU Time used by Statement" "User CPU Time used by Subsection" "System CPU Time used by Subsection" "User CPU Time" "System CPU Time" "Total System CPU for a Statement" "Total User CPU for a Statement"

Description: The total CPU time (in seconds and microseconds) used by database manager agent process.

Usage: This element along with the other CPU-time related elements can help you identify applications or queries that consume large amounts of CPU.

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

System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.
Note: If this information is not available for your operating system, this element will be returned as 0. For example, they are not available on OS/2.

System CPU Time used by Agent

Snapshot Level Application	Logical Data Grouping appl	Monitor Switch Basic
Resettable	Yes
Element Name Element Type	agent_sys_cpu_time time
Related Information	"User CPU Time used by Agent" "User CPU Time used by Statement" "System CPU Time used by Statement" "User CPU Time used by Subsection" "System CPU Time used by Subsection" "User CPU Time" "System CPU Time" "Total System CPU for a Statement" "Total User CPU for a Statement"

Description: The total system CPU time (in seconds and microseconds) used by the database manager agent process.

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

It includes CPU time for both SQL and non-SQL statements, as well as CPU time for any fenced User Defined Functions (UDFs)

System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.
Note: If this information is not available for your operating system, this element will be set to 0. For example, it is not available for OS/2.

User CPU Time used by Statement

Snapshot Level Application	Logical Data Grouping appl stmt	Monitor Switch Statement Statement
Resettable	No
Element Name Element Type	stmt_usr_cpu_time time
Related Information	"System CPU Time used by Agent" "User CPU Time used by Agent" "System CPU Time used by Statement" "User CPU Time used by Subsection" "System CPU Time used by Subsection" "User CPU Time" "System CPU Time" "Total System CPU for a Statement" "Total User CPU for a Statement"

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

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

System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.
Note: If this information is not available for your operating system, this element will be set to 0. For example, it is not available for OS/2.

System CPU Time used by Statement

Snapshot Level Application	Logical Data Grouping appl stmt	Monitor Switch Statement Statement
Resettable	No
Element Name Element Type	stmt_sys_cpu_time time
Related Information	"System CPU Time used by Agent" "User CPU Time used by Statement" "User CPU Time used by Agent" "User CPU Time used by Subsection" "System CPU Time used by Subsection" "User CPU Time" "System CPU Time" "Total System CPU for a Statement" "Total User CPU for a Statement"

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

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

System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code.
Note: If this information is not available for your operating system, this element will be set to 0. For example, it is not available for OS/2.

User CPU Time

Event Type Connection Transaction Statement	Logical Data Grouping conn_event xaction_event stmt_event
Element Name Element Type	user_cpu_time time
Related Information	"System CPU Time used by Agent" "User CPU Time used by Statement" "System CPU Time used by Statement" "User CPU Time used by Subsection" "System CPU Time used by Subsection" "User CPU Time used by Agent" "System CPU Time" "Total System CPU for a Statement" "Total User CPU for a Statement"

Description: The total user CPU time (in seconds and microseconds) used by the database manager agent process, the unit of work, or the statement.

Usage: This element along with the other related CPU-time elements can help you understand the level of activity within an application, and can help you identify applications that could benefit from additional tuning.
Note: If this information is not available for your operating system, this element will be set to 0. For example, it is not available for OS/2.

System CPU Time

Event Type Connection Transaction Statement	Logical Data Grouping conn_event xaction_event stmt_event
Element Name Element Type	system_cpu_time time
Related Information	"System CPU Time used by Agent" "User CPU Time used by Statement" "System CPU Time used by Statement" "User CPU Time used by Subsection" "System CPU Time used by Subsection" "User CPU Time" "User CPU Time used by Agent" "Total System CPU for a Statement" "Total User CPU for a Statement"

Description: The total system CPU time (in seconds and microseconds) used by the database manager agent process, the unit of work, or the statement.

Usage: This element along with the other related CPU-time elements can help you understand the level of activity within an application, and can help you identify applications help could benefit from additional tuning.
Note: If this information is not available for your operating system, this element will be set to 0. For example, it is not available for OS/2.

User CPU Time used by Subsection

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Basic
Resettable	No
Event Type Statement	Logical Data Grouping subsection_event
Element Name Element Type	ss_usr_cpu_time time
Related Information	"System CPU Time used by Agent" "User CPU Time used by Statement" "System CPU Time used by Statement" "User CPU Time used by Agent" "System CPU Time used by Subsection" "User CPU Time" "System CPU Time" "Total System CPU for a Statement" "Total User CPU for a Statement"

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

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

System CPU Time used by Subsection

Snapshot Level Application	Logical Data Grouping subsection	Monitor Switch Basic
Resettable	No
Event Type Statement	Logical Data Grouping subsection_event
Element Name Element Type	ss_sys_cpu_time time
Related Information	"System CPU Time used by Agent" "User CPU Time used by Statement" "System CPU Time used by Statement" "User CPU Time used by Subsection" "System CPU Time used by Subsection" "User CPU Time" "User CPU Time used by Agent" "Total System CPU for a Statement" "Total User CPU for a Statement"

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

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

Total System CPU for a Statement

Snapshot Level Dynamic SQL	Logical Data Grouping dynsql	Monitor Switch Statement
Resettable	Yes
Element Name Element Type	tot_s_cpu_time time
Related Information	"Total User CPU for a Statement" "Elapsed Statement Execution Time"

Description: The total system CPU time for an SQL 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.

Total User CPU for a Statement

Snapshot Level Dynamic SQL	Logical Data Grouping dynsql	Monitor Switch Statement
Resettable	Yes
Element Name Element Type	tot_u_cpu_time time
Related Information	"Total System CPU for a Statement" "Elapsed Statement Execution Time"

Description: The total user CPU time for an SQL statement.

Usage: Use this element with Elapsed Statement Execution Time and to evaluate the longest running statements.

Snapshot Monitoring Elements

The following elements provide information about monitoring applications. They are returned as output for every snapshot:

Last Reset Timestamp
Input Database Alias
Snapshot Time
Number of Nodes in Partition

Last Reset Timestamp

Snapshot Level Database Manager Database Application Table Space Table DCS Database DCS Application	Logical Data Grouping db2 dbase appl tablespace_header table_header dcs_dbase dcs_appl	Monitor Switch Basic Basic Basic Buffer Pool Table Basic Basic
Resettable	No
Element Name Element Type	last_reset timestamp
Related Information	"Resetting Monitor Data" "Input Database Alias"

Description: Indicates the date and time that the monitor counters were reset for the application issuing the GET SNAPSHOT.

Usage: You can use this element to help you determine the scope of information returned by the database system monitor.

If the counters have never been reset, this element will be zero.

The database manager counters will only be reset if you reset all active databases.

Input Database Alias

Snapshot Level Database Application Table Space Buffer Pool Table Lock	Logical Data Grouping dbase appl_id_info tablespace_header bufferpool table_header dbase_lock	Monitor Switch Basic Basic Buffer Pool Buffer Pool Table Basic
Resettable	No
Element Name Element Type	input_db_alias information
Related Information	"Resetting Monitor Data" "Last Reset Timestamp" "Database Alias Used by Application"

Description: The alias of the database provided when calling the snapshot function.

Usage: This element can be used to identify the specific database to which the monitor data applies. It contains blanks unless you requested monitor information related to a specific database.

The value of this field may be different than the value of the "Database Alias Used by Application" monitor element since a database can have many different aliases. Different applications and users can use different aliases to connect to the same database.

Snapshot Time

Snapshot Level Database Manager	Logical Data Grouping collected	Monitor Switch Basic
Resettable	No
Element Name Element Type	time_stamp timestamp
Related Information	None

Description: The date and time when the database system monitor information was collected.

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.

Number of Nodes in Partition

Snapshot Level Database Manager	Logical Data Grouping db2	Monitor Switch Basic
Resettable	No
Event Type Database Manager	Logical Data Grouping log_header_event
Element Name Element Type	num_nodes_in_db2_instance information
Related Information	None

Description: The number of nodes on the instance where the snapshot was taken.

Usage: Use this element to determine the number of nodes for an instance. For non-partitioned system databases, this value will be 1.

Event Monitoring Elements

The following elements provide information about monitoring applications. They are returned as output for events:

Number of Event Monitor Overflows
Time of First Event Overflow
Time of Last Event Overflow
Byte Order of Event Data
Version of Monitor Data
Event Monitor Name
Partial Record
Event Time

Number of Event Monitor Overflows

Event Type Overflow Record	Logical Data Grouping overflow_event
Element Name Element Type	count counter
Related Information	None

Description: The number of consecutive overflows that have occurred.

Usage: You may use this element to get an indication of how much monitor data has been lost.

The event monitor sends one overflow record for a set of consecutive overflows.

Time of First Event Overflow

Event Type Overflow Record	Logical Data Grouping overflow_event
Element Name Element Type	first_overflow_time timestamp
Related Information	"Number of Event Monitor Overflows"

Description: The date and time of the first overflow recorded by this overflow record.

Usage: Use this element with "Time of Last Event Overflow" to calculate the elapsed time for which the overflow record was generated.

Time of Last Event Overflow

Event Type Overflow Record	Logical Data Grouping overflow_event
Element Name Element Type	last_overflow_time timestamp
Related Information	"Number of Event Monitor Overflows"

Description: The date and time of the last overflow recorded this overflow record.

Usage: Use this element with "Time of First Event Overflow" to calculate the elapsed time for which the overflow record was generated.

Byte Order of Event Data

Event Type Event Log Header	Logical Data Grouping log_header_event
Element Name Element Type	byte_order information
Related Information	None

Description: The byte ordering of numeric data, specifically whether the event data stream was generated on a "big endian" server (for example, a RISC System/6000) or "little endian" server (for example, a PS/2).

Usage: This information is needed to allow you to interpret numeric data in the data stream, since the byte order of integers on a "big endian" server is the reverse of the byte order on a "little endian" server.

If the application that processes the data recognizes that it is running on one type of computer hardware (for example, a big endian computer), while the event data was produced on the other type of computer hardware (for example, a little endian computer), then the monitoring application will have to reverse the bytes of numeric data fields before interpreting them. Otherwise, byte reordering is not required.

This element can be set to one of the following API constants:

SQLM_BIG_ENDIAN
SQLM_LITTLE_ENDIAN

Version of Monitor Data

Event Type Event Log Header	Logical Data Grouping log_header_event
Element Name Element Type	version information
Related Information	None

Description: The version of the database manager that produced the event monitor data stream.

Usage: The data structures used by the event monitor may change between releases of the database manager. As a result, your monitor applications should check the version of the data stream to determine if they can process the data they will be receiving.

For this release, this element is set to the API constant SQLM_DBMON_VERSION6.

Event Monitor Name

Event Type Event Log Header	Logical Data Grouping log_header_event
Element Name Element Type	event_monitor_name information
Related Information	None

Description: The name of the event monitor that created the event data stream.

Usage: This element allows you to correlate the data that you are analyzing to a specific event monitor in the system catalog tables. This is the same name that can be found in the NAME column of the SYSCAT.EVENTMONITORS catalog table, which is the name specified on the CREATE EVENT MONITOR and SET EVENT MONITOR statements.

Partial Record

Event Type
Database
Table
Table Space
 
Connection
Statement
 
Transaction

Logical Data Grouping
db_event
table_event
tablespace_event
bufferpool_event
conn_event
stmt_event
subsection_event
xaction_event

Element Name
Element Type

partial_record
information

Related Information

None

Description: Indicates that an event monitor record is only a partial record.

Usage: Most event monitors do not output their results until database deactivation. You can use the FLUSH EVENT MONITORS statement to force monitor values to the event monitor output writer (see FLUSH EVENT MONITOR). This allows you to force event monitor records to the writer without needing to stop and restart the event monitor. This data element indicates whether an event monitor record was the result of flush operation and so is a partial record.

Flushing an event monitor does not cause its values to be reset. This means that a complete event monitor record is still generated when the event monitor is triggered.

Event Time

Event Type Table Space Table	Logical Data Grouping tablespace_event table_event
Element Name Element Type	event_time information
Related Information	None

Description: The date and time an event occurred.

Usage: You can use this element to help relate events chronologically.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]