Return to Home Page

Reorganizing the DB2 Catalog & Directory

by Craig S. Mullins

Since Version 4 of DB2 it has been possible for DBAs to expediently reorganize the DB2 Catalog and DB2 Directory in a systematic manner using the native IBM REORG utility. This article will describe how to reorganize the DB2 Catalog and provide implementation tips and advice.

The DB2 Catalog is the central repository for DB2 object and user meta data. DB2 is constantly referring to that meta data as it processes applications and queries. The physical condition of the table spaces and indexes that comprise the DB2 Catalog is therefore a major component in overall DB2 subsystem performance.

Likewise, the DB2 Directory contains internal control structures such as DBDs. skeleton cursor tables, and skeleton package tables that can be accessed only by DB2 itself. The information in the DB2 Directory is critical for database access, utility processing, plan and package execution, and logging. It is quite obvious that efficient access to this critical information should be of paramount importance.

Prior to DB2 Version 4, reorganization of the DB2 Catalog and DB2 Directory using was not possible using the REORG utility. The only option for any type of "reorganization" activity was to run the RECOVER INDEX utility on DB2 Catalog indexes. This rebuilt the indexes, but had no impact on the underlying data housed in actual physical table spaces.

As of DB2 Version 4 it is permitted to execute the REORG utility on table spaces and indexes in the DB2 catalog database (DSNDB06) and on specific table spaces (SCT02, SPT01, and DBD01) in the DB2 directory database (DSNDB01).

To determine when to reorganize the system catalog, DBAs can use the same basic indicators used to determine whether application table spaces should be reorganized. Although it always has been a wise course of action to execute RUNSTATS on the DB2 Catalog table spaces, it becomes even more important now that these table spaces can be reorganized. The RUNSTATS utility collects statistical information that is used by the optimizer to generate access paths. Additionally, these statistics can be analyzed to determine when a REORG should be run. When RUNSTATS is run for a catalog table space, the statistics about that system catalog table space are gathered and then stored in the DB2 Catalog tables themselves! The table contained in Figure 1 should server as a basic guide to help in determining when to reorganize system catalog table spaces and indexes.

Note that the data in Figure 1 can be used for data contained in application table spaces and indexes as well as DB2 catalog table spaces and indexes. For application table spaces, though, you may also wish to use the PERCDROP column in SYSIBM.SYSTABLEPART to determine when to reorganize application table spaces. PERCDROP does not apply to DB2 Catalog table spaces because tables can not be dropped from the DB2 Catalog.

The column and table name where the statistic can be found is given in the first two columns of the chart. The third column indicates whether the statistic is applicable for a table space or an index. The fourth column gives an indication of the impact of the statistic. A plus (+) sign indicates that you should REORG more frequently as the value in that column gets larger. A minus (-) sign indicates that you should REORG more frequently as the value gets smaller. As the number of "+" or "-" signs increases, the need to REORG becomes more urgent. For example, as FAROFFPOS gets larger, the need to REORG is more urgent, as indicated by the five plus (+) signs.

For the SYSDBASE, SYSVIEWS and SYSPLAN catalog table spaces, the value for the FAROFFPOS and NEAROFFPOS columns of SYSINDEXPART can be higher than for other table spaces before they need to be reorganized.

In addition to the guidelines in Figure 1, consider DB2 Catalog and DB2 Directory reorganization in the following situations:

• To reclaim space and size table spaces appropriately when DB2 Catalog and Directory data sets are not using a significant portion of their allocated disk space (PRIQTY).
• When it is necessary to move the DB2 Catalog and Directory to a different storage device.
• When the DB2 Catalog and Directory data sets contain a large number of secondary extents.

It is a more difficult prospect to determine when the DB2 Directory table spaces should be reorganized. The RUNSTATS utility does not maintain statistics for these "table spaces" like it can for the DB2 Catalog.

However, it is possible to base the reorganization of the DB2 Directory table spaces on the reorganization schedule of the DB2 Catalog table spaces. In fact, in certain situations, it is imperative that specific DB2 Directory table spaces are reorganized when a "companion" DB2 Catalog table space is reorganized. The chart contained in Figure 2 provides information on keeping the DB2 Catalog and DB2 Directory table spaces "in sync."

These table spaces are logically related and DB2 requires that you reorganize them at the same time to keep them synchronized.

Database Name: DSNDB06

Table Spaces:
SYSCOPY contains image copy information
SYSDBASE contains database object information
SYSDBAUT contains database and database authority information
SYSDDF contains data distribution details
SYSGPAUT contains resource authority information
SYSGROUP contains storage group information
SYSOBJ contains object/relational information
SYSPLAN contains plan information
SYSPKAGE contains package information
SYSSTATS contains optimization statistics
SYSSTR contains translation and check constraint information
SYSUSER contains user authority information
SYSVIEWS contains view information

Database Name: DSNDB01

Table Spaces:
DBD01 contains database descriptor information (1 table)
SCT01 contains skeleton cursor table information (1 table)
SPT02 contains skeleton package table information (1 table)
SYSLGRNX contains recovery log range information (1 table)
SYSUTILX contains utility processing information (2 tables)

The DB2 Catalog is composed of 13 table spaces and 63 tables all in a single database, DSNDB06. There are six DB2 Directory table spaces (refer to Figures 3 and 4). DB2 has different rules for different sets of these table spaces. There are three groupings of table spaces:

1. Can not be reorganized at all
2. Can be reorganized using normal REORG procedures
3. Can be reorganized using special REORG procedures

There are only two table spaces in the first grouping of table spaces which can not be reorganized at all: DSNDB01.SYSUTILX and DSNDB01.SYSLGRNX. Do not attempt to reorganize these table spaces as DB2 will not permit it.

The second grouping of table spaces are those that the REORG utility processes as it would any other table space:

• DSNSB06.SYSCOPY
• DSNDB06.SYSDDF
• DSNSB06.SYSGPAUT
• DSNDB06.SYSOBJ
• DSNSB06.SYSPKAGE
• DSNSB06.SYSSTATS
• DSNSB06.SYSSTR
• DSNSB06.SYSUSER
• DSNSB01.SCT02
• DSNSB01.SPT01

The third, and final grouping of table spaces, must be processed differently than other table spaces:

• DSNDB06.SYSDBASE
• DSNDB06.SYSDBAUT
• DSNDB06.SYSGROUP
• DSNDB06.SYSPLAN
• DSNDB06.SYSVIEWS
• DSNDB01.DBD01

These six table spaces require special "handling and care." Because they have a different internal configuration than most other table spaces, a different calculation is required for the size of the unload data set (SYSREC) used during the REORG utility. These table spaces contain internal links. Links are internal pointers that tie the information in their tables together hierarchically. A link can be thought of as a type of parent-child relationship that. Due to these links, the BUILD and SORT phases of the REORG utility are not executed.

The WORKDDN, SORTDATA, SORTDEVT, SORTNUM options are ignored when reorganizing these table spaces.

Also, the REORG utility can not be restarted from the last checkpoint when used against these six table spaces. Instead, it must be restarted from beginning of the PHASE.

Also, as mentioned before, a different set of steps must be executed during reorganization for these table spaces.

The following steps should be used when reorganizing the six "different" table spaces (DSNDB06.SYSDBASE, DSNDB06.SYSDBAUT, DSNDB06.SYSGROUP, DSNDB06.SYSPLAN, DSNDB06.SYSVIEWS, and DSNDB01.DBD01):

1. Calculate size of unload data set (SYSREC)

The SYSREC data set for the "special" table spaces has a different format than the other table spaces. This causes a special calculation to be required to determine its size. The equation to use is:

DATA SET 
SIZE IN BYTES = (28 + LONGROW) * NUMROWS

NUMROWS is the number of rows to be contained in the data set and LONGROW is the length of the longest in the table space. The value for LONGROW can be determined by running the following SQL statement:

SELECT MAX(RECLENGTH)
FROM   SYSIBM.SYSTABLES
WHERE  DBNAME = 'DSNDB06'
AND    TSNAME = 'name of table space'
AND    CREATOR = 'SYSIBM';

2. Ensure incompatible operations are not executing

3. Start database DSNDB01 and DSNDB06 for read only access

4. Run QUIESCE and DSN1CHKR utilities

5. Take full image copy of entire DB2 Catalog and Directory table spaces

6. Start DSNDB01 and DSNDB06 for utility access

7. Execute REORG utility

8. Take full image copy of entire DB2 Catalog and Directory table spaces

9. Start table space and associated indexes for read/write access

The following steps should be used when reorganizing the remaining, "regular" system catalog and directory table spaces:

1. Calculate size of unload data set (SYSREC) using the normal calculation:
   
   DATA SET SIZE IN BYTES = LONGROW * NUMROWS
   
   In this case it is unnecessary to add the additional 28 bytes to the length of the longest row. This is because these system catalog table spaces do not utilize links.
2. Ensure that incompatible operations are not concurrently executing (see the next section for an explanation of incompatible operations).
3. Start the table space and its associated indexes for read only access.
4. Run CHECK INDEX on all indexes associated with the table space that is being reorganized.
5. Take a full image copy of the entire DB2 Catalog and Directory table spaces.
6. Start the table space and its associated indexes for utility access.
7. Execute the REORG utility.
8. Take a full image copy of the entire DB2 Catalog and Directory table spaces.
9. Start the table space and any associated indexes for read/write access.

These steps should be familiar to you because they closely follow the steps executed during the reorganization of an application data table space. There are several additional required steps added as precautions because of the critical nature of the DB2 catalog and directory.

In addition to the procedures outlined previously, there are several restrictions on the manner in which the REORG TABLESPACE utility can be used with system catalog table spaces. Firstly, recall that the SYSUTILX and SYSLGRNX table spaces in the DB2 Directory can not be reorganized.

Furthermore, when reorganizing the DB2 Catalog (DSNDB06) and DB2 Directory (DSNDB01) table spaces the following options can not be used:

• The UNLOAD ONLY option is not permitted.
• The LOG YES option is not permitted because image copies are explicitly required following a DB2 Catalog and/or DB2 Directory reorganization.

Also, the reorganization of two specific table spaces are treated differently than any other in the manner in which the are tracked by DB2. Generally, DB2 will record the reorganization of any table space in the SYSIBM.SYSCOPY system catalog table. However, DB2 records the reorganization of the DSNSB06.SYSCOPY and DSNDB01.DBD01 table spaces in the log instead.

Finally, in many 24 x 7 environments, it may be necessary to reorganize the system catalog and dictionary while it is being accessed. However, because of the central nature of the system catalog and directory to the operation of DB2, there are restrictions on concurrent activity during catalog reorganization. These restrictions on concurrent activity are listed below:

• ALTER, DROP, and CREATE statements can not be executed during the reorganization of any DB2 Catalog or DB2 Directory table space with the exception of SYSIBM.SYSSTR and SYSIBM.SYSCOPY.
  
• The BIND and FREE commands can not be issued when the following table spaces are being reorganized: 
     SYSIBM.SYSDBAUT
     SYSIBM.SYSDBASE
     SYSIBM.SYSGPAUT
     SYSIBM.SYSPKAGE
     SYSIBM.SYSPLAN
     SYSIBM.SYSSTATS
     SYSIBM.SYSUSER
     SYSIBM.SYSVIEWS
  
• No DB2 utility can be running while SYSIBM.SYSCOPY, SYSIBM.SYSDBASE, SYSIBM.SYSDBAUT, SYSIBM.SYSSTATS, and/or SYSIBM.SYSUSER are being reorganized.
  
• No plan or package may be executed during the reorganization of SYSIBM.SYSPLAN and SYSIBM.SYSPKAGE.
  
• The GRANT and REVOKE statements can not be issued when REORG is being run on SYSIBM.SYSDBASE, SYSIBM.SYSDBAUT, SYSIBM.SYSGPAUT, SYSIBM.SYSPKAGE, SYSIBM.SYSPLAN, and/or SYSIBM.SYSUSER.

The ability to reorganize the DB2 catalog and directory table spaces provides the DBA with a potent new tool for his system tuning arsenal. If you have not yet started to run RUNSTATS on the system catalog table spaces, begin to do so immediately. This will enable you to determine when your system catalog will need to be reorganized. Good luck and happy reorganizing.

From DB2 Update (Xephon) July 2000.

© 2000 Craig S. Mullins, All rights reserved.
Home.