-CANCEL THREAD command (Db2)

Environment for -CANCEL THREAD

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

Authorization for -CANCEL THREAD

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

Option descriptions for -CANCEL THREAD

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.

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

Usage notes for -CANCEL THREAD

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