Locking activity (BSTATDR)

SUSPENSIONS (ALL)

Total number of suspensions

SUSPENSIONS (LOCK ONLY)

number of suspends due to lock conflict (QTXASLOC)

In the DB2 Statistics Overview Report--averages and the DB2 Statistics Lock Report, this value is the average number of suspensions due to waiting for lock per CREATE THREAD during this statistics interval. This counter is incremented any time a thread has a conflicting lock request, such as an updater requesting exclusive access to a page being used by another thread.

Tuning Tip: In a multitasking system, suspensions occur in the normal course of the events. If applications are well-tuned, taking frequent commits and holding on to resources for the fewest possible instructions, suspensions can be minimized. If suspensions cause frequent timeouts, consider row-level locking.

SUSPENSIONS (LATCH ONLY)

Number of suspends due to latch conflicts (QTXASLAT)

In the DB2 Statistics Lock Report, this value is the average number of times suspended due to latching per CREATE THREAD during this statistics interval. This number is incremented when a latch conflict exists between two DB2 threads or internal serialization processing.

Tuning Tip: Latches are generally of extremely short duration. Unless the time is a significant component of overall wait time, it is not a factor which should cause tuning problems.

SUSPENSIONS (OTHER)

Number of suspends due to other conflicts (QTXASOTH)

In the DB2 Statistics Lock Report, this value is the average number of times suspended due to other reasons per CREATE THREAD during this statistics interval. This number is incremented when DB2 internal processes collide.

Tuning Tip: This number is not generally of significance in tuning. Unusually high numbers should be reported to IBM service.

TIMEOUTS

Number of lock timeouts (QTXATIM)

This count is incremented every time a DB2 thread waits longer to get a page than the timeout interval, which is specified with DSNZPARM IRLMRWT on installation panel DSNTIPI. By default, it is 60 seconds. Utilities can be allowed to wait several multiples of IRLMRWT.

Tuning Tip: Lock timeouts are usually caused by an application failing to commit in time for the other thread to gain access to data on pages. Often the problem can be resolved by reducing the time between commits and putting updateable statements near to their commit logic. Every time a timeout occurs, DB2 writes the holder and suspender to the MSTR job log. Normally, this number should be as close to zero as possible.

DEADLOCKS

Number of deadlocks (QTXADEA)

This count is incremented every time DB2 encounters a deadlock situation for which the IRLM must cancel a task involved in a deadly embrace.

Tuning Tip: Deadlocks are caused by threads requesting access to two resources which can never be resolved. DB2 chooses its victim by selecting the thread that has done the least number of updates. It records the deadlock in the MSTR address space job log. In well-tuned systems, this number should be low.

LOCK REQUESTS

Number of lock requests (QTXALOCK)

In the DB2 Statistics Lock Report, this value is the average number of lock requests per CREATE THREAD during this statistics interval.This counter is incremented for each call to the IRLM lock manager to acquire a lock on a page or row or to acquire a claim or drain on a data set.

Tuning Tip: Each lock request is processed by the IRLM. Lock avoidance techniques should show reductions in overall counts and overhead, since latches execute totally within DB2.

UNLOCK REQUESTS

Number of unlock requests (QTXAUNLK)

This count is incremented when the application has finished processing a page or row, or a claim or drain can be released. This amount is significant as to cross memory processing and reflects the normal release of resources.

QUERY REQUESTS

Number of query requests (QTXAQRY)

This counter is incremented every time the IRLM gets a request to read data.

Tuning Tip: This information is useful in determining the read activity on a system but does not include lock avoidance techniques.

CHANGE REQUESTS

Number of change requests (QTXACHG)

This counter is incremented every time the IRLM is asked to change a lock from one type to another (for example, from S to X).

Tuning Tip: The information is useful in determining the overall volatility of the system.

OTHER REQUESTS

Number of other IRLM requests (QTXAIRLM)

This counter is incremented every time the IRLM receives a lock request not included in the other counts.

LOCK ESCALATION (SHARED)

Number of lock escalations to shared mode (QTXALES)

This count is incremented every time the number of locks against a single table space exceeds the number set in DSNZPARM NUMLKTS on installation panel DSNTIPJ or in the LOCKMAX clause of the CREATE table space statement.

Tuning Tip: This situation is not normal unless you are using repeatable read (RR). If it occurs often, consider changing the LOCKSIZE or LOCKMAX to a higher value or consider binding the plan with cursor stability (CS) or uncommitted read (UR).

LOCK ESCALATION (EXCL)

Number of lock escalations to exclusive mode (QTXALEX)

This counter is incremented every time the number of updateable locks against a single table space exceeds the DSNZPARM NUMLKTS on installation panel DSNTIPJ or in the LOCKMAX clause of the CREATE table space statement. It occurs when the LOCKSIZE parameter is specified as ANY and DB2 has escalated the lock owner to an exclusive lock of the entire table.

Tuning Tip: This situation is extremely undesirable. It is usually caused by leaving the LOCKSIZE(ANY) default. To resolve this situation, consider changing the parameter to LOCKSIZE(PAGE) or in special situations, LOCKSIZE(ROW), which causes the offending application to take the -904 unavailable resource error rather than cause general unavailability to the rest of the users. This situation is almost always caused by application failure to commit in a timely fashion and can be resolved by application code changes as well as by DBA action.

DRAIN REQUESTS

Number of drain requests (QTXADRNO)

This counter is incremented each time a utility or command requests a serialization against a page set resource.

Tuning Tip: This number is of significance in determining the amount of utility and command activity that requests some serial access to a resource.

DRAIN REQUESTS FAILED

Number of unsuccessful drain requests (QTXADRUN)

This counter is incremented when a potential drainer (utility or command) cannot obtain use of a page set because the claim count has not dropped to zero within the utility timeout value set in IRLMWRT of DSNZPARM.

Tuning Tip: This number is of significance in determining the number of unsuccessful utility and command processes due to user activity in the resource.

CLAIM REQUESTS

Number of claim requests (QTXACLNO)

This number is incremented every time a user executes an SQL statement that increments the use count of a table space, partition, or q space data set. This number gives an overall level of SQL activity in this system.

CLAIM REQUESTS FAILED

Number of unsuccessful claim requests (QTXACLUN)

This number is incremented every time a user issues a request for a claim to an SQL resource but cannot acquire it, usually because a utility or command DRAIN is on the object being sought.

Tuning Tip: This number is of some significance in determining contention between SQL and other types of utilities or commands.