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 the following table.
| 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 table space, 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 table space. An error exists in the LOB column of the base table space. |
| AREST | The table space, 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 table space or the LOB table space is in the Auxiliary Warning status. This warning status indicates an error in the LOB column of the base table space or an invalid LOB in the LOB table space. |
| CHKP | The Check Pending status has been set for this table space or partition. |
| COPY | The Copy Pending flag has been set for this table space or partition. |
| DEFER | Deferred restart is required for the object. |
| GRECP | The table space, table space 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 table space, table space 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 table space, table space 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 table space, table space partition, index, index partition, or logical index partition. |
| REFP | The table space, 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 table space, table space partition, index, index partition, or logical index partition. |
| RESTP | The table space or index is in the Restart Pending status. |
| RO | The table space, tables pace partition, index, index partition, or logical index partition has been started for read-only processing. |
| RW | The table space, table space partition, index, index partition, or logical index partition has been started for read and write processing. |
| STOP | The table space, table space partition, index, index partition, or logical index partition has been stopped. |
| STOPE | The table space or index is stopped because of an invalid log RBA or LRSN in one of its pages. |
| STOPP | A stop is pending for the table space, table space partition, index, index partition, or logical index partition. |
| UT | The table space, table space partition, index, index partition, or logical index partition has been started for the execution of utilities only. |
| UTRO | The table space, table space 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 table space, table space 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 table space, table space 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. |
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 Information
-DISPLAY BUFFERPOOL (BP0)
DSNB401I ALLOCATED = 2000 TO BE DELETED = 0
IN USE/UPDATED = 12
DSNB403I ALLOCATED = 100000 TO BE DELETED = 0
BACKED BY ES = 91402
DSNB404I 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, because hiperpools are no longer supported as of V8). The output also shows us the current settings for each of the sequential steal and deferred write thresholds.
For additional information on buffer pools 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)
]
A higher read efficiency value is better than a lower one because it indicates that pages, once read into the buffer pool, are used more frequently. 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 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.
Utility Execution Information
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
Stored Procedure and UDF Information
If your organization uses stored procedures and/or user-defined functions (UDFs), 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
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.
1 comment:
He goes on to provide, “a quick tour of the useful information provided by the DISPLAY command.”
Log Buffer #135
Post a Comment