Reorganizing tables offline

You might need to disable table reorganization and reorganize tables offline to maintain server stability and to complete daily server activities.

Before you begin

The following table lists the tables in the database, the page size of the tables, and the buffer pools that are required to reorganize the tables. You use the buffer pool and the page size when you create a temporary table space during reorganization.
Table 1. Database table names and associated page sizes and buffer pools
Table name Page size Buffer pool
AF_BITFILES 16 K IBMDEFAULTBP
AS_VOLUME_STATUS 16 K IBMDEFAULTBP
BF_AGGREGATED_BITFILES 16 K IBMDEFAULTBP
BF_BITFILE_EXTENTS 16 K IBMDEFAULTBP
BF_DEREFERENCED_CHUNKS 16 K IBMDEFAULTBP
BF_QUEUED_CHUNKS 16 K IBMDEFAULTBP
GROUP_LEADERS 16 K IBMDEFAULTBP
BACKUP_OBJECTS 32 K LARGEBUFPOOL1
ARCHIVE_OBJECTS 32 K LARGEBUFPOOL1

About this task

Schedule offline reorganization for tables where there is an unacceptable level of database growth or degradation in server performance.

If you disable reorganization, monitor the database usage and the file systems that the database uses to ensure that you do not run out of space. For best results, monitor the database usage weekly. You can exclude reorganization of specific tables to increase the speed of reorganization. The Tivoli® Storage Manager server issues a message for tables that must be reorganized when reorganization for a specific table is disabled.

Procedure

To reorganize a table offline, complete the following steps:

  1. When the server is running, determine the amount of temporary space that is required to organize a table. The temporary space that is required is twice the value of the table size. To determine the table size, issue the following commands:
     db2 connect to tsmdb1
     db2 set schema tsmdb1
     db2 "call sysproc.reorgchk_tb_stats('T','tsmdb1.tablename')
     db2 "select tsize from session.tb_stats"
  2. Create a temporary table space to use during the reorganization. Issue the following commands:
    db2 "CREATE SYSTEM TEMPORARY 
    TABLESPACE REORG PAGESIZE pagesize 
    MANAGED BY SYSTEM USING ('path') BUFFERPOOL bufferpool 
    DROPPED TABLE RECOVERY OFF 

    The path that the database instance user owns indicates a directory. The directory is at least twice the value of the tsize table size, and is on the fastest reliable available disk.

  3. Complete a full database backup that includes the volume history.
  4. Stop the server.
  5. Issue the following commands from a DB2® command window:
    db2 force application all
    db2stop
    db2start
    db2 connect to tsmdb1
    db2 update db cfg for tsmdb1 using auto_tbl_maint off
    db2 "reorg table tsmdb1.tablename allow no access use reorg"
    db2 "drop tablespace reorg"
    db2 update db cfg for tsmdb1 using auto_tbl_maint on

    If the commands take too long to run, stop the reorganization and restore the database by using the database backup and volume history.

  6. Start the server.
  7. From the DB2 command window, issue the following commands:
    Tip: Each command is entered on one line, but is displayed here on multiple lines to make it easier to read.
    db2 connect to tsmdb1
    db2 "runstats on table tsmdb1.tablename with distribution 
    and sampled detailed indexes all"

    Ensure that the RUNSTATS command completes processing.