Avoiding catalog contention when dropping a table space

Dropping an object can cause catalog contention because the Db2 database manager must remove all rows that relate to that object from all tables in the catalog and directory. When the database manager removes the catalog and directory table rows, it must get an X lock on every row.

Procedure

Take the following steps to avoid catalog contention when you explicitly or implicitly drop a table space. These steps reduce the number of locks that the database manager must acquire when it processes the DROP statement.

  1. Run the MODIFY STATISTICS utility with the DELETE and AGE(*) options on the table space to remove all statistics history for the table space.
  2. Run the MODIFY RECOVERY utility on the table space to remove rows from the SYSIBM.SYSCOPY catalog table, the SYSIBM.SYSOBDS catalog table, and the SYSIBM.SYSLGRNX directory table that are related to the table space.
  3. Drop the table space.
  4. Issue the COMMIT statement as soon as possible after you drop the table space.