Database size, database reorganization, and performance considerations for Tivoli Storage Manager Version 6 servers

The following symptoms might be encountered:

1. The amount of space that is used by the database grows continuously over time, and is beyond expectations for the normal increase in client space requirements. Despite the use of server expiration and despite the fact that the number of objects in the server remains the same or decreases, the space that is used by the database continues to increase.
2. Server performance is negatively affected over time. For example, server operations run increasingly slower over time, even though the workload has not changed.

Server-initiated table and index reorganization features were introduced in server levels 6.1.5.10 and 6.2.3. Additional reorganization fixes were delivered in later fix packs. In future server fix packs, additional fixes are planned, subject to the discretion of IBM.

To obtain the most complete version of the reorganization code, upgrade to the latest server level. An upgrade ensures that the latest version of the server reorganization software is running, which should provide best results. Also note that many reorganization fixes are being deployed only in server Versions 6.2 and 6.3, and to obtain those fixes, you must upgrade to those server versions. For information about fixes that are planned for the future, see the section Relevant APARs, below.

In addition, for databases with enabled deduplication, database growth might be caused by unexpected interactions. See Techdoc 1596944 and the section Verifying that the number of objects managed by the server is relatively static of Techdoc 1592404 for additional information.

Expected reorganization activity
If server-initiated table and index reorganization is enabled, every table is reorganized, one-by-one, during the reorganization window. After all table reorganizations are complete, table reorganization is followed by a reorganization of the indices of each table. 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 several days 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.

APAR IC81015 prevents the unconditional reorganization of all tables and indices such that reorganization starts based on actual need. This APAR is currently available only in server level 6.2.4. The Local fix section of the APAR provides manual instructions to obtain this behavior on other server levels.

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, F2, or F3 is indicated and 20 days have passed, the table will be reorganized. (APAR IC83382 reduces the checks to F1 and F2 only.) 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.

Obtaining 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"

Miscellaneous details

• Several new server options can be used to tailor the reorganization process. See Reorganization options.
• The server pauses periodically after performing 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. If neither server table nor server index reorganization is enabled, DB2 initiated reorganization is enabled. DB2 initiated reorganization is not recommended.

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 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.X 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.

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.

Releasing space to the operating system

With the DB2 fix packs shipped with server levels 6.1.5.10, 6.2.3, and 6.3.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 V6.3), see Releasing space in DB2 9.5 tablespaces. For information about databases that were formatted with server V6.2 and V6.3, see Releasing space in DB2 9.7 tablespaces.

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 V6.3 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. Therefore, 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
db2 ALTER TABLESPACE USERSPACE1 REDUCE MAX
db2 ALTER TABLESPACE IDXSPACE1 REDUCE MAX
db2 ALTER TABLESPACE LARGESPACE1 REDUCE MAX
db2 ALTER TABLESPACE LARGEIDXSPACE1 REDUCE MAX

These commands start separate processes in DB2 that release space. The commands can increase I/O activity, and might affect server performance. To minimize the impact, issue the next ALTER TABLESPACE command only after the previous command is completed. You can monitor the progress of the 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.

Best 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.

Compressing indices on V6.2 and V6.3 servers

V6.2 and 6.3 servers are delivered with DB2 9.7, which supports index compression. When a V6.2 or 6.3 server formats a new database, index compression is enabled on it. However, when a V6.1 server is upgraded to V6.2 or 6.3, index compression is not enabled. Server APAR IC78604 will automatically enable index compression on V6.2 and 6.3 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.

Offline 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. Six 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
BF_AGGREGATED_BITFILES	16K	IBMDEFAULTBP
BF_BITFILE_EXTENTS	16K	IBMDEFAULTBP
BF_DEREFERENCED_CHUNKS	16K	IBMDEFAULTBP
BF_QUEUED_CHUNKS	16K	IBMDEFAULTBP
BACKUP_OBJECTS	32K	LARGEBUFPOOL1
ARCHIVE_OBJECTS	32K	LARGEBUFPOOL1

To determine whether or not a particular table needs to be reorganized, please 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.

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"
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

Data 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.

Relevant APARs
This section briefly describes some APARs that are scheduled to ship in future server fixing levels, 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.