Monitoring threads with DISPLAY THREAD commands

The DISPLAY THREAD command output displays information about threads that are processing locally and for distribute requests, stored procedures or user-defined functions executed by threads, and parallel tasks. It can also indicate that a system quiesce is in effect as a result of the ARCHIVE LOG command.

Begin general-use programming interface information.

The DISPLAY THREAD command allows you to select which type of information you want to include in the display by using one or more of the following:

Active, indoubt, postponed-abort, or pooled threads
Allied threads that are associated with the address spaces whose connection-names are specified
Allied threads
Distributed threads
Distributed threads that are associated with a specific remote location
Detailed information about connections with remote locations
A specific logical unit of work ID (LUWID)

The information that is returned by the DISPLAY THREAD command reflects a dynamic status. By the time the information is displayed, the status might have changed. Moreover, the information is consistent only within one address space and is not necessarily consistent across all address spaces.

To use the TYPE, LOCATION, DETAIL, and LUWID keywords, you must have SYSOPR authority or higher.

More information about how to interpret this output can be found in the topics describing the individual connections and in the description of message DSNV408I, which is part of message DSNV401I.

Displaying information about indoubt threads

Use the DISPLAY THREAD TYPE(INDOUBT) command to find information about allied and database access indoubt threads. This command provides information about threads where Db2 is a participant, a coordinator, or both.

About this task

Begin general-use programming interface information. The TYPE(INDOUBT) option tells you which systems still need indoubt resolution and provides the LUWIDs that you need to recover indoubt threads. A thread that has completed phase 1 of commit and still has a connection with its coordinator is in the prepared state and is displayed as part of the DISPLAY THREAD active report. If a prepared thread loses its connection with its coordinator, it enters the indoubt state and terminates its connections to any participants at that time. Any threads that are in the prepared or indoubt state when Db2 terminates are indoubt after Db2 restart. However, if the participant system is waiting for a commit or rollback decision from the coordinator, and the connection is still active, Db2 considers the thread active. End general-use programming interface information.

If a thread is indoubt at a participant, you can determine whether a commit or abort decision was made at the coordinator by issuing the DISPLAY THREAD command at the coordinator as described previously. If an indoubt thread appears at one system and does not appear at the other system, the latter system backed out the thread, and the first system must therefore do the same.

Examples

Begin general-use programming interface information.

Resetting indoubt threads with IP address and resync port number

For example, assume that you issue a DISPLAY THREAD command with TYPE(INDOUBT), and the output includes the following DSNV406I message:

DSNV406I -DB2C INDOUBT THREADS -                 
COORDINATOR          STATUS      RESET URID                 AUTHID
FFFF:10.97.217.50.   COMMITTED-H YES   0000000056765A422C57 PAULMCW
.1332

You can issue the following command to reset the indoubt unit of work by specifying the IP address (FFFF:10.97.217.50) and the resync port number of the coordinator (1332) from the message:

RESET INDOUBT IPADDR(::FFFF:10.97.217.50..1332)

Resetting indoubt threads with LUWIDs or tokens

Assume that you need to purge information about two indoubt threads. The first has an LUWID of DB2NET.LUNSITE0.A11A7D7B2057.0002, and the second has a token of 442. You can use the following command to purge the information:

-RESET INDOUBT LUWID(DB2NET.LUNSITE0.A11A7D7B2057.0002,442)

End general-use programming interface information.

Displaying information by location

You can use the DISPLAY THREAD command to display thread information for particular locations.

Procedure

Begin general-use programming interface information. To display information by location:

Issue the DISPLAY THREAD command with the LOCATION option, followed by a list of location names.

Example

For example, you can specify an asterisk (*) after the THREAD and LOCATION options:

-DISPLAY THREAD(*) LOCATION(*) DETAIL

When you issue this command, Db2 returns messages like the following:

DSNV401I - DISPLAY THREAD REPORT FOLLOWS -
DSNV402I - ACTIVE THREADS -
   NAME     ST 1 A 2    REQ ID        AUTHID   PLAN     ASID TOKEN
   SERVER   RA   *     2923 DB2BP     ADMF001  DISTSERV 0036    20 3 
    V437-WORKSTATION=ARRAKIS, USERID=ADMF001,
          APPLICATION NAME=DB2BP
    V436-PGM=NULLID.SQLC27A4, SEC=201, STMNT=210
    V445-09707265.01BE.889C28200037=203 ACCESSING DATA FOR
     (  1)2002:91E:610:1::5 4 
    V447-INDEX SESSID           A ST TIME
    V448-(  1) 446:1300 5       W S2 9802812045091
DISPLAY ACTIVE REPORT COMPLETE
DSN9022I - DSNVDT '-DISPLAY THREAD' NORMAL COMPLETION

Key: Description
1: The ST (status) column contains characters that indicate the connection status of the local site. The TR indicates that an allied, distributed thread has been established. The RA indicates that a distributed thread has been established and is in receive mode. The RD indicates that a distributed thread is performing a remote access on behalf of another location (R) and is performing an operation that involves DCE services (D). Currently, Db2 supports the optional use of DCE services to authenticate remote users.
2: The A (active) column contains an asterisk that indicates that the thread is active within Db2. It is blank when the thread is inactive within Db2 (active or waiting within the application).
3: This LUWID is unique across all connected systems. This thread has a token of 20 (it appears in two places in the display output).
4: This is the location of the partner. If the RDBNAME is not known, the location column contains either an SNA LUNAME or IP address.
5: If the connection uses TCP/IP, the SESSID column contains "local:remote", where local specifies the Db2 TCP/IP port number and remote specifies the partner's TCP/IP port number.

End general-use programming interface information.

Displaying information for non-Db2 locations

Because Db2 does not receive a location name from non-Db2 locations, you must enter the LUNAME or IP address of the location for which you want to display information.

About this task

The LUNAME is enclosed by the less-than (<) and greater-than (>) symbols. The IP address can be in the dotted decimal or colon hexadecimal format.

Db2 uses the one of the following formats in messages that display information about non-Db2 requesters:

LUNAME notation
Dotted decimal format
Colon hexadecimal format

Procedure

Begin general-use programming interface information. To display information for non-Db2 locations:

Issue the DISPLAY THREAD command with the LOCATION option.

Example

For example, if you want to display information about a non-Db2 database management system (DBMS) with the LUNAME of LUSFOS2, enter the following command:

-DISPLAY THREAD (*) LOCATION (<LUSFOS2>)

End general-use programming interface information.

Displaying conversation-level information about threads

Use the DISPLAY THREAD command with the LOCATION and DETAIL options to display information about conversation activity when distribution information is displayed for active threads.

About this task

The DETAIL option has no effect on the display of indoubt threads.

Procedure

Begin general-use programming interface information. To display conversation-level information about threads:

Issue the DISPLAY THREAD command with the LOCATION and DETAIL options:

-DISPLAY THREAD(*) LOCATION(*) DETAIL

Db2 returns the following messages, which indicate that the local site application is waiting for a conversation to be allocated in Db2, and a Db2 server that is accessed by a DRDA client using TCP/IP.

DSNV401I - DISPLAY THREAD REPORT FOLLOWS -
DSNV402I - ACTIVE THREADS -
   NAME     ST A   REQ ID           AUTHID   PLAN     ASID   TOKEN
   TSO      TR *     3 SYSADM       SYSADM   DSNESPRR 002E       2
    V436-PGM=DSNESPRR.DSNESM68, SEC=1, STMNT=116
    V444-DB2NET.LUND0.A238216C2FAE=2 ACCESSING DATA AT
     (  1)USIBMSTODB22-LUND1
    V447--INDEX SESSID           A ST TIME
    V448--(  1) 0000000000000000 N 1  A1 2  9015816504776
   TSO      RA *    11 SYSADM       SYSADM   DSNESPRR 001A      15
    V445-STLDRIV.SSLU.A23555366A29=15 ACCESSING DATA FOR
     (  1)123.34.101.98
    V447--INDEX SESSID           A ST TIME
    V448--(  1) 446:3171  3        S2    9015611253108
DISPLAY ACTIVE REPORT COMPLETE
DSN9022I - DSNVDT '-DISPLAY THREAD' NORMAL COMPLETION

Key: Description
1: The information on this line is part of message DSNV447I. The conversation A (active) column for the server is useful in determining when a Db2 thread is hung and whether processing is waiting in the network stack (VTAM® or TCP/IP) or in Db2. A value of W indicates that the thread is suspended in Db2 and is waiting for notification from the network that the event has completed. A value of N indicates that control of the conversation is in the network stack.
2: The information on this line is part of message DSNV448I. The A in the conversation ST (status) column for a serving site indicates that a conversation is being allocated in Db2. A 2 would indicate DRDA access. An R in the status column would indicate that the conversation is receiving, or waiting to receive a request or reply. An S in this column for a server indicates that the application is sending, or preparing to send a request or reply.
3: The information on this line is part of message DSNV448I. The SESSID column has changed. If the connection uses VTAM, the SESSID column contains a VTAM session identifier. If the connection uses TCP/IP, the SESSID column contains "local:remote", where local specifies the Db2 TCP/IP port number and remote specifies the partner's TCP/IP port number.

Displaying threads by LUWID

Use the optional LUWID keyword, which is valid only when DDF is started, to display threads by logical unit of work identifiers. The LUWIDs are assigned to the thread by the site that originated the thread.

About this task

Begin general-use programming interface information. You can use an asterisk (*) in an LUWID keyword, like you can use an asterisk in a LOCATION name. For example, issue the command DISPLAY THREAD TYPE(INDOUBT) LUWID(NET1.*) to display all the indoubt threads whose LUWID has a network name of NET1. The command DISPLAY THREAD TYPE(INDOUBT) LUWID(IBM.NEW*) displays all indoubt threads whose LUWID has a network name of "IBM®" and whose LUNAME begins with "NEW."

Use the DETAIL keyword with the DISPLAY THREAD LUWID command to show the status of every conversation that is connected to each displayed thread, and to indicate whether a conversation is using DRDA access.

Procedure

To display threads by LUWIDs:

Issue the DISPLAY THREAD command with the following options:

-DISPLAY THREAD(*) LUWID (luwid) DETAIL

Db2 returns the following message:

-DISPLAY THREAD(*) LUWID (luwid) DET
  DSNV401I - DISPLAY THREAD REPORT FOLLOWS -
  DSNV402I - ACTIVE THREADS -
  NAME     ST A   REQ ID           AUTHID   PLAN     ASID   TOKEN
  BATCH    TR       5 TC3923S0     SYSADM   TC392    000D       2
   V436-PGM=*.TC3923S0, SEC=1, STMNT=116
   V444-DB2NET.LUNSITE0.A11A7D7B2057=2  1 ACCESSING DATA AT
    (  1)USIBMSTODB22-LUNSITE1
   V447--INDEX SESSID           A ST TIME
   V448--(  1) 00C3F4228C5A244C   S2 2  8929612225354
  DISPLAY ACTIVE REPORT COMPLETE
  DSN9022I - DSNVDT '-DISPLAY THREAD' NORMAL COMPLETION

Key

Description

1

In the preceding display output, you can see that the LUWID has been assigned a token of 2. You can use this token instead of the long version of the LUWID to cancel or display the given thread. For example:

-DISPLAY THREAD(*) LUWID(2) DET

2

In addition, the status column for the serving site contains a value of S2. The S means that this thread can send a request or response, and the 2 means that this is a DRDA access conversation.

End general-use programming interface information.

Displaying threads by type

Use the DISPLAY THREAD command to display threads by type. For example, you can display only the active threads that are executing a stored procedure or user-defined function.

Procedure

Begin general-use programming interface information. To display threads by type:

Issue the DISPLAY THREAD command with the TYPE keyword.

For example:

-DISPLAY THREAD(*) TYPE(PROC)

Monitoring all DBMSs in a transaction

Use the DETAIL keyword of the DISPLAY THREAD command to monitor all of the requesting and serving database management systems (DBMSs) that are involved in a transaction.

About this task

Begin general-use programming interface information. For example, you could monitor an application that runs at USIBMSTODB21 requesting information from USIBMSTODB22, which must establish conversations with secondary servers USIBMSTODB23 and USIBMSTODB24 to provide the requested information. The following figure depicts such an example. In this example, ADA refers to DRDA access, and SDA refers to Db2 private protocol access. USIBMSTODB21 is considered to be upstream from USIBMSTODB22. USIBMSTODB22 is considered to be upstream from USIBMSTODB23. Conversely, USIBMSTODB23 and USIBMSTODB22 are downstream from USIBMSTODB22 and USIBMSTODB21 respectively.

Begin figure description. Relationship of transactions. End figure description. — Figure 1. Example of a Db2 transaction that involves four sites

The application that runs at USIBMSTODB21 is connected to a server at USIBMSTODB22 by using DRDA access. If you enter the DISPLAY THREAD command with the DETAIL keyword from USIBMSTODB21, you receive the following output:

-DISPLAY THREAD(*) LOC(*) DET
   DSNV401I - DISPLAY THREAD REPORT FOLLOWS -
   DSNV402I - ACTIVE THREADS -
   NAME     ST A   REQ ID           AUTHID PLAN     ASID   TOKEN
   BATCH    TR *     6 BKH2C        SYSADM YW1019C  0009       2
    V436-PGM=BKH2C.BKH2C, SEC=1, STMNT=4
    V444-USIBMSY.SSLU.A23555366A29=2 ACCESSING DATA AT
     (  1)USIBMSTODB22-SSURLU
    V447--INDEX SESSID           A ST TIME
    V448--(  1) 0000000300000004 N R2 9015611253116
   DISPLAY ACTIVE REPORT COMPLETE
  11:26:23 DSN9022I - DSNVDT '-DISPLAY THREAD' NORMAL COMPLETION

This output indicates that the application is waiting for data to be returned by the server at USIBMSTODB22.

The server at USIBMSTODB22 is running a package on behalf of the application at USIBMSTODB21 to access data at USIBMSTODB23 and USIBMSTODB24 by Db2 private protocol access. If you enter the DISPLAY THREAD command with the DETAIL keyword from USIBMSTODB22, you receive the following output:

-DISPLAY THREAD(*) LOC(*) DET
   DSNV401I - DISPLAY THREAD REPORT FOLLOWS -
   DSNV402I - ACTIVE THREADS -
   NAME     ST A   REQ ID           AUTHID PLAN     ASID   TOKEN
   BATCH    RA *     0 BKH2C        SYSADM YW1019C  0008       2
    V436-PGM=BKH2C.BKH2C, SEC=1, STMNT=4
    V445-STLDRIV.SSLU.A23555366A29=2 ACCESSING DATA FOR
     (  1)USIBMSTODB21-SSLU
    V444-STLDRIV.SSLU.A23555366A29=2 ACCESSING DATA AT
     (  2)USIBMSTODB23-OSSLU
     (  3)USIBMSTODB24-OSSURLU
    V447--INDEX SESSID           A ST TIME
    V448--(  1) 0000000300000004   S2 9015611253108
    V448--(  2) 0000000600000002   S1 9015611253077
    V448--(  3) 0000000900000005 N R1 9015611253907
   DISPLAY ACTIVE REPORT COMPLETE
  11:26:34 DSN9022I - DSNVDT '-DISPLAY THREAD' NORMAL COMPLETION

This output indicates that the server at USIBMSTODB22 is waiting for data to be returned by the secondary server at USIBMSTODB24.

The secondary server at USIBMSTODB23 is accessing data for the primary server at USIBMSTODB22. If you enter the DISPLAY THREAD command with the DETAIL keyword from USIBMSTODB23, you receive the following output:

-DISPLAY THREAD(*) LOC(*) DET
   DSNV401I - DISPLAY THREAD REPORT FOLLOWS -
   DSNV402I - ACTIVE THREADS -
   NAME     ST A   REQ ID           AUTHID PLAN     ASID   TOKEN
   BATCH    RA *     2 BKH2C        SYSADM YW1019C  0006       1
    V445-STLDRIV.SSLU.A23555366A29=1 ACCESSING DATA FOR
         (  1)USIBMSTODB22-SSURLU
    V447--INDEX SESSID           A ST TIME
    V448--(  1) 0000000600000002 W R1 9015611252369
   DISPLAY ACTIVE REPORT COMPLETE
  11:27:25 DSN9022I - DSNVDT '-DISPLAY THREAD' NORMAL COMPLETION

This output indicates that the secondary server at USIBMSTODB23 is not currently active.

The secondary server at USIBMSTODB24 is also accessing data for the primary server at USIBMSTODB22. If you enter the DISPLAY THREAD command with the DETAIL keyword from USIBMSTODB24, you receive the following output:

-DISPLAY THREAD(*) LOC(*) DET
   DSNV401I - DISPLAY THREAD REPORT FOLLOWS -
   DSNV402I - ACTIVE THREADS -
   NAME     ST A   REQ ID           AUTHID PLAN     ASID   TOKEN
   BATCH    RA *     2 BKH2C        SYSADM YW1019C  0006       1
    V436-PGM=*.BKH2C, SEC=1, STMNT=1
    V445-STLDRIV.SSLU.A23555366A29=1 ACCESSING DATA FOR
     (  1)USIBMSTODB22-SSURLU
    V447--INDEX SESSID           A ST TIME
    V448--(  1) 0000000900000005   S1 9015611253075
   DISPLAY ACTIVE REPORT COMPLETE
  11:27:32 DSN9022I - DSNVDT '-DISPLAY THREAD' NORMAL COMPLETION

This output indicates that the secondary server at USIBMSTODB24 is currently active.

The conversation status might not change for a long time. The conversation could be hung, or the processing could be taking a long time. To determine whether the conversation is hung, issue the DISPLAY THREAD command again and compare the new timestamp to the timestamps from previous output messages. If the timestamp is changing, but the status is not changing, the job is still processing. If you need to terminate a distributed job, perhaps because it is hung and has been holding database locks for a long time, you can use the CANCEL DDF THREAD command if the thread is in Db2 (whether active or suspended). If the thread is in VTAM, you can use the VARY NET TERM command. End general-use programming interface information.

Accessible long description of graphic

Begin detailed description.

This figure depicts four requesting and serving DBMSs. These DBMSs are named USIBMSTODB21, USIBMSTODB22, USIBMSTODB23, and USIBMSTODB24.

USIBMSTODB21 connects to USIBMSTODB22 through DRDA. USIBMSTODB22, in turn, connects to USIBMSTODB23 and USIBMSTODB24 through Db2 private protocol access. USIBMSTODB22 uses information from USIBMSTODB23 and USIBMSTODB24 to answer the request from USIBMSTODB21.

End detailed description.