Locking (BACCTDR)

TIMEOUT

Number of lock timeouts (QTXATIM)

This number is incremented every time a DB2 thread waits to get a resource longer than the timeout interval. It is specified with the 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.

DEADLOCK

Number of deadlocks (QTXADEA)

This counter 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 job log. In well-tuned systems, this number should be low. The most frequent cause of deadlock situations are ascending key indexes. The Type 2 indexes can resolve many of these problems because no index locks are taken.

ESCAL (SHR)

Number of times the maximum page locks per table space are exceeded, and the table space lock escalates from a page lock (IS) to a table space lock (S) for this thread (QTXALES)

This counter 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 the number set in the LOCKMAX clause of the CREATE TABLESPACE statement.

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

ESCAL (EXCL)

Number of times the maximum page locks per table space are exceeded and the table space lock escalates from a page lock (IX) to a table space lock (X) (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 TABLESPACE 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, 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). This will cause the offending application to take the -904 unavailable resource error rather than cause general unavailability to the rest of the users. This 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.

MAX LOCKS HELD

Maximum number of page or row locks held (QTXANPL)

This value represents the highest number of page or row locks held during a plan execution.

Tuning Tip: This number may not exceed the NUMLKUS (number of locks per user) count in DSNZPARM. If it does, the user will get a -904 resource unavailable message with a 00C90096 reason code. This number is a significant tuning knob which should be observed when migrating an application from one system to another, particularly if the target DB2 has a different number for the maximum locks threshold. The application can free locks by committing resources more often. If LOCKSIZE(ROW) is specified, DB2 holds a lock for each row on the page. Depending on the row size, this can be extremely costly. Consider the cost of row-level locking carefully before implementing.

LOCK REQ

Lock request count (QTXALOCK)

In the DB2 Accounting Lock/Latch Report, this value is the average number of lock requests per plan execution. 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, because latches execute totally within DB2.

UNLOCK REQ

Unlock request count (QTXAUNLK)

This counter is incremented when the application has finished processing the page or row or when a claim or drain can be released.

Tuning Tip: This amount is significant as to cross memory processing and reflects the normal release of resources.

QUERY REQ

Query request count (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 but does not include lock avoidance techniques.

CHANGE REQ

Change request count (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).

OTHER REQUEST

Other IRLM request count (QTXAIRLM)

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

LOCK SUSP

Number of suspends due to lock conflict (QTXASLOC)

In the DB2 Accounting Overview Report and the DB2 Accounting Lock/Latch Report, this value is the average number of suspends due to lock conflict per plan execution. This counter is incremented any time a thread has a conflicting lock request, such as an updater requesting exclusive access to a page another thread is using.

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 can minimize suspensions. If suspensions cause frequent timeouts, consider row-level locking.

LATCH SUSP

Number of suspends due to latch conflicts (QTXASLAT)

In the DB2 Accounting Lock/Latch Report, this value is the average number of times suspended due to latching per plan execution. This number is incremented when a latch conflict exists between two DB2 threads or internal serialization processing takes place.

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

OTHER SUSP

Number of suspends due to other conflicts (QTXASOTH)

In the DB2 Accounting Lock/Latch Report, this value is the average number of times suspended due to other reasons per plan execution. This number is incremented when DB2 internal processes collide.

Tuning Tip: This number is not generally of significance in tuning.

TOTAL SUSP

Total number of suspensions due to lock conflicts, latch conflicts, and other conflicts (QTXASLOC + QTXASLAT + QTXASOTH)