System Monitor Guide and Reference

Database and Application Activity

The following sections provide information on database and application activity.

Locks and Deadlocks

The following elements provide information about locks and deadlocks:

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

Locks Held

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

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

Description:  The total number of deadlocks that have occurred.

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

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

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

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

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

Number of Lock Escalations

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

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

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

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

There are several possible causes for excessive lock escalations:

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

To resolve these problems, you may be able to:

• Increase the locklist configuration parameter value. See the Administration Guide for a description of this configuration parameter.
• Increase the maxlocks configuration parameter value. See the Administration Guide for a description of this configuration parameter.
• Identify the applications with large numbers of locks (see "Maximum Number of Locks Held"), or those that are holding too much of the lock list, using the following formula:

    (((locks held * 36) / (locklist * 4096)) * 100)
  

  and comparing the value to maxlocks. These applications can also cause lock escalations in other applications by using too large a portion of the lock list. These applications may need to resort to using table locks instead of row locks, although table locks may cause an increase in Lock Waits and Time Waited On Locks.

Exclusive Lock Escalations

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.

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.

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

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

Lock Mode

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

Lock Status

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.

Lock Object Type Waited On

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

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

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

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

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

Lock Node

Description:  The node involved in a lock.

Usage:  This can be used for troubleshooting.

Number of Lock Timeouts

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

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

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

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

Lock Escalation

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

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

Lock Mode Requested

Description:  The lock mode being requested by the application.

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

Lock Wait Information

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

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

Lock Waits

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

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

Description:  The name of a table space.

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

It is equivalent to the TBSPACE column in the database catalog table SYSCAT.TABLESPACE. At the application level, application-lock level, and deadlock monitoring level, this is the name of the table space that the application is waiting to lock. Another application currently holds a lock on this table space.

At the lock level, this is the name of the table space against which the application currently holds a lock.

At the table space level (when the buffer pool monitor group is ON), this is the name of the table space for which information is returned.

Current Agents Waiting On Locks

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

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

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

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

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

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

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

Sequence Number Holding Lock

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

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

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

Rolled Back Agent

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

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

Rolled Back Sequence Number

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

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

Rollforward Monitoring

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

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

Rollforward Timestamp

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

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

Description:  The type of rollforward in progress.

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

Log Being Rolled Forward

Description:  The log being processed.

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

Log Phase

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

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

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

Table Activity

The following elements provide information about the tables:

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

Table Type

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

Description:  The name of the table.

Usage:  Along with "Table Schema Name", this element can help you determine the source of contention for resources.

At the application-level, application-lock level, and deadlock-monitoring-level, this is the table that the application is waiting to lock, because it is currently locked by another application. For snapshot monitoring, this item is only valid when the "lock" monitor group information is turned on, and when "Lock Object Type Waited On" indicates that the application is waiting to obtain a table lock.

For snapshot monitoring at the object-lock level, this item is returned for table-level and row-level locks. The table reported at this level is the table against which this application holds these locks.

For snapshot and event monitoring at the table level, this is the table for which information has been collected. This element is blank for temporary tables, reorganization tables, and tables that were dropped. Table names are only provided for catalog and user tables. For snapshot monitoring, this element is only valid when the "table" monitor group information is turned on.

Table Schema Name

Description:  The schema of the table.

Usage:  Along with "Table Name", this element can help you determine the source of contention for resources.

For application-level, application-lock-level, deadlock-monitoring-level, this is the schema of the table that the application is waiting to lock, because it is currently locked by another application. This element is only set if "Lock Object Type Waited On" indicates that the application is waiting to obtain a table lock. For snapshot monitoring at the application-level and application-lock levels, this item is only valid when the "lock" monitor group information is turned on.

For snapshot monitoring at the object-lock level, this item is returned for table and row level locks. The table reported at this level is the table against which this application holds these locks.

For snapshot and event monitoring at the table level, this element identifies the schema of the table for which information has been collected. This element is blank for temporary tables, reorganization tables, and tables that were dropped. Schema names are provided only for catalog and user tables. For snapshot monitoring, this element is valid only when the "table" monitor group information is turned on.

Rows Deleted

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

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.

In a federated system, multiple rows can be inserted, per INSERT statement, because the federated server can push INSERT FROM SUBSELECT to the data source, when appropriate.

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

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.

For a federated system, you can compute the average time to return a row to the federated server from the data source:

   average time = rows returned / aggregate query response time

You can use these results to modify CPU speed or communication speed parameters in SYSCAT.SERVERS. Modifying these parameters can impact whether the optimizer does or does not send requests to the data source.

Rows Written

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

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

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

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

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

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

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

Usage:  This element is provided for information purposes only. It is returned for compatibility with previous versions of the database system monitor, and it may not uniquely identify the table. Use "Table Name" and "Table Schema Name" to identify the table.

Page Reorganizations

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

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

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

SQL Cursors

The following elements provide information about the SQL cursors:

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

Open Remote Cursors

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

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

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

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

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

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

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

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

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

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

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

Rollback Statements Attempted

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

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

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

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

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

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

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

Internal Rollbacks

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

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

Internal Rollbacks Due To Deadlock

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

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

Usage:  You can use this element to monitor the progress of a transaction.

Note:

This element is similar to the cur_reqs field in the sqlestat output. See Appendix D, DB2 Version 1 sqlestat Users for more information on sqlestat equivalent data elements.

Statement Node

Description:  Node where the statement was executed.

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

Binds/Precompiles Attempted

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

Note:

Statement event monitors do not log fetches.

The following elements provide details about the SQL statements:

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

Statement Type

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

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

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

It can be one of the following.

For SQL operations:

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

For non-SQL operations:

• RUN STATISTICS
• REORG
• REBIND
• REDISTRIBUTE
• GET TABLE AUTHORIZATION
• GET ADMINISTRATIVE AUTHORIZATION

Package Name

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

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

Section Number

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

Cursor Name

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

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

Application Creator

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

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

Statement Operation Start Timestamp

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

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

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

Event Stop Time

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

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

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

Event Start Time

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

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

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

Most Recent Statement Elapsed Time

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

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

SQL Dynamic Statement Text

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

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

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

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

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

Statement Sorts

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

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)

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

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

Query Number of Rows Estimate