Troubleshooting
Problem
Unexpected database growth and degraded performance can occur over time on a Tivoli Storage Manager Version 7.1.1.200 or later 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.
Tip: Beginning with Version 7.1.3, IBM Tivoli Storage Manager is now IBM Spectrum Protect. Some applications such as the software fulfillment systems and IBM License Metric Tool use the new product name. However, the software and its product documentation continue to use the Tivoli Storage Manager product name. To learn more about the rebranding transition, see Technote 1963634 .
If you installed IBM Spectrum Protect V7.1.3 or later, and you set up directory-container storage pools, some of the information in this document might not apply to you. For more information, see If I use directory-container storage pools, is index or table reorganization necessary?
Cause
Database growth and degraded server performance might result from one or more of the following causes:
| Tivoli Storage Manager version | Tablespace type | Availability of reclaimable space feature |
| Originally installed V6.1, or upgraded to V6.1 | DB2 9.5 | Not available. |
| Originally installed V6.2 | DB2 9.7 | Is available. |
| Originally installed V6.3 | DB2 9.7 | Is available. |
| Originally installed V7.1 | DB2 9.7 Tip: Tivoli Storage Manager V7.1 or later is installed with a DB2 V10.5 database. However, the tablespace type for the DB2 V10.5 database is DB2 9.7. |
Is available. |
| Originally installed V8.1 | DB2 9.7 | Is available. |
- Lack of the reclaimable space feature
If you originally 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 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.
- If you are not sure whether the database was created by installing Tivoli Storage Manager V6.1 or by installing a later version, issue the following SELECT command:
db2 "select cast(TBSP_NAME as char(30)), reclaimable_space_enabled from table(mon_get_tablespace('',-1)) where TBSP_NAME in ('USERSPACE1','IDXSPACE1','LARGESPACE1','LARGEIDXSPACE1')" > reclaimable_space.txt
In the output, the RECLAIMABLE_SPACE_ENABLED column displays a value of 0 for V6.1 databases, even if the server was later upgraded to V6.2 or later.
Tip: The MON_GET_TABLESPACE table function does not exist on V6.1 servers.
If the database was created during installation of Tivoli Storage Manager V6.2 or later, the RECLAIMABLE_SPACE_ENABLED column shows a value of 1, as in the following sample output:
- bash-3.2$ db2 "select cast(TBSP_NAME as char(30)), reclaimable_space_enabled from table(mon_get_tablespace('',-1)) where TBSP_NAME in ('USERSPACE1','IDXSPACE1','LARGESPACE1','LARGEIDXSPACE1')"
1 RECLAIMABLE_SPACE_ENABLED
------------------------------ -------------------------
USERSPACE1 1
IDXSPACE1 1
LARGEIDXSPACE1 1
LARGESPACE1 1
4 record(s) selected.
bash-3.2$
- Insufficient resources for database reorganization
If you installed Tivoli Storage Manager V6.2, V6.3, V7.1, or V8.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 release storage space
When you delete large amounts of data from the server, it does not mean that large amounts of storage space are released from the database.
- Management of 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
| Issue | Tivoli Storage Manager version | Potential causes and resolutions |
| You are seeing database growth and performance degradation. | You originally installed Tivoli Storage Manager V6.1, or upgraded from V6.1, and you have not converted your DB2 9.5 table spaces to DB2 9.7 table spaces. | 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:
|
You have Tivoli Storage Manager V7.1 or later 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:
|
| 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:
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:
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, take one of the following actions:
|
| 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 . For information about optimizing database performance, see Configuration best practices . |
| 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. | For information about optimizing database performance, see Configuration best practices . |
| 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 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 maximize performance? |
| You are seeing performance degradation or interruptions in server operations. | You have any version of Tivoli Storage Manager installed. | You are running table reorganization during a period of heavy server workload. Table reorganization and server operations deadlock. In this case, adjust the options to exclude the table involved in the deadlock or adjust the reorganization window to a time where the server workload is lighter. See How do I set reorganization options to maximize performance? |
| 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. | To gather information about the issue, follow the instructions in Techdoc 1590928 . This information will be useful when you contact IBM Software Support. |
| You are using directory-container storage pools, and are not sure whether index and table reorganization is necessary. | You have IBM Spectrum Protect V7.1.3 or later installed. | See If I use directory-container storage pools, is index or table reorganization necessary? |
| You converted all primary, deduplicated storage pools to directory-container storage pools, and the size of the database is larger than expected. | You have IBM Spectrum Protect V7.1.6 or later installed. | See After I convert all primary, deduplicated storage pools to directory-container storage pools, the database is larger than expected . |
| The following message is displayed repeatedly in the activity log: ANR0318I Reorganization of indices for table <table name> ended with sqlCode -911. |
You have IBM Spectrum Protect V7.1.3 or later installed. | To resolve the issue, take one or both of the following actions:
Tip: You can change the value of the INDEXREORGPERIOD option without applying the fix for APAR IT18356. |
This section includes the following topics:
- Using scripts to convert a database to use DB2 9.7 table spaces
- Why is my reorganization taking so long?
- How can I find out the status of my reorganization?
- Tips for optimizing reorganization
- How do I set reorganization options to maximize performance?
- Canceling index reorganizations
- Releasing space to the operating system
- Online index reorganization
- Locating temporary space to perform offline reorganization
- Offline table reorganization
- Offline index reorganization
- Disabling reorganization for selected tables
- Taking action in response to message ANR3706W
- Calculating the amount of unused space in a database
- If I use directory-container storage pools, is index or table reorganization necessary?
- After I convert all primary, deduplicated storage pools to directory-container storage pools, the database is larger than expected
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 number of 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.
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 is required 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 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.
The Tivoli Storage Manager server runs the following automatic queries:
- After table reorganization is completed and 20 days have elapsed since the table reorganization started, the Tivoli Storage Manager server queries the database to determine whether additional reorganization of tables is needed.
- After index reorganization is completed and 7 days have elapsed since the index reorganization started, the Tivoli Storage Manager server queries the database to determine whether additional reorganization of indexes is needed.
After the reorganization of all tables and their indexes is completed, the data is consolidated in the respective tables and index spaces. Free space is located at the end of the tables and table spaces. To release this free space to the operating system file system, follow the instructions in Releasing space to the operating system .
DB2 calculates reorganization flags for the tables and indexes. F1 and F2 apply to tables. F5, F7, and F8 apply to indexes.
If 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.
If index reorganization is in progress at the end of the reorganization window, reorganization continues. It is not possible to pause index reorganizations.
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 table 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 - It is not possible to obtain the status of index reorganization from DB2. The server issues ANR0317I and ANR0318I. The server trace class TBREORG can be used.
To optimize the reorganization process, review the following information and take appropriate action:
- Several server options can be used to tailor the reorganization process. For instructions, see 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?
- 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. After the database backup is completed, if the reorganization window has not passed, reorganization activity can continue. In addition, if needed, the server initiates a full database backup while 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 after 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 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.
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 workloads. For example, when the server has a heavy workload of expiration, migration, or reclamation tasks, do not run reorganization.
ALLOWREORGINDEX
By setting this server option, you can enable or disable server-initiated index reorganization. If the option is not specified, it defaults to ALLOWREORGINDEX YES.
For more information about the ALLOWREORGINDEX server option, see ALLOWREORGINDEX .
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 .
DISABLEREORGCLEANUPINDEX
The DISABLEREORGCLEANUPINDEX option is deprecated.
Tip: You do not have to remove this option from the server options file. If this option is present in the server options file, it is ignored and does not result in warning or error messages.
DISABLEREORGINDEX
The DISABLEREORGINDEX option is deprecated.
Tip: You do not have to remove this option from the server options file. If this option is present in the server options file, it is ignored and does not result in warning or error messages.
DISABLEREORGNEWINDEX
This server option is not described in the product documentation, and is used in extremely rare cases to resolve unexpected errors. Before you use this option, contact IBM Software Support.
The option specifies whether online index reorganization is disabled for the tables that are listed. To use the option, you must halt the server, update the options file, and then restart the server. If you do not specify table names with the option, or if the option is not in the options file, no tables are disabled.
Restriction: The indexes of the following tables are automatically excluded from index reorganization processing and cannot be specified by using the DISABLEREORGNEWINDEX option:
BF_DEREFERENCED_CHUNKS
BF_QUEUED_CHUNKS
REPLICATING_OBJECTS
SD_REFCOUNT_UPDATES
STAGED_EXPIRING_OBJECTS
STAGED_OBJECT_IDS
DISABLEREORGTABLE
The DISABLEREORGTABLE option specifies whether online table reorganization is disabled for table names that are specified in the tables list. If the option is not specified, no tables are disabled.
For more information about the DISABLEREORGTABLE server option, see DISABLEREORGTABLE .
Restriction: The following tables are automatically excluded from table reorganization processing and cannot be specified by using the DISABLEREORGTABLE option:
BF_DEREFERENCED_CHUNKS
BF_QUEUED_CHUNKS
REPLICATING_OBJECTS
SD_CHUNK_COPIES
SD_CHUNK_LOCATIONS
SD_CLOUD_ORPHANED
SD_DEDUP_AUDIT
SD_NON_DEDUP_LOCATIONS
SD_RECON_ORDER
SD_REFCOUNT_UPDATES
SD_REPLICATED_CHUNKS
STAGED_EXPIRING_OBJECTS
STAGED_OBJECT_IDS
INDEXREORGPERIOD
This server option is not described in the product documentation, and is used in extremely rare cases to resolve unexpected errors.
After index reorganization is completed, and the number of days that are specified by the INDEXREORGPERIOD option have elapsed since the index reorganization started, the IBM Spectrum Protect server queries the database to determine whether additional index reorganization is required.
The default value for this option is 7 days.
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 .
If the server initiates index reorganization that must be canceled, you can use DB2 commands to cancel index reorganization. Work that is already completed is not lost because DB2 reorganization uses redo logging. After reorganization is completed normally, the server initiates a DB2 RUNSTATS command on the 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 index reorganization through DB2, complete the following steps:
- 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 - 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 - Scroll backwards until you find the Application handle entry.
For example, the entry might be similar to this:
Application handle = 53586
Important: Ensure that the correct application handle is found. - 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 the DB2 FORCE APPLICATION command is asynchronous. - 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.
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:
- For information about databases that were formatted with server V6.2 and V6.3, see Releasing space in DB2 9.7 table spaces .
- For information about databases that were formatted with server V7.1 or later, see Releasing space in server V7.1 and later databases .
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 or later. 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 the num_extents_left column value changes to 0 or -1, the command is finished.
Tip: As an alternative to this procedure, you can run a Perl script, analyze_DB2_formulas.pl:
analyze_DB2_formulas_v1_15.zip
After the script is extracted from the compressed file, you can verify that the script was downloaded correctly by calculating the checksum and comparing it to the following values:
- On AIX, HP-UX, Linux, and Solaris operating systems, you will typically see md5sum, for example:
md5sum analyze_DB2_formulas.pl
- On Windows operating systems, you will typically see cksum, for example:
cksum analyze_DB2_formulas.pl
The script itself does not release space, but it generates a summary.txt file, which displays the tables that must be reorganized.
Releasing space in server V7.1 and later databases
If you installed Tivoli Storage Manager V7.1 or later, or upgraded a V5 server directly to V7.1 or later, the server database has a new table and tablespace implementation. You still have DB2 9.7 table spaces, but each large table is placed into a distinct table space for the data, and a distinct table space for the indexes for that table.
If you perform offline reorganization on a table, issue the DB2 ALTER TABLESPACE REDUCE MAX command on the two associated table spaces for that table. Complete the following steps:
1. Determine the applicable tablespace names by issuing the following commands:
db2 connect to tsmdb1
db2 set schema tsmdb1
db2 "select cast(tbspace as char(40)) as DB2_table_space_for_data from syscat.tables where tabname='<table name>'"
db2 "select cast(index_tbspace as char(40)) as DB2_table_space_for_indexes from syscat.tables where tabname='<table name>'"
2. Issue the DB2 ALTER TABLESPACE REDUCE MAX command on the two associated table spaces for that table:
db2 connect to tsmdb1
db2 set schema tsmdb1
db2 ALTER TABLESPACE <DB2_TABLE_SPACE_FOR_DATA> REDUCE MAX
db2 ALTER TABLESPACE <DB2_TABLE_SPACE_FOR_INDEXES> 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.
Tip: As an alternative to this procedure, you can run a Perl script, analyze_DB2_formulas.pl. To obtain the script, see Releasing space in DB2 9.7 table spaces .
The script itself does not release space, but it generates a summary.txt file, which displays the tables that must be reorganized.
In Tivoli Storage Manager 7.1.1.200 and later, the online index reorganization process was updated to automatically clean up extents. (Extents are parts of files that are compared with other file extents to identify duplicates.) The goal of the update was to automatically release unused space and thereby improve system performance. If you enable online index reorganization, the reorganization and cleanup process is run automatically on all tables, and you cannot exclude individual tables from the process.
During lab testing and in production environments where Tivoli Storage Manager 7.1.1.300 is installed and where indexes were significantly fragmented, index reorganization with automatic extent cleanup was shown to improve overall system performance.
Index reorganization with automatic extent cleanup offers the following additional advantages:
- The process does not result in deadlocks of the Tivoli Storage Manager server database.
- The process is fault tolerant and designed to run to completion. If the process is interrupted inadvertently (for example, when you restart the server), the process resumes where it left off.
- Based on reports from business partners and customers, the process has no negative impact on server resources or operations.
After the cleanup operation is run on all indexes, the released space automatically becomes available for future index growth. Alternatively, to return the space to the file system, you can issue the DB2 ALTER TABLESPACE statement with the REDUCE option set to the MAX value. For more information, see ALTER TABLESPACE statement .
By returning space to the file system, you might be able to reduce the time that is required for server database backups and limit required storage space on database backup media.
If you do not want to enable online index reorganization, set the ALLOWREORGINDEX server option to NO before you upgrade the server to 7.1.1.300.
To run offline reorganization, you must have enough temporary free space. If your database backups are stored in FILE device classes, determine whether sufficient space for the reorganization is available 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.
A Tivoli Storage Manager server has several large tables for which online server-initiated table reorganization might require several months to run. Other tables 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 |
| ARCHIVE_OBJECTS |
| BACKUP_OBJECTS |
| BF_AGGREGATED_BITFILES |
| BF_BITFILE_EXTENTS |
Tivoli Storage Manager handles large amounts of deduplicated data. If you are not using directory-container storage pools, the processing of deduplicated data 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 that is 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.
- 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" - 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, has at least twice the value of tsize from the previous step, and 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 perform offline reorganization for a suggestion. - 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) .
- Halt the server.
- 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" - 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. - 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 - Start the server.
The server must continue running until the RUNSTATS command, which will be started in Step 9, completes processing. Until that time, server performance will be degraded. As the command runs, server performance should improve. The RUNSTATS command might require many hours to complete processing. - 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" - Optional: From another DB2 command window, log in with the DB2 instance user ID and obtain the RUNSTATS command status (for the command in Step 9b) by running the following command:
db2pd -d tsmdb1 -runstats
If you must manually reorganize tables, even if it is only to resolve fragmented indexes, you can follow the instructions in Offline table reorganization .
You can disable reorganization for selected tables. 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.
The default value for the DISABLEREORGTABLE option is that no tables are excluded. However, to enhance server performance, the preferred practice is to disable reorganization for the following tables:
- BF_AGGREGATED_BITFILES
- BF_BITFILE_EXTENTS
- BACKUP_OBJECTS
- ARCHIVE_OBJECTS
For more information about the option, see the DISABLEREORGTABLE topic.
If you disable reorganization on specified tables, but reorganization is recommended for those tables, you will see message ANR3706W. To run the reorganization process for those tables, follow the instructions in Offline table reorganization .
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 by the DISABLEREORGTABLE server option must be monitored. To assist you, when the server starts and every seven days afterward (irrespective of the values for the ALLOWREORGTABLE server options), the server issues warning messages on excluded tables that require reorganization.
If a table requires reorganization, a message that is similar to the following message is displayed:
ANR3706W Reorganization is required on table BF_AGGREGATED_BITFILES, which is excluded from online reorganization.
Tip: In previous releases, message ANR3497W notified users about the need to reorganize tables or indexes. That message is deprecated as a result of the updated index-reorganization process in 7.1.1.200 and later. If you installed Tivoli Storage Manager 7.1.1.200 or later, and table reorganization is required, message ANR3706W is displayed.
Message ANR3706W indicates that a table has been flagged for reorganization, however, that table is explicitly excluded from online reorganization. Note that just because a table has been flagged for reorganization based on criteria, it doesn't always mean that immediate action is required. The only time immediate action should be considered is under the following circumstances:
1. The database has experienced consistent growth under static work-loads.
- Excluding a table from online reorganization prevents defragmentation of that table. This causes the table to consistently grow in size, which has a direct impact on the overall utilization of the database.
If you are concerned about the database growth, see Calculating the amount of unused space in a database to determine how much space could be released by completing an offline reorganization of the table excluded from online reorganization. If the amount of space that could be released outweighs the cost of completing an offline reorganization, see Offline table reorganization.
2. The database is exhibiting performance degradation.
- Excluding a table from online reorganization prevents the table from being properly organized, which can lead to performance issues when accessing information in that table.
The most noticeable performance degradation will occur during database backups and inventory file expiration. If either of these processes are experiencing longer than normal run times under otherwise static work-loads, an offline reorganization of the excluded table(s) could provide benefit.
To learn more about possible causes, see Cause . To review possible solutions, see Resolving and preventing problems with database growth and degraded performance .
You can calculate the amount of unused space in a server database by using the analyze_DB2_formulas.pl script. To obtain the script, see Releasing space in DB2 9.7 table spaces .
If you determine that the amount of unused space is significant, consider releasing the unused space to the operating system by using offline table reorganization. In this way, you can decrease the size of the server database and potentially improve system performance.
Remember: Offline table reorganization requires system downtime. Before you start offline table reorganization, weigh the advantages of decreasing server database size against the disadvantages of temporarily shutting down the system.
To calculate the amount of unused space in a Tivoli Storage Manager server database on a Windows operating system, complete the following steps:
- Ensure that a Perl interpreter is installed on the system. The Perl interpreter is required to run the analyze_DB2_formulas.pl script.
Tip: For a list of Perl interpreters that are available at no cost, see the Related information section at the end of this document. - Log in with the instance user ID and run the following script from a DB2 command window:
Start > Run > db2cmd - Change the current directory to the target directory for the files that are generated by using the script.
- Start the script. For example, if the script is in the current directory, issue the following command:
perl analyze_DB2_formulas.pl
The script creates a subdirectory with a name that reflects the date and time. The subdirectory includes a summary.out file. - To determine the amount of unused space, review the summary.out file. For example:
c:\temp\temp_2015_0825_0553AM>perl analyze_DB2_formulas.pl
Created log file directory c:/temp/temp_2015_0825_0553AM/20150825-0555
Log files are available under c:/temp/temp_2015_0825_0553AM/20150825-0555
The script was SUCCESSFUL
c:\temp\temp_2015_0825_0553AM>
c:\temp\temp_2015_0825_0553AM>type 20150825-0555\summary.out
BEGIN SUMMARY
BF_AGGREGATED_BITFILES needs to be reorganized. estimated savings Table 50 GB, Index 477 GB
BF_BITFILE_EXTENTS needs to be reorganized. estimated savings Table 42 GB, Index 173 GB
BACKUP_OBJECTS needs to be reorganized. estimated savings Table 4 GB, Index 4 GB
ARCHIVE_OBJECTS needs to be reorganized. estimated savings Table 2 GB, Index 0 GB
AS_SEGMENTS needs to be reorganized. estimated savings Table 0 GB, Index 0 GB
Total estimated savings 752 GB
END SUMMARY
c:\temp\temp_2015_0825_0553AM>
The Total estimated savings entry does not reflect the savings from offline table reorganization. Instead, you must add the numbers from the tables that require reorganization, like this:
50 + 42 + 4 + 2 = 98 GB
Depending on the total database size, the savings might be insignificant.
To calculate the amount of unused space in a Tivoli Storage Manager server database on an AIX, HP-UX, Linux, or Oracle Solaris system, complete the following steps:
- Verify that the Perl interpreter is installed. If not, install the Perl interpreter by following the instructions in the operating system documentation.
- Log in with the DB2 instance user ID.
- Change the current directory to the target directory for the files that are generated by using the script.
- Start the script. For example, if the script is in the current directory, issue the following command:
perl analyze_DB2_formulas.pl
The script creates a subdirectory with a name that reflects the date and time. The subdirectory includes a summary.out file. - To determine the amount of unused space, review the summary.out file. For example:
-bash-4.2$ perl analyze_DB2_formulas.pl
Created log file directory /tmp/temp_2015_0825_0805AM/20150825-0607
Log files are available under /tmp/temp_2015_0825_0805AM/20150825-0607
The script was SUCCESSFUL
-bash-4.2$
-bash-4.2$ cat 20150825-0607/summary.out
BEGIN SUMMARY
BF_AGGREGATED_BITFILES needs to be reorganized. estimated savings Table 50 GB, Index 477 GB
BF_BITFILE_EXTENTS needs to be reorganized. estimated savings Table 42 GB, Index 173 GB
BACKUP_OBJECTS needs to be reorganized. estimated savings Table 4 GB, Index 4 GB
ARCHIVE_OBJECTS needs to be reorganized. estimated savings Table 2 GB, Index 0 GB
AS_SEGMENTS needs to be reorganized. estimated savings Table 0 GB, Index 0 GB
Total estimated savings 752 GB
END SUMMARY
-bash-4.2$
The Total estimated savings entry does not reflect the savings from offline table reorganization. Instead, you must add the numbers from the tables that require reorganization, like this:
50 + 42 + 4 + 2 = 98 GB
Depending on the total size of the database, the savings might be insignificant.
Use the Perl script, analyze_DB2_formulas.pl. To obtain the script, see Releasing space in DB2 9.7 table spaces .
If you installed IBM Spectrum Protect V7.1.3 or later, and you set up directory-container storage pools, inline data deduplication is used for the storage pools. The use of inline data deduplication reduces the risk of database growth and degraded system performance.
Table reorganization
The likelihood that you will have to run offline table reorganization is reduced. Offline table reorganization is not necessary for table names starting with SD, for example, SD_REFCOUNT_UPDATES. (If you believe that offline reorganization is necessary for a table that starts with SD, contact IBM Software Support for guidance before you proceed with the reorganization.)
The inventory component of the server might become fragmented over time. To reclaim database space, you might have to run offline reorganization for the following tables:
BACKUP_OBJECTS
ARCHIVE_OBJECTS
For instructions about running offline table reorganization, see Offline table reorganization .
Index reorganization
You do not have to run index reorganization because the server reorganizes indexes automatically.
After you convert all primary, deduplicated storage pools to directory-container storage pools, you can run table reorganization. In this way, you can reduce the size of the database and ensure that the space can be used for other purposes.
Complete the following steps:
1. Determine the number of rows in the BF_BITFILE_EXTENTS table by issuing the following commands:
- db2 connect to TSMDB1
db2 set schema=TSMDB1
select count(*) from BF_BITFILE_EXTENTS
2. Take one of the following actions:
- If the BF_BITFILE_EXTENTS table contains more than 1 million rows, run offline table reorganization. For instructions, see Offline table reorganization .
- If the BF_BITFILE_EXTENTS table contains fewer than 1 million rows, run online table reorganization by issuing the following commands:
- db2 connect to TSMDB1
db2 set schema=TSMDB1
db2 "reorg table BF_BITFILE_EXTENTS"
db2 "reorg table BF_AGGREGATED_BITFILES"
db2 "RUNSTATS ON TABLE tsmdb1.BF_BITFILE_EXTENTS WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL"
db2 "RUNSTATS ON TABLE tsmdb1.BF_AGGREGATED_BITFILES WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL"
Related Information
Was this topic helpful?
Document Information
Modified date:
26 October 2021
UID
swg21683633