-CANCEL THREAD (DB2)

The DB2® command CANCEL THREAD cancels processing for specific local or distributed threads.

Abbreviation: -CAN THD

Environment

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

Data sharing scope: Member

Authorization

To execute this command, you must use a privilege set of the process that includes one of the following authorities:
  • SYSOPR authority
  • SYSCTRL authority
  • SYSADM authority

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.

Syntax

>>-CANCEL--+-THREAD(token)-----------+--+------+--+-------+----->
           '-DDF THREAD(-+-luwid-+-)-'  '-DUMP-'  '-LOCAL-'   
                         '-token-'                            

>--+-----------+-----------------------------------------------><
   '-NOBACKOUT-'   

Option descriptions

THREAD (token)
Identifies a specific thread, either distributed or not, whose processing you want to cancel. DB2 assigns a token to each thread that is unique for that DB2 subsystem, but not necessarily unique across subsystems.

The token is a one- to six-digit decimal number. You can determine what the token is by issuing the DB2 command DISPLAY THREAD or by using an IFI READS call for IFCID 0147 or 0148. The token can also appear after the equal sign in DB2 messages that display an LUWID. If the token is 0, the thread of that token cannot be canceled.

DDF THREAD( luwid )
Identifies distributed threads for which you want to cancel processing. luwid is a logical unit of work identifier (LUWID), consisting of:
  • A fully qualified LU network name, which consists of:
    • A one- to eight-character network ID
    • A period
    • A one- to eight-character network LU name
  • An LUW instance number, which consists of 12 hexadecimal characters that uniquely identify the unit of work
If you enter three fields separated by periods, DB2 assumes that you are entering an LUWID.

Two or more distributed threads might have the same LUWID. All distributed threads with the same LUWID are canceled.

The LUWID can be determined from the DB2 DISPLAY THREAD command and other DB2 messages.

DUMP
Start of changeSpecifies that a dump is generated when CANCEL THREAD is issued, and the thread is active in DB2. The dump can be used for diagnostic purposes.

When you cancel a thread that is not currently active in DB2, DB2 performs a hard cancel and no dump is provided. A thread is considered to be not currently active in DB2 when it has left DB2 to perform application work.

End of change
LOCAL
Specifies that DB2 search the home address space of the allied agent for a thread that matches token. If DB2 finds a thread, it performs a soft cancel on that thread. The LOCAL option applies only to a CANCEL THREAD command that is issued through the IFI interface. The IFI application must run in the same address space as the thread that needs to be canceled, and the thread must be connected to DB2 through the RRS attachment facility.
NOBACKOUT
Specifies that DB2 is not to attempt to back out the data during transaction rollback processing. Canceling the thread with NOBACKOUT leaves objects in an inconsistent state. Do not issue this command with NOBACKOUT unless you have a plan to resolve the data inconsistency.

Multiple NOBACKOUT requests are allowed. However, if the thread is active and the request is accepted, subsequent requests are ignored. You can choose to issue a subsequent request if a request fails (as indicated by message DSNI032I). Objects that the thread modifies are recovered (backed out). If back out processing fails, the objects are marked REFRESH PENDING (REFP) and either RECOVER PENDING (RECP) or REBUILD PENDING (RBDP or PSRBD) in the database exception table. Resolve the REFP status of the object by running the RECOVER utility to recover the object to a prior point in time or by running LOAD REPLACE on the object.

Usage notes

Canceling distributed threads: Canceling a distributed thread can cause the thread to enter the indoubt state. Message DSNL450I is issued if the CANCEL command causes the DDF thread to be converted from active to indoubt. DB2 releases the resources that the thread holds when the indoubt state is resolved by automatic indoubt resolution with the coordinator, or by resolution with the command RECOVER INDOUBT.

If a thread that is specified in the command is part of a global transaction, the command is executed against all threads in the global transaction.

The CANCEL command schedules a thread to be terminated in DB2. To terminate, the thread must be processing within DB2. If the thread does not terminate, it could be:
  • Processing outside of DB2, possibly in the application. If that is the case, the thread does not terminate until the application makes a request to DB2. Use the z/OS CANCEL command to terminate the application immediately.
  • Hung up in a network operation. Use VTAM® or TCP/IP commands to cause the network operation to return processing to DB2, which will then allow the thread to be terminated. See the following topic for details.

Canceling threads on applications that update NOT LOGGED tables spaces: Take care when you issue a CANCEL command for an application that updates a NOT LOGGED table space. The CANCEL THREAD command can cause the NOT LOGGED table space to be placed in the LPL, so that the table space must be recovered.

Canceling local threads: The CANCEL command schedules a thread to terminate. Threads that are not in DB2 terminate immediately.

Canceling SNA distributed threads with VTAM Commands: If the CANCEL command does not terminate a distributed thread, it is possible that it is hung up in VTAM. Use the VTAM VARY NET,TERM command to cancel the thread's VTAM sessions.

To cancel a thread's VTAM session, you need to know the VTAM session IDs (SIDs) that correspond to the thread. Take the following steps:
  1. Issue the DB2 command DISPLAY THREAD(*) LUWID(nnnn) DETAIL. (The value of nnnn is the token or LUWID provided by CANCEL DDF THREAD.)
    This gives you the VTAM session IDs that must be canceled. Sessions are identified by the column header SESSID as shown in the following DISPLAY THREAD output:
    -DIS THD(*) LUWID(123) DETAIL
    DSNV401I - DISPLAY THREAD REPORT FOLLOWS:                       
    DSNV402I - ACTIVE THREADS:                                      
    NAME     ST A   REQ ID           AUTHID    PLAN      ASID TOKEN 
    BATCH    TR *     5 BKH2C        SYSADM    BKH2      000D   123 
     V444-DB2NET.LUND0.C4B23F1F4D06=123 ACCESSING DATA AT           
      (  1)SAN JOSE-LUND1                                           
     V447--INDEX SESSID           A ST TIME                         
     V448--(  1) 00D3590EA1E89701     S1    0923816181452           
     V448--(  1) 00D3590EA1E89822   N R1    0923816181584           
    DISPLAY ACTIVE REPORT COMPLETE                                  
    DSN9022I - DSNVDT '-DIS THD' NORMAL COMPLETION     

    The N indicates the thread is processing in VTAM.

  2. Record positions 3 through 16 of SESSID for the threads to be canceled. (In the preceding DISPLAY THREAD output, the values are D3590EA1E89701 and D3590EA1E89822.)
  3. Issue the VTAM command DISPLAY NET to display the VTAM session IDs. The ones you want to cancel match the SESSIDs in positions 3 through 16 and the corresponding session IDs are in bold. The following is an output example of this command:
    D NET,ID=LUND0,SCOPE=ACT
     
    IST097I DISPLAY ACCEPTED
    IST075I NAME = LUND0, TYPE = APPL
       IST486I STATUS= ACTIV, DESIRED STATE= ACTIV
       IST171I ACTIVE SESSIONS = 0000000005, SESSION REQUESTS = 0000000000
       IST206I SESSIONS:
       IST634I NAME     STATUS           SID          SEND   RECV  VR  TP  NETID
       IST635I LUND1    ACTIV-S     D24B171032B76E65  0051   0043   0   0  NET2
       IST635I LUND1    ACTIV-S     D24B171032B32545  0051   0043   0   0  NET2
       IST635I LUND1    ACTIV-R     D2D3590EA1E89701  0022   0031   0   0  NET2
       IST635I LUND1    ACTIV-R     D2D3590EA1E89802  0022   0031   0   0  NET2
       IST635I LUND1    ACTIV-R     D2D3590EA1E89822  0022   0031   0   0  NET2
       IST314I END
  4. Issue the VTAM command VARY NET,TERM for each of the VTAM SIDs associated with the DB2 thread. In this case, you might need to cancel only the session ID that DISPLAY THREAD shows to be processing in VTAM (D2D3590EA1E89822).

Canceling TCP/IP Distributed Threads with TCP/IP Commands: If the CANCEL command does not terminate a distributed thread, the thread might be hung up in TCP/IP. Use the TCP/IP DROP command to cancel the thread's connection ID. To do this, you need to first determine the TCP/IP connection ID that corresponds to the thread.

Depending on whether the thread is a DB2 requester or server thread, take the following steps:
  • Terminating TCP/IP connection for a requester thread:
    1. Issue the DB2 command DISPLAY THREAD(*) LUWID(nnnn) DETAIL. (The value of nnnn is the token or LUWID provided by DISPLAY THREAD.)
      Find the IP address and local port for the connection to the partner, as shown in the following DISPLAY THREAD output:
      #display thread(*) detail 
                                         
      DSNV401I  # DISPLAY THREAD REPORT FOLLOWS -                           
      DSNV402I  # ACTIVE THREADS -                                          
      NAME     ST A   REQ ID           AUTHID   PLAN     ASID TOKEN         
      TEST0001 TR       4 CTHDCORID001 SYSADM   DONSQL1  0027    19         
       V444-USIBMSY.SYEC715B.C4B220851392=19 ACCESSING DATA AT              
        (  1)-STL714A-::FFFF:9.112.114.102..446                             
       V447--INDEX SESSID           A ST TIME                               
       V448--(  1) 1028:446         N R2 0923814011448                      
      DISPLAY ACTIVE REPORT COMPLETE                                        
      DSN9022I  # DSNVDT '-DIS THD' NORMAL COMPLETION                                                       

      In this case, the partner's IP address and port is 9.112.114.102 446, and the local port is 1028. N indicates that the thread is processing in TCP/IP.

    2. Determine the associated TCP/IP connection ID:
      d tcpip,,netstat,conn,ipaddr=9.112.114.102    
                                 
      EZZ2500I NETSTAT CS V2R10 TCPIP                                       
      USER ID  CONN     LOCAL SOCKET           FOREIGN SOCKET         STATE    
      V71BDIST 0000049D 9.112.114.103..1028    9.112.114.102..446     ESTBLSH  
      1 OF 1 RECORDS DISPLAYED                                                 
    3. Terminate the connection:
      v tcpip,,drop,conn=0000049d 
                           
      EZZ0060I PROCESSING COMMAND: VARY TCPIP,,DROP,   
       CONN=0000049D                                   
      EZZ0053I COMMAND VARY DROP COMPLETED SUCCESSFULLY                                                
  • Terminating TCP/IP connection for a server thread::
    1. Issue the DB2 command DISPLAY THREAD(*) LUWID(nnnn) DETAIL. (The value of nnnn is the token or LUWID provided by CANCEL THREAD.)
      Find the IP address and local port for the connection to the partner, as shown in the following DISPLAY THREAD output:
      !display thread(*) detail                                     
                                                                    
      DSNV401I ! DISPLAY THREAD REPORT FOLLOWS -                    
      DSNV402I ! ACTIVE THREADS -                                   
      NAME     ST A   REQ ID           AUTHID   PLAN     ASID TOKEN 
      TEST0001 RA *     2 CTHDCORID001 SYSADM   DONSQL1  002D    11 
       V445-USIBMSY.SYEC715B.C4B24232F81D=11 ACCESSING DATA FOR     
        (  1)::FFFF:9.112.114.103                                   
       V447--INDEX SESSID           A ST TIME                       
       V448--(  1) 446:1029         W R2 0923816315680              
      DISPLAY ACTIVE REPORT COMPLETE                                
      DSN9022I ! DSNVDT '-DIS THD' NORMAL COMPLETION                              

      In this case, the partner's IP address is 9.112.114.103 and the local port is 1029.

    2. Determine the associated TCP/IP connection ID:
      d tcpip,,netstat,conn,ipaddr=9.112.114.103 
                                   
      EZZ2500I NETSTAT CS V2R8 TCPIP                                       
      USER ID  CONN     LOCAL SOCKET           FOREIGN SOCKET         STATE   
      V61ADIST 0000048E 9.112.114.102..446     9.112.114.103..1029    ESTABLS 
      1 OF 1 RECORDS DISPLAYED                                                                                              

      Find the entry where the foreign socket shows the partner's IP address and port (9.112.114.103 1029) and note the CONN.

    3. Terminate the connection:
      v tcpip,,drop,conn=0000048e 
                            
      EZZ0060I PROCESSING COMMAND: VARY TCPIP,,DROP,    
       CONN=0000048E                                    
      EZZ0053I COMMAND VARY DROP COMPLETED SUCCESSFULLY 
Start of changeUsing TCP/IP commands to cancel accelerated threads: If the CANCEL command does not terminate an accelerated thread (a thread that has active accelerator processes running), the thread might be hung up in TCP/IP. Use the TCP/IP DROP command to cancel the thread's connection ID. To do this, you need to first determine the TCP/IP connection ID that corresponds to the thread.
  • To terminate a TCP/IP connection for an accelerated thread:
    1. Issue the DB2 command DISPLAY THREAD(*) ACCEL(*) DETAIL.
    2. Find the IP address and local port for the connection to the partner, as shown in the following DISPLAY THREAD output:
       )DISPLAY THREAD(*) ACCEL(*) DETAIL
                        
       DSNV401I  ) DISPLAY THREAD REPORT FOLLOWS -
       DSNV402I  ) ACTIVE THREADS - 681
       NAME     ST A   REQ ID           AUTHID   PLAN     ASID TOKEN
       BATCH    AC *    39 ACCEL1       SYSADM   DSNTEP2  002B     3
        V666 ACC=BLINK1,ADDR=::FFFF:9.30.30.177..446:1080
        V436-PGM=DSNTEP2.DSNTEP2, SEC=1, STMNT=2256
       DISPLAY ACTIVE REPORT COMPLETE
       DSN9022I  ) DSNVDT '-DIS THD' NORMAL COMPLETION
      In this case, the partner's IP address is 9.30.30.177, the port is 446, and the local port is 1080. AC indicates that the thread is processing in an accelerator.
    3. Determine the associated TCP/IP connection ID:
      D TCPIP,,NETSTAT,CONN,IPADDR=9.30.30.177
                        
       EZD0101I NETSTAT CS V1R10 TCPIP
       USER ID  CONN     STATE
       V91ADBM1 000009E3 ESTBLSH
         LOCAL SOCKET:   ::FFFF:9.30.222.34..1080
         FOREIGN SOCKET: ::FFFF:9.30.30.177..446
       1 OF 1 RECORDS DISPLAYED
       END OF THE REPORT
      In this case, the associated TCP/IP connection ID is 000009E3.
    4. Terminate the connection:
       V TCPIP,,DROP,CONN=000009E3
                        
       EZZ0060I PROCESSING COMMAND: VARY TCPIP,,DROP,CONN=000009E3
       EZZ0053I COMMAND VARY DROP COMPLETED SUCCESSFULLY
End of change

Examples

Example: Canceling a local thread
To cancel a non-distributed thread whose token you found through the DISPLAY THREAD command and to produce a diagnostic dump, issue:
-CANCEL THREAD (123) DUMP
Example: Canceling a distributed thread
To cancel a distributed thread whose LUWID you found through the DISPLAY THREAD command, issue:
-CANCEL DDF THREAD (LUDALLAS.DB2SQL1.3042512B6425)
Suppose that the output from -DISPLAY THREAD shows that the thread-ID and token associated with this LUWID is 45162. You can also cancel this thread by issuing either of the following commands:
-CANCEL DDF THREAD (45162)
-CANCEL THREAD (45162)