BLOCKING_THREADS table function

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

To query a table function, you must use a privilege set of the process that includes one of the following privileges or authorities. Database privilege or authority must be held for each database specified or for each database containing the specified table, index, or space.

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.

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

Table 1. 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 (27)	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	CHAR (8)	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 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 Continues in next row.
SUBTYPE (cont.)		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	CHAR (8)	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 (274)	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.
INFO (cont.)
INFO (cont.)

Notes:

n refers to the n-th item after n-1 items that are not empty strings.
This message refers to all of the objects specified in table-list, index-list and space-list. This message will be returned when there are no blockers to report across the set of input objects.
p refers to the p-th character in the option parameter.
For the -DISPLAY BLOCKERS command, the NAME column in the command output contains the qualifier of the object if the qualifier is invalid. Otherwise, the NAME column contains the name of the object. For the BLOCKING_THREADS table function, the OBJECT_NAME column contains the qualifier of the object if the qualifier is invalid. Otherwise, the OBJECT_NAME column contains the name of the object.
m can be one of the following:

1

The qualifier is missing.

2

The qualifier is too long.

3

The qualifier contains an invalid character.

4

The name starts with an invalid character.

5

The name is too long.

6

The name contains an invalid character.

7

Incomplete specification. The qualifier, name, period, or escape character is missing.
A complete report cannot be provided. Decrease the number of objects that are specified as input.
This diagnostic message indicates that the table space listed in the message has been stopped when an access was attempted. User can start the table space and re-run the DISPLAY BLOCKERS command or the BLOCKING_THREADS table function.

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'));