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, tables, indexes, or spaces that are specified in the input parameter and the other objects in the hierarchy of the specified objects.

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

Read syntax diagramSkip visual syntax diagramBLOCKING_THREADS(dbnametable-listNULLone or more blanksempty string,index-listNULLone or more blanksempty string,space-listNULLone or more blanksempty string,optionsNULLone or more blanksempty string)

table-list:

Read syntax diagramSkip visual syntax diagram,table-schema.table-name

index-list:

Read syntax diagramSkip visual syntax diagram,index-schema.index-name

space-list:

Read syntax diagramSkip visual syntax diagram,database-name.space-name

The schema is SYSIBMADM.

Start of changeFL 508 dbnameEnd of change
Start of changeAn 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
*dbstring1*dbstring2* All databases with names that contain the strings dbstring1 and dbstring2
End of change
Start of changeFL 508 table-listEnd of change
Start of changeAn expression that returns a value that is castable to a CLOB data type with an SBCS or MIXED subtype. The expression must have an actual length that is no greater than 65536 bytes. The value of the expression must refer to a list of tables for which information is to be displayed. This is a required positional parameter.

Null, blank, or an empty string are allowed. Pattern matching and range specification is not supported. Special characters such as colon (:), asterisk (*), and underscore (_) are treated as part of a schema or table name. Lower case characters will not be folded to become upper case.

More than one table can be listed separated by commas. Each table is qualified by its schema name.

The tables specified must not identify any of the following objects:
  • Declared global temporary tables
  • Created global temporary tables
  • Aliases
  • Views

Scope of processing: the table, the table space that contains the table, the database that contains the table and indexes in the table.

End of change
Start of changeFL 508 index-listEnd of change
Start of changeAn expression that returns a value that is castable to a CLOB data type with SBCS or MIXED subtype. The expression must have an actual length that is no greater than 65536 bytes. The value of the expression must refer to a list of indexes for which information is to be displayed. This is a required positional parameter.

Null, blanks, or empty strings are allowed. Pattern matching and range specification are not supported. Special characters such as colon (:), asterisk (*), and underscore (_) are treated as part of a schema or table name. Lower case characters will not be folded to become upper case.

More than one index can be listed separated by commas. Each index is qualified by its schema name. The index schema cannot be SESSION.

Scope of processing: the index, the table and table space associated with the index, and the database that contains the index.

End of change
FL 508 space-list
An expression that returns a value that is castable to a CLOB ta type with SBCS or MIXED subtype. The expression must have an actual length that is no greater than 65536 bytes. The value of the expression must refer to a list of table spaces or index spaces for which information is to be displayed. This is a required positional parameter.

Null, blanks, or empty strings are allowed. Pattern matching and range specification are not supported. Special characters such as colon (:), asterisk (*), and underscore (_) are treated as part of a schema or table name. Lower case characters will not be folded to become upper case.

More than one table space or index space can be listed separated by commas. Each table or index space is qualified by its database name.

Scope of processing (index space): the index, the table and table space associate with the index, and the database that contains the index.

Scope of processing (table space): the table space, the database that contains the table space, the table in the table space, and the indexes associated with the table in the table space.

Start of changeFL 508 optionsEnd of change
Start of changeAn expression that returns a value that is castable to a CLOB data type with an SBCS subtype. The expression must have an actual length that is no greater than 65536 bytes. This is a required positional parameter.

Null, blanks, or empty strings are allowed. Lower case characters will not be folded to become upper case.

Possible values of the expression:

FL 508
DDLONLY
Specifies that only certain lock types involved in a DDL statement will be processed and returned in the output. These lock types could be obtained by any DDL statement execution. If DDLONLY is not specified, all possible lock types will be processed and returned in the output.
The lock types included when DDLONLY is specified are:
  • Database locks (DBID)
  • Page set locks
  • Data set locks (partition)
  • Lock-specific partitions
  • Page set or data set open locks
  • Database exception table (DBET) entry locks
  • Table locks for segmented table spaces
  • Package locks
  • Cursor stability drain locks
  • Repeatable read drain locks
  • Write drain locks
  • Load database definition (DBD) locks
End of change
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 Start of changeVARCHAR (27)End of change 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 Start of changeCHAR (8)End of change 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
Start of change14End of change
Start of changeCursor stability drain lockEnd of change
Start of change15End of change
Start of changeRepeatable read drain lockEnd of change
Start of change16End of change
Start of changeWrite drain lockEnd of change
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
Start of changeFL 508 The internal identifier of the object descriptor depending on the value for OBJECT_TYPE. When OBJECT_TYPE is:
T
The internal identifier of the table object descriptor.1
F
The internal identifier of the table space file object descriptor.2
G
The internal identifier of the table space page set object descriptor.2
I
The internal identifier of the index fan set object descriptor.1
X
The internal identifier of the index page set object descriptor.2
D
This column will be zero.
P
The internal identifier of the object descriptor of the object that a package depends on.
End of change
The high order bit is always off in the OBID columns in the catalog. Alternatively, you can identify the object using OBJECT_QUALIFIER and OBJECT_NAME.
  1. The high order bit is off if the descriptor is for the base instance. The high order bit is on if the descriptor is for the clone instance.
  2. The high order bit is off if the descriptor is for the first instance of the object. The high order bit is on if the descriptor is for the second instance of the object.
DBNAME Start of changeCHAR (8)End of change The database name
OBJECT_QUALIFIER VARCHAR (128)
Start of changeFL 508 The meaning of OBJECT_QUALIFIER depends on the value for OBJECT_TYPE. When OBJECT_TYPE is:
T
The schema of the table
F or G
The database name of the table space
I
The schema of the index
X
The database name of the index
D
This column will contain NULL or blank.
P

The qualifier of the object that a package depends on.

End of change

Start of changeIf OBJECT_TYPE is not present, OBJECT QUALIFIER is the database name of a table space or the schema of a table or index.End of change
OBJECT_NAME VARCHAR (128)
Start of changeFL 508 The meaning of OBJECT_NAME depends on the value for OBJECT_TYPE. When OBJECT_TYPE is:
T
The name of the table
F or G
The name of the table space
I
The name of the index
X
The name of the index space
D
This column will contain NULL or blank.
P

The name of the object that a package depends on.

End of change

Start of changeIf OBJECT_TYPE is not present, OBJECT QUALIFIER is the name of a table space, table, or index.End of change
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 Start of changeVARCHAR (274)End of change 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.
Start of change
E
Error messages. Possible types are:
  • PROCESSING TERMINATED DUE TO ERROR
  • DIAGNOSTIC MESSAGES ISSUED, INCOMPLETE REPORT
  • INVALID OPTION SPECIFIED: POSITION P3
  • FL 508 INVALID TABLE: ITEM n REASON m.1, 4, 5
  • FL 508 INVALID INDEX: ITEM n REASON m.1, 4, 5
  • FL 508 INVALID SPACE: ITEM n REASON m.1, 4, 5
  • NO TABLES, INDEXES, OR SPACES SPECIFIED
  • OBJECT NOT SUPPORTED: ITEM n1
End of change
INFO (cont.)   Start of change
E (cont.)
  • FL 508 TABLE NOT FOUND: ITEM n.1
  • FL 508 INDEX NOT FOUND: ITEM n.1
  • FL 508 SPACE NOT FOUND: ITEM n.1
  • UNABLE TO PROCESS SYSPACKDEP.BNAME
  • UNABLE TO PROCESS SYSPAKDEP.BQUALIFIER
  • FL 508 INSUFFICIENT PRIVILEGE ON TABLE NOT FOUND: ITEM n.1
  • FL 508 INSUFFICIENT PRIVILEGE ON INDEX NOT FOUND: ITEM n.1
  • FL 508 INSUFFICIENT PRIVILEGE ON SPACE NOT FOUND: ITEM n.1
  • USER IS NOT AUTHORIZED TO ACCESS THE DATABASE
  • DATABASE DOES NOT EXIST
  • NO DATABASES FOUND
  • MISSING PH16439 - AGE VALUES MAY BE UNKNOWN
  • MISSING PH16439 - TIMESTAMP AND AGE VALUES MAY BE UNKNOWN
  • MISSING PH16439 - REPORT INCOMPLETE
  • MISSING PH10826 - REPORT INCOMPLETE
End of change
INFO (cont.)  
Start of change
I
Informational messages. Possible types are:
  • IRLM OUTPUT OVERFLOW, INCOMPLETE REPORT6
  • CLAIMERS REPORT OVERFLOW, INCOMPLETE REPORT6
  • INSUFFICIENT PRIVILEGE ON 1 OR MORE DATABASES
  • IRLM PTF UO03483 NOT FOUND, OPTIMIZATION DISABLED
  • NO LOCKS OR CLAIMS FOUND.2
  • NO LOCKS OR CLAIMS FOR THE DATABASE
D
Diagnostic and serviceability information. The process encountered an unexpected problem. The report might not be complete. The diagnostic message can look like this:
  • DSNISETO:000F 00C90081 DSNDB06.SYSTSTAB7
End of change
Start of changeFL 508 OBJECT_TYPEEnd of change Start of changeCHAR(1)End of change Start of changeThe type of the object identified by DBID, OBID, OBJECT_QUALIFIER, and OBJECT_NAME:
T
Table
I
Index fan set descriptor
F
Table space file descriptor
G
Table space page set descriptor
X
Index page set descriptor
D
Database
S
Table space or index space specified in space_list
P
An object that a package depends on

This column is not available to BLOCKING_THREADS (dbname).

End of change
Notes:
  1. n refers to the n-th item after n-1 items that are not empty strings.

  2. 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.

  3. p refers to the p-th character in the option parameter.
  4. 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.
  5. 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.
  6. A complete report cannot be provided. Decrease the number of objects that are specified as input.
  7. 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'));
Start of changeFL 508 Retrieve information about the threads that hold locks and claims involved in a DDL statement against the specified table, index, and space and any other objects in the hierarchy of the specified objects.
   SELECT *
     FROM TABLE (SYSIBMADM.BLOCKING_THREADS('S1.TB1' , 'S2.IX2' , 'DB3.SP3' , 'DDLONLY'));
End of change