-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
- DISPLAY privilege
- System DBADM authority
- SYSOPR authority
- SYSCTRL authority
- SYSADM authority
- 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. A logged-on z/OS user ID must be defined in RACF or a similar security server.
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
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