-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, if you do not specify the LOCAL option, you must use a privilege set of the process that includes one of the following authorities:
  • SYSOPR authority
  • SYSCTRL authority
  • SYSADM authority

If you specify the LOCAL option, no authorization is needed.

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. Start of changeA logged-on z/OS user ID must be defined in RACF or a similar security server.End of change

Syntax

Read syntax diagramSkip visual syntax diagramCANCELTHREAD( token)DDF THREAD(luwidtoken)DUMPLOCALNOBACKOUTFORCE

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 147 or 148. 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
Identifies distributed threads for which you want to cancel processing.
( luwid )
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. For example, for package-based continuous block fetch, the requester opens a secondary connection for each statement in an application. At the server, all database access threads that process work for those secondary connections 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.

( token )
See THREAD (token).
DUMP
Specifies 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.

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.

FORCE
Attempts to purge the thread of a remote connection from the Db2 server. The FORCE option is accepted only after a request to CANCEL THREAD is issued without the FORCE option.
Attention: The FORCE option can potentially affect the Db2 subsystem. Use it to cancel threads that impact the Db2 subsystem and cannot be canceled without FORCE.

Start of changeThe FORCE option can be used for a thread is hung in the network, but it cannot help for threads that are hung while Db2 is processing them.End of change

Usage notes

Canceling local threads:
The CANCEL command schedules a thread to terminate. Threads that are not in Db2 terminate immediately.
Canceling Db2 threads before canceling jobs or purging transactions:
If you need to cancel or purge jobs or transactions with active Db2 threads, the best practice is to issue the Db2 CANCEL THREAD command before you cancel or purge the tasks. When the CANCEL THREAD command is issued, Db2 abnormally terminates (abends) the threads at predictable locations in Db2. This process results in orderly termination of the tasks.
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 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 allows the thread to be terminated.
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
Using 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

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)