-DISPLAY BLOCKERS command (Db2)

The -DISPLAY BLOCKERS command displays locks and claims that active threads hold against the databases, tables, indexes, or spaces that are specified in the command.

Abbreviation: -DIS BL

Environment for -DISPLAY BLOCKERS

This command can be issued from a z/OS® console, a DSN session under TSO, a DB2I panel (DB2COMMANDS), an IMS or CICS terminal, or a program using the instrumentation facility interface (IFI).

Authorization for -DISPLAY BLOCKERS

To execute this command, you must use a privilege set of the process that includes one of the following authorities. For a database privilege or authority, it must be held for each database that is 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.

Db2 commands that are issued from a logged-on z/OS console or TSO SDSF can be checked by Db2 authorization using primary and secondary authorization IDs. A logged-on z/OS user ID must be defined in RACF or a similar security server.

Syntax for -DISPLAY BLOCKERS

object-spec:

Notes:

¹ At least one option is required, and if specified, this option can only be specified once.

table-spec:

index-spec:

space-spec:

Option descriptions for -DISPLAY BLOCKERS

(database-name,…)

Identifies one or more databases that you want to display information for. The dbname and dbstring variables can have any of the forms that are listed in the following table, where dbname1 and dbname2 represent any 1- to 8-character string, and dbname represents any 1- to 7-character string:

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`
`dbstring1*dbstring2`	All database with names that begin with `dbstring1` and end with `dbstring2`

(*)

Displays information about all databases that are defined to the Db2 subsystem for which the privilege set of the process has the required authorization.

Start of change If * is specified in a comma-separated list, it must be the first item in the list. End of change

FL 508 TABLE

Specifies that you want to display information about one or more tables.

Abbreviation: TB

(table-schema.table-name, ...

One or more tables can be specified, separated by commas or blanks. Table names must be qualified by schema. 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 be folded to become upper case. The tables specified must not identify an of the following objects:

Declared global temporary table
Created global temporary table
Alias
View

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

FL 508 INDEX

Specifies that you want to display information about one or more indexes.

Abbreviation: IX

(index-schema.index-name, ...

One or more indexes can be specified, separated by commas or blanks. Index names must be qualified by schema. Pattern matching and range specification are not supported. Special characters, such as colon (:), asterisk (*), and underscore (_) are treated as part of a schema or index name. Lower case characters will be folded to become upper case. Schema must not be SESSION.

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

FL 508 SPACENAM

Specifies that you want to display information about one or more spaces.

Abbreviation: SPACE, SP

(database-name.space-name, ...

One or more table space or index space names can be specified, separated by commas or blanks. Table space and index space names must be qualified by database. Pattern matching and range specification are not supported. Special characters, such as colon (:), asterisk (*), and underscore (_) are treated as part of a database or space name. Lower case characters will be folded to become upper case.

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

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

FL 508 DDLONLY

Specifies that only certain lock types involved in a DDL statement will be processed and returned in output. These lock types could be obtained by any DDL statement execution.

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

DETAIL

Displays additional report information about each lock or claim. If DETAIL is not specified, a basic summary report is produced that displays a maximum of 8,000 locks and claims. When DETAIL is specified, the report displays a maximum of 1,000 locks and claims.

Output for -DISPLAY BLOCKERS

The DSNT369I message displays output from the DISPLAY BLOCKERS command. The output consists of information about the specified set of databases, tables, indexes, and spaces and the objects associated with each.

The output from this command has the following general structure. In your output, some of these lines might be included more than once and some might not be included at all. Which lines are included depends on the output being displayed.

The output begins with the following lines:

DSNT360I - ***********************************************************
DSNT369I - *  DISPLAY BLOCKERS SUMMARY REPORT
           *  report-time
DSNT360I - ***********************************************************
DSNT397I -

After line DSNT397I, information that is related to the requested databases is displayed.

When the DISPLAY BLOCKERS command is issued, the output that follows line DSNT397I has the following general format. Each character string is displayed with a maximum length of eight characters.

T    LT         DBNAME  DBID  NAME   OBID  AGE (SHORT)   TOKEN  MEMBER
---- ---------  ------  ----  ----   ----  -----------   -----  ------  
type subtype    dbname  dbid  name¹ obid  age-short   token  member

An object name or package name ending with an asterisk (*) indicates that the full name exceeds eight characters in length.

Without the DETAIL option, the command displays a maximum of 5,000 locks and claims.

Displaying blockers for a database

When the DISPLAY BLOCKERS command is issued with database input, the output that follows line DSNT397I has the following general format. Each character string is displayed with a maximum length of eight characters.

T    LT         DBNAME  DBID  NAME   OBID  AGE (SHORT)   TOKEN  MEMBER
---- ---------  ------  ----  ----   ----  -----------   -----  ------  
type subtype    dbname  dbid  name   obid  age (short)   token  member

FL 508 Displaying blockers for a table, index, or space

When you specify a TABLE, INDEX, or SPACENAM, the output that follows DSNT3971I has the following general format. Each character string is displayed with a maximum length of eight characters.

T     LT         DBNAME  DBID  NAME  O        OBID  AGE (SHORT)  TOKEN  MEMBER
----  ---------  ------  ----  ----  -------  ----  -----------  -----  ------
type  subtype    dbname  dbid  name  objtype  obid  age (short)  token  member

DETAIL option

When you specify the DETAIL option, the output begins with the following lines instead:

DSNT360I - ***********************************************************
DSNT369I - *  DISPLAY BLOCKERS DETAILED REPORT
           *  report-time
DSNT360I - ***********************************************************
DSNT397I -

The output that follows line DSNT397I includes the following additional information.

                CONNID: conn-id
                CORRID: corr-id
                AGE: age
                USERID: userid
                LUWID: luw-identifier
                OBJQUALIFIER: object-qualifier
                OBJNAME: object-name ¹
                PKGNAME: package-name ¹
                STATE: state
                DURATION: duration
                PAGE/RID: page-or-rid
                ACQUIRED: timestamp

An object name or package name that exceeds eight characters in length is displayed in its entirety. Up to three lines of output might be required to display a full name. If more than one line is required, the name is enclosed in single quotation marks (').

With the DETAIL option, the command displays a maximum of 1,000 locks and claims.

Error messages and informational messages might also be included in your output. The following messages may be included:

If the number of locks or claims exceeds the maximum number that can be displayed, message DSNT306I is issued.
FL 508 If the DETAIL keyword is specified with no database, table, index, or space, message DSN9010I is issued.
FL 508 If a database name is specified with one or more INDEX, TABLE, or SPACENAM keywords, message DSN9011I is issued.
FL 508 If keywords are specified that are not supported by the current function level, message DSNT758II.
If the input database name parameters exceed 128 characters in length. message DSN9041I is issued. Each parameter specified and the commas separating the parameters are counted in the 128 character length. DISPLAY BLOCKERS command processing stops if this situation occurs.

For more information about error and information messages that could be included in your output, read the INFO column information in BLOCKING_THREADS table function.

report-type

The type of report, SUMMARY or DETAILED.

report-time

The date and time when the report was generated.

T type

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.
E: Indicates that an error occurred.

LT subtype

The subtype of the lock or claim that is held by a blocking thread. For locks, the possible values are:

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

10: Table lock for segmented table space
12: Package lock
14: Cursor stability drain lock
15: Repeatable read drain lock
16: Write drain 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

For claims, the possible values are:

20: WR claim
40: RR claim
60: RR, WR claim
80: CS claim
A0: CS, WR claim

DBNAME dbname

The name of the database that contains the lock

DBID dbid

The database identifier (DBID) for the database

NAME name

The name of the object (table, index, or table space) that contains the lock.

FL 508 O objtype

The object type. Objtype can have one of the following values:

G: Table space page set descriptor
F: Table space file descriptor
T: Table
X: Index page set descriptor
I: Index fan set descriptor
D: Database
S: Table space or index space specified in space_list

OBID obid

FL 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.¹
F: The internal identifier of the table space file object descriptor.²
G: The internal identifier of the table space page set object descriptor.²
I: The internal identifier of the index fan set object descriptor.¹
X: The internal identifier of the index page set object descriptor.²
D: This column will be zero.
P: The internal identifier of the object descriptor of the object that a package depends on.

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.

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

AGE (SHORT) age-short

AGE age

The elapsed time between the beginning of the thread and the execution of the DISPLAY BLOCKERS command, with the following abbreviated keywords: D for days, H for hours, M for minutes, and S for seconds. Microseconds are represented by up to six decimal digits.

The AGE (SHORT) value is displayed in a shortened format that consists of the two most significant duration values. For example, if a lock has been held for 3 days, 17 hours, 23 minutes, 19 seconds, and 12.49038 microseconds, the displayed AGE value is 3 D 17 H. The locks are still ordered by AGE value from longest to shortest, even if multiple locks have the same shortened AGE value.

In the DETAIL output, the entire AGE value is displayed with a maximum length of 27 bytes.

TOKEN token

The token that is assigned to the thread or blanks for a thread with no token.

MEMBER member

The name of the Db2 data sharing member if applicable. Blanks are displayed if Db2 subsystem is not a data sharing member.

CONNID: conn-id

A connection identifier for the thread. conn-id can have one of the following values:

BATCH: The thread is from a batch region.
TSO: The thread is from a TSO terminal.
UTILITY: The thread is from a utility.
IMS identifier: The thread is from an IMS terminal.
CICS identifier: The thread is from a CICS® terminal.
CONSOLE: The thread is from the console.
The connection name of the thread at the requesting location: The thread is a distributed database access thread that uses system-directed access and or application-directed access from a Version 3 or later Db2 requester.
SERVER: The thread is a distributed database access thread that uses application-directed access from a non-Db2 requester.

CORRID: corr-id

A correlation identifier of the thread that is associated with the space name. corr-id can have one of the following values:

job name: The JCL job name. This value is displayed if the thread is from a batch region.
TSO logon identifier: The TSO logon user ID. This value is displayed if the thread is from a TSO terminal.
pst#.psbname: This value is displayed if the thread is from an IMS terminal.
entry ID.thread number.tran id: This value is displayed if the thread is from a CICS terminal. See Troubleshooting Db2 from CICS.
CONSOLE: This value is displayed if the thread is from the console.
0nn: This value is displayed if the thread is for a system agent. nn is a number.
028.DBAAnn: This value is displayed if the thread is a distributed database access thread that is being created. nn ranges from 01 to 99. This value indicates that the database access agent (DBAA) is in its connection processing. This processing consists of establishing the DBAA thread and validating and verifying the user's ID.
All RACF® requests are serialized on a single Db2 RACF service task, which might increase the validation time when multiple DBAAs are being created concurrently.
The correlation ID of the thread at the requesting location: This value is displayed for an existing distributed database access thread that uses system-directed access or application-direct access from a Db2 requester.
extnam: The first 12 characters in the DDM external name (EXTNAM) parameter of the DDM EXCSAT command that is received as part of the SQL CONNECT. This value is displayed for an existing distributed database access thread that uses application-directed access from a requester that is not Db2.

USERID: user-id

The user ID, which can have one of the following values:

TSO logon identifier: The TSO logon user ID. This value is displayed if the thread is from a TSO terminal.
AXBUSER: This value is displayed if the thread is from an IMS message-driven region and RACF is used.
sign-on ID or LTERM: The user ID that was used to sign on the terminal that is attached to IMS or the logical terminal (LTERM) value. This value is displayed if the thread is from an IMS message-drive region.
PSB name: The program specification block (PSB) name. This value is displayed if the thread is from an IMS nonmessage-driven region and RACF is not used.
user-id, term id, or tran id: The user ID, terminal ID, transaction ID, or other ID, as specified in the resource control table (RCT) if the thread is from CICS.

LUWID:luw-identifier

The logical-unit-of-work identifier of the thread. If the LUW-ID is an '*', the thread has no LUW-ID. If it is not an '*', the value is a logical-unit-of-work-id that is assigned to the thread followed by an '=' sign and a token value, which can be used in place of logical-unit-of-work-id in any Db2 command that accepts logical-unit-of-work-id as input.

FL 508 OBJQUALIFIER: objqualifier

The schema of a table or index for OBJTYPEs ‘T’ and ‘I’. For other object types, the object qualifier is displayed in the DBNAME report column of the summary row.

STATE: state

The state of the lock that is held by a blocking thread. Possible values are:

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

DURATION: duration

The duration of the lock or claim that is held by a blocking thread. For locks, the possible values are:

20: Manual
21: Manual+1
40: Commit
41: Commit+1
60: Allocation
80: Plan
81: Utility
FE: Interest

For claims, the possible values are:

CM: Commit
CH: Cursor hold
AL: Allocation

PAGE/RID: page-or-rid

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 data set number if the lock is a data set lock
The partition number if the lock is a partition lock

ACQUIRED: timestamp

The time when the lock or claim was acquired

The output ends with the following lines:

******* DISPLAY BLOCKERS ENDED      **********************
DSN9022I - DSNTDDIS 'DISPLAY BLOCKERS' NORMAL COMPLETION

Examples for -DISPLAY BLOCKERS

Displaying summary information about locks and claims that are held against specified databases

The following command displays a basic summary report about locks and claims that are held against the Db2 catalog and directory databases.

-DISPLAY BLOCKERS (DSNDB06,DSNDB01)