IBM Support

Resolving and preventing issues related to database growth and degraded performance in Tivoli Storage Manager servers

Troubleshooting


Problem

Unexpected database growth and degraded performance can occur over time on a Tivoli Storage Manager Version 6 or Version 7 server. However, you can take steps to resolve, and potentially prevent, these issues.

Symptom

You might encounter the following symptoms:

  • 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 following conditions:
    - Expiration is running.
    - The number of objects in the server remains the same or decreases.
  • Server performance degrades over time. For example, server operations run increasingly slower, even though the workload has not changed.

Cause



Tip: If you installed Tivoli Storage Manager 7.1.1.200 or later, follow the instructions in Technote 1683633.

Database growth and degraded server performance might result from one or more of the following causes:

    • Lack of the reclaimable space feature

      If you installed Tivoli Storage Manager V6.1, or upgraded the server to V6.1, the server database uses DB2 9.5 table spaces. DB2 9.5 table spaces do not have the reclaimable space feature. Even when V6.1 servers are upgraded to server V6.2 or later, the DB2 9.5 table spaces remain.

      If the reclaimable space feature is enabled, you can return unused space to the file system of the operating system.

      To determine whether the reclaimable space feature is available to you, review the following table.

    • Tivoli Storage Manager versionTablespace typeAvailability of reclaimable space feature
      Originally installed V6.1, or upgraded to V6.1DB2 9.5Not available.
      V6.2DB2 9.7Is available.
      V6.3DB2 9.7Is available.
      V7.1DB2 9.7

      Tip: Tivoli Storage Manager V7.1 is installed with a DB2 V10.5 database. However, the tablespace type for the DB2 V10.5 database is DB2 9.7.
      Is available.

    • Insufficient resources for database reorganization

      If you installed Tivoli Storage Manager V6.2, V6.3, or V7.1, the reclaimable space feature is available.

      However, certain server workloads (especially servers with large deduplicated storage pools) leave insufficient resources for online database reorganization to run without generating errors or conflicting with server operations. You can resolve these issues by canceling server operations so that reorganization can progress, but canceling server operations is often undesirable.
    • Failure to free up storage

      When you delete large amounts of data from the server, it does not mean that large amounts of storage from the database are freed up.
    • More objects over time

      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 table provides resources to help you resolve or prevent problems that are related to database growth and degraded performance. Often, database growth and degraded performance are related to issues with table or index reorganization.

Resolving and preventing problems with database growth and degraded performance

IssueTivoli Storage Manager versionPotential causes and resolutions
You are seeing database growth and performance degradation.You have Tivoli Storage Manager V6.1 installed, or you upgraded from V6.1 to V6.2. The server database is using DB2 9.5 table spaces, and space is not being reclaimed.

Follow the instructions in Using scripts to convert the server database to use DB2 9.7 table spaces.
You are seeing degradation in server operations and reorganization activities, for example:
  • Reorganization for one or more tables requires a considerable amount of time, and this prevents reorganization from running on other tables.
  • The server halts because the active log becomes full when index reorganization is running.
  • Server applications are canceled during reorganization to resolve deadlocks. The ANR1880W message is issued.
You have Tivoli Storage Manager V6.3 or V7.1 installed, or you converted your DB2 9.5 table spaces to DB2 9.7 table spaces.The issues might be caused by the following factors:
  • The degradation in server operations and reorganization activities might be caused by a heavy server workload, especially if you are running the data deduplication process. You might be able to resolve the issues by disabling reorganization for selected tables that are problematic. If you have Tivoli Storage Manager V6.3.5 or V7.1.1 installed, follow the instructions in Disabling reorganization for selected tables.
  • For databases with deduplicated storage pools, database growth might be caused by unexpected interactions. For instructions about resolving this issue, see Techdoc 1596944.
You deleted large amounts of data from the server, but storage space was not freed to the operating system.You have any version of Tivoli Storage Manager installed.Generally, after you delete large amounts of data from a server, you must complete the following steps to free storage space to the operating system:
  1. Enable table reorganization and index reorganization. The preferred method is to run online reorganization. For instructions, see the product documentation for your installed version:
    V6.2
    V6.3
    V7.1
    V7.1.1
  2. Release space to the operating system by running database manager commands. For instructions, see Releasing space to the operating system.

If you experience locking problems or unacceptable server performance degradation during online reorganization, you can disable reorganization for selected tables. For example, a locking problem can be indicated by error message ANR1880W:
Server transaction was canceled because of a conflicting lock on table
<table name>.

In case of locking or performance problems during online reorganization, follow the instructions in Disabling reorganization for selected tables.

If you are unsure whether to run online reorganization or offline reorganization, consider the following factors:
  • The main advantage of running online reorganization is that the Tivoli Storage Manager server continues to operate without interruptions. The main disadvantage is that online reorganization does not work on all indexes.
  • The main advantage of running offline reorganization is that it is approximately 100 times faster than online reorganization. The main disadvantage of offline reorganization is that the server must be halted during the reorganization process.
For instructions about running offline table reorganization, see Offline table reorganization.

For instructions about running offline index reorganization, see Offline index reorganization.

For tips about locating space to run offline reorganization, see Locating temporary space to run offline reorganization.

To determine whether a specific table must be reorganized, see the documentation for the DB2 REORGCHK command. The preferred method is to run the REORGCHK command with the CURRENT STATISTICS parameter. (If you run the REORGCHK command with the default parameter, UPDATE STATISTICS, it could have a negative impact on server performance.) To run the command, you must be logged in with the DB2 instance user ID. After running the command, review the output:

  • If F1 or F2 is indicated, run table reorganization.
  • If F5, F7, or F8 is indicated on any index for any table, run table reorganization on that table.
The space that is required for the server database grows over time, even though the number of objects in the database is relatively stable.
You have any version of Tivoli Storage Manager installed.Occasionally, it appears that a server workload is static, but in fact more and more objects are being managed, and this causes the database to grow.

For instructions about how to diagnose and resolve this issue, see Techdoc 1592404.

See also How can I optimize database performance?

You are not experiencing database issues, but you would like to maintain good system performance and avoid issues.You have any version of Tivoli Storage Manager installed.See How can I optimize database performance?
The reorganization of tables and indexes is taking more time than expected.

Or, you would like to see tips for optimizing the reorganization process so that you can avoid issues in the future.
You have any version of Tivoli Storage Manager installed.To take advantage of the most advanced features for online reorganization, upgrade the server to V6.3.5 or V7.1.1.

To better understand the reorganization process and possible causes for delays, see Why is my reorganization taking so long?

To obtain the status of reorganization processes for your system, see How can I find out the status of my reorganization?

See also Tips for optimizing reorganization and How do I set reorganization options to optimize performance?
You are seeing performance degradation or interruptions in server operations.You have any version of Tivoli Storage Manager installed.This issue can have different causes:
  • You might have the DB_DB2_KEEPTABLELOCK server option set to NO. In some cases, this can result in performance degradation. For details, see DB_DB2_KEEPTABLELOCK.
  • You are running index reorganization during a period of heavy server workload. Index reorganization and server operations deadlock. In this case, follow the instructions in Canceling index reorganizations.
Server-initiated reorganization is not working as expected. After following the troubleshooting procedures in this document, you are not able to resolve the issue.You have any version of Tivoli Storage Manager installed.Follow the instructions in Techdoc 1590928 to gather information about the issue. This information will be useful when you contact IBM Software Support.
Procedures, tips, and questions


Using scripts to convert a database to use DB2 9.7 table spaces

You can use scripts to convert a database to use DB2 9.7 table spaces. After you have the upgraded table spaces, and the reorganization process is completed, you can release free space to the operating system file system. The scripts must be run while the server is halted. The run time is dependent on many factors, for example:
- the initial size of the database
- the type of disk
- the amount of memory and cores

To obtain a better estimate of the downtime that is required for your configuration, try the reorganization scripts on a test system that matches, as closely as possible, the production system that is being reorganized. Contact IBM Software Support to obtain the scripts and instructions.



Why is my reorganization taking so long?

If server-initiated table and index reorganization is enabled, every table is reorganized as needed, 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 indexes and the duration of the reorganization window each day, reorganization might take many weeks or months to finish. After a table or index reorganization is completed, the RUNSTATS command is automatically 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 table spaces. To release this free space to the operating system file system, follow the instructions in 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.

You can log in with the DB2 instance user ID, and then use the following command to view the reorganization flags for the tables and their indexes:


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 indexes for that table are reorganized.

In addition, for table BF_BITFILE_EXTENTS, if F7 or F8 is indicated, the table indexes 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.

How can I find out the status of my reorganization?

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 index reorganizations, when the reorganization starts, message ANR0317I is issued. When reorganization is completed, message ANR0318I is issued.
  • When a reorganization is completed, either for a table or its indexes, the RUNSTATS command is run on the table. When the command starts running, message ANR0336I is issued. When the command is completed, message ANR0337I is issued.
  • To obtain a trace of reorganization activity, use the server trace class TBREORG.
  • To obtain the status of reorganization while it is in progress, ensure that you are logged in with the DB2 instance user ID. Then, take the following steps:

    1. Run the following command:
    db2 connect to tsmdb1

    2. In a DB2 CLP window, run the following command:

    db2pd -d tsmdb1 -reorg index > db2pd-reorg-index.txt

Tips for optimizing reorganization

To optimize the reorganization process, review the following information and take appropriate action:

  • Several new server options can be used to tailor the reorganization process, as described in How do I set reorganization options to optimize performance?
  • Even though table reorganization is enabled 24 hours a day by default, the preferred method is to schedule reorganization during a window where there is low server activity. For guidelines about identifying the best time to schedule table reorganization, see How do I set reorganization options to optimize performance?
  • If index reorganization is enabled, ensure that only minimal server activity occurs while index reorganization is running. If the reorganizations have not completed when server activity needs to resume, cancel the current index reorganization. Follow the instructions in Canceling index reorganizations.
  • Tivoli Storage Manager enables table reorganization by default. If you install Tivoli Storage Manager V7.1.1 or later, index reorganization is also enabled by default. If the server has table or index reorganization enabled, DB2 reorganization capabilities are disabled. DB2 initiated reorganization is not recommended.
  • 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.
  • Ensure that Tivoli Storage Manager server-initiated reorganization is set. You can enable server-initiated index reorganization by using the ALLOWREORGINDEX server option. You can enable server-initiated table reorganization by using the ALLOWREORGTABLE server option. For instructions, see How do I set reorganization options to optimize performance?
  • Ensure that index and table reorganization takes place on a regular basis. Plan on having a reorganization window of several hours a day for both server-initiated reorganization activity and other DB2 maintenance 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 indexes of tables, if index reorganization is enabled. If you suspect that your database is fragmented, or you are experiencing other issues with server-initiated reorganization, use the perl script that is attached to Technote 1590928 to collect the information that is required to investigate reorganization status.


How do I set reorganization options to optimize performance?

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 this server option, you can enable or disable server-initiated table reorganization. If the option is not specified, it defaults to ALLOWREORGTABLE YES.

For more information about the ALLOWREORGTABLE server option, see ALLOWREORGTABLE.

ALLOWREORGINDEX

By setting this server option, you can enable or disable server-initiated index reorganization.

The default settings for this server option are shown in the following table:

Tivoli Storage Manager version Default setting
V6.1NO
V6.2NO
V6.3NO
V7.1.0NO
V7.1.1YES

For more information about the ALLOWREORGINDEX server option, see ALLOWREORGINDEX.

If index reorganization is enabled, set the server option DB_DB2_KEEPTABLELOCK. For instructions, see DB_DB2_KEEPTABLELOCK.

REORGBEGINTIME and REORGDURATION

Table and index reorganization are intensive operations that require significant processor resources, and active and archive log resources.

Define a daily window for starting server-initiated reorganization processes. This window is defined by two server options, REORGBEGINTIME and REORGDURATION.



For more information about the REORGBEGINTIME server option, see REORGBEGINTIME.
For more information about the REORGDURATION server option, see REORGDURATION.

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 be completed.

DB_DB2_KEEPTABLELOCK



By setting this server option, you can set the DB2 DB2_KEEPTABLELOCK variable.

This option is not available via the SETOPT command. 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.

To determine the appropriate setting for the DB_DB2_KEEPTABLELOCK option, review the following table:
Tivoli Storage Manager versionIs index reorganization running?Preferred setting
V6.1YesDB_DB2_KEEPTABLELOCK NO
V6.1NoDB_DB2_KEEPTABLELOCK YES (default setting)
V6.2YesDB_DB2_KEEPTABLELOCK NO
V6.2NoDB_DB2_KEEPTABLELOCK YES (default setting)
V6.3YesDB_DB2_KEEPTABLELOCK NO
(default setting)
V6.3NoDB_DB2_KEEPTABLELOCK NO
V7.1YesDB_DB2_KEEPTABLELOCK NO
(default setting)
V7.1NoDB_DB2_KEEPTABLELOCK NO

If index reorganization is running, but you set DB_DB2_KEEPTABLELOCK to YES, you might see the symptoms that are 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 you are running a V6.1 server on a Microsoft Windows operating system.
  • Performance degradation might occur if you are 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. Prevent index reorganization by setting the following server option:
    ALLOWREORGINDEX NO
  3. Restore default server behavior by setting the following server option:
    DB_DB2_KEEPTABLELOCK YES
  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. Allow index reorganization by setting the following server option:
    ALLOWREORGINDEX YES
  3. Set the following server option:
    DB_DB2_KEEPTABLELOCK NO
    In this way, you can prevent the situation that is described in APAR IC77773.
  4. Set the server options REORGBEGINTIME and REORGDURATION appropriately to ensure that the reorganization will not conflict with other activities.
  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. Ensure that you are logged in with the DB2 instance user ID. Then, determine the application ID of the reorganization process by issuing the following commands in a DB2 Command Line Processor window:
    a. db2 connect to tsmdb1
    b. 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.
    For example, the entry might be similar to this:
    Application handle                         = 53586

    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. The process is canceled if you do not see a
    Most recent operation
    of type
    Reorganize message. 

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

After reorganization is completed, free space should be consolidated near the end of the table spaces and tables.


If the server database has DB2 9.5 table spaces, space cannot be released to the operating system.
If the server database has DB2 9.7 table spaces, you have the option to release space to the operating system:
Releasing space in DB2 9.7 table spaces

If you installed Tivoli Storage Manager V6.2 or V6.3, you have DB2 9.7 table spaces with reclaimable space enabled. These instructions are also applicable to V6.2 and V6.3 servers that are upgraded to V7.1. Issuing the ALTER TABLESPACE REDUCE command on these table spaces is much more likely to release free space to the operating system than DB2 9.5 table spaces.

Ensure that you are logged in with the DB2 instance user ID. Then, run the following commands to determine whether the server has DB2 9.7 or DB2 9.5 table spaces. For DB2 9.7 table spaces, the value in the reclaimable_space_enabled column of the following select is 1. The value is 0 for DB2 9.5 table spaces.

db2 connect to tsmdb1
db2 set schema tsmdb1
db2 "select reclaimable_space_enabled from table(mon_get_tablespace('',-1)) where ( TBSP_NAME='USERSPACE1' or TBSP_NAME='IDXSPACE1' or TBSP_NAME='LARGESPACE1' or TBSP_NAME='LARGEIDXSPACE1' )"


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

db2 connect to tsmdb1
db2 set schema tsmdb1

To reduce the size of the DB2 database, issue the following commands. After you run the commands, the file system that contains the DB2 database shows more free space. 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, for example, DB21034E/SQL0290N pairs.

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 table spaces, but each of the large tables are placed into a distinct table space for the data, and a distinct table space for the indexes for that table. The following table indicates the assignments:

Table nameDB2 table space for dataDB2 table space for indexes
ARCHIVE_OBJECTSARCHOBJDATASPACEARCHOBJIDXSPACE
BACKUP_OBJECTSBACKOBJDATASPACEBACKOBJIDXSPACE
BF_AGGREGATED_BITFILESBFABFDATASPACEBFABFIDXSPACE
BF_BITFILE_EXTENTSBFBFEXTDATASPACEBFBFEXTIDXSPACE


If you perform offline reorganization for any of these four tables, issue the DB2 ALTER TABLESPACE REDUCE MAX command on the two associated table spaces for that table. For example, if you performed offline reorganization on the BF_AGGREGATED_BITFILES table, 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 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.

How can I optimize database performance?

For information about optimizing database performance, see the documentation for your Tivoli Storage Manager version:


Locating temporary space to run 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 on the file system that is accessed by the Tivoli Storage Manager server.

If the space is insufficient, 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.

Tip: 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.

Offline table reorganization

A 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, you can run offline table reorganization. Offline table reorganization has exclusive access to the database and proceeds much faster than online table reorganization.

The following tables are potentially problematic:

Table name
AF_BITFILES
ARCHIVE_OBJECTS
AS_VOLUME_STATUS
BACKUP_OBJECTS
BF_AGGREGATED_BITFILES
BF_BITFILE_EXTENTS
BF_DEREFERENCED_CHUNKS
BF_QUEUED_CHUNKS
GROUP_LEADERS
REPLICATED_OBJECTS

Tivoli Storage Manager V6.3.4.200 and later, and V7, include enhancements to handle larger amounts of deduplicated data. This might cause more fragmentation in the indexes of the BF_AGGREGATED_BITFILES and BF_BITFILE_EXTENTS tables, especially if the server ingests more than 2T of data per day. Depending on your server workloads, you might have 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. Monitor 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 a specific table must be reorganized, see the DB2 REORGCHK documentation. Running the DB2 REORGCHK command with the CURRENT STATISTICS parameter rather than the UPDATE STATISTICS parameter (the default) is recommended because of the potential for impacting server performance. Ensure that you are logged in with the DB2 instance user ID before you run the REORGCHK command.

In the subsequent paragraphs, <tablename> is used to indicate the table of interest.

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

After you obtain 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. Review Step 6 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. Ensure that you are logged in with the DB2 instance user ID. Then, from a DB2 command line window, 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.

    In the following commands, <path> indicates a directory that is owned by the database instance user, that has at least twice the value of tsize from the previous step, and that is on the fastest and most reliable available disk.

    If the directory <path>/temp1-8K does not exist, issue the following command:
    mkdir <path>/temp1-8K

    If the directory <path>/temp2-16K does not exist, issue the following command:
    mkdir <path>/temp2-16K

    If the directory <path>/temp3-32K does not exist, issue the following command:
    mkdir <path>/temp3-32K

    Then, issue the following commands:

    db2 "CREATE SYSTEM TEMPORARY TABLESPACE REORG8K PAGESIZE 8K
    MANAGED BY SYSTEM USING ('<path>/temp1-8K') BUFFERPOOL REPLBUFPOOL1 DROPPED TABLE RECOVERY OFF"


    db2 "CREATE SYSTEM TEMPORARY TABLESPACE REORG16K PAGESIZE 16K
    MANAGED BY SYSTEM USING ('<path>/temp2-16K') BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY OFF"


    db2 "CREATE SYSTEM TEMPORARY TABLESPACE REORG32K PAGESIZE 32K
    MANAGED BY SYSTEM USING ('<path>/temp3-32K') BUFFERPOOL LARGEBUFPOOL1 DROPPED TABLE RECOVERY OFF"

    If you are unable to locate enough temporary space, see the section Locating temporary space to run 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 completing this step. For instructions, see BACKUP DB (Back up the database).
  4. Halt the server.
  5. Ensure that you are logged in with the DB2 instance user ID. Then, issue the following commands from the DB2 command window:

    a.  db2 force application all

    b. db2stop

    c. db2start

    d. db2 connect to tsmdb1

    e. db2 "DROP TABLESPACE TEMPSPACE1"

    f. db2 "DROP TABLESPACE LGTMPTSP"

    g. db2 update db cfg for tsmdb1 using auto_tbl_maint off

    h.
     Determine the page size of the table:
    db2 "select t1.PAGESIZE from syscat.tablespaces t1 left join syscat.tables t2 on (t1.TBSPACEID=t2.TBSPACEID) where t2.tabname='<table name>'"


    For example:
    db2 "select t1.PAGESIZE from syscat.tablespaces t1 left join syscat.tables t2 on (t1.TBSPACEID=t2.TBSPACEID) where t2.tabname='REPLICATED_OBJECTS'"
    PAGESIZE
    -----------
    8192

    i.
    If the PAGESIZE is 8192:
    db2 "reorg table tsmdb1.<tablename> allow no access use REORG8K"

    If the PAGESIZE is 16384:
    db2 "reorg table tsmdb1.<tablename> allow no access use REORG16K"

    If the PAGESIZE is 32768:
    db2 "reorg table tsmdb1.<tablename> allow no access use REORG32K"
  6. Optional: To obtain the reorganization status (Step 5i), open another DB2 command window. Log in with the DB2 instance user ID and run the following command:
    db2pd -d tsmdb1 -reorg

    The command output shows the amount of completed work as compared with the amount of total work.

    The final step of offline reorganization rebuilds table indexes. The indexes are crucial for successful operation of the server; consequently, offline reorganization must continue until completion. If reorganization fails or is interrupted, you must restore the database by using the point-in-time restore method. For instructions, see DSMSERV RESTORE DB (Restore a database to a point-in-time). The indexes could be manually rebuilt with the help of IBM Software Support, but that would take many hours.
  7. Issue the following commands from the DB2 command window that you opened in Step 5:

    a. db2 "create system temporary tablespace TEMPSPACE1 pagesize 16k bufferpool ibmdefaultbp"

    b. db2 "create system temporary tablespace LGTMPTSP pagesize 32k bufferpool largebufpool1"

    c. db2 "drop tablespace REORG8K"

    d. db2 "drop tablespace REORG16K"

    e. db2 "drop tablespace REORG32K"

    f. db2 update db cfg for tsmdb1 using auto_tbl_maint on
  8. Start the server.

    The server should not be halted until the RUNSTATS command (see Step 9) 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.
  9. Ensure that you are logged in with the DB2 instance user ID. Then, from the DB2 command window, issue the following commands:

    a.  db2 connect to tsmdb1

    b. db2 "RUNSTATS ON TABLE tsmdb1.<tablename> WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL"
  10. Optional: From another DB2 command window, log in with the DB2 instance user ID and obtain the RUNSTATS command status (Step 9b) by running the following command:
    db2pd -d tsmdb1 -runstats

Offline index reorganization

If you must manually reorganize tables, even if it is only to resolve fragmented indexes, you can follow the instructions in Offline table reorganization. However, you might experience a problem with index reorganization on the BF_BITFILE_EXTENTS table.

For instructions about how to resolve the server halt problem when cleanup index reorganization is run on the BF_BITFILE_EXTENTS table, see Techdoc 1653582.




Disabling reorganization for selected tables

If you installed Tivoli Storage Manager V6.3.5 or V7.1.1, or upgraded the server to V6.3.5 or V7.1.1, you can take advantage of the updates that were introduced by APAR IC95301. The updates make it possible to disable reorganization for selected tables and indexes. If your system includes servers with heavy workloads, and you are running the data deduplication process, consider disabling reorganization for selected tables. In this way, you might be able to accelerate the reorganization process and enhance server performance.

For more information about the new server options, see the online product documentation:

If you disable reorganization on specified tables, but reorganization is recommended for those tables, you will see message ANR3497W. To run the reorganization process for those tables, follow the instructions in Offline table reorganization.

APAR IC82352 introduced an INDEX REORG CLEANUP PAGES ALL reorganization on the BF_BITFILE_EXTENTS table 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.

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), must be monitored. To assist you, when the server starts and every seven days afterward (irrespective of the values for the ALLOWREORGTABLE and ALLOWREORGINDEX server options), the server issues warning messages on excluded tables that require reorganization.

If a table requires reorganization, the following message is displayed:
ANR3497W, "Reorganization is required on table <table name>. The reason code is 1."

If the indexes for a table require reorganization, 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 option is required on the BF_BITFILE_EXTENTS table, the following message is displayed:
ANR3497W, "Reorganization is required on table BF_BITFILE_EXTENTS. The reason code is 3."

[{"Product":{"code":"SSGSG7","label":"Tivoli Storage Manager"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Server","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"6.1;6.2;6.3;7.1;7.1.1","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}}]

Document Information

Modified date:
17 June 2018

UID

swg21452146