December 21, 2010

DB2 Catalog Changes

In the early years dynamic SQL was never used in a production environment. There were several reasons for this, but the biggest was catalog contention. The locks held in any user's dynamic ad hoc SQL statement could bring down a new version of a program being moved into production.

As dynamic SQL's use has grown with Java's emergence, customers continue to struggle with deadlock and timeout issues on the catalog. The continued contention caused by having multiple tables in the same table space led IBM to split up the tables into separate table spaces. IBM moved the tables into partition by growth (PBG) universal table spaces. PBG table space can be managed like a segmented table space, but it also provides the capability extend very large tables into partitions.

 During the final phase of migration (new function mode) of an existing DB2 subsystem to DB2 10, all new available DB2 10 features are made available. During this phase of the migration, some of the DB2 catalog tables will be moved into the new PBG table space. Here's the list:

  • DSNDB01.DBD01
  • DSNDB01.SPT01

IBM also reduced locking by removing internal links. Now each table space uses row-level locking, and all tables are in reordered-row format.

It's important to be aware of these table spaces, because this change could impact your current utility jobs like ImageCopy and reorgs.

In a future DB2utor blog entry, I will cover some of the other catalog changes as well.