How to Extract Information from the System Catalog Tables

When working with a database, sometimes you ask the following questions.

• How many buffer pools are there in the database and what table spaces use them?

• Are there any Event Monitors and are they active?

• What are the privileges given to users of this database?

You can answer all of these questions and more by querying the system catalog tables or views. In general, there is at least one system catalog table, and therefore one system catalog view, for each database object type. Table D.1 lists some of the system catalog views. For example, if you want to know about all the tables in the database, query the SYSCAT.TABLES view. If you want to know about all the indexes, query the SYSCAT.INDEXES view.

Table D.1. System Catalog Views

Database Object	SYSCAT System Catalog View
Table	TABLES
View	VIEWS
Index	INDEXES
Data type	DATATYPES
Column	COLUMNS
Table space	TABLESPACES
Buffer pool	BUFFERPOOLS
Package	PACKAGES
Constraints	CHECKS
Referential integrity	REFERENCES
Partition groups	NODEGROUPS
Partition group definitions	NODEGROUPDEF
Stored procedures	PROCEDURES
Sequences	SEQUENCES
Event Monitors	EVENTMONITORS

The following examples show how you can extract useful information from the SYSCAT views. A database connection is required.

Example 1: Extracting Buffer Pool Data

To find out how many buffer pools are in the database and their information, issue the statement:

 SELECT * FROM SYSCAT.BUFFERPOOLS 

Figure D.1 shows sample output for this command. In this database, only one buffer pool is defined, IBMDEFAULTBP. This is the default buffer pool DB2 creates when the database is created. The buffer pool has an ID of 1. Its size is 1MB (250 pages x 4096 bytes per page). ESTORE (Extended Storage) is not enabled for this buffer pool.

Figure D.1. Sample buffer pool information

NOTE

For detailed descriptions of all the columns in system catalog tables and views, search for the table/view name in the DB2 Information Center or refer to the SQL Reference Manual, Volume 1.

You can get the same information using the Control Center, as shown in Figures D.2 and D.3.

Figure D.2. The SYSCAT.BUFFERPOOLS view in the Control Center

Figure D.3. Contents of the SYSCAT.BUFFERPOOLS view

From the Control Center, expand the All Systems folder until you find the database you want. All system catalogs tables (the SYSIBM tables) and user tables are stored in the Tables folder. All system catalog views (the SYSCAT views) and user-defined views are stored in the Views folder. Open the Views folder to display all the views. The contents of the selected folder in the left pane are displayed in the top right pane.

In the top right pane, click on the view you are interested in. The definition of the view is displayed in the bottom right pane of Figure D.2. In this example, it is the SYSCAT.BUFFERPOOLS view.

Double-click on the view name to display its contents (or right-click on the view name and select Open). Figure D.3 shows the contents of the SYSCAT.BUFFERPOOLS view.

Note that the information in Figure D.3 is the same as the output of the SELECT * FROM SYSCAT.BUFFERPOOLS statement (see Figure D.1).

Example 2: Determining Which Table Spaces Use the Buffer Pool

To find out which table spaces use the buffer pool found in Example 1, you need to query the SYSCAT.TABLESPACES view. Use the DESCRIBE TABLE command to display the columns defined in the view; then you can issue queries to display the contents of the columns you are really interested in. For example, the DESCRIBE TABLE SYSCAT.TABLESPACES statement displays the output shown in Figure D.4.

Figure D.4. Columns defined in a table

In Figure D.4, the columns you really need are the TBSPACE and BUFFERPOOLID columns. Issue

 SELECT TBSPACE, BUFFERPOOLID FROM SYSCAT.TABLESPACES 

to see the results shown in Figure D.5. You can see that there are four table spaces in the database, and all of them are using the buffer pool with an ID of 1. From Example 1, you know this buffer pool ID corresponds to the IBMDEFAULTBP buffer pool.

Figure D.5. Contents of the TABSPACE and BUFFERPOOLID columns

You can obtain the same information by displaying the contents of the SYSCAT.TABLESPACES view in the Control Center.

Example 3: Checking for Privileges and Authorization

A user with user ID guest complains that he is not able to insert any rows into a table called SYLVIAQ.TABLE1. The first thing you need to know is whether the user guest has the authority to insert into SYLVIAQ.TABLE1.

You are not sure which SYSCAT view contains the table privilege information. Therefore, you issue the LIST TABLES FOR SCHEMA SYSCAT statement to display all the available SYSCAT views, and hope to see one that might give you the table privilege information.

You browse through the output and find a table called TABAUTH. This might be the table you are looking for. You issue

 DESCRIBE TABLE SYSCAT.TABAUTH 

to display its contents. Figure D.6 shows the output.

Figure D.6. Output of the DESCRIBE TABLE command

Figure D.6 displays the contents of the SYSCAT.TABAUTH view. The view contains 13 columns: GRANTOR, GRANTEE, GRANTEETYPE…UPDATEAUTH. The DELETEAUTH column contains information about whether a user has DELETE privileges. Likewise, the INSERTAUTH column contains information about whether a user has INSERT privileges. You issue the following statement to find out what privileges the user guest has on SYLVIAQ.TABLE1:

 SELECT * FROM SYSCAT.TABAUTH WHERE GRANTEE = 'GUEST' 

As you can see in Figure D.7, the INSERTAUTH column has a value N. This means that the user guest does not have insert privileges; therefore, he cannot insert any rows. Note the SELECTAUTH column has a value Y. This means the user has SELECT privileges for the table SYLVIAQ.TABLE1.

Figure D.7. User privileges in a table

You can get the same information using the Control Center. First, browse through all the views in the Views folder, then identify the SYSCAT.TABAUTH view and display its contents.

Now you know how to query the system catalog tables to get the information you need. If you prefer, you can use the Control Center to display the contents of the system catalog tables without having to issue any SQL statements.