Displaying thread information about stored procedures

Issue the DISPLAY THREAD command to display thread information about stored procedures.

About this task

This command tells whether:

A thread is waiting for a stored procedure to be scheduled.
A thread is executing within a stored procedure.

Procedure

Begin general-use programming interface information. To display active threads that are running stored procedures and user-defined functions:

Issue the DISPLAY THREAD command.

For example:

-DISPLAY THREAD(*) TYPE(PROC)

End general-use programming interface information.

Example

Example 1: The following example of output from the DISPLAY THREAD command shows a thread that is executing an external SQL procedure or an external stored procedure.

-display thread(*) type(proc) detail
DSNV401I ! DISPLAY THREAD REPORT FOLLOWS -
DSNV402I ! ACTIVE THREADS -
NAME     ST A   REQ ID           AUTHID   PLAN     ASID TOKEN
BATCH    SP       3 CALLWLM      SYSADM   PLNAPPLX 0022     5
  V436-PGM=*.MYPROG, SEC=2, STMNT=1
  V429 CALLING PROCEDURE=SYSADM  .WLMSP             ,
       PROC=V61AWLM1, ASID=0085, WLM_ENV=WLMENV1
DISPLAY ACTIVE REPORT COMPLETE
DSN9022I ! DSNVDT '-DISPLAY THREAD' NORMAL COMPLETION

The SP status indicates that the thread is executing within the stored procedure. An SW status indicates that the thread is waiting for the stored procedure to be scheduled.

Example 2: This example of output from the DISPLAY THREAD command shows a thread that is executing a native SQL procedure. If you do not specify the DETAIL option, the output will not include information that is specific to the stored procedure.

Issuing the command

-display
thread(*) type(proc) detail

results in the following output:

SERVER RA * 22325 driver.exe USRT010 DISTSERV 0067 8
V437-WORKSTATION=CALADAN, SERID=USRT010, 
APPLICATION NAME=driver.exe 
V436-PGM=USRT010.MARKETWATCH_F1, SEC=3, STMNT=39 
V442-CRTKN=9.30.129.213.15369.090129190416 
V445-G91E81D8.C3B9.C3AB43861A26=8 ACCESSING DATA FOR 
( 1)::FFFF:9.30.129.213 
V447--INDEX SESSID A ST TIME V448--( 1) 50105:2364 W S2 902911191075

Example 3: The following example of output from the DISPLAY THREAD command shows a thread that is executing a user-defined function.

-display thread(*) type(proc) detail
DSNV401I ! DISPLAY THREAD REPORT FOLLOWS -
DSNV402I ! ACTIVE THREADS -
NAME     ST A   REQ ID           AUTHID   PLAN     ASID TOKEN
BATCH    SP      27 LI33FN1      SYSADM   MYPLAN   0021     4
 V436-PGM=*.MYPROG, SEC=2, STMNT=1
 V429 CALLING FUNCTION =SYSADM  .FUNC1             ,
      PROC=V61AWLM1, ASID=0085, WLM_ENV=WLMENV1
DISPLAY ACTIVE REPORT COMPLETE
DSN9022I ! DSNVDT '-DISPLAY THD' NORMAL COMPLETION