 |
Reorganizing the DB2 Catalog & Directory >> | |
||||||||||||||||||||||||||||||||||||
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. What Are the DB2 Catalog and Directory? 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). When Should the DB2 Catalog and Directory be Reorganized? 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. Figure 1. Reorganization Indicators
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:
Synchronizing System Catalog Reorganization 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." Figure 2. Reorganization Indicators
These table spaces are logically related and DB2 requires that you reorganize them at the same time to keep them synchronized.
Figure 3. DB2 Catalog Table Spaces Database Name: DSNDB06
Figure 4. DB2 Directory Table Spaces Database Name: DSNDB01
Reorganizing the DB2 Catalog 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:
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:
The third, and final grouping of table spaces, must be processed differently than other tablespaces:
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. Steps to REORG the Six "Special" 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):
Steps to REORG Regular Table Spaces The following steps should be used when reorganizing the remaining, "regular" system catalog and directory table spaces:
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. Catalog Reorganization Restrictions 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:
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:
Synopsis 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.  |