adsm.se

The following symptoms may be encountered:

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

Before server fixing levels 6.1.5.10 or 6.2.3.00, table and index reorganization did not work properly.

Servers running at a level below 6.1.5.10 or 6.2.3.00, should be upgraded to these levels or above. This will ensure the latest versions of the server reorganization code is running, providing the greatest benefit.
Expected reorganization activity
If server-initiated table and index reorganization is enabled, every table is reorganized, one-by-one, during the reorganization window. After all table reorganizations complete, table reorganization is followed by reorganization of the indices of each table. Depending on the amount of time that it takes to reorganize each table and its indices and the duration of the reorganization window each day, reorganization can take many days or weeks to finish. After a table or index reorganization completes, runstats is performed on the table. Depending on the size of the table, runstats can take several days to complete. After runstats completes, reorganization activity continues. Each table or index is reorganized once, and is not reorganized again for at least twenty days after it was first reorganized.

After the initial reorganization of all tables and the indices for all tables completes, the data is consolidated in the respective tables and index spaces. Free space is located at the end of the tables and the tablespaces. If this free space must be released to the operating system, see the section Releasing Space to the Operating System for details.

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

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

 
Obtaining Reorganization Status

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

• For table reorganizations, when a reorganization of a table starts message ANR0293I is issued, and when the reorganization of the table has completed, message ANR0294I is issued.
• For reorganizations of the indices for a table, when one starts message ANR0317I is issued, and when it completes, message ANR0318I is issued.
• When a reorganization completes, either for the table or the indices on the table, runstats is run on the table. When runstats begins, message ANR0336I is issued, and when it completes, message ANR0337I is issued.
• The server trace class TBREORG can be used to obtain a trace of reorganization activity.

Miscellaneous Details

1. There are several new server options for tailoring reorganization processing. See the section Reorganization Options below for details.
2. As the server pauses periodically after performing some reorganization activity, this allows the server to initiate a database backup, if necessary. If a database backup is currently running, no reorganization activity is started until the database backup completes. Thus having a current database backup takes precedence over reorganization activity. Once the database backup completes, 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.
3. Even though by default table reorganization is enabled 24 hours a day, it is recommended that table reorganization occur during a scheduled reorganization window where there is low server activity.
4. If index reorganization is enabled, ensure that there is very minimal server activity occurring while index reorganization is running. If reorganizations have not completed when server activity needs to resume, cancel the in-flight index reorganization. See Canceling Index Reorganizations for details.
5. Tivoli Storage Manager enables table reorganization by default. If the server has table or index reorganization enabled, DB2 reorganization capabilities are disabled. If neither server table nor server index reorganization is enabled, DB2 initiated reorganization is enabled–this is not recommended.

Reorganization Options

The following sections document the server options which 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 (expiration, migration, reclamation, etc.) workloads.

ALLOWREORGTABLE

Server option ALLOWREORGTABLE YES or ALLOWREORGTABLE NO are used to enable or disable server initiated table reorganization. If the option isn’t specified, it defaults to ALLOWREORGTABLE YES. This option is not available via SETOPT. The server needs to be halted, the server options file updated, and the server restarted for changes in this option to take effect.

ALLOWREORGINDEX

Server option ALLOWREORGINDEX YES or ALLOWREORGINDEX NO are used to enable or disable server initiated index reorganization. If the option isn’t specified, it defaults to ALLOWREORGINDEX NO. This option is not available via SETOPT. The server needs to be halted, the server options file updated, and the server restarted for changes in this option to take effect.

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

DB_DB2_KEEPTABLELOCK

for details.

REORGBEGINTIME and REORGDURATION

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

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

For information on REORGBEGINTIME see Techdoc 7021759.

For information on REORGDURATION see Techdoc 7021760.

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

DB_DB2_KEEPTABLELOCK

Server option DB_DB2_KEEPTABLELOCK NO or DB_DB2_KEEPTABLELOCK YES are used to set the DB2 DB2_KEEPTABLELOCK variable. If the option isn’t specified, it defaults to DB_DB2_KEEPTABLELOCK YES which is consistent with server levels before 6.1.5.10 and 6.2.3.00. This option is not available via SETOPT. The server needs to be halted, the server options file updated, and the server restarted for changes in this option to take effect.

If index reorganization is not running, the server should run with DB_DB2_KEEPTABLELOCK YES, which is the default behavior.

If index reorganization is running, the server should run with DB_DB2_KEEPTABLELOCK NO. Not doing so may result in the symptoms as 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):
1. If running a 6.1 Windows server
2. If running either a 6.1 or 6.2 server and doing large EXPORT NODE or IMPORT NODE operations.

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

When index reorganization needs to run, do the following:
1. Halt the server.
2. Set the server option
   ALLOWREORGINDEX YES
   which will allow index reorganizations to run.
3. Set the server option
   DB_DB2_KEEPTABLELOCK NO
   which prevents hitting APAR IC77773.
4. Set the server options REORGBEGINTIME and REORGDURATION appropriately according to installation requirements.
5. Restart the server.
6. Once index reorganization completes, disable index reorganization as indicated previously.

Canceling index reorganizationsIf the server has initiated an index reorganization and it needs to be cancelled, 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. Since DB2 reorganization uses redo logging, work that is already completed is not lost. After a reorganization completes normally, the server initiates a DB2 runstats on that table to optimize server access to the data in that table. Canceling that reorganization means that runstats won’t be run.

To cancel an index reorganization through DB2:

1. Determine the application ID of the reorganization process, by issuing the following commands in a DB2 Command Line Processor window:
   1. db2 connect to tsmdb1
   2. db2 get snapshot for all applications >application.out
2. Examine the application.out file and find the “Most recent operation” entry like this:
   Most recent operation = ReorganizeIf that line isn’t there, look for an entry like this:
   Application name = db2reorg
3. Scroll backwards until finding the “Application handle” entry. It will look like something like this:
   Application handle = NNNNN (where NNNNN is the actual application handle)
   Ensure that the correct application handle is found.
4. Issue the following command in the DB2 Command Line Processor Window substituting in the actual application handle in for NNNNN:
   db2 “force application (NNNNN)”
5. Because the nature of the command being canceled and that the DB2 FORCE APPLICATION command is asynchronous, it might take up to 30 minutes for the process to be canceled.
6. To verify that it has been canceled, issue steps 1b and 2 again. If there is no “Most recent operation” of type Reorganize message displayed, it has been canceled.

Attention: The DB2 FORCE APPLICATION command, if issued against a system critical process, can cause server instability and possibly cause the DB2 database to crash. It is crucially important that only the application that is running the index reorganization be forced in this manner. 
Releasing Space to the Operating System

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

After reorganization completes, free space should be consolidated near the end of the tablespaces and tables. To release space to the operating system, issue the DB2 ALTER TABLESPACE command. For information about databases that were formatted with server V6.1 (even if the server has been upgraded to server V6.2), see Releasing Space in DB2 9.5 Tablespaces. For information about databases that were formatted with server V6.2, see Releasing Space in DB2 9.7 Tablespaces.

Releasing Space in DB2 9.5 Tablespaces

Databases that were formatted by a V6.1 server have DB2 9.5 tablespaces. Upgrading a V6.1 server to V6.2 does not convert the tablespaces. DB2 9.5 has limited capacity for releasing space to the operating system. To free additional space, issue the following commands from a DB2 command-line window:
db2 connect to tsmdb1
db2 ALTER TABLESPACE USERSPACE1 REDUCE
db2 ALTER TABLESPACE IDXSPACE1 REDUCE
db2 ALTER TABLESPACE LARGESPACE1 REDUCE
db2 ALTER TABLESPACE LARGEIDXSPACE1 REDUCE
If issuing these commands do not free sufficient space, you can convert the DB2 9.5 tablespaces to DB2 9.7 tablespaces. For help with this conversion, contact Tivoli Storage Manager service. Be aware, however, that conversion is a time-intensive, multi step manual process that must be done while the server is halted. For this reason, tablespace conversion is strongly discouraged. 

Releasing Space in DB2 9.7 Tablespaces

Databases that are created with server V6.2 define DB2 9.7 tablespaces with reclaimable space enabled. Therefore, issuing ALTER TABLESPACE REDUCE on these tablespaces is much more likely to release free space to the operating system than DB2 9.5 tablespaces.

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

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

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

db2 connect to tsmdb1
db2 set schema tsmdb1
db2 ALTER TABLESPACE USERSPACE1 REDUCE MAX
db2 ALTER TABLESPACE IDXSPACE1 REDUCE MAX
db2 ALTER TABLESPACE LARGESPACE1 REDUCE MAX
db2 ALTER TABLESPACE LARGEIDXSPACE1 REDUCE MAX

These commands start separate processes in DB2 that release space. The commands can increase I/O activity, and might affect server performance. To minimize the impact, issue the next ALTER TABLESPACE command only after the previous command completes. You can monitor the progress of the command (for example, USERSPACE1) by examining the num_extents_left column of the MON_GET_EXTENT_MOVEMENT_STATUS procedure as follows:

db2 connect to tsmdb1
db2 set schema tsmdb1

 
Best practices

Following best practices tends to give the best results. Here are some best practices associated with database health:

1. Ensure that the machine on which the server is running is properly provisioned. This includes memory; available CPU resources; sufficient space in active log and archive log directories; regular database backups including the volume history; and that the database, logs, the database backups, and the volume history are all placed on high-performance reliable storage.
2. Tivoli Storage Manager server initiated reorganization is the recommended setting.
3. A reorganization window of a few hours a day will probably provide sufficient time for both server initiated reorganization activity and other DB2 maintenance to be performed once steady-state has been obtained. For databases that are quite fragmented, a larger reorganization window might be needed for some number of weeks until steady-state has been obtained. Steady-state is defined as reorganization having been run on all tables, and indices of tables, if index reorganization has been enabled.
4. Table reorganization might be sufficient to databases for servers which are not running deduplication. Both table and index reorganization are recommended on servers running deduplication.

Compressing indices on V6.2 servers6.2 servers ship DB2 9.7, which supports index compression. When a 6.2 server formats a new database, index compression is enabled on it. However, when a 6.1 server is upgraded to 6.2, index compression isn’t enabled. Server APAR IC78604 will automatically enable index compression on 6.2 servers. Note, that the actual compression of the indices occurs when reorganization runs on the indices of that table. Consequently, reorganization of indices must be enabled to compress the indices.

It is possible the active logs might be create/written to a directory other than what is specified in the dsmserv.opt file (ACTIVELOGDIR). If this is discovered, the DB2 configuration will need to be reviewed. Issue the following commands, as the instance owner:

db2 connect to tsmdb1
db2 get db cfg for tsmdb1

The “Path to log files = ” value should be reviewed to see if it matches with what the Tivoli Storage Manager has defined for the active log (‘ActiveLogDir’ from QUERY OPTION output). If these paths are not the same, the DB2 configuration can be updated by using the following steps:

1. Halt the Tivoli Storage Manager server
2. As the instance owner and from the instance home directory, issue the following commands:

db2start
db2 connect to tsmdb1
db2 update db cfg using NEWLOGPATH </path/to/active/logs>
db2 terminate
db2stop
db2start
db2 connect to tsmdb1; db2 get db cfg | grep “Path to log files”

3. If the path has been corrected, then stop DB2 (db2stop) and start the server normally

In order to find out what might have happened, the db2diag.log file should be reviewed. One reason this could happen is if DB2 is not able to access the log directory, DB2 can then update the log path to the default. Below is an example from a db2diag.log:

2010-11-08-13.52.25.668822-360 E38541A479 LEVEL: Error
PID : 5636338 TID : 1544 PROC : db2sysc 0
INSTANCE: tsminst1 NODE : 000 DB : TSMDB1
APPHDL : 0-7 APPID: *LOCAL.tsminst1.101108194849
AUTHID : TSMINST1
EDUID : 1544 EDUNAME: db2agent (TSMDB1) 0
FUNCTION: DB2 UDB, data protection services, sqlpgnlp, probe:2700
MESSAGE : ADM1814E The current log path “/tsmactivelog/NODE0000/” is invalid.

2010-11-08-13.52.25.749264-360 E39021A545 LEVEL: Error
PID : 5636338 TID : 1544 PROC : db2sysc 0
INSTANCE: tsminst1 NODE : 000 DB : TSMDB1
APPHDL : 0-7 APPID: *LOCAL.tsminst1.101108194849
AUTHID : TSMINST1
EDUID : 1544 EDUNAME: db2agent (TSMDB1) 0
FUNCTION: DB2 UDB, data protection services, sqlpgnlp, probe:2960
MESSAGE : ADM1811E DB2 will now switch to the default log path
“/home/tsminst1/NODE0000/SQL00001/SQLOGDIR/”

VSS system state restores fail with the following error logged in the dsmerror.log:

ANS5250E An unexpected error was encountered.
TSM function name : vssRequestor::prepareForRestore
TSM function : QueryInterface(IID_IVssBackupComponentsEx) returned
unknown error 4346(dec) 0x000010fa
TSM return code : 4346
TSM file : vssreq.cpp (7837)

ANS5250E An unexpected error was encountered.
TSM function name : vssPreRestore
TSM function : prepareForRestore() returned ’4346′
TSM return code : 4346
TSM file : vssrest.cpp (1162)

In order to perform Systemstate operations on Windows 2003, you first have to apply Windows 2003 SP 2 and the following Windows VSS hotfixes:
http://support.microsoft.com/kb/940349

http://support.microsoft.com/kb/934016

Note that this hotfix is documented as required in order to perform systemstate backup and restore:
http://www-01.ibm.com/support/docview.wss?rs=663&context=SSGSG7&uid=swg21197133&loc=en_US&cs=utf-8&lang=en

*NOTES on Windows 2003 requirements:

• If you plan to back up System State or use Open File Support with the VSS snapshot provider, you must install Service Pack 1 (SP1) or higher Service Pack level, and you must install Microsoft hotfix roll-up packages 934016 (for COM+) and 940349 (for VSS). The hotfix roll-up packages can be found at Microsoft’s support pagehttp://support.microsoft.com.
• If you plan to use an x64-bit machine, apply the Microsoft hotfix for knowledge base article 908675. The hotfix is included in Service Pack 2 for Microsoft Windows 2003. In case you cannot apply the entire Service Pack this particular hotfix can be provided by Microsoft upon request. See details at http://support.microsoft.com/kb/908675.

Scheduled backup hangs

When a Tivoli Storage Manager (TSM) client and server are configured to use the SESSIONINITIATION SERVERONLY option, this indicates that only the Tivoli Storage Manager server can initiate scheduled backup operations. If a scheduled operation experiences a communication problem and is interrupted, it is possible that the client could become unresponsive, leading to an incomplete or failed backup and missed future scheduled operations.

The issue in this situation is that the Tivoli Storage Manager client does not have the ability to re-establish the lost session due to the firewall and SESSIONINITIATION settings, and the Tivoli Storage Manager server does not currently have functionality to provide for session re-establishment. Because the scheduled event is still IN PROGRESS (as shown by the QUERY EVENT command output), subsequent scheduled backups also will not run, meaning that no scheduled backups from the affected node will run again without intervention.

This situation will require that the Tivoli Storage Manager client scheduler be halted and restarted, and the DELETE EVENT command will need to be issued on the Tivoli Storage Manager server to reset the scheduled event state for the affected node.

For example, to delete records for events with scheduled start times prior to 08:00 on May 26, 2011 (05/26/2011), and whose startup window has passed, issue the following command:
delete event 05/26/2011 08:00

Different include statements have different effects on the data being backed up.

• DIRMC management_class1
  This binds all the directory structures to the ‘management_class1′.
• INCLUDE c:\test\* management_class2
  This binds all the files in the directory \test to ‘management_class2′ which includes files with extensions and files with no extensions.
• INCLUDE c:\test\*.* management_class3
  This binds all the files in the directory \test that have an extension to management ‘management_class3′ but files with no extensions will still bind to the default management class.

Schedule server-initiated reorganizations to start during periods when server activity is low. Use this option together with the REORGBEGINTIME option. The REORGBEGINTIME option specifies the earliest time that the server can start a reorganization.

You can update this server option without stopping and restarting the server by using the SETOPT command.
See SETOPT (Set a server option for dynamic update).

Syntax

>>-REORGDURation–nn——————————————-><

Parameters

nn
Specifies the number of hours during which a reorganization can start. The minimum value is 1, the maximum value is 24. The default value is 24.

Example

Specify an interval of four hours during which a reorganization can start.
reorgduration 4

Schedule server-initiated reorganizations to start during periods when server activity is low. Use this option together with the REORGDURATION option. The REORGDURATION specifies an interval during which reorganization can start.

You can update this server option without stopping and restarting the server by using the SETOPT command.
See SETOPT (Set a server option for dynamic update).

Syntax

>>-REORGBEGINTime–hh:mm—————————————><

Parameters

hh:mm
Specifies the time that the server can start a reorganization: The default start time 6:00 a.m. Use a 24-hour format to specify the time.

Examples

Specify 6:00 a.m. as the earliest time that a reorganization can start.
reorgbegintime 06:00
Specify 8:30 p.m. as the earliest time that a reorganization can start.
reorgbegintime 20:30
Specify noon as the earliest time that a reorganization can start.
reorgbegintime 12:00
Specify 3:30 p.m. as the earliest time that a reorganization can start.
reorgbegintime 15:30
Specify midnight as the earliest time that a reorganization can start.
reorgbegintime 00:00