Although a DB2 performance monitor is probably the best solution for gathering information about your DB2 subsystems and databases, you can gain significant insight into “what is going on out there” using the simple DISPLAY command.
In today's blog post, and a series of subsequent posts, we will learn about the DISPLAY command and how it can be used to monitor various aspects of DB2.
There are eight variations of the DISPLAY command that you can utilize, depending on the type of information you are looking for. 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 start with coverage of what is probably the most often-used variation of the DISPLAY command, looking into database information.
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 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 here:
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.
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.
And be sure to tune in for the next blog post which will look into using DISPLAY to uncover information about your DB2 buffer pools.
0 comments:
Post a Comment