Relocating inactive tablespaces or segments to tier two storage


Relocating inactive tablespaces or segments to tier two storage
Prev	SmartTier use cases for DB2	Next

It is general practice to use partitions in databases. Each partition maps to a unique tablespace. For example in a shopping goods database, the orders table can be portioned into orders of each quarter. Q1 orders can be organized into Q1_order_tbs tablespace, Q2 order can be organized into Q2_order_tbs.

As the quarters go by, the activity on older quarter data decreases. By relocating old quarter data into Tier-2, significant storage costs can be saved. The relocation of data can be done when the database is online.

For the following example use case, the steps illustrate how to relocate Q1 order data into Tier-2 in the beginning of Q3. The example steps assume that all the database data is in the /DBdata filesystem.

To prepare to relocate Q1 order data into Tier-2 storage for DB2

Obtain a list of containers belonging to Q1_order_tbs.
```
$ db2inst1$  db2 list  tablespaces
```

Find the tablespace-id for the tablespace Q1_order_tbs.

$ db2inst1$ db2  list  tablespace containers for <tablespace-id>

Find the path names for the containers and store them in file Q1_order_files.txt.

#cat  Q1_order_files.txt 
          NODE0000/Q1_order_file1.f
          NODE0000/Q1_order_file2.f
          ...
          NODE0000/Q1_order_fileN.f

To relocate Q1 order data into Tier-2

Prepare a policy XML file. For the example, the policy file name is Q1_order_policy.xml. Below is a sample policy.

This is policy is for unconditional relocation and hence there is no WHEN clause. There are multiple PATTERN statements as part of the SELECT clause. Each PATTERN selects a different file.

<?xml version="1.0"?>
<!DOCTYPE PLACEMENT_POLICY SYSTEM "/opt/VRTSvxfs/etc/\
placement_policy.dtd">
<PLACEMENT_POLICY Version="5.0" Name="selected files">
    <RULE Flags="data" Name="Key-Files-Rule">
        <COMMENT>
            This rule deals with key important files.
        </COMMENT>

        <SELECT Flags="Data">
            <DIRECTORY Flags="nonrecursive" > NODE0000</DIRECTORY>
            <PATTERN> Q1_order_file1.f  </PATTERN>
            <PATTERN> Q1_order_file2.f  </PATTERN>
            <PATTERN> Q1_order_fileN.f  </PATTERN>
        </SELECT>

        <RELOCATE>
             <COMMENT>
                Note that there is no WHEN clause.
             </COMMENT>
             <TO>
                <DESTINATION>
                    <CLASS> tier2 </CLASS>
                </DESTINATION>
             </TO>
         </RELOCATE>

    </RULE>
</PLACEMENT_POLICY>

Validate the policy Q1_order_policy.xml.

# fsppadm validate /DBdata Q1_order_policy.xml

Assign the policy.

# fsppadm assign /DBdata Q1_order_policy.xml

Enforce the policy.
```
# fsppadm enforce /DBdata
```

Prev	Up	Next
Relocating old archive logs to tier two storage using SmartTier	Home	Relocating active indexes to premium storage