Displaying statistics about stored procedures

Issue the DISPLAY PROCEDURE command to display statistics about stored procedures that are accessed by Db2 applications.

About this task

This command can display the following information about stored procedures:

Status (started, stop-queue, stop-reject, or stop-abend)
Number of requests that are currently running and queued
Maximum number of threads that are running a stored procedure load module and queued
Count of timed-out SQL CALLs

Procedure

To display information about all stored procedures in all schemas that have been accessed by Db2 applications:

Issue the DISPLAY PROCEDURE command.

Begin general-use programming interface information. For example:

-DISPLAY PROCEDURE

End general-use programming interface information.

Note: To display information about a native SQL procedure, you must run the procedure in DEBUG mode. If you do not run the native SQL procedure in DEBUG mode (for example, in a production environment), the DISPLAY PROCEDURE command will not return output for the procedure.

If you do run the procedure in DEBUG mode the WLM environment column in the output contains the WLM ENVIRONMENT FOR DEBUG that you specified when you created the native SQL procedure. The DISPLAY PROCEDURE output shows the statistics of native SQL procedures as '0' if the native SQL procedures are under the effect of a STOP PROCEDURE command.

Example

Begin general-use programming interface information. The following example shows two schemas (PAYROLL and HRPROD) that have been accessed by Db2 applications. You can also display information about specific stored procedures.

DSNX940I csect - DISPLAY PROCEDURE REPORT FOLLOWS-
------ SCHEMA=PAYROLL
PROCEDURE          STATUS    ACTIVE  QUED  MAXQ  TIMEOUT  FAIL  WLM_ENV
PAYRPRC1           
                   STARTED        0     0     1       0      0  PAYROLL
PAYRPRC2           
                   STOPQUE        0     5     5       3      0  PAYROLL
PAYRPRC3           
                   STARTED        2     0     6       0      0  PAYROLL
USERPRC4           
                   STOPREJ        0     0     1       0      1  SANDBOX
------ SCHEMA=HRPROD
PROCEDURE          STATUS    ACTIVE  QUED  MAXQ  TIMEOUT  FAIL  WLM_ENV
HRPRC1             
                   STARTED        0     0     1       0      1  HRPROCS
HRPRC2             
                   STOPREJ        0     0     1       0      0  HRPROCS
DISPLAY PROCEDURE REPORT COMPLETE
DSN9022I  = DSNX9COM '-DISPLAY PROC' NORMAL COMPLETION

End general-use programming interface information.