Stopping Db2 for z/OS stored procedure threads

You have several options for stopping an ODBA stored procedure thread. Some are better than others due to the potential for hung threads or other availability issues.

The methods in the following list are presented in the order in which you should try them.
  1. Db2 for z/OS -STOP PROCEDURE command
  2. Cancel the thread in Db2 for z/OS
  3. IMS STOP REGION commands
  4. The REFRESH option of the z/OS MVS command VARY WLM
  5. Recycle IMS

Db2 for z/OS -STOP PROCEDURE command

The Db2 for z/OS -STOP PROCEDURE command is the best way to stop a thread because it has the fewest potential negative side effects. The -STOP PROCEDURE command prevents Db2 for z/OS from accepting SQL CALL statements for one or more stored procedures.

For example, the following command stops stored procedures USERPRC1 and USERPRC3. While the -STOP PROCEDURE command is in effect, attempts to execute these stored procedure are queued.
-STOP PROCEDURE(USERPRC1,USERPRC3)

Cancel the thread in Db2 for z/OS

Use Db2 for z/OS -DISPLAY THREAD and -CANCEL THREAD commands to display threads and locks held, and then cancel the threads from Db2 for z/OS.

-DISPLAY THREAD(*) TYPE(PROC)
-CANCEL THREAD(token)

IMS STOP REGION commands

Use IMS commands to display the hung threads and then use one of the following commands to stop them.

  • /STOP REGION reg# | job
  • /STOP REGION reg# | job ABDUMP tran

The REFRESH option of the z/OS MVS command VARY WLM

You can use the REFRESH option of the z/OS MVS VARY WLM command to refresh the WLM environment; that is, it recycles the WLM environment.

Refreshing the WLM environment is problematic for ODBA because it terminates the stored procedures without using the IMS AIB DPSB or TALL calls to terminate the ODBA threads. Normally, when ODBA receives a TERM or TALL request, connections to IMS wait for active threads to return to the DRA before terminating. VARY REFRESH does not allow this, which can result in hung threads.

The following example shows the VARY command syntax.
VARY WLM,APPLENV=xxxx,REFRESH

Recycle IMS

As a last resort, you can recycle the IMS address space to stop an ODBA thread if the previously described methods do not work.