Relocating active indexes to premium storage


Relocating active indexes to premium storage
Prev	SmartTier use cases for DB2	Next

The database transaction rate depends upon how fast indexes can be accessed. If Indexes reside on slow storage, the database transaction rate suffers. Tier-0 storage is generally too expensive to be practical to relocate the entire table data to Tier-0. Indexes are generally much smaller in size and are created to improve the database transaction rate, hence it is more practical to relocate active indexes to Tier-0 storage. Using SmartTier you can move active indexes to Tier-0 storage.

For the following telephone company database example procedure, assume the call_details table has an index call_idx on the column customer_id.

To prepare to relocate call_idx to Tier-0 storage for DB2

Find the tablespace where call_idx resides.

$ db2inst1$ 	db2 connect to PROD
$ db2inst1$ 	db2 select index_tbspace from syscat.tables \
where tabname='call_details'

In this example, the index is in tablespace tbs_call_idx. To get the tablespace id for tbs_call_idx and the list of containers:
```
$ db2inst1$ 	db2  list tablespaces
```
Note the tablespace id for tbs_call_idx.
List the containers and record the filenames in the tabelspace tbs_call_idx.
```
$ db2inst1$ 	db2 list tablespace containers for <tablespace-id>
```

Store the files in index_files.txt.

# cat  index_files.txt
/DB2data/NODE0000/IDX/call1.idx
/DB2data/NODE0000/IDX/call2.idx
/DB2data/NODE0000/IDX/call3.idx

To relocate call_idx to Tier-0 storage

Prepare the policy index_policy.xml.

Example policy:

<?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> call*.idx  </PATTERN>
        </SELECT>

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

    </RULE>
</PLACEMENT_POLICY>

Assign and enforce the policy.

# fsppadm validate /DBdata index_policy.xml
# fsppadm assign /DBdata index_policy.xml
# fsppadm enforce /DBdata

Prev	Up	Next
Relocating inactive tablespaces or segments to tier two storage	Home	Relocating all indexes to premium storage