Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » DB2 » DB2 Mainframe Articles Archive » Display Your Way to DB2 Knowledge
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 4455
 

Display Your Way to DB2 Knowledge

by Anne Stevens

Although a DB2 performance monitor is probably the best solution for gathering information about your DB2 subsystems and databases, you can gain significant insight using the simple DISPLAY command. The DISPLAY command can be used to return information about the status of DB2 data sharing groups, databases and tablespaces, 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 tablespaces. 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 DBNAME database including information about the tablespaces and indexes in that database. So, with a simple command you can easily find all of the tablespaces and indexes within any database —  pretty powerful stuff. But the status information for each space 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.

ARBDP Index is in Advisory Rebuild Pending status; the index should be rebuilt to improve performance and allow the index to be used for index-only access again.
AREO* The tablespace, index, or partition is in Advisory Reorg Pending status; the object should be reorganized to improve performance. This status is new as of DB2 V8.
ACHKP The Auxiliary Check Pending status has been set for the base tablespace. An error exists in the LOB column of the base tablespace.
AREST The tablespace, index space, or partition is in Advisory Restart Pending status. If back-out activity against the object is not already underway, either issue the RECOVER POSTPONED command or recycle the specifying LBACKOUT=AUTO.
AUXW Either the base tablespace or the LOB tablespace is in the Auxiliary Warning status. This warning status indicates an error in the LOB column of the base tablespace or an invalid LOB in the LOB tablespace.
CHKP The Check Pending status has been set for this tablespace or partition.
COPY The Copy Pending flag has been set for this tablespace or partition.
DEFER Deferred restart is required for the object.
GRECP The tablespace, tablespace partition, index, index partition, or logical index partition is in the group buffer pool Recover Pending state.
ICOPY The index is in Informational Copy Pending status.
INDBT In-doubt processing is required for the object.
LPL The tablespace, tablespace partition, index, index partition, or logical index partition has logical page errors.
LSTOP The logical partition of a non-partitioning index is stopped.
PSRBD The entire non-partitioning index space is in Page Set Rebuild Pending status.
OPENF The tablespace, tablespace partition, index, index partition, or logical index partition had an open data set failure.
PSRCP Indicates Page Set Recover Pending state for an index (non-partitioning indexes).
PSRBD The non-partitioning index space is in a Page Set Rebuild Pending status.
RBDP The physical or logical index partition is in the Rebuild Pending status.
RBDP* The logical partition of a non-partitioning index is in the Rebuild Pending status, and the entire index is inaccessible to SQL applications. However, only the logical partition needs to be rebuilt.
RECP The Recover Pending flag has been set for this tablespace, tablespace partition, index, index partition, or logical index partition.
REFP The tablespace, index space, or index is in Refresh Pending status.
RELDP The object has a release dependency.
REORP The data partition is in a REORG Pending state.
REST Restart processing has been initiated for the tablespace, tablespace partition, index, index partition, or logical index partition.
RESTP The tablespace or index is in the Restart Pending status.
RO The tablespace, tablespace partition, index, index partition, or logical index partition has been started for read-only processing.
RW The tablespace, tablespace partition, index, index partition, or logical index partition has been started for read and write processing.
STOP The tablespace, tablespace partition, index, index partition, or logical index partition has been stopped.
STOPE The tablespace or index is stopped because of an invalid log RBA or LRSN in one of its pages.
STOPP A stop is pending for the tablespace, tablespace partition, index, index partition, or logical index partition.
UT The tablespace, tablespace partition, index, index partition, or logical index partition has been started for the execution of utilities only.
UTRO The tablespace, tablespace partition, index, index partition, or logical index partition has been started for RW processing, but only RO processing is enabled because a utility is in progress for that object.
UTRW The tablespace, tablespace partition, index, index partition, or logical index partition has been started for RW processing, and a utility is in progress for that object.
UTUT The tablespace, tablespace partition, index, index partition, or logical index partition has been started for RW processing, but only UT processing is enabled because a utility is in progress for that object.
WEPR Write error page range information.

Table 1: DB2 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:

      • USE displays what processes are using resources for the page sets in the database
      • CLAIMERS displays the claims on the page sets in the database
      • LOCKS displays the locks held on the page sets in the database
      • LPL displays the logical page list entries
      • WEPR 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.

Buffer Pool Details

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

-DISPLAY BUFFERPOOL (BP0)

DSNB401I < BUFFERPOOL NAME BP0, BUFFERPOOL ID 0, USE COUNT 90
DSNB402I < VIRTUAL BUFFERPOOL SIZE = 2000 BUFFERS
    ALLOCATED      =     2000   TO BE DELETED    =       0
     IN USE/UPDATED =       12
DSNB403I < HIPERPOOL SIZE = 100000 BUFFERS, CASTOUT = YES
    ALLOCATED      =   100000   TO BE DELETED   =       0
    BACKED BY ES   =    91402
DSNB404I < THRESHOLDS -
     VPSEQUENTIAL         = 80  HPSEQUENTIAL         = 80
     DEFERRED WRITE       = 50  VERTICAL DEFERRED WRT = 10
    IOP SEQUENTIAL       = 50
DSNB405I < HIPERSPACE NAMES - @001SSOP
DSN9022I < DSNB1CMD '-DISPLAY BUFFERPOOL' NORMAL COMPLETION

We can see by reviewing these results that BP0 has been assigned 2,000 pages, all of which have been allocated. Furthermore, we see that it is backed by a hiperpool of 100,000 pages (so this is not a V8 subsystem). The output also shows us the current settings for 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.

Finally, you can gather even more information about your buffer pools using the LIST and LSTATS parameters. The LIST parameter lists the open tablespaces and indexes within the specified buffer pools; the LSTATS parameter lists statistics for the tablespaces 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.

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.

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

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.  It shows:

      • 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

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 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).
      • To display active and in-doubt connections to DB2 for a specified connection or all connections, use the DISPLAY THREAD command.
      • 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.

--

Anne Stevens is an independent consultant and freelance writer.

Contributors : Anne Stevens
Last modified 2006-01-06 11:13 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone