75 ways to demystify DB2 #76: Techtip : Using Rebalance to Change Storage Path and Move Data

Body

In the case customer wants to move the data from the current storage path to another storage path, or, simply wants to add/remove storage path, we can do it by rebalance. Below is an example:

1. Create a database with a storage path:

db2 "CREATE DATABASE SAMPLE ON /home/chungao/storpath1 DBPATH ON /home/chungao/datapath"

After the database is created, we can see the database files are created under the DBPATH (/home/chungao/datapath) and the container files are under storpath1 (/home/chungao/storpath1).

2. Add a new storage path and drop the old storage path:

db2 "ALTER DATABASE ADD STORAGE ON '/home/chungao/storpath2' DROP STORAGE ON '/home/chungao/storpath1'"

Now we can see that old container is still under /home/chungao/storpath1 and new container is under /home/chungao/storpath2. Storage path /home/chungao/storpath1 is now in the drop pending state.

3. Use the following query to determine the affected tablespaces:

db2 "SELECT DISTINCT TBSP_ID, VARCHAR(TBSP_NAME, 30) as TBSP_NAME
  FROM SYSIBMADM.SNAPTBSP
  WHERE TBSP_USING_AUTO_STORAGE = 1
    AND TBSP_CONTENT_TYPE IN ('ANY','LARGE')
    ORDER BY TBSP_ID"

In this example there are 3 outputs:

TBSP_ID              TBSP_NAME                    

-------------------- ------------------------------

                   0 SYSCATSPACE                  

                   2 USERSPACE1                   

                   3 SYSTOOLSPACE                 

  3 record(s) selected.

4. Now we need to rebalance the affected tablespaces:

db2 "ALTER TABLESPACE <tablespace_name> REBALANCE"

In this case:

1. db2 "ALTER TABLESPACE SYSCATSPACE REBALANCE"
2. db2 "ALTER TABLESPACE USERSPACE1 REBALANCE"
3. db2 "ALTER TABLESPACE SYSTOOLSPACE REBALANCE"
5. After rebalance the affected tablespaces, we can see that the container files are now moved from /home/chungao/storpath1 to /home/chungao/storpath2.

This is a way to add/delete storage path, also it can be used to remove container files from one path to another.