BLOCKING_THREADS

The BLOCKING_THREADS function returns a table that contains one row for each lock or claim that threads hold against the databases that are specified in the input parameter.

Authorization

The set of privileges for each specified database must include one of the following privileges or authorities:

DISPLAYDB privilege
DISPLAY privilege
DBMAINT authority
DBCTRL authority
DBADM authority
System DBADM authority
SYSOPR authority
SYSCTRL authority
SYSADM authority

For implicitly created databases, the database privilege or authority can be held on the implicitly created database or on DSNDB04.

Syntax

The schema is SYSIBMADM.

dbname

An expression that returns a value that is a built-in character string or graphic string data type that is not a LOB. The expression must have an actual length that is no greater than 128 bytes. The value of the expression must refer to a database for which information is to be displayed. Multiple databases can be specified in the following forms:

Table 1. Forms of `dbname` and `dbstring`
Form	Displays the information of...
`dbname1:dbname2`	All databases with names, in UNICODE order, that are between `dbname1` and `dbname2` inclusive
`dbname*`	All databases with names that begin with the string `dbname`
`*dbname`	All databases with names that end with the string `dbname`
`dbname`	All databases with names that contain the string `dbname`
`dbstring1dbstring2*`	All databases with names that contain the strings `dbstring1` and `dbstring2`

The result of the function is a table with the format shown in the following table. All of the columns are nullable.

Table 2. Format of the resulting table for BLOCKING_THREADS
Column name	Data type	Contains...
ACQUIRED_TS	TIMESTAMP (12)	The time when the lock or claim was acquired
AGE	VARCHAR (128)	The elapsed time between when the lock or claim was acquired and when the function was invoked, with the following abbreviated keywords: D Days H Hours M Minutes S Seconds. Microseconds are represented by up to six decimal digits.
CORRID	VARCHAR (255)	The correlation identifier of the thread. If the STATE value indicates a physical lock (P-lock), CORRID has a null value or empty string.
CONNID	VARCHAR (255)	The connection identifier of the thread. If the STATE value indicates a P-lock, CONNID has a null value or empty string.
USERID	VARCHAR (255)	The user ID of the thread. If the STATE value indicates a P-lock, USERID has a null value or empty string.
LUWID	VARCHAR (255)	The logical-unit-of-work identifier of the thread. If the STATE value indicates a P-lock, LUWID has a null value or empty string.
SSID	CHAR (4)	In data sharing, the Db2 data sharing member where the thread is running. Otherwise, this column is unused.
TYPE	CHAR (1)	The type of blocker held by the thread. TYPE can have one of the following values: L Lock C Claim I Indicates that the row is for informational purposes. The INFO column contains the information. E Indicates that an error occurred. The INFO column explains the error. D Indicates that the row is for serviceability purposes. The INFO column contains the serviceability information.
SUBTYPE	CHAR (2)	The type of lock or claim that blocking threads hold. Locks for blocking threads can have one of the following values: 00 Page lock 01 Database lock 02 Page set lock 03 Data set lock (partition) 05 Index compression lock 06 Lock-specific partition 07 Page set or data set open 08 Utility I/O damage assessment 09 Page set piece locks Continues in next row.
SUBTYPE (cont.)		0A Database exception table (DBET) entry locks 0D Buffer manager (BM) SYSLGRNG recording lock or group buffer pool (GBP) conversion dependent lock 0E Utility serialization lock 0F Mass delete lock for table 10 Table lock for segmented table space 12 Package lock 18 Row lock 1F Distributed data facility (DDF) communications database (CDB) P-lock 22 Resource limit facility (RLF) P-lock 27 DBET logical page list (LPL) or group buffer pool recovery pending (GRECP) locks 30 LOB lock 32 LPL recovery lock 36 Serialization lock for adding partitions 39 Load database definition (DBD) lock 3A Compression dictionary build lock 3B Compression dictionary load lock 41 Utility catalog access lock
SUBTYPE (cont.)		Claims for blocking threads can have one of the following values: 20 WR claim 40 RR claim 60 RR, WR claim 80 CS claim A0 CS, WR claim
DURATION	CHAR (2)	The duration of the lock or claim. Locks can have one of the following values: 20 Manual 21 Manual+1 40 Commit 41 Commit+1 60 Allocation 80 Plan 81 Utility FE Interest Claims can have one of the following values: CM Commit CH Cursor hold AL Allocation
STATE	CHAR (2)	The state in which the lock is held. Locks can have one of the following values: 01 Unprotected share 02 Intent shared 03 Intent exclusive 04 Shared 05 Update 06 Shared intent exclusive 07 Non-shared update 08 Exclusive 09 Intent exclusive P-lock 0A Intent shared P-lock 0B Shared intent exclusive P-lock
DBID	SMALLINT	The database identifier
OBID	SMALLINT	The object identifier
DBNAME	VARCHAR (24)	The database name
OBJECT_QUALIFIER	VARCHAR (128)	The schema of a table or index
OBJECT NAME	VARCHAR (128)	The name of a table, index, or table space
PAGENUM_OR_RID	VARCHAR (10)	Indicates one of the following values in hexadecimal format: The page number if the lock is a page lock The record ID if the lock is a row lock The dataset number if the lock is a dataset lock The partition number if the lock is a partition lock
PACKAGE	VARCHAR (512)	The qualified package name (COLLECTION_ID.NAME.CONTOKEN) if the lock is a package lock
CATALOG_LEVEL	VARCHAR (10)	The catalog level. This column is currently not used and reserved for future use.
INFO	VARCHAR (1024)	Informational text if TYPE is I, D, or E

The function returns an informational message (TYPE=I) for any specified databases that do not hold any locks or claims.

Examples

Retrieve information about the threads that hold locks and claims against the Db2 catalog and directory databases:

   SELECT *
     FROM TABLE (SYSIBMADM.BLOCKING_THREADS('DSNDB06,DSNDB01'));