Database size, database reorganization, and performance considerations

Technote (troubleshooting)


Problem(Abstract)

Unexpected database growth and degraded performance can occur over time on a Tivoli Storage Manager server version 6 or later.

Symptom

You might encounter the following symptoms:

  1. The amount of space that is used by the database grows continuously over time, and exceeds your expectations for the normal increase in client space requirements. The space that is used by the database continues to increase despite the fact that the following actions occur:
    - expiration is running
    - number of objects in the server remains the same or decreases
  2. The server performance degrades over time. For example, server operations run increasingly slower over time, even though the workload has not changed.

Cause

The database growth and degraded server performance might be a result of one or more of the following causes:

    1. Tivoli Storage Manager servers that were originally formatted on server V6.1 have DB2 9.5 tablespaces which do not have the reclaimable storage feature. Even when these servers are upgraded to server V6.2 or later, the tablespaces are still DB2 9.5.
    2. Earlier versions of the Tivoli Storage Manager server fixing levels have limited online reorganization capability.
    3. Tivoli Storage Manager servers that were formatted with V6.2 or later have DB2 9.7 tablespaces, which have the reclaimable storage feature, but certain server workloads (especially servers with large deduplicated storage pools) leave insufficient resources for online database reorganization to run without errors or conflicting with the server operations. Resolving these conflicts is done by canceling server operations to allow reorganization to progress, but this is often undesirable.
    4. When you delete large amounts of data from the server, it does not mean that large amounts of storage from the database is freed up.
    5. Occasionally, it appears that the workload on a server is static, but in fact more and more objects are being managed over time, and this causes the database to grow.

Resolving the problem

The following list indicates the problem resolution steps for the listed causes:

    1. You can use scripts to convert your database to use DB2 9.7 tablespaces. After you have the upgraded tablespaces, you can release free space to the operating system after the reorganization processes completes. The scripts must be run while the server is halted and require about t 10 to 30 hours to run. However, it can take over a hundred hours to complete. The great variability in run times is dependent on many factors such as:
      - the initial size of the database
      - the type of disk
      - amount of memory, cores, etc

      To give you a better estimate of the down time that is needed for your configuration, you can run the scripts on a test system that is close to your production environment. Contact IBM Software Service to obtain the scripts and instructions.
    2. The latest level of V6.3 and V7.1 Tivoli Storage Manager servers have the most advanced online reorganization code available. Upgrade to the latest fixing level to have that function available to you.
    3. Even when your database is converted to use DB2 9.7 tablespaces and you are running the latest reorganization code, your workloads might be so intense that your server and reorganization activities are not able to progress as needed. In server levels 6.3.5 and 7.1.1, you can disable reorganization on specific tables that are problematic. See the section IC95301, below, for details on that support.

      For excluded tables on the fixing levels, you will be informed if reorganization needs to be run on those tables. If so, see the section Offline table reorganization, below, for instructions.

      In addition, for databases with deduplicated storage pools, database growth might be caused by unexpected interactions. See Techdoc 1596944 for details on quantifying these unexpected interactions.
    4. Generally, after you delete large amounts of data from your server, you will need to run reorganization and use database manager commands to free the storage to the operating system. If you delete large amounts of data on your server, you can enable both online table and index reorganization. If you experience locking problems or unacceptable server performance degradation while online reorganization is running, see the section IC95301, below, for details on how to resolve those problems. If you need to run offline reorganization, see the section Offline Table Reorganization, below, for instructions. After the reorganizations have completed, see the section Releasing space to the operating system, below for instructions.

      To determine whether a particular table needs to be reorganized, consult the DB2 REORGCHK documentation. Running reorgchk with CURRENT STATISTICS rather than UPDATE STATISTICS (the default) is recommended because of the potential for impacting server performance. If F1, F2, or F3 is indicated, table reorganization should be run. If F5, F7, or F8 is indicated on any index for any table, table reorganization should be run on that table.
    5. To determine whether the number of objects that your server is managing is static over time, see section Verifying that the number of objects managed by the server is relatively static of Techdoc 1592404 for additional diagnosis information.


Hide details for Expected reorganization activityExpected reorganization activity
If server-initiated table and index reorganization is enabled, as needed, every table is reorganized, one-by-one, during the reorganization window. After all table reorganizations are complete, index reorganization occurs, as needed. Depending on the amount of time that it takes to reorganize each table and its indices and the duration of the reorganization window each day, reorganization can take many weeks or months to finish. After a table or index reorganization is completed, the RUNSTATS command is run on the table. Depending on the size of the table, the command can take many hours to complete. After the RUNSTATS command is completed, reorganization activity continues. Each table or index is reorganized once, and is not reorganized again for at least 20 days after it was first reorganized.

After the initial reorganization of all tables and their indices is completed, the data is consolidated in the respective tables and index spaces. Free space is located at the end of the tables and the tablespaces. If this free space must be released to the operating system, see Releasing space to the operating system.

After table and index reorganization is completed and 20 days have elapsed, the Tivoli Storage Manager server queries the database to determine whether additional reorganization of any tables or indices is needed.

The following command can be used to view the reorganization flags for the tables and the indices of the tables:
db2 reorgchk current statistics on table all >db2reorgchk.out

If F1 or F2 is indicated and 20 days have passed, the table will be reorganized. If F5 is indicated on any index for a table and 20 days have passed since the last index reorganization, the indices for that table are reorganized.

In addition, for table BF_BITFILE_EXTENTS, if F7 or F8 is indicated, the table indices are reorganized with the CLEANUP ONLY option. This is a result of APAR IC82352.

If a table reorganization is in progress at the end of the reorganization window, it is paused until the reorganization window starts the next day, when reorganization is resumed.


Hide details for Obtaining reorganization statusObtaining reorganization status

Reorganization status can be obtained from the Tivoli Storage Manager server as follows:

  • For table reorganizations, when the reorganization starts, message ANR0293I is issued. When the reorganization of the table is completed, message ANR0294I is issued.
  • For reorganizations of the indices for a table, when the reorganization starts, message ANR0317I is issued. When reorganization is completed, message ANR0318I is issued.
  • When a reorganization is completed, either for the table or the indices on the table, the RUNSTATS command is run on the table. When the command starts running, message ANR0336I is issued. When the command is complete, message ANR0337I is issued.
  • The server trace class TBREORG can be used to obtain a trace of reorganization activity.
  • If a reorganization is running, the following SQL select can be run in a DB2 CLP Window after performing a 'db2 connect to tsmdb1' command:

    db2 "select varchar(table_name,60) as table, reorg_status, reorg_start, reorg_end, case when reorg_max_counter = 0 then 0 else (reorg_current_counter * 100)/reorg_max_counter end as percent, reorg_current_counter, reorg_max_counter, reorg_phase, reorg_max_phase from table(snapshot_tbreorg('TSMDB1',-2)) as x order by reorg_start"


Hide details for Miscellaneous details Miscellaneous details
  • Several new server options can be used to tailor the reorganization process. See Reorganization options.
  • The server pauses periodically after performing table reorganization activity. During the pauses, the server can initiate a database backup, if necessary. If a database backup is currently running, no reorganization activity is started until the database backup is completed. Thus, having a current database backup takes precedence over reorganization activity. Once the database backup is completed, and if the reorganization window has not passed, reorganization activity can continue. In addition, if needed, the server initiates a full database backup while an index reorganization is running. _
  • Even though table reorganization is enabled 24 hours a day by default, it is recommended that table reorganization occur during a scheduled reorganization window where there is low server activity.
  • If index reorganization is enabled, ensure that only very minimal server activity occurs while index reorganization is running. If the reorganizations have not completed when server activity needs to resume, cancel the in-flight index reorganization. See Canceling index reorganizations.
  • Tivoli Storage Manager enables table reorganization by default. If the server has table or index reorganization enabled, DB2 reorganization capabilities are disabled. DB2 initiated reorganization is not recommended.



Hide details for Reorganization options Reorganization options

The following sections document the server options that pertain to reorganization. Before starting the server, study these options carefully. It is paramount that these options are set such that reorganization activity does not impact regular server operations. In other words, reorganization activity should not run when the server is under heavy backup, archive, or internal processing (for example, expiration, migration, or reclamation) workloads.

ALLOWREORGTABLE
    By setting the server options ALLOWREORGTABLE YES or ALLOWREORGTABLE NO, you can enable or disable server-initiated table reorganization. If the option is not specified, it defaults to ALLOWREORGTABLE YES. This option is available via the SETOPT command beginning in server versions 6.2.4 and 6.3.3. Before those fixing levels, for changes in this option to take effect, the server must be halted, the server options file updated, and the server restarted.

ALLOWREORGINDEX
    By setting the server options ALLOWREORGINDEX YES or ALLOWREORGINDEX NO, you can enable or disable server-initiated index reorganization. If the option is not specified, it defaults to ALLOWREORGINDEX NO. This option is available via the SETOPT command beginning in server versions 6.2.4 and 6.3.3. Before those fixing levels, for changes in this option to take effect, the server must be halted, the server options file updated, and the server restarted.

    If index reorganization is enabled, set the server option DB_DB2_KEEPTABLELOCK. See DB_DB2_KEEPTABLELOCK.

REORGBEGINTIME and REORGDURATION

    Table and index reorganization are intensive operations that require significant CPU resources and active and archival log resources. There might be times during the day that these reorganizations should not run, since they impact server operations. Additionally, reorganization involves obtaining locks on the database that might interfere with server operations and cause performance issues, deadlocks, or similar problems. Finally, if the server is halted during the reorganization window, index reorganization activity halts, but an in-flight table reorganization continues because table reorganization is an asynchronous activity from the server.

    Define a daily window in which server initiated reorganization work is initiated. This window is defined by two server options, REORGBEGINTIME and REORGDURATION.

    For information about REORGBEGINTIME, see Techdoc 7021759.

    For information about REORGDURATION, see Techdoc 7021760.

    Reorganization of a table or index might still be active when the defined window is complete. Under some conditions, table reorganization will be paused after it has run for a time. If, after being paused, the current time is outside of the reorganization window, the reorganization will remain paused until the next window on the following day, and will resume then. However, index reorganizations cannot be paused; they run until completed, unless canceled. See Canceling index reorganizations. Consequently, no heavy server activity should be scheduled until at least an hour after the defined schedule window to allow any current index reorganization to complete.

DB_DB2_KEEPTABLELOCK
    By setting the server options DB_DB2_KEEPTABLELOCK NO and DB_DB2_KEEPTABLELOCK YES, you can set the DB2 DB2_KEEPTABLELOCK variable. On Version 6.1.5.10, 6.2.3, and later servers, if the option is not specified, it defaults to DB_DB2_KEEPTABLELOCK YES, which is consistent with server levels earlier than V6.1.5.10 and 6.2.3. For server levels 6.3.0 and later, the default is DB_DB2_KEEPTABLELOCK NO. This option is not available via SETOPT. The server must be halted, the server options file must be updated, and the server must be restarted for changes in this option to take effect.

    If index reorganization is not running, V6.1 and 6.2 servers should run with DB_DB2_KEEPTABLELOCK YES, which is the default; V6.3 and V7 servers should run with DB_DB2_KEEPTABLELOCK NO, which is the default.

    If index reorganization is running, the server should run with DB_DB2_KEEPTABLELOCK NO. Not doing so may result in the symptoms documented in APAR IC77773.

    However, there are two cases in which running with DB_DB2_KEEPTABLELOCK NO might cause performance degradations during normal server activities (when index reorganization is not running):
    • Performance degradation might occur if running a V6.1 Windows server.
    • Performance degradation might occur if running any V6 or V7 server while undertaking large EXPORT NODE or IMPORT NODE operations.

    If performance problems are experienced, complete the following steps for normal server operations (when index reorganization does not need to run):
    1. Halt the server.
    2. Set the server option
      ALLOWREORGINDEX NO
      which will prevent index reorganizations from running.
    3. Set the server option
      DB_DB2_KEEPTABLELOCK YES
      which restores the default server behavior.
    4. Restart the server.
    5. Allow the server to run until index reorganization needs to run; monthly reorganization should be sufficient.

    When index reorganization is required, complete the following steps:
    1. Halt the server.
    2. Set the server option
      ALLOWREORGINDEX YES
      which will allow index reorganizations to run.
    3. Set the server option
      DB_DB2_KEEPTABLELOCK NO
      which prevents the situation described in APAR IC77773.
    4. Set the server options REORGBEGINTIME and REORGDURATION appropriately according to installation requirements.
    5. Restart the server.
    6. Once index reorganization is completed, disable index reorganization as indicated previously.

Hide details for Canceling index reorganizations Canceling index reorganizations

If the server initiates an index reorganization that must be canceled, DB2 commands can be used to cancel that process. This must be done if normal server operations need to start because index reorganization and normal server operations can deadlock. Work that is already completed is not lost because DB2 reorganization uses redo logging. After a reorganization is completed normally, the server initiates a DB2 RUNSTATS command on that table to optimize server access to the data in that table. Canceling the reorganization means that the RUNSTATS command will not be run.

To cancel an index reorganization through DB2, complete the following steps:

  1. Determine the application ID of the reorganization process by issuing the following commands in a DB2 Command Line Processor window:
    1. db2 connect to tsmdb1
    2. db2 get snapshot for all applications >application.out
  2. Examine the application.out file and find the "Most recent operation" entry like this:
    Most recent operation = Reorganize

    If that line is missing, look for an entry like the following one:
    Application name = db2reorg
  3. Scroll backwards until you find the "Application handle" entry. It will look similar to the following entry:
    Application handle = NNNNN (where NNNNN is the actual application handle)
    Ensure that the correct application handle is found.
  4. Issue the following command in the DB2 Command Line Processor Window:
    db2 "force application (NNNNN)"
    where NNNNN is the application handle.
    It might take up to 30 minutes for the process to be canceled. This is because of the nature of the command being canceled, and because of the fact that the DB2 FORCE APPLICATION command is asynchronous.
  5. To verify that the process is canceled, repeat Steps 1B and 2 again. If there is no "Most recent operation" of type Reorganize message displayed, the process has been canceled.

Attention: If issued against a system critical process, the DB2 FORCE APPLICATION command can cause server instability and possibly cause the DB2 database to stop responding. It is crucial that only the application that is running the index reorganization be forced to stop in this manner.


Hide details for Releasing space to the operating systemReleasing space to the operating system

With the DB2 fix packs shipped with server levels 6.1.5.10, 6.2.3, 6.3.0, and 7.1.0, database space can be safely released to the operating system.

After reorganization is completed, free space should be consolidated near the end of the tablespaces and tables. To release space to the operating system, issue the DB2 ALTER TABLESPACE command. For information about databases that were formatted with server V6.1 (even if the server has been upgraded to V6.2 or later), see Releasing space in DB2 9.5 tablespaces. For information about databases that were formatted with server versions V6.2 and V6.3, see Releasing space in DB2 9.7 tablespaces. For releasing about databases that were formatted with server versions 7.1 or later, see Releasing space in V7.1 servers.

Releasing space in DB2 9.5 tablespaces

Databases that were formatted by a V6.1 server have DB2 9.5 tablespaces. Upgrading a V6.1 server to V6.2 or later versions does not convert the tablespaces. DB2 9.5 has limited capacity for releasing space to the operating system. To free additional space, issue the following commands from a DB2 command-line window:


    db2 connect to tsmdb1
    db2 ALTER TABLESPACE USERSPACE1 REDUCE
    db2 ALTER TABLESPACE IDXSPACE1 REDUCE
    db2 ALTER TABLESPACE LARGESPACE1 REDUCE
    db2 ALTER TABLESPACE LARGEIDXSPACE1 REDUCE

If you cannot free sufficient space by issuing these commands, you can convert the DB2 9.5 tablespaces to DB2 9.7 tablespaces. For help with this conversion, contact Tivoli Storage Manager service. Be aware, however, that conversion is a time-intensive, multistep manual process that must be done while the server is halted. For this reason, tablespace conversion is strongly discouraged.

Releasing space in DB2 9.7 tablespaces

Databases that are created with V6.2 and V6.3 servers define DB2 9.7 tablespaces with reclaimable space enabled. These instructions are also applicable to V6.2 and V6.3 servers that are upgraded to V7.1. Issuing ALTER TABLESPACE REDUCE on these tablespaces is much more likely to release free space to the operating system than DB2 9.5 tablespaces.

For DB2 9.7 tablespaces, the values in the reclaimable_space_enabled column of the following select is 1. The values are 0 for DB2 9.5 tablespaces.

db2 connect to tsmdb1
db2 set schema tsmdb1
db2 "select reclaimable_space_enabled from table(mon_get_tablespace('',-1))    where tbsp_id in (2,4,5,6)"

To release space from DB2 9.7 tablespaces, issue the following commands:

db2 connect to tsmdb1
db2 set schema tsmdb1

The following db2 ALTER commands start separate asynchronous processes in DB2. Running more than one ALTER TABLESPACE command is not recommended because they can conflict with each other and result in command failures, e.g. DB21034E/SQL0290N pairs.

db2 ALTER TABLESPACE USERSPACE1 LOWER HIGH WATER MARK
db2 ALTER TABLESPACE IDXSPACE1 LOWER HIGH WATER MARK
db2 ALTER TABLESPACE LARGESPACE1 LOWER HIGH WATER MARK
db2 ALTER TABLESPACE LARGEIDXSPACE1 LOWER HIGH WATER MARK
db2 ALTER TABLESPACE USERSPACE1 REDUCE MAX
db2 ALTER TABLESPACE IDXSPACE1 REDUCE MAX
db2 ALTER TABLESPACE LARGESPACE1 REDUCE MAX
db2 ALTER TABLESPACE LARGEIDXSPACE1 REDUCE MAX

You can monitor the progress of each command (for example, USERSPACE1) by examining the num_extents_left column of the MON_GET_EXTENT_MOVEMENT_STATUS procedure as follows:

db2 connect to tsmdb1
db2 set schema tsmdb1


db2 "select num_extents_left from
table(sysproc.MON_GET_EXTENT_MOVEMENT_STATUS('USERSPACE1',-1)) "


Depending on the tablespace reduction that you are monitoring, replace USERSPACE1 with IDXSPACE1, LARGESPACE1, or LARGEIDXSPACE1.

When num_extents_left changes to 0 or -1, the command is finished.


Releasing space in server V7.1 databases
If you installed a server with V7.1 or later, or upgraded a V5 server directly to V7.1, there is a new table and tablespace implementation in your database. You still have DB2 9.7 tablespaces, but each of the large tables are placed into a distinct tablespace for the data and a distinct tablespace for the indices for that table. The following table indicates the assignments:

Table Name DB2 Tablespace for Data DB2 Tablespace for Indices
ARCHIVE_OBJECTS ARCHOBJDATASPACE ARCHOBJIDXSPACE
BACKUP_OBJECTS BACKOBJDATASPACE BACKOBJIDXSPACE
BF_AGGREGATED_BITFILES BFABFDATASPACE BFABFIDXSPACE
BF_BITFILE_EXTENTS BFBFEXTDATASPACE BFBFEXTIDXSPACE


If you perform offline reorganization for any of these four tables, issue the DB2 ALTER TABLESPACE REDUCE MAX command on the two associated tablespaces for that table. For example, if you performed offline reorganization on BF_AGGREGATED_BITFILES, you would run the following commands to free space to the operating system:

db2 connect to tsmdb1
db2 set schema tsmdb1
db2 ALTER TABLESPACE BFABFDATASPACE LOWER HIGH WATER MARK
db2 ALTER TABLESPACE BFABFIDXSPACE LOWER HIGH WATER MARK
db2 ALTER TABLESPACE BFABFDATASPACE REDUCE MAX
db2 ALTER TABLESPACE BFABFIDXSPACE REDUCE MAX


You can use the MON_GET_EXTENT_MOVEMENT_STATUS command as documented in the previous section to monitor the progress of the ALTER TABLESPACE command.

For the other tables in the database, use the instructions in the previous section.



Hide details for Best practicesBest practices

The following are best practices associated with database health:

  • Ensure that the computer on which the server is running is adequately provisioned. Verify factors such as memory, available CPU resources, and sufficient space in active log and archive log directories. Ensure that regular database backups are performed, including the volume history. The database, logs, database backups, and the volume history should all be located on high-performance, reliable storage.
  • Ensure that Tivoli Storage Manager server-initiated reorganization is set.
  • A reorganization window of several hours a day for both server-initiated reorganization activity and other DB2 maintenance should be performed once a steady state is achieved. For databases that are significantly fragmented, a larger reorganization window might be required for weeks or months until a steady state is achieved. A steady state is defined as reorganization having been run on all tables, and indices of tables, if index reorganization is enabled.
  • Table reorganization might be sufficient on servers that are not running deduplication. Both table and index reorganization are recommended on servers that are running deduplication.



Hide details for Compressing indices on V6.2 and later serversCompressing indices on V6.2 and later servers

V6.2, V6.3, and V7.1 servers are delivered with versions of DB2 which support index compression. When these servers format a new database, index compression is enabled on it. However, when a V6.1 server is upgraded to V6.2 or later versions, index compression is not enabled. Server APAR IC78604 will automatically enable index compression on V6.2 and later servers. Note that the actual compression of the indices occurs when reorganization runs on the indices of that table. Consequently, reorganization of indices must be enabled to compress the indices.


Hide details for Offline table reorganizationOffline table reorganization

Tivoli Storage Manager server has several large tables for which online server-initiated table reorganization might require several months to run. Other tables (such as BF_DEREFERENCED_CHUNKS and BF_QUEUED_CHUNKS, APAR IC81115) have reorganization disabled because of locking issues. If this is unacceptable and the server can be halted for many hours, offline table reorganization can be performed. Offline table reorganization has exclusive access to the database and proceeds much faster than online table reorganization. Several tables are potentially problematic. The following table lists those tables and their page sizes, and indicates the buffer pools that are required to reorganize them:

Table name Page size Buffer pool
AF_BITFILES 16K IBMDEFAULTBP
AS_VOLUME_STATUS 16K IBMDEFAULTBP
BF_AGGREGATED_BITFILES 16K IBMDEFAULTBP
BF_BITFILE_EXTENTS 16K IBMDEFAULTBP
BF_DEREFERENCED_CHUNKS 16K IBMDEFAULTBP
BF_QUEUED_CHUNKS 16K IBMDEFAULTBP
GROUP_LEADERS 16K IBMDEFAULTBP
BACKUP_OBJECTS 32K LARGEBUFPOOL1
ARCHIVE_OBJECTS 32K LARGEBUFPOOL1

Server fixing level 6.3.4.200 ships enhancements to handle larger amounts of deduplicated data. This might cause more fragmentation in the indices for BF_AGGREGATED_BITFILES and/or BF_BITFILE_EXTENTS, especially if your server ingests more than 2T of data per day. Depending on your server workloads, you might need to disable both table and index reorganization to maintain server stability and to reliably complete daily server activities. With reorganization disabled, if you experience unacceptable database growth or server performance degradation, schedule offline reorganization for those tables.

If reorganization is disabled on the server, you must ensure that you do not run out of space by monitoring the database usage and the file systems that are used by the database. The QUERY DB and QUERY DBSPACE server commands can be used to monitor the amount of free space that is available.

To determine whether or not a particular table needs to be reorganized, consult the DB2 REORGCHK documentation. Running reorgchk with CURRENT STATISTICS rather than UPDATE STATISTICS (the default) is recommended because of the potential for impacting server performance.

In the subsequent paragraphs, <tablename> is used to indicate the table of interest; <pagesize> is the corresponding pagesize (the 'K' at the end is required); and <bufferpool> is the corresponding buffer pool.

To calculate the amount of time required, an estimate is provided, but there is no guarantee that your server will perform at this rate. Consequently, you should plan on poorer performance than this estimate. On IBM internal test systems, offline reorganization runs at a rate of about 1 billion rows in two hours.

Start by determining the number of rows of <tablename>. From a DB2 command-line window, run the listed commands while the server is running. Running the SELECT command might take several hours and could negatively affect server performance during this time.
db2 connect to tsmdb1
db2 "select count_big(*) from tsmdb1.<tablename>"

Once you have the number of rows, you can estimate the amount of downtime required to perform the offline reorganization of the table.

The following procedure describes how to reorganize a table. Read Step 8 to understand the consequences of stopping the table reorganization before it finishes.

  1. While the server is running, determine how much temporary space is required to reorganize <tablename>. The following instructions indicate how to obtain the size of the table, tsize, for which the units are bytes. The required amount of temporary space is twice the value of tsize. From a DB2 command line window, issue the following commands:
    1. db2 connect to tsmdb1
    2. db2 set schema tsmdb1
    3. db2 "call sysproc.reorgchk_tb_stats('T','tsmdb1.<tablename>') "
    4. db2 "select tsize from session.tb_stats"
  2. Create a temporary tablespace to use during the reorganization. <path> indicates a directory that is owned by the database instance user, has at least twice the value of tsize from the previous step, and is on the fastest reliable available disk:

    db2 "CREATE SYSTEM TEMPORARY TABLESPACE REORG PAGESIZE <pagesize>
    MANAGED BY SYSTEM USING ('<path>') BUFFERPOOL <bufferpool> DROPPED TABLE RECOVERY OFF"

    If you are unable to find enough temporary space, see the section Locating temporary space to perform offline reorganization for a suggestion.
  3. Obtain a full server database backup including the volume history. This is an essential step. Do not proceed without performing this step.
  4. Halt the server.
  5. Issue the following commands from the DB2 command window:
    1. db2 force application all
    2. db2stop
    3. db2start
    4. db2 connect to tsmdb1
    5. db2 update db cfg for tsmdb1 using auto_tbl_maint off
    6. db2 "reorg table tsmdb1.<tablename> allow no access use reorg"
    7. db2 "drop tablespace reorg"
    8. db2 update db cfg for tsmdb1 using auto_tbl_maint on
  6. Start the server. The server should not be halted until the RUNSTATS command (see the next step) is completed. Until the command is completed, server performance will be degraded. As the command runs, server performance should improve. The RUNSTATS command might require many hours to complete.
  7. From the DB2 command window, issue the following commands:
    1. db2 connect to tsmdb1
    2. db2 "RUNSTATS ON TABLE tsmdb1.<tablename> WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL"
  8. If Step 5F takes too long, stop the reorganization and restore the database by using the database backup and volume history from Step 3. Note that the final step of offline reorganization rebuilds the indices for the table (for details, see Classic (offline) table reorganization). The indices for these tables are crucial for successful operation of the server; consequently, offline reorganization must be allowed to complete. If reorganization fails or is interrupted, the database must be restored. The indices could be manually rebuilt with the help of IBM Software Support, but that would take many hours.
  9. From another DB2 command window, the reorganization status (Step 5F) can be obtained by running the following command:
    db2pd -d tsmdb1 -reorg
  10. From another DB2 command window, the RUNSTATS command status (Step 7B) can be obtained by running the following command:
    db2pd -d tsmdb1 -runstats


Hide details for Offline index reorganization Offline index reorganization

If you need to manually reorganize tables, even if it is only to resolve fragmented indices, you can follow the instructions in the Offline table reorganization section of this document. However, you might experience a problem with index reorganization on the BF_BITFILE_EXTENTS table on certain levels of the Tivoli Storage Manager server. See Techdoc 1653582 for details on how to resolve the server halt problem when cleanup index reorganization runs on the BF_BITFILE_EXTENTS table.


Hide details for Locating temporary space to perform offline reorganizationLocating temporary space to perform offline reorganization


To run offline reorganization, you must have enough temporary free space. If your database backups are stored in FILE device classes, determine whether there is sufficient space for the reorganization using that space. If there is not sufficient space, you can delete database backups that are not needed.

For instance, to determine if the oldest database backups can be deleted, use the QUERY VOLHISTORY command with the TYPE=DBB parameter to view the existing database backups.

If the oldest database backups can be deleted, use the DELETE VOLHISTORY command with the TYPE=DBB and TODATE parameters to delete the database backups that are not needed.
Note: if you store database backups in multiple device classes, you must ensure that you do not delete database backups that you want to keep.

For example, to delete database backups that are five days old and older, issue the following command:
delete volhistory type=dbb todate=today-5

Review the database backup file system with the most free space to determine whether it has enough space to start the reorganization.


Hide details for Data to gather in case of reorganization issuesData to gather in case of reorganization issues

If you are experiencing difficulties with server-initiated reorganization, follow the instructions in Techdoc 1590928 to gather the information that will be required by IBM Software Support.


Hide details for Relevant APARsRelevant APARs
This section briefly describes some APARs related to reorganization processing. Those that are scheduled to ship in future server fixing levels are subject to the discretion of IBM. For more information, click the links.

APARs IC81261 and IC83725 address server hangs that might occur during reorganization processing.

APAR IC82886 addresses the clustered index on the BF_AGGREGATED_BITFILES table, which might cause unexpected database growth for some deduplication customers. Techdoc 1592404 provides instructions on how to convert that clustered index to a regular index.

APAR IC82352 addresses a server performance problem.

APAR IC83091 addresses enhanced reorganization error handling.

APAR IC83382 prevents the server from using the DB2 reorganization formula F3 during table reorganization processing.

APAR IC87531 addresses a client defect which might be experienced while table and index reorganization are running.

APAR IC94985 prevents a table reorganization from causing a hang in other server processes.

APAR IC95301 introduces support for excluding certain tables from reorganization processing. See the next section in this document for information about that APAR.


Hide details for APAR IC95301APAR IC95301
APAR IC95301 introduces support such that table and index reorganization can be disabled for certain tables. This requirement has been observed on very busy servers that run deduplication, but other customers might need this capability. This APAR is targeted for fixing levels 6.3.5 and 7.1.1 (subject to change at the discretion of IBM).

Customers have experienced some of the following issues:

  • Long time periods to run table reorganizations, which prevents reorganization from running on other tables.
  • Server halting because the active log becomes full when index reorganization is running.
  • Server applications canceling while reorganization is running to resolve deadlocks (ANR1880W message is issued).

APAR IC82352 introduced an INDEX REORG CLEANUP PAGES ALL reorganization on BF_BITFILE_EXTENTS that can cause the server to halt because of the active log becoming full.

Disabling reorganization on large tables allows reorganization to run on the other tables while the server is running. This prevents server outages from occurring, and prevents server applications from being canceled because of reorganization activity.

A complete list of table names may be obtained by issuing the following commands in a DB2 command window:
db2 connect to tsmdb1
db2 "select tabname from syscat.tables where tabschema='TSMDB1' and
       type='T' "


The following options are introduced with APAR IC95301:

DISABLEREORGTable <tablelist>

Use this option when you have table reorganization enabled (ALLOWREORGTABLE YES), but you want table reorganization disabled for tables that are specified in the table list. This option is not available via the SETOPT command; consequently, for changes in this option to take effect, you must halt the server, update the server options file, and restart the server. If the option is not specified, it defaults to no tables being specified for this option. The following tables are already excluded from table reorganization processing and cannot be specified for this option: STAGED_EXPIRING_OBJECTS, STAGED_OBJECT_IDS, BF_DEREFERENCED_CHUNKS, and BF_QUEUED_CHUNKS.

DISABLEREORGIndex <tablelist>
Use this option when you have index reorganization enabled (ALLOWREORGINDEX YES), but you want index reorganization disabled for tables that are specified in the table list. This option is not available via the SETOPT command; consequently, for changes in this option to take effect, you must halt the server, update the server options file, and restart the server. If the option is not specified, it defaults to the ARCHIVE_OBJECTS, BACKUP_OBJECTS, BF_AGGREGATED_BITFILES and BF_BITFILE_EXTENTS tables listed in the table list. The following tables are already excluded from index reorganization processing and cannot be specified for this option:
STAGED_EXPIRING_OBJECTS, STAGED_OBJECT_IDS, REPLICATING_OBJECTS, REPLICATED_OBJECTS, BF_DEREFERENCED_CHUNKS, and BF_QUEUED_CHUNKS.

DISABLEREORGCleanupindex <tableList>
Use this option to prevent an INDEX REORG with the CLEANUP PAGES ALL option from running on BF_BITFILE_EXTENTS. This is only needed for server that runs fixing levels 6.3.3.0 or later, to prevent the active log from filling and the server halting on busy servers. What generally triggers the need for this type of reorganization activity is a large amount of deletion activity on storage pools where deduplication is enabled. This option is not available via the SETOPT command; consequently, for changes in this option to take effect, you must halt the server, update the server options file, and restart the server. If the option is not specified, no tables are excluded from this processing. If it is specified, only the BF_BITFILE_EXTENTS table can be listed.

Tables that require reorganization might cause database growth and server performance degradation over time. Tables that are excluded from reorganization processing (either by the DISABLEREORGTABLE, DISABLEREORGINDEX, or DISABLEREORGCLEANUPINDEX server options; or those tables that are specifically excluded by the server itself), need to be monitored. To assist you, when the server starts and every seven days afterwards (irrespective of the values for the ALLOWREORGTABLE and ALLOWREORGINDEX server options), the server issues warning messages on excluded tables that need reorganization.

If a table needs to be reorganized, the following message is displayed: ANR3497W, "Reorganization is required on table <table name>. The reason code is 1."

If the indices for a table needs to be reorganized, the following message is displayed: ANR3497W, "Reorganization is required on table <table name>. The reason code is 2."

If an index reorganization with the CLEANUP PAGES ALL needs to performed on BF_BITFILE_EXTENTS, the following message is displayed: ANR3497W, "Reorganization is required on table BF_BITFILE_EXTENTS. The reason code is 3."

Disabling reorganization on certain tables before you have APAR IC95301
If you want to disable index or table reorganization on a table and you are not using a server version that includes APAR IC95301, complete the following steps. The commands must be issued from a DB2 command line window.

1) Connect to the DB2 database by issuing the following command:

db2 connect to tsmdb1

2) Determine the name of the global attribute for the table that you want reorganization to be disabled for. The name for disabling table reorganization is REORG_TB_tablename, where tablename is the name of the table. Similarly, the name for disabling index reorganization is REORG_IX_tablename.

For example, if you want to disable index reorganization on BF_AGGREGATED_BITFILES, the global variable name is REORG_IX_BF_AGGREGATED_BITFILES.

3) Determine whether the global attribute is defined by issuing the following command:

db2 "select datetime from tsmdb1.global_attributes where owner='RDB and name='<globalVar>'"

where globalVar is the global variable name from step 2.

If the following output is displayed, the global attribute is not defined.

DATETIME
--------------------------

  0 record(s) selected.

4) Depending on the whether the global attribute is set or not, complete one of the following tasks:
  • If the global attribute is not defined, from the results in step 3, issue the following command:
      db2 "insert into tsmdb1.global_attributes
              (owner,name,type,length,datetime)
              values('RDB','<globalVar>',5,6,current date) "

      where globalVar is the global variable name from step 2.

      After the global variable is defined, use the db2 update command to update the
      datetime value subsequently.
  • If the global attribute is defined, from the results in step 3, issue the following command:
      db2 "update tsmdb1.global_attributes set datetime=current date where owner='RDB' and name='<globalVar>' "

      where globalVar is the global variable name from step 2.

      Note that "current date" is a special DB2 register which causes today's date to be
      specified for the command. Do not set a future date as that will not have the desired
      effect.

5) Repeat steps 2 - 4 as needed for each variable that you need to set.

6) These instructions must be repeated every nineteen days to ensure that reorganization
processing is not scheduled for the tables of interest. For example if you need to disable
both table and index reorganization for BF_BITFILE_EXTENTS, you must issue the following commands
every nineteen days after you complete the settings for the first time.

db2 connect to tsmdb1
db2 "update global_attributes set datetime=current date where owner='RDB' and name='REORG_TB_BF_BITFILE_EXTENTS' "
db2 "update global_attributes set datetime=current date where owner='RDB' and name='REORG_IX_BF_BITFILE_EXTENTS' "





Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Tivoli Storage Manager
Server

Software version:

6.1, 6.2, 6.3, 7.1

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows 2003, Windows 2008

Reference #:

1452146

Modified date:

2014-05-28

Translate my page

Machine Translation

Content navigation