-DISPLAY PROCEDURE command (Db2)

The Db2 command DISPLAY PROCEDURE 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

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

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

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

Option descriptions

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

Output

Message DSNX940I indicates the beginning of the output of the command.

Usage notes

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.

Examples

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