Blog
DB2utor

Categories

November 01, 2011

Using the Display Database Command

DISPLAY DATABASE is one of the most useful and versatile commands in the DBA toolbox. It's used to  display information about the status of the database, table space and/or index space. It also displays who is using an object, any locks that are being held, any claimers and any pages in error.

However, it can be challenging viewing display database command results, because it's not obvious which columns are displayed and what they mean. To get the details, look up message ID DSNT397I in the DB2 10 for z/OS MESSAGES Guide.

The columns of information change based on the type of information (USE, CLAIMERS, LOCKS, LPL, WEPR) requested.

All options have these columns in common: NAME, TYPE, PART STATUS.

USE: NAME, TYPE, PART  STATUS, CONNID, CORRID, USERID
CLAIMERS: NAME, TYPE, PART  STATUS, CONNID, CORRID, CLAIMINFO
LOCKS: NAME, TYPE, PART  STATUS, CONNID, CORRID, LOCKINFO
LPL: NAME, TYPE, PART  STATUS, LPL PAGES
WEPR: NAME, TYPE, PART  STATUS, PHYERRLO, PHYERRHI, CATALOG, PIECE

If an information parameter type isn't specified, the default columns are based on WEPR format. Here's the basic command that I’ve used for years to find any objects in a restricted state:

-DIS DB(*) SPACE(*) RESTRICT

As another example, say you've made some changes to an object and DB2 is advising a reorg. To find these notifications, issue this command:

-DIS DB(*) SPACE(*) RESTRICT ADV(AREO*)

IBM has improved DISPLAY DATABASE over the past few releases by providing some nice filtering capabilities. First, you can filter on the object name through wild cards:

-DIS DB(dbname*)          : Display all database names starting with dbname
-DIS DB(*dbname)        : Display all database names ending with dbname.
-DIS DB(*dbname*)        : Display database names that contain dbname
-DIS DB(dbname1:dbname2)    : Display all database names between dbname1 and dbname2
-DIS DB(*dbstring1*dbstring2)    : Display all database names that contain string1 and string2

This same type of filter works with the space name parameter.

When DISPLAY DATABASE is issued, it displays only 50 lines (the default setting). To increase this setting, use the LIMIT keyword. LIMIT(100), for example, would display 100 lines of output. To see all output without limit, use LIMIT(*).

A final tip: When issuing a command with a parameter such as LOCKS, you'll get all rows for the name filter, even if the object doesn't have a lock associated with it. So use the very handy ONLY parameter to return only the rows that contain locks. For example, here's how you'd display all objects on the system that have active locks:

-DIS DB(*) SPACE(*) LOCKS LIMIT(*) ONLY

So what have you learned from your own use of the display database command? Please share your experiences in Comments.