The DB2 Catalog Gets a Makeover

3 Pages

Before DB2 10, the catalog table space SYSDBASE contained the catalog tables SYSCOLAUTH, SYSCOLUMNS, SYSFIELDS, SYSFOREIGNKEYS, SYSINDEXES, SYSINDEXPART, SYSKEYS, SYSRELS, SYSSYNONYMS, SYSTABAUTH, SYSTABLEPART, SYSTABLES, and SYSTABLESPACE. This list of catalog tables covers just about every object that could be created in a database after successfully executing a CREATE DATABASE statement.

You can examine these tables with DSN1PRNT, which exposes much more information about a row in one of the tables in the previous paragraph than the columns listed in the SQL reference manual’s catalog description. Using SYSTABLES as an example, if a SELECT * were run against SYSTABLES, the first column displayed would be the column containing the table name, the column "NAME." However, in the DSN1PRNT output for a data page in a SYSDBASE containing a SYSTABLES’ row, besides the usual row header information, you would find:

  • A 4-byte forward ring pointer pointing to the next SYSTABLES record or SYSTABLESPACE record
  • Followed by a 4-byte backward ring pointer pointing to the previous SYSTABLES record or SYSTABLESPACE record
  • A 4-byte child pointer pointing to a SYSCOLUMNS record
  • A 4-byte child pointer pointing to a SYSRELS record
  • A 4-byte child pointer pointing to a SYSINDEXES record
  • A 4-byte child pointer pointing to a SYSTABAUTH record
  • A 4-byte child pointer pointing to a SYSSYNONYMS record.

All this information appears before any data that might be displayed via an SQL SELECT statement or any column listed in the SQL reference manual. It’s information exposed only by running something such as DSN1PRNT.

What should be apparent, based on the list of catalog tables contained in SYSDBASE, is the tremendous potential for contention on catalog table spaces with multiple tables. After all, SYSDBASE does use page-level locking. Any single data page in SYSDBASE could contain rows from one of the multiple tables that comprise SYSDBASE. Inserting a row for a new table into SYSTABLES, for example, could inadvertently lock a table space page containing rows for multiple other catalog tables, preventing someone else from completing their ALTER or CREATE.

DB2 10 to the Rescue

DB2 10 removes the links from the SYSDBASE, SYSPLAN, SYSDBAUT, SYSVIEWS, and SYSGROUP table spaces in the DB2 catalog (DSNDB06) and the DBD01 table space in the DB2 directory (DSNDB01). The six table spaces will be completely removed from DB2 with each table in those disappearing table spaces being moved to their own table space—one table per table space just as we suggest for user data. That translates to 13 new table spaces in the catalog database DSNDB06 just to replace the one table space, SYSDBASE. An example would be SYSTABLES moving to the new table space, SYSTSTAB. Of course, the huge benefit of this one-to-one move is that, in the case of SYSTABLES, as an example. SYSTABLES is now the only table in the table space, SYSTSTAB. This should have a huge positive effect on reducing catalog contention.

Another significant benefit is that the new table spaces being created will all be partition-by-growth universal table spaces; they’ll use the new (as of DB2 9) Reordered Row Format (RRF) and leverage row-level locking. When they’re migrated to partition-by-growth table spaces, they’ll use a SEGSIZE 64 and MAXPARTITIONS set to 1.

Besides the five table spaces that use links, two other table spaces in DSNDB06 are being replaced: SYSOBJ and SYSPKAGE. These seven pre-DB2 10 NFM table spaces will be replaced by a total of 53 new table spaces, each containing one and only one table. The complete list of new table spaces will be available in Appendix A of the DB2 10 SQL Reference.

All these catalog changes are performed by job DSNTIJEN as part of the move to DB2 10 ENFM. DSNTIJEN is comprised of multiple steps that use CATENFM and REORG SHRLEVEL REFERENCE to move the catalog from shared table spaces to dedicated table spaces.

If you’re upgrading from DB2 V8 to DB2 10 using skip migration, the DB2 catalog tables SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS are created and loaded from the original Real-Time Statistics tables (RTSs) SYSIBM.TABLESPACESTATS and SYSIBM.INDEXSPACESTATS. Because DB2 V8 didn’t support BIGINT, TOTALROWS and SPACE in SYSTABLESPACESTATS and TOTALENTRIES in SYSINDEXSPACESTATS are also converted from FLOAT to BIGINT. DSNTIJEN is also responsible for changing the data types and lengths of some other existing catalog columns.

Other Changes

Besides moving the existing catalog tables to the partition-by-growth universal table spaces, with each table ending up in its own table space, DB2 10 introduces a few other changes:

  • The directory objects (SYSUTIL, SYSUTILX, and SYSLGRNX) were changed to row-level locking.
  • The columns in SYSUTIL and SYSUTILX were reordered, and their page sizes changed to 32KB.
  • SELECT is now allowed against SYSLGRNX.

In addition, the directory table space, SPT01, will move to a partition-by-growth table space with a page size of 32KB. SPT01 will have a maximum partition size of 64GB and the maximum number of partitions will be set to 1. SPT01 will also take advantage of two Binary Large Object (BLOB) columns to eliminate the size issues found in previous versions of DB2; one BLOB column will be for the package section data and the other for the static explain blocks. Compression introduced in DB2 9 will still work for the base table rows of SPT01.

Finally, the user-defined tables SYSIBM.SYSDUMMYA, SYSIBM.SYSDUMMYE, and SYSIBM.SYSDUMMYU are moved into the DB2 catalog as part of the migration to CM. The columns SYSVIEWS.TEXT, SYSTRIGGERS.TEXT, SYSROUTINES_SRC.CREATEMST, and SYSPACKSTMT.TEXT are being moved from text columns to LOB columns with DB2 10 NFM.


The catalog and directory are the heart and soul of DB2—the parts that keep everything tied together and working correctly. They’re the single most important external components of DB2. The extensive enhancements to the catalog and directory reflect their importance. These are exciting changes you should find beneficial.

Acknowledgement: Special thanks to Chris Leung, Frank Bower and Jay Yothers, DB2 for z/OS Development, IBM Silicon Valley Lab, for their help with this article.  

To see the sidebar "Skip-Level Migration", click here

3 Pages