DB2 DISPLAY DATABASE LOCKS command

Using this DB2 command you can visualize the status and the locks currently held at a database, table space, or index space level.

The syntax of the command is:

DISPLAY DATABASE(db) SPACENAM(sp) LOCKS

where db is the database name and sp is the table space or index space name. The option SPACENAM is optional. If not used or if SPACENAM(*) is specfied, DB2 displays lock information for all table spaces and index spaces in the database.

For example, Figure shows the display of DISPLAY DATABASE LOCKS command.

Message DSNT362I in Figure displays the overall status of the database, which can hold one of the following values:

• RO, the database is started for read-only activity.

• RW , the database is started for read and write activity.

• STOP, the database is stopped.

• STOPP, the database is in stop pending status or stop is in progress.

• UT, the database is started for utility processing only.

Message DSNT397I in Figure displays all the lock-related information at a table space or index space level:

• NAME is the name of table space or index.

• TYPE is the type of DB2 object being displayed and can hold one of two values:

− TS, for table space

− IX, for index.

• PART refers to the partition number in a partitioned table space or index and is blank for a nonpartitioned table space or index.

• STATUS shows the current status of the table space or index and can hold one

of the following values:

− RW , the object is started for read and write activity.

− RO, the object is started for read-only activity.

− STOP, the object is stopped.

− STOPP, the object is in stop pending status or stop is in progress.

− STOPE, the object is implicitly stopped for a problem with the log relative byte address in a page, indicating inconsistency.

− REST, the object is being restarted.

− CHKP, the object is in check pending status.

− COPY, the object is in copy pending status. An image copy is required for this object.

− RECP, the object is in recovery pending status.

− RECP*, the logical partition is in recovery pending status, so the entire index is inaccessible to SQL. The logical partition needs to be recovered.

− PSRCP, the index is in a page set recovery pending status.

− UT, the object is started for utility processing only.

− UTRO, a utility is in process on the object that allows read-only access.

− UTRW, a utility is in process on the object that allows read and write access.

− UTUT, a utility is in process on the object that allows access only to utilities.

− LPL, the object has pages or ranges of pages that are unavailable because of logical or physical damage.

− LSTOP, the logical partition is stopped.

• CONNID is the connection identifier for the thread and can be one of the following:

− BATCH, if the thread is from a batch region.

− TSO, if the thread is from a TSO terminal.

− UTILITY, if the thread is from a utility.

− An IMS identifier, if the thread is from an IMS terminal.

− A CICS identifier, if the thread is from a CICS terminal.

− CONSOLE if the thread is from the console.

CORRID is the correlation identifier of the thread associated with the space name and can be one of the following:

− A job-name if the thread is from a batch region.

− A TSO logon identifier if the thread is from a TSO terminal.

− A PST#.PSBNAME, if the thread is from an IMS terminal.

− An entry ID.thread number.tran id if the thread is from a CICS terminal.

− CONSOLE, if the thread is from the console.

• LOCKINFO is the most important in terms of locking information. The information provided in this column can be classified in four types:

− Lock qualifier

- H, if the lock is held by the process.

- W, if the process is waiting for the lock.

− Lock identifier shows the type of lock:

- IS, a lock with read intentions.

- IX, a lock with write intentions.

- S, a lock with read-only capability.

- U, a lock with update capability.

- SIX, a lock with a protocol that does not lock a page while reading, but locks the page with update intention while updating.

- X, an exclusive lock.

− Lock object shows the object being locked:

- S, a table space lock.

- T, a table lock.

- C, a cursor-stability-read drain lock.

- R, a repeatable-read drain lock.

- W, a write drain lock.

- P, a partition lock.

− Lock duration shows when the lock is freed if it is held (lock qualifier H), and the position in the wait queue if the process is waiting for the lock (lock qualifier W):

- A, lock is freed at deallocation time (End of application).

- C, lock is freed at commit time.

- H, lock is freed when all cursors are closed (Held across commit).

- M, lock is freed by the system (Manual).

- P, lock is freed when the plan is complete.