The heart of any DB2 subsystem or DB2 data sharing group is the DB2 catalog and directory. Yet, in spite of their importance, they’re almost invisible to many, and to others are often considered just another set of tables. The catalog and directory were seldom talked about before DB2 Version 8. Everyone knows they’re there and assumes they always will be. They also assume they will always work. Without these tables, however, you aren’t going to get far if you try to do anything with DB2 for z/OS. However, what happens if you should find yourself without them because of some data mishap or worse? How can you get them back?

It doesn’t matter whether you’re dealing with a localized disaster or a full-blown, move to another site-type of catastrophe. At some point, you’re going to have to get the DB2 catalog and directory back online and functioning before you can do anything else with DB2. This article takes a look at catalog and directory tables, why they’re important, and how you can ensure they will always be there if or when you need them (or at least how you can get them back if they disappear).

Before discussing what’s involved to recover the catalog and directory, let’s consider what makes up the objects we call DB2’s catalog and directory. The DB2 catalog is in database DSNDB06 while the directory lives in DSNDB01. The catalog has literally doubled in size during its lifetime, going from 11 tablespaces in DB2 V1 to a whopping 22 tablespaces in V8. The catalog could possibly grow as large as 32 tablespaces by the time DB2 9 becomes available. The directory, however, even though it’s of equal or greater importance, is considerably smaller. It currently consists of only five tablespaces and really hasn’t changed much in size (number of objects) over the years. One tablespace was added back in V2.3 and three have been renamed since DB2’s inception.

The catalog consists of 82 tables in V8 (80 in V7) that externally have similar characteristics to other tables in DB2. SQL can be run against the catalog to query its contents and even change the data in a few columns in some tables. As of V8, one noticeable difference between user tables and the tables in the catalog is that most of the catalog tables are now encoded using Unicode. However, that should have little, if any, impact on backup and recovery. The directory is quite different from the catalog. SQL can’t be used against any of the directory tables. In fact, in most cases, you probably shouldn’t be poking around inside the directory for any reason.

Another thought: Are your data sets managed in SMS? This is a requirement if you want to take advantage of the DB2 V8 features BACKUP SYSTEM and RESTORE SYSTEM. When using SMS, consider how you set up your storage groups. The catalog and directory should be in its own storage group, with the Boot Strap Data Set (BSDS) and active logs in another, all separated from your user data that’s in its own storage group.

SYSCOPY is usually the source of information about your recovery assets, but for the DB2 catalog and directory, not all the information you might need can be found there. SYSCOPY doesn’t contain details about SYSUTILX, DBD01, SYSLGRNX, and SYSCOPY itself; this information is written to the DB2 logs.

Preparing Your Backups

All the copy methods available in DB2 will work on the catalog and directory. Three specific catalog/directory tablespaces require special handling and you should manage them cautiously as far as the COPY utility is concerned:

  • DSNDB01.DBD01

First, only full image copies are allowed. Although incremental image copies are allowed against all other catalog and directory tablespaces, if it’s attempted for DSNDB06.SYSCOPY, DSNDB01.DBD01, or DSNDB01. SYSUTILX, DB2 will change the copy to a full image copy. These three tablespaces can’t be used together in a list. DSNDB06.SYSCOPY, DSNDB01. DBD01, and DSNDB01.SYSUTILX must each be on its own copy statement. Also, DSNDB01.SYSUTILX must be in its own copy job step. If you specify SHRLEVEL(REFERENCE) when copying SYSUTILX, this copy must be the only copy job running on the Sysplex. The indexes on DSNDB06. SYSCOPY and DSNDB01.SYSUTILX, if defined with the COPY YES attribute, are an exception; they can be copied along with their tablespace.

SHRLEVEL(CHANGE), another popular copy option, is allowed on all the catalog and directory tablespaces. You may want to consider using it when there are concerns about concurrently running copies. The copy utility uses DSNDB06.SYSCOPY, DSNDB01. DBD01, and DSNDB01.SYSUTILX, so be careful to avoid copying these tablespaces while executing COPY for other objects, both user and DB2. If you must copy these three tablespaces simultaneously as copies for other tablespaces, consider using the SHRLEVEL (CHANGE) COPY option. This will minimize contention, although you must use the log along with the image copy and you won’t be able to perform a TOCOPY recovery.

4 Pages