-DISPLAY PROCEDURE command (Db2)

The -DISPLAY PROCEDURE command displays statistics about stored procedures that are accessed by Db2 applications.

This command displays one line of output for each stored procedure that a Db2 application has accessed. You can qualify stored procedure names with a schema name.

The information returned by the DISPLAY PROCEDURE command reflects a dynamic status. By the time the information is displayed, it is possible that the status could have changed.

Abbreviation: -DIS PROC

Environment for -DISPLAY PROCEDURE

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: Group or member

Authorization for -DISPLAY PROCEDURE

To run this command, you must use a privilege set of the process that includes one of the following privileges or authorities:
  • DISPLAY privilege
  • System DBADM authority
  • SYSOPR authority
  • SYSCTRL authority
  • SYSADM authority
If you specify DISPLAY PROCEDURE *.* or schema.partial-name *, the privilege set of the process must include one of the following authorities:
  • SYSOPR authority
  • SYSCTRL authority
  • SYSADM authority

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

If you are using an external security product to authorize usage of DISPLAY PROCEDURE, define SYSOPR as a user to the external security product for those cases in which DISPLAY PROCEDURE SCOPE(GROUP) runs on a remote system and SYSOPR is used as the authorization ID.

Syntax for -DISPLAY PROCEDURE

Read syntax diagramSkip visual syntax diagramDISPLAY PROCEDURE( *.*)(,schema.procedure-nameschema.partial-name*procedure-namepartial-name*)SCOPE(LOCALGROUP)

Option descriptions for -DISPLAY PROCEDURE

(*.*)
Displays information for all stored procedures in all schemas that Db2 applications have accessed since Db2 was started.
( schema.procedure-name )
Displays the specified stored procedure in the specified schema.
( schema.partial-name *)
Displays a set of stored procedures in the specified schema that Db2 applications have accessed since Db2 was started. The names of all procedures in the set begin with partial-name and can end with any string, including the empty string. For example, PAYROLL.ABC* displays information for all stored procedure names beginning with ABC in the PAYROLL schema.
( procedure-name )
Displays one or more specific stored procedure names in the SYSPROC schema. If no procedures are named, Db2 displays information for all stored procedures that have been accessed by Db2 applications.
( partial-name *)
Displays information for a set of stored procedures in the SYSPROC schema that Db2 applications have accessed since Db2 was started. The names of all procedures in the set begin with partial-name and can end with any string, including the empty string. For example, ABC* displays information for all stored procedures in the SYSPROC schema with names that begin with ABC.
SCOPE
Specifies the scope of the command.
( LOCAL )
Specify to display information about procedures on the local member only.
(GROUP)
Specify to display information about procedures on all members of the data sharing group.

Usage notes for -DISPLAY PROCEDURE

Displaying native SQL procedures

Native SQL procedures are not displayed in the DISPLAY PROCEDURE output unless you run the procedures in DEBUG mode. If you run a 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 a native SQL procedure as 0 if the STOP PROCEDURE command is processing the native SQL procedure.

Trace information for commands issued with group scope
If this command is issued with options that specify group scope in a Db2 data sharing member, it also runs on all other active members. IFICID 090 trace records for other group members can show that the same command was issued by the SYSOPR authorization ID from the 016.TLPKN5F correlation ID, in addition to the trace records from the member where the original command was issued. See Command scope in Db2 data sharing.

Output for -DISPLAY PROCEDURE

The DSNX940I message indicates the beginning of the output for the -DISPLAY PROCEDURE command.

DSNX940I csect-name - DISPLAY PROCEDURE REPORT FOLLOWS-


------ SCHEMA = schema
PROCEDURE  STATUS  ACTIVE  QUED MAXQ  TIMEOUT FAIL WLM_ENV
procedure  status active  qued  maxq  timeout  fail wlm_env
procedure  status active  qued  maxq  timeout  fail wlm_env
procedure  status active  qued  maxq  timeout  fail wlm_env


------ SCHEMA = schema
csect-name ALL PROCEDURES STARTED FOR THIS SCHEMA.
PROCEDURE  STATUS  ACTIVE  QUED MAXQ  TIMEOUT FAIL WLM_ENV
procedure  status active  qued  maxq  timeout  fail wlm_env
procedure  status active  qued  maxq  timeout  fail wlm_env
procedure  status active  qued  maxq  timeout  fail wlm_env

------ SCHEMA = schema
PROCEDURE  STATUS  ACTIVE  QUED MAXQ  TIMEOUT FAIL WLM_ENV
procedure  status active  qued  maxq  timeout  fail wlm_env
procedure  status active  qued  maxq  timeout  fail wlm_env
procedure  status active  qued  maxq  timeout  fail wlm_env
csect-name PROCEDURES begin - end STOP (act)
...

csect-name ALL OTHER PROCEDURES IN OTHER SCHEMAS STOPPED act

csect-name DISPLAY PROCEDURE REPORT COMPLETE

The output that is displayed varies depending on the qualifying stored procedures. Error messages might also be included in the output.

The following information can be provided in the output:

SCHEMA = schema
The schema name for the stored procedures that are included in the output.

The schema name and lines that follow are repeated as many times as needed to satisfy the DISPLAY PROCEDURE request.

PROCEDURE procedure
The name of the stored procedure.

One line of output is included for each requested stored procedure.

STATUS status
The status of the stored procedure. The status can be any of the following values:
STARTED
The stored procedure can accept SQL CALL requests.
STOPABN
The stored procedure was stopped because of a previous abnormal termination of the stored procedure application. SQL CALL requests for the stored procedure are rejected.
STOPQUE
The stored procedure was stopped. SQL CALL requests for the stored procedure are queued until the stored procedure is started or the timeout interval that is defined by the STORTIME subsystem parameter expires.
STOPREJ
The stored procedure was placed in this status by the STOP PROCEDURE command with the ACTION(REJECT) option. SQL CALL requests for the stored procedure are rejected.

If the stored procedure is in any of the stopped states, you can use the START PROCEDURE command to start the procedure.

ACTIVE active
The number of Db2 agents that are currently running this stored procedure.
QUED qued
The number of Db2 agents that are currently waiting for this stored procedure to be scheduled.
MAXQ maxq
The maximum number of Db2 agents that have waited for this stored procedure to be scheduled since Db2 was started.
TIMEOUT timeout
The number of times that an SQL CALL statement timed out while it waited for a stored procedure to be scheduled since Db2 was started.
An SQL CALL statement can time out for any of the following reasons:
  • The procedure can be stopped by the STOP PROCEDURE command with the ACTION(QUEUE) option. This specification causes requests for the procedure to time out after the interval that is defined by the STORTIME subsystem parameter is exceeded.
  • The Db2 stored procedures address space might not have enough MVS TCBs to handle the volume of SQL CALL statements.
  • The MVS dispatching priority of the Db2 stored procedures address space might be too low to process the stored procedure requests in a timely manner.
FAIL fail
The number of times that this procedure abended since it was last started.
WLM_ENV wlm_env
The MVS Workload Manager environment name for this stored procedure.
csect-name
The name of the control section that issued the message.
ALL PROCEDURES STARTED FOR THIS SCHEMA.
This line is displayed when both of the following conditions are true:
  • A STOP PROCEDURE(*.*) command operation was in effect when DISPLAY PROCEDURE was issued.
  • All of the procedures are started for the schema that is identified in a preceding line in the output.
PROCEDURES begin - end STOP (act)
This line is displayed when a STOP PROCEDURE command caused a range of procedures to be stopped. This situation can occur when the STOP PROCEDURE command specifies a partial name with a pattern-matching character (*), for example, -STOP PROCEDURE(ABC*) .
begin
The first procedure name in the range of procedures that are currently stopped.
end
The last procedure name in the range of procedures that are currently stopped.
act
The action that was specified on the STOP PROCEDURE command.
ALL OTHER PROCEDURES IN OTHER SCHEMAS STOPPED act
This line is displayed when both of the following conditions are true:
  • A STOP PROCEDURE(*.*) command operation was in effect when the DISPLAY PROCEDURE command was issued.
  • Any procedures that are not already listed in this DISPLAY PROCEDURE output are stopped.

act indicates the action that was specified on the STOP PROCEDURE command.

DISPLAY PROCEDURE REPORT COMPLETE
The report completed successfully.

Error messages for -DISPLAY PROCEDURE

The following messages in the DISPLAY PROCEDURE output indicate that an error occurred:

csect-name PROCEDURE HAS NOT BEEN ACCESSED OR IS NOT DEFINED
The routine name that was supplied on the DISPLAY PROCEDURE command either does not exist or has not been accessed since Db2 started. The specific procedure name might not be present in the SYSIBM.SYSROUTINES catalog table.

Processing continues for any additional routine names that were supplied on the command.

DISPLAY TERMINATED WITH MAX LINES
The DISPLAY PROCEDURE report was truncated. The report was generated in response to a command from an MVS console, and more than 254 messages were generated. Only 254 messages are returned.

Command processing completes. The truncated report is written to the MVS console.

Reissue the DISPLAY PROCEDURE command and specify a smaller number of routines. Specify a list of routines, a schema qualifier, or a partial routine name in the DISPLAY command rather than the default (*.*). Alternatively, reissue the command from the DB2 Commands panel of DB2I or from a batch job rather than from the MVS console.

DSNX978I DISPLAY TERMINATED DUE TO INSUFFICIENT MESSAGE SPACE
See DSNX978I.
DSNX944I csect-name THE STORED PROCEDURE FUNCTION IS NOT AVAILABLE
See DSNX944I.
DSNX994I SCOPE(GROUP) COMMAND CANNOT BE PROCESSED DUE TO COMMAND ALREADY IN PROGRESS
See DSNX994I.

Examples for -DISPLAY PROCEDURE

Example: Displaying information about all stored procedures that have been accessed
The following command displays information about all stored proceduresthat applications have accessed in a Db2 subsystem.
-DISPLAY PROCEDURE

The output is similar to the following output:

DSNX940I = DSNX9DIS DISPLAY PROCEDURE REPORT FOLLOWS-
 
------ SCHEMA=SYSPROC
PROCEDURE      STATUS    ACTIVE    QUED  MAXQ  TIMEOUT  FAIL   WLM_ENV
USERPRC1       STARTED     0         0     1      0       0    SANDBOX
USERPRC2       STOPQUE     0         5     5      3       0    SANDBOX
USERPRC3       STARTED     2         0     6      0       0    SANDBOX
USERPRC4       STOPREJ     0         0     1      0       0    SANDBOX
DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE
DSN9022I = DSNX9COM '-DISPLAY PROC' NORMAL COMPLETION
Example: Displaying information about a specific stored procedure
The following command displays information about stored procedures USERPRC2 and USERPRC4 in the SYSPROC schema. The SYSPROC schema is the default schema if the schema name is not explicitly specified.
-DISPLAY PROCEDURE(USERPRC2,USERPRC4)

The output is similar to the following output:

DSNX940I = DSNX9DIS DISPLAY PROCEDURE REPORT FOLLOWS-
 
------ SCHEMA=SYSPROC
PROCEDURE      STATUS    ACTIVE    QUED  MAXQ  TIMEOUT  FAIL   WLM_ENV
USERPRC2       STOPQUE     0         5     5      3      0     SANDBOX
USERPRC4       STOPREJ     0         0     1      0      0     SANDBOX
DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE
DSN9022I = DSNX9COM '-DISPLAY PROC' NORMAL COMPLETION
Example: Displaying information about all stored procedures in specified schemas
The following command displays information about all stored procedures in the PAYROLL schema and in the HRPROD schema:
-DISPLAY PROCEDURE(PAYROLL.*,HRPROD.*)

The output is similar to the following output.

DSNX940I = DSNX9DIS DISPLAY PROCEDURE REPORT FOLLOWS-
 
------ SCHEMA=PAYROLL
PROCEDURE      STATUS    ACTIVE    QUED  MAXQ  TIMEOUT  FAIL   WLM_ENV
PAYPRC1        STARTED     0         0     1      0      0     PAYROLL
PAYPRC2        STOPQUE     0         5     5      3      0     PAYROLL
PAYPRC3        STARTED     2         0     6      0      0     PAYROLL
USERPRC4       STOPREJ     0         0     1      0      0     SANDBOX
 
------ SCHEMA=HRPROD
PROCEDURE      STATUS    ACTIVE    QUED  MAXQ  TIMEOUT  FAIL   WLM_ENV
HRPRC1         STARTED     0         0     1      0      0     HRPROCS
HRPRC2         STOPREJ     0         0     1      0      0     HRPROCS
DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE
DSN9022I = DSNX9COM '-DISPLAY PROC' NORMAL COMPLETION
Example: Displaying information about stopped procedures
Suppose that all stored procedures in schema SYSADM that begin with the characters "SP" have been stopped. The following command displays information about those stored procedures.
-DISPLAY PROCEDURE(SYSADM.SP*)

The output is similar to the following output.

DSNX940I = DSNX9DIS DISPLAY PROCEDURE REPORT FOLLOWS-
 
------ SCHEMA=SYSADM
PROCEDURE      STATUS    ACTIVE    QUED  MAXQ  TIMEOUT  FAIL   WLM_ENV
SPC1           STOPQUE     0         0    0      0       0     WLMENV1
SPC2           STOPQUE     0         0    0      0       0     WLMENV3
DSNX9DIS PROCEDURES SP - SP* STOP QUEUE
DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE
DSN9022I = DSNX9COM '-DISPLAY PROC' NORMAL COMPLETION