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"
"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"

Locks Held

Snapshot Information Level Database Application Lock	API Structure(s) sqlm_dbase sqlm_appl sqlm_dbase_lock sqlm_appl_lock	Monitor Switch Basic Basic Basic Basic
Resettable	No
API Element Name Element Type	locks_held gauge
Related Information	"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 Information Level Database	API Structure(s) sqlm_dbase	Monitor Switch Basic
Resettable	No
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Lock
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API Element Name Element Type	deadlocks counter
Related Information	"Resetting Monitor Data" "When Counters are Initialized" "Database Activation Timestamp" "Connection Request Start Timestamp" "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.

Lock Escalations

Snapshot Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection Transaction	Event Record(s) sqlm_db_event sqlm_conn_event sqlm_xaction_event
API 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".
Identify applications holding too much of the lock list, using the following formula:

Exclusive Lock Escalations

Snapshot Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection Transaction	Event Record(s) sqlm_db_event sqlm_conn_event sqlm_xaction_event
API Element Name Element Type	x_lock_escals counter
Related Information	"When Counters are Initialized" "Database Activation Timestamp" "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 "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 Information Level Application Lock	API Structure(s) sqlm_appl sqlm_lock sqlm_lock_wait	Monitor Switch Lock Lock Lock
Resettable	No
Event Type Deadlock	Event Record(s) sqlm_dlconn_event
API 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 Information Level Lock	API Structure(s) sqlm_lock	Monitor Switch Basic
Resettable	No
API 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 Information Level Application Lock	API Structure(s) sqlm_appl sqlm_appl_lock sqlm_lock sqlm_lock_wait	Monitor Switch Lock Lock Basic Lock
Resettable	No
Event Type Deadlock	Event Record(s) sqlm_dlconn_event
API 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 Information Level Application Lock	API Structure(s) sqlm_appl sqlm_appl_lock sqlm_lock	Monitor Switch Lock Lock Basic
Resettable	No
Event Type Deadlock	Event Record(s) sqlm_dlconn_event
API 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".

Number of Lock Timeouts

Snapshot Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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	Event Record(s) sqlm_xaction_event
API Element Name Element Type	locks_held_top counter
Related Information	"Locks Held" "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	Event Record(s) sqlm_deadlock_event
API 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 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"

Lock Waits

Snapshot Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Lock Lock
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl sqlm_appl_lock	Monitor Switch Lock Lock
Resettable	Yes
Event Type Database Connection Transaction	Event Record(s) sqlm_db_event sqlm_conn_event sqlm_xaction_event
API 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 Information Level Table Space Application Lock	API Structure(s) sqlm_tablespace sqlm_appl_lock sqlm_lock sqlm_lock_wait	Monitor Switch Buffer Pool Basic Lock Lock
Resettable	No
Event Type Deadlock Table Space	Event Record(s) sqlm_dlconn_event sqlm_tablespace_header
API 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.

If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 18 characters are currently used.

Current Agents Waiting On Locks

Snapshot Information Level Database Lock	API Structure(s) sqlm_dbase sqlm_dbase_lock	Monitor Switch Basic Basic
Resettable	No
API 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 Information Level Application	API Structure(s) sqlm_appl	Monitor Switch Unit of Work
Resettable	No
API 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 Information Level Application Lock	API Structure(s) sqlm_appl sqlm_lock_wait	Monitor Switch Lock Lock
Resettable	No
Event Type Deadlock	Event Record(s) sqlm_dlconn_event
API 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 Information Level Application Lock	API Structure(s) sqlm_appl sqlm_appl_lock sqlm_lock_wait	Monitor Switch Lock Lock Lock
Resettable	No
API 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 Information Level Application Lock	API Structure(s) sqlm_appl sqlm_appl_lock sqlm_lock_wait	Monitor Switch Lock Lock Lock
Resettable	No
Event Type Deadlock	Event Record(s) sqlm_dlconn_event
API 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.

If you are using the database system monitor APIs, note that the API constant SQLM_APPLID_SZ is used to define the length of this element. Only the first 30 characters are currently used.

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 Information Level Application	API Structure(s) sqlm_appl sqlm_appl_lock	Monitor Switch Basic Basic
Resettable	No
Event Type Deadlock	Event Record(s) sqlm_dlconn_event
API 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	Event Record(s) sqlm_deadlock_event
API 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

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 Information Level Table Space	API Structure(s) sqlm_rollfwd_info	Monitor Switch Basic
Resettable	No
API 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 Information Level Table Space	API Structure(s) sqlm_rollfwd_ts_info	Monitor Switch Basic
Resettable	No
API 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 Information Level Table Space	API Structure(s) sqlm_rollfwd_info	Monitor Switch Basic
Resettable	No
API Element Name Element Type	rf_type information
Related Information	None

Description: The type of rollforward in progess.

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 Information Level Table Space	API Structure(s) sqlm_rollfwd_info	Monitor Switch Basic
Resettable	No
API 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 Information Level Table Space	API Structure(s) sqlm_rollfwd_info	Monitor Switch Basic
Resettable	No
API 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 Information Level Table Space	API Structure(s) sqlm_rollfwd_info	Monitor Switch Basic
Resettable	No
API 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"

Table Type

Snapshot Information Level Table	API Structure(s) sqlm_table	Monitor Switch Table
Resettable	No
Event Type Table	Event Record(s) sqlm_table_event
API 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 Information Level Table Application Lock	API Structure(s) sqlm_table sqlm_appl sqml_appl_lock sqlm_lock sqlm_lock_wait	Monitor Switch Table Lock Lock Lock Lock
Resettable	No
Event Type Table Deadlock	Event Record(s) sqlm_table_event sqlm_dlconn_event
API 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.

If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 18 characters are currently used.

Table Schema Name

Snapshot Information Level Table Application Lock	API Structure(s) sqlm_table sqlm_appl sqlm_appl_lock sqlm_lock sqlm_lock_wait	Monitor Switch Table Lock Lock Lock Lock
Resettable	No
Event Type Table Deadlock	Event Record(s) sqlm_table_event sqlm_dlconn_event
API 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.

If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 8 characters are currently used.

Rows Deleted

Snapshot Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Table Application	API Structure(s) sqlm_table sqlm_appl sqlm_stmt sqlm_subsection	Monitor Switch Table Basic Basic Statement
Resettable	Yes
Event Type Connection Table Statement Transaction	Event Record(s) sqlm_conn_event sqlm_table_event sqlm_stmt_event sqlm_xaction_event
API 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 Information Level Table Application	API Structure(s) sqlm_table sqlm_appl sqlm_stmt sqlm_subsection	Monitor Switch Table Basic Basic Statement
Resettable	Yes
Event Type Connection Table Statement Transaction	Event Record(s) sqlm_conn_event sqlm_table_event sqlm_stmt_event sqlm_xaction_event
API 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 Information Level Table	API Structure(s) sqlm_table	Monitor Switch Table
Resettable	Yes
Event Type Table	Event Record(s) sqlm_table_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl sqlm_stmt	Monitor Switch Basic Basic Basic
Resettable	Yes
Event Type Database Connection Statement	Event Record(s) sqlm_db_event sqlm_conn_event sqlm_stmt_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl sqlm_stmt	Monitor Switch Basic Basic Basic
Resettable	Yes
Event Type Database Connection Statement	Event Record(s) sqlm_db_event sqlm_conn_event sqlm_stmt_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl sqlm_stmt	Monitor Switch Basic Basic Basic
Resettable	Yes
Event Type Database Connection Statement	Event Record(s) sqlm_db_event sqlm_conn_event sqlm_stmt_event
API 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 Information Level Application Table Lock	API Structure(s) sqlm_appl sqlm_table sqlm_appl_lock sqlm_lock	Monitor Switch Lock Table Lock Lock
Resettable	No
API 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.

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 Information Level Application	API Structure(s) sqlm_appl	Monitor Switch Basic
Resettable	No
API 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 Information Level Application	API Structure(s) sqlm_appl	Monitor Switch Basic
Resettable	No
API 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 Information Level Application	API Structure(s) sqlm_appl	Monitor Switch Basic
Resettable	No
Event Type Connection	Event Record(s) sqlm_conn_event
API Element Name Element Type	rej_curs_blk counter
Related Information	"Accepted Block Cursor Requests" "Open Local Cursors" "Open Local 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 Information Level Application	API Structure(s) sqlm_appl	Monitor Switch Basic
Resettable	No
Event Type Connection	Event Record(s) sqlm_conn_event
API Element Name Element Type	acc_curs_blk counter
Related Information	"Rejected Block Cursor Requests" "Open Local Cursors" "Open Local 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 Information Level Application	API Structure(s) sqlm_appl	Monitor Switch Basic
Resettable	No
API 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 Information Level Application	API Structure(s) sqlm_appl	Monitor Switch Basic
Resettable	No
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Table Space Application	API Structure(s) sqlm_dbase sqlm_tablespace sqlm_appl	Monitor Switch Basic Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Connection	Event Record(s) sqlm_conn_event
API 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 Information Level Application	API Structure(s) sqlm_appl	Monitor Switch Basic
Resettable	No
API 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 C. "DB2 Version 1 sqlestat Users" for more information on sqlestat equivalent data elements.

Statement Node

Snapshot Information Level Application	API Structure(s) sqlm_stmt	Monitor Switch Statement
Resettable	No
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_appl	Monitor Switch Basic Basic
Resettable	Yes
Event Type Database Connection	Event Record(s) sqlm_db_event sqlm_conn_event
API 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"
"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 Information Level Application	API Structure(s) sqlm_appl sqlm_stmt	Monitor Switch Statement Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_stmt_event
API 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 Information Level Application	API Structure(s) sqlm_appl sqlm_stmt	Monitor Switch Basic Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_stmt_event
API 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

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 Information Level Application	API Structure(s) sqlm_appl sqlm_stmt	Monitor Switch Statement Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_stmt_event
API 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.

If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 8 characters are currently used.

Section Number

Snapshot Information Level Application	API Structure(s) sqlm_appl sqlm_stmt	Monitor Switch Statement Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_stmt_event
API 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 Information Level Application	API Structure(s) sqlm_appl sqlm_stmt	Monitor Switch Statement Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_stmt_event
API 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.

If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 8 characters are currently used.

Application Creator

Snapshot Information Level Application	API Structure(s) sqlm_appl sqlm_stmt	Monitor Switch Statement Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_stmt_event
API 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.

If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 8 characters are currently used.

Statement Operation Start Timestamp

Snapshot Information Level Application	API Structure(s) sqlm_appl sqlm_stmt	Monitor Switch Statement Statement
Resettable	No
API 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 Information Level Application	API Structure(s) sqlm_appl sqlm_stmt	Monitor Switch Statement Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_stmt_event
API Element Name Element Type	stmt_stop (Snapshot) stop_time (event) Timestamp
Related Information	"Resetting Monitor Data" "Statement Operation Start Timestamp" "Statement Operation"

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.

SQL Dynamic Statement Text

Snapshot Information Level Application	API Structure(s) sqlm_stmt	Monitor Switch Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_stmt_event
API Element Name Element Type	Relative offsets are used to return the text. See data structures in sqlmon.h> stmt_text (Event) 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 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 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 Information Level Application	API Structure(s) sqlm_appl sqlm_stmt	Monitor Switch Statement Statement
Resettable	No
API 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 Information Level Application	API Structure(s) sqlm_stmt	Monitor Switch Statement
Resettable	Yes
Event Type Statement	Event Record(s) sqlm_stmt_event
API 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	Event Record(s) sqlm_stmt_event
API 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 API Reference for information about the content of the SQLCA.

Query Number of Rows Estimate

Snapshot Information Level Application	API Structure(s) sqlm_stmt	Monitor Switch Statement
Resettable	No
API 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.

Query Cost Estimate

Snapshot Information Level Application	API Structure(s) sqlm_stmt	Monitor Switch Statement
Resettable	No
API 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.

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 Information Level Application	API Structure(s) sqlm_subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_subsection_event
API 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 Information Level Application	API Structure(s) sqlm_subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_subsection_event
API 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 Information Level Application	API Structure(s) sqlm_subsection	Monitor Switch Statement
Resettable	No
API 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 Information Level Application	API Structure(s) sqlm_subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_subsection_event
API 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 Information Level Application	API Structure(s) sqlm_subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_subsection_event
API 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 Information Level Application	API Structure(s) sqlm_subsection	Monitor Switch Statement
Resettable	No
API 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 Information Level Application	API Structure(s) sqlm_subsection	Monitor Switch Statement
Resettable	No
API 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 Information Level Application	API Structure(s) sqlm_subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_subsection_event
API 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 Information Level Application	API Structure(s) sqlm_subsection	Monitor Switch Statement
Resettable	No
API 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 Information Level Application	API Structure(s) sqlm_subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_subsection_event
API 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 Information Level Application	API Structure(s) sqlm_subsection	Monitor Switch Statement
Resettable	No
Event Type Statement	Event Record(s) sqlm_subsection_event
API 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.

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 Information Level Statement	API Structure(s) sqlm_stmt sqlm_subsection	Monitor Switch Statement Statement
Resettable	No
API 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 Information Level Database Application	API Structure(s) sqlm_dbase sqlm_stmt	Monitor Switch Statement Statement
Resettable	No
API Element Name Element Type	agents_top water mark
Related Information	"Number of Agents Working on a Statement" "Degree of Parallelism"

Description: This is the maximum number of agents that were used when executing the statement.

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

Degree of Parallelism

Snapshot Information Level Statement	API Structure(s) sqlm_stmt	Monitor Switch Statement
Resettable	No
API 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.

"CPU Time Used"

CPU Time Used

Snapshot Information Level Application Statement Subsection	API Structure(s) sqlm_appl sqlm_stmt sqlm_subsection	Monitor Switch Basic Statement Statement
Resettable	Yes, at the application level No, at other levels
Event Type Connection Transaction Statement Subsection	Event Record(s) sqlm_conn_event sqlm_xaction_event sqlm_stmt_event sqlm_subsection_event
API Element Name Element Type	agent_usr_cpu_time agent_sys_cpu_time stmt_usr_cpu_time stmt_sys_cpu_time ss_usr_cpu_time ss_sys_cpu_time user_cpu_time system_cpu_time time
Related Elements	None

Description: The total CPU time (in seconds and microseconds) used by database manager agents, while working on behalf of the application, transaction, statement, or subsection.

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

These counters include 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.

Usage: These elements can help you identify applications or queries that consume large amounts of CPU.
Note: If this information is not available for your operating system, these elements will be returned as 0. For example, they are not available on OS/2.

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"

Last Reset Timestamp

Snapshot Information Level Database Manager Database Application Table Space Table	API Structure(s) sqlm_db2 sqlm_dbase sqlm_appl sqlm_tablespace_header sqlm_table_header	Monitor Switch Basic Basic Basic Buffer Pool Table
Resettable	No
API 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 Information Level Database Application Table Space Buffer Pool Table Lock	API Structure(s) sqlm_dbase sqlm_appl_id_info sqlm_tablespace_header sqlm_bufferpool sqlm_table_header sqlm_dbase_lock	Monitor Switch Basic Basic Buffer Pool Buffer Pool Table Basic
Resettable	No
API 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.

If you are using the database system monitor APIs, note that the API constant SQLM_IDENT_SZ is used to define the length of this element. Only the first 8 characters are currently used.

Snapshot Time

Snapshot Information Level Database Manager	API Structure(s) sqlm_collected	Monitor Switch Basic
Resettable	No
API 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.

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

[ DB2 List of Books | Search the DB2 Books ]