How to redistribute data and reclaim TSM DB space manually by using DB2 commands

If you did not redistribute the DB directories when you added them to the TSM server or if you used RECLAIMSTORAGE=NO when running EXTEND DBSPACE command, then you need to run the following DB2 commands to manually redistribute data and reclaim space from the old DB directories. 

The redistribution process, also known as rebalancing, only works with DB2 version 9.7 or later table spaces. Please note that the rebalancing process uses considerable resources and you need to follow the below tips before you start the procedure.

• Run the process when the server is not handling a heavy workload.

• To redistribute data to new directories, the storage paths must be able to host the directories and data. Make sure that sufficient disk space is available for the operation.

• The time that is required to redistribute data and reclaim space might vary. File system layout, the ratio of new paths to existing storage paths, server hardware, and concurrent operations are all factors in determining the time requirement. Start the process with one small and one medium-sized table space and then try a larger table space. Use your results as a reference to estimate the time that is needed to process remaining table spaces.

• Do not interrupt the process. If you try to stop it, for example, by halting the process that is completing the work, you must stop and restart the DB2 server. When the server is restarted, it will go into crash recovery mode, which takes several minutes, after which the process resumes.

• For the best performance, rebalance a table space and then reduce the size for that table space. While the size for the first table space is being reduced, you can start rebalancing on the second table space to save some time.

Steps to redistribute or rebalance the TSM DB directories

Use the following steps to redistribute data and then reclaim space for each table space. 

1) Open the DB2 command line processor and issue the following command:

db2 connect to tsmdb1

2) List DB2 table spaces by issuing the following command. To display details about each table space, including the total size of a table space and how many bytes are used in each file system where the table space is located, include show detail.

db2 list tablespaces show detail

You only need to redistribute data on Database Managed Space (DMS) table spaces. The following example output shows where the table space type is identified:

Tablespaces for Current Database---------------------------------------------- Tablespace ID            = 0 Name                     = SYSCATSPACE Type                     = Database managed space  <---DMS table space. Contents                 = All permanent data. Regular table space. State                    = 0x0000 Detailed explanation: Normal

Also Read: Use this DB2 command to extend TSM DB space when the TSM server is down or unable to startup

3) Use the list that you obtained in Step 2 to identify each DMS table space. For each DMS table space, issue the following command to start redistribution of data for the first DMS space:

db2 alter tablespace tablespace_name rebalance 

For example : db2 alter tablespace SYSCATSPACE rebalance

4) Monitor the data redistribution progress by issuing the following command:

db2list utilities show detail

If the rebalance process is running, the command output shows Type = REBALANCE, and also indicates how many extents are moved and how many remain to be moved. The following example output shows where these details are displayed

ID = 6219Type = REBALANCE                   <--- Data is being redistributed.Database Name = AX4Partition Number = 0Description = Tablespace ID: 37Start Time = 04/27/2009 21:37:37.932471State = ExecutingInvocation Type = UserThrottling:Priority = UnthrottledProgress Monitoring:Estimated Percentage Complete = 15Total Work = 22366 extents         <--- Total extents to be moved.Completed Work = 3318 extents      <--- Total extents moved.Start Time = 01/03/2017 21:37

The value in the Completed Work field should increase as the redistribution progresses. The db2diag log also records status about the process, including start and complete time and what percentage of the process is complete at a certain time.

5) After the redistribution process is completed, reduce the size for each table space. During and after the operation, table spaces have a much larger total size because directories are added. Issue the following command

db2 alter tablespace tablespace_name reduce max

For Example: db2 alter tablespace SYSCATSPACE reduce max