Quick and Easy Performance Data Using Db2 Display

Although a Db2 performance monitor is probably the best method of gathering information about your Db2 subsystems and databases, you can gain significant insight into your Db2 environment using the simple DISPLAY command. The DISPLAY command can be used to return information about the status of Db2 data sharing groups, databases and table spaces, threads, stored procedures, user-defined functions, utilities, and traces; it can also monitor the Resource Limit Facility (RLF) and distributed data locations. Let’s take a quick tour of the useful information provided by the DISPLAY command.

Database Information

There are eight variations of the DISPLAY command that you can utilize, depending on the type of information you are looking for. Probably the most often-used variation of the DISPLAY command is the DATABASE option. By running the DISPLAY DATABASE command, you can gather information on Db2 databases and table spaces. The output of the basic command will show the status of the objects specified along with any exception states that apply. For example:

-DISPLAY DATABASE(DBNAME)

Issuing this command will display details on the database named DBNAME, including information about the table spaces and indexes in that database. Using this simple command you can easily find all the table spaces and indexes within any database.

The status information for each table space and index is useful, too. When a status other than RO or RW is encountered, the object is in an indeterminate state or is being processed by a Db2 utility. The possible statuses that Db2 can assign to a page set are detailed in table 1.

Table 1: Page Set Status Definitions.

Of course, there are many additional options that can be used in conjunction with the DISPLAY DATABASE command. The following options can be used to narrow down the amount of information displayed:

• The SPACENAM option can be used to limit the display to a named table space; furthermore, you can combine SPACENAM with PART to limit the display to only specific partitions
• The USE option displays what processes are using resources for the page sets in the database
• The CLAIMERS option displays the claims on the page sets in the database
• The LOCKS option displays the locks held on the page sets in the database
• The LPL option displays the logical page list entries
• The WEPR option displays the write error page range information.

Additionally, for partitioned page sets, you can specify which partition, or range of partitions, that you wish to display.

The OVERVIEW option can be specified to display each object in the database on its own line. This condenses the output of the command and makes it easier to view. The OVERVIEW keyword cannot be specified with any other keywords except SPACENAM, LIMIT, and AFTER.

Another tactic that can be used to control the amount of output generated by DISPLAY DATABASE is to use the LIMIT parameter. The default number of lines returned by the DISPLAY command is 50, but the LIMIT parameter can be used to set the maximum number of lines returned to any numeric value. For example:

-DISPLAY DATABASE(DBNAME) LIMIT(300)

Using the LIMIT parameter in this manner would increase the limit to 200 lines of output. To indicate no limit, you can replace the numeric limit with an asterisk (*).

Finally, you can choose to display only objects in restricted or advisory status using either the ADVISORY or RESTRICT key word. For example:

-DISPLAY DATABASE(DBNAME) SPACENAM(*) RESTRICT LIMIT(*)

Which will product output like this:

Buffer Pool Details

The DISPLAY BUFFERPOOL command can be issued to display the current status and allocation information for each buffer pool. For example:

We can see by reviewing these results that BP0 has been assigned 20,000 pages, all of which have been allocated. Also, there are currently 485 in-use/updated pages in BP0. The output also shows us the current settings in use for the bufferpool, including PGFIX, page stealing method, frame size, and each of the sequential steal and deferred write thresholds.

For additional information-buffer-pool details, you can specify the DETAIL parameter. Using DETAIL(INTERVAL) produces buffer pool usage information since the last execution of DISPLAY BUFFERPOOL. To report on buffer pool usage since the pool was activated, specify DETAIL(*). In each case, Db2 will return detailed information on buffer-pool usage such as the number of GETPAGEs, prefetch usage, and synchronous reads. The detailed data returned after executing this command can be used for rudimentary buffer pool tuning. For example, you can monitor the read efficiency of each buffer pool using the following formula:

  (Total GETPAGEs) / [ (SEQUENTIAL PREFETCH) +  
                       (DYNAMIC PREFETCH) + 
                       (SYNCHRONOUS READ) ]

The higher the number, the better. Additionally, if buffer pool I/O is consistently high, you might consider adding pages to the buffer pool to handle more data.

You can gather even more information about your buffer pools using the LIST and LSTATS parameters. The LIST parameter lists the open table spaces and indexes within the specified buffer pools; the LSTATS parameter lists statistics for the table spaces and indexes reported by LIST. Statistical information is reset each time DISPLAY with LSTATS is issued, so the statistics are as of the last time LSTATS was issued.

Finally, you can use a related command, DISPLAY GROUPBUFFERPOOL (for a data sharing implementation), to display the status of Db2 group buffer pools and their related statistics (size, castout threshold, checkpoint interval, and so on.

Utility Execution Statistics

If you are charged with running (IBM) Db2 utilities, another useful command is DISPLAY UTILITY. Issuing a DISPLAY UTILITY command will cause Db2 to display the status of all active, stopped, or terminating utilities.

So, if you are in over the weekend running REORGs, issuing an occasional DISPLAY UTILITY allows you to keep up-to-date on the status of the job. By monitoring the current phase of the utility and matching this information with the utility phase information, you can determine the relative progress of the utility as it processes.

For the IBM COPY, REORG, and RUNSTATS utilities, the DISPLAY UTILITY command also can be used to monitor the progress of particular phases. The COUNT specified for each phase lists the number of pages that have been loaded, unloaded, copied, or read.

You also can check the progress of the CHECK, LOAD, RECOVER, and MERGE utilities using DISPLAY UTILITY. The number of rows, index entries, or pages, that have been processed are displayed by this command.

Log Information

You can use the DISPLAY LOG command to display information about the number of logs, their current capacity, and the setting of the LOGLOAD parameter. This information pertains to the active logs.

DISPLAY ARCHIVE will show information about your archive logs. For example:

The report shows the following information:

• The subsystem parameter values for MAX RTU (COUNT) and DEALLC PERIOD TIME as recorded in
• the DSNZPARM load module
• Current specifications for the COUNT and TIME parameters
• Availability status of allocated archive log data sets
• Volume and data set names that are associated with current archive log read requests
• KEY LABEL information

IDAA Information

If you have an accelerator (IBM Db2 Analytics Accelerator, or IDAA), then you can view the status of your accelerator servers using the DISPLAY ACCEL command. For example:

This shows the accelerator (multiple if you have more than one), its status, the number of requests (29,084,832) since it was started, number of active requests, queued request, and the maximum number queued.

Stored Procedure and UDF Information

If your organization uses stored procedures and/or user-defined functions, the DISPLAY command once again comes in handy. You can use the DISPLAY PROCEDURE command to monitor stored procedure statistics. This command will return the following information:

• 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
• The WLM environment in which the stored procedure executes

Here is an example of the output of the DISPLAY PROCEDURE command:

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.

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, as documented in Table 2.

Table 2: Possible UDF and Procedure States.

Display Stats

Another interesting option is the DISPLAY STATS command.  The manuals describes it somewhat nebulously as being able to display “statistics about the use of resources by Db2 for certain processes.” Fair enough, but what does that mean?

Well, there are three options that can be useful for DBA and sysprogs to use. The first two are specific to index FTBs and their usage.

The first option, DIS STATS(INDEXMEMORYUSAGE), will return statistics about the current amount of memory that Db2 uses for fast traversal of indexes. The list will show the DBID, PSID, DBNAME, and IX-SPACE, as well as the LVL and PART number, along with the SIZE in KB of memory it is using for fast index traversal.

The next option, DIS STATS(INDEXMEMORYUSAGE), can be used to display of index traverse counts for a specific index, or for a specified number of indexes.

The final option for DIS STATS is LOGREADERTASKS, which can be used to show statistics about log reading tasks, if you use IDAA or IBM Data Gate. The information returned by this command includes the current status of the log reading tasks (RUNNING. READING, SUSP READ, SUSP EOS), the current RBA or LRSN position of the log reading task within the log record, the number of records that have been received, the amount of time that has elapsed since the log reading task was started, the correlation ID that identifies the system task, and the location name of the IDAA or Data Gate instance that initiated the asynchronous log reader task.

Other Information

There is a wealth of additional information that the DISPLAY command can uncover.

• For distributed environments, use DISPLAY DDF to show DDF configuration and status information, as well as statistical details on distributed connections and threads; use DISPLAY LOCATION to show information about distributed threads.
• For data sharing, you can use the DISPLAY GROUP command to display information about the data-sharing group (including the version of Db2 for each member); and (as mentioned earlier) DISPLAY GROUPBUFFERPOOL can be used to show information about the status of Db2 group buffer pools.
• If you use the Resource Limit Facility, the DISPLAY RLIMIT command can be used to show the status of the RLF, including the ID of the active Resource Limit Specification Table (RLST).
• You can also use the DISPLAY PROFILE command to determine if profiling is active or inactive. The status codes that can be returned by this command are as follows:
  • ON - Profiling is active.
  • OFF - Profiling is inactive.
  • SUSPENDED - Profiling was active, but is now suspended due to error conditions.
  • STARTING - Profiling is being started, but has not completed.
  • STOPPING - Profiling has been stopped, but has not completed.
• The DISPLAY BLOCKERS command can be used to identify locks and claims that active threads hold against a database (or databases). This can help if you are having problems getting work done because of concurrent locking issues.

• To display active and in-doubt connections to Db2 for a specified connection or all connections, use the DISPLAY THREAD command. A Db2 thread can be an allied thread, a database access thread, or a parallel task thread. Threads can be active, inactive, indoubt, or postponed. There are a number of options for displaying thread information, and you can narrow or expand the type and amount of information you wish to retrieve based on type of thread and the amount of detail you desire.
• The Db2 command DISPLAY DYNQUERYCAPTURE can be used to display all currently active dynamic query capture monitors.
• The Db2 command DISPLAY ML displays the current status of IBM Db2 AI for z/OS.

And finally, the DISPLAY TRACE command can be used to list your active trace types and classes along with the specified destinations for each.

Summary

The Db2 DISPLAY command is indeed a powerful, and simple tool that can be used to gather a wide variety of details about your Db2 subsystems and databases. Every DBA should know how to use DISPLAY and its many options to simplify their day-to-day duties and job tasks.

Craig S. Mullins is president of Mullins Consulting, Inc., an independent consulting and strategy firm specializing in database management and mainframe systems. Craig has worked with Db2 for z/OS since Version 1 and has experience as an application developer and DBA. He has been appointed an IBM Gold Consultant and IBM Champion for Data and AI by IBM, and as an Influential Mainframer by Planet Mainframe. You can contact Craig via his web site at http://www.MullinsConsulting.com