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.
