Using the DISPLAY Command, Part 4

In this fourth entry of our series on the DISPLAY command, we take a look at using the DISPLAY command to monitor DB2 stored procedures and user-defined functions. Part 1 of this series focused on using DISPLAY to monitor details about you database objects; Part 2 focused on using DISPLAY to monitor your DB2 buffer pools. And Part 3 covered utility execution and log information. 

If your organization uses stored procedures and/or user-defined functions (UDFs), the DISPLAY command once again comes in handy. 

Stored Procedures

You can use the DISPLAY PROCEDURE command to monitor stored procedure statistics. The output will consist of one line for each stored procedure that a DB2 application has accessed. You can qualify stored procedure names with a schema name.

DISPLAY PROCEDURE returns the following information:

• The status, that is, whether the named procedure is currently started or stopped
• How many requests are currently executing
• The high-water mark for concurrently running requests
• How many requests are currently queued
• How many times a request has timed out
• How many times a request has failed
• The WLM environment in which the stored procedure executes

Here is an example of what will be output by the DISPLAY PROCEDURE command:

DSNX940I = DSNX9DIS DISPLAY PROCEDURE REPORT FOLLOWS
PROCEDURE  STATUS   ACTIVE  QUED  MAXQ  TIMEOUT  FAIL WLM_ENV
CUSTPROC   STARTED    0       0     1      0       0  WLMDB21
SAMPPRC1   STOPQUE    0       5     5      3       0  WLMSAMP
SAMPPRC2   STARTED    2       0     6      0       0  WLMSAMP
GETDATA1   STOPREJ    0       0     1      0       0  WLMDB21
DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE

DSN9022I = DSNX9COM '-DISPLAY PROC' NORMAL COMPLETION

Keep in mind that the information returned by  DISPLAY PROCEDURE is dynamic. By the time the information is displayed, it is possible that the status could have changed.

User-Defined Functions (UDFs)

For UDFs, you can use the DISPLAY FUNCTION SPECIFIC command to monitor UDF statistics. This command displays one output line for each function that a DB2 application has accessed.  Similar to what is shown for stored procedures, the DISPLAY FUNCTION SPECIFIC command will show:

• Whether the named function is currently started or stopped, and why
• How many requests are currently executing
• The high-water mark for concurrently running requests
• How many requests are currently queued
• How many times a request has timed out
• The WLM environment in which the function executes

When displaying information about stored procedures and UDFs using the DISPLAY PROCEDURE and DISPLAY FUNCTION SPECIFIC commands, a status is returned indicating the state of the procedure or UDF. A procedure or UDF can be in one of four potential states:

STARTED	Requests for the function can be processed
STOPQUE	Requests are queued
STOPREJ	Requests are rejected
STOPABN	Requests are rejected because of abnormal termination

Summary

When using stored procedures and/or user-defined functions, be sure to use the DISPLAY command to keep track of their status.