Navigating the Db2 catalog

This section shows you how to use row commands to navigate the catalog and to locate the objects you want. In the previous section, you learned how to use the catalog display facilities.

Some row commands perform different, but related, functions depending on the type of object displayed. The CL (columns) row command, used in this section, is one of those commands. When CL is executed in a row containing a table object, it opens a window of table columns. When CL is executed in a row containing an index object, it opens a window of index columns.

The flexibility of a single command to perform similar functions makes File-AID for Db2 Object Administration easier for you to learn and remember command names. With CL, for example, you only have to remember one command to display related columns for column users, indexes, tables, and views.

What’s in this section

While performing the step-by-step instructions for navigating the catalog, you learn these new techniques:

Using File-AID for Db2 Object Administration to generate a WHERE clause to query the catalog
Controlling the location of a window when it opens
Displaying related tablespaces, tables, and indexes.

This section also includes information about object-type codes.

About object-type codes

In this section, you learn how a single row command (CL) displays columns for an object. The row commands that display related information about any object are based on the following object-type codes:

Code	Description	Code	Description
AL	Aliases	PL	Plans
CA	Column Authorizations	QA	Sequence Authorizations
CL	Columns	RA	Resource Authorizations
DA	Database Authorizations	SA	System Authorizations
DB	Databases	SC	Schemas
DT	Datatypes	SG	Storage groups
FK	Foreign keys	SP	Stored Procedures
FN	Functions	SQ	Sequence objects
IP	Index partitions	SY	Synonyms
IX	Indexes	TA	Table Authorizations
LK	Limit Keys	TB	Tables
MA	Schema Authorizations	TP	Table partitions
MQ	Materialized Query Tables	TR	Triggers
PA	Plan Authorizations	TS	Tablespaces
PK	Packages	VA	View Authorizations
PKA	Packages Authorizations	VW	Views

For example, to display related plans for an object, you would execute the PL row command for that object.

File-AID for Db2 Object Administration uses these same object-type codes for other functions. For example, fast-path commands use these codes.

Step-by-step instructions

These instructions show you how to display an IBM sample database using the Database Display Facility. Then you use row commands to display related objects. The sequential steps include:

Displaying the Sample Database
Displaying Tablespace and Related Table Details
Displaying Columns and Indexes

This section uses a sample Db2 database provided by IBM named DSN8D91A. If this database is not available on your system, substitute one that you can access.

Displaying the sample database

To start the navigation, this section uses the Database Display Facility to locate and display IBM’s sample database.

Access File-AID for Db2 Object Administration from the File-AID for Db2 Utilities Menu. The File-AID for Db2 Object Administration Main Menu should be displayed.
Select DATABASE from the Main Menu.
The Database Display Facility pops up (see the following figure).
Database Display Facility
Select NAME to select a display of databases by name.
A WHERE clause window opens (see the following figure). In this window, File-AID for Db2 Object Administration provides a default SQL WHERE clause to query the Db2 catalog. In the window’s title is the name of the Db2 catalog table to be queried, in this case, SYSDATABASE.
Note that File-AID for Db2 Object Administration has positioned the cursor on the percent sign (%).
WHERE Clause to Query the Catalog. File-AID for Db2 provides a default WHERE clause that you can modify as necessary.
Complete the WHERE clause by inserting the letters DSN8 before the percent sign.
The percent sign acts as a wildcard to list all databases beginning with DSN8. The statement should look like the following example:
DB.NAME LIKE ’DSN8%’
Press <Go> to execute the WHERE clause.

A display window opens that shows databases with names that begin with DSN8 (see the following figure). When a list of databases is displayed, you can place the cursor on the sequence numbers and press <Help>. This opens a window listing all of the row commands available in the database display window. The default <Help> key is PF1.

Databases Selected by WHERE Clause. This window is maximized.

Displaying tablespace and related table details

This section shows you how to display details about tablespaces in a database and about tables in a tablespace.

When many row commands are executed, they open a new window. The upper-left corner of these windows is placed where your cursor is currently positioned. You don’t have to move the cursor before executing a row command. You’ll find, however, that you want to control the placement so new windows do not overlay important information in existing windows.

Be sure the database display is showing before you continue. Your screen should look similar to the previous figure .

Type the TS (tablespace detail) row command next to DSN8D91A (row 1 in the previous figure), but don’t press Enter yet. (Remember, if you’re on a different release of Db2, this name will be different.)
Place the cursor where you want the upper-left corner of the new window to be placed and then press Enter.
A window opens that shows related tablespaces in the database (see the following figure). When a list of tablespaces is displayed, you can place the cursor on the sequence numbers and press <Help>. This opens a window listing all of the row commands available in the tablespace display window. The default <Help> key is PF1.
Related Tablespaces Display
Type the TB (table detail) row command next to DSN8S91E in the tablespace display window, but don’t press Enter yet.
Place the cursor where you want the next window to be opened and press Enter.
A table display window opens (see the following figure). Only one table is listed: EMP. The upper-left corner is located where your cursor was.
Tables in Tablespace Display
Maximize the table display window.
Resizing a window to the size of the screen makes it easier to read. For a reminder on maximizing windows, see Techniques for Managing Windows.

When a list of tables is displayed, you can place the cursor on the sequence numbers and press <Help>. This opens a window listing all of the row commands available in the table display window.

Displaying columns and indexes

The last section opened a window showing the tables in tablespace DSN8D91E. This section uses that display to locate related columns and related indexes.

The maximized table display must be showing before you continue.

Execute the CL (display column) row command next to EMP.
A window opens that displays columns in the table (see the following figure). When a list of columns is displayed, you can place the cursor on the sequence numbers and press <Help>. This opens a window listing all of the row commands available in the column display window.
Table Columns
Press <End> to remove the table columns window.
The maximized table display window must be showing.
Execute the IX (related indexes) row command next to EMP.
The table indexes window opens (see the following figure).
Table Indexes
Execute the CL row command in the sequence area next to XEMP1.
An index columns window opens (see the following figure). Compare this display with the one you generated using the same CL row command in step 1 of this procedure.
Index Columns
Press <End> to return to the Tables in Tablespace Display window.

Displaying partitions and Limitkey values

Table EMP is in a partitioned tablespace. You can display the partitions and limitkey values from the table display.