Blog
DB2utor

Categories

August 09, 2011

DB2 10 Reduced Catalog Contention

For years DBAs have had to schedule times to perform DB2 object management tasks--like using DDL statements such as CREATE and ALTER or making program changes requiring BIND and REBIND--because of contention on the DB2 catalog. This contention is usually caused by running a dynamic SQL statement which takes catalog locks. DB2 object changes using DDL cannot be made while these dynamic SQL statements are running.

DB2 10, however, offers some new function to reduce contention. First, catalog links are eliminated, and catalog tables are converted from page-level locking to row-level locking. In addition, each table is put into a separate partition-by-growth table space. The advantage of using partition-by-growth table space is better space map management and improved availability by avoiding out of space conditions. A new partition is added to the table space as the previous partition fills up.

You'll benefit from this redesign when executing any of these DDL statements:

  • CREATE TABLESPACE, TABLE, and INDEX
  • ALTER TABLESPACE, TABLE, INDEX
  • DROP TABLESPACE, TABLE, and INDEX
  • CREATE VIEW, SYNONYM, and ALIAS
  • DROP VIEW and SYNONYM, and ALIAS
  • COMMENT ON and LABEL ON
  • GRANT and REVOKE of table privileges
  • RENAME TABLE
  • RENAME INDEX
  • ALTER VIEW

I'd like to hear about your experiences with the catalog redesign. Have you seen reduced deadlock timeouts? Has it allowed you to modify the way you schedule and implement DB2 object changes?