DB2 10.5 for Linux, UNIX, and Windows

Data management using multi-temperature storage

You can configure your databases so that frequently accessed data (hot data) is stored on fast storage, infrequently accessed data (warm data) is stored on slightly slower storage, and rarely accessed data (cold data) is stored on slow, less-expensive storage. As hot data cools down and is accessed less frequently, you can dynamically move it to the slower storage.

In database systems, there is a strong tendency for a relatively small proportion of data to be hot data and the majority of the data to be warm or cold data. These sets of multi-temperature data pose considerable challenges if you want to optimize the use of fast storage by trying not to store cold data there. As a data warehouse consumes increasing amounts of storage, optimizing the use of fast storage becomes increasingly important in managing storage costs.

Storage groups are groups of storage paths with similar qualities. Some critical attributes of the underlying storage to consider when creating or altering a storage group are available storage capacity, latency, data transfer rates, and the degree of RAID protection. You can create storage groups that map to different classes of storage in your database management system. You can assign automatic storage table spaces to these storage groups, based on which table spaces have hot, warm, or cold data. To convert database-managed table spaces to use automatic storage, you must issue an ALTER TABLESPACE statement specifying the MANAGED BY AUTOMATIC STORAGE option and then perform a rebalance operation.

Because current data is often considered to be hot data, it typically becomes warm and then cold as it ages. You can dynamically reassign a table space to a different storage group by using the ALTER TABLESPACE statement, with the USING STOGROUP option.

The following example illustrates the use of storage groups with multi-temperature data. Assume that you are the DBA for a business that does most of its processing on current-fiscal-quarter data. As shown in Figure 1, this business has enough solid-state drive (SSD) capacity to hold data for an entire quarter and enough Fibre Channel-based (FC) and Serial Attached SCSI (SAS) drive capacity to hold data for the remainder of the year. The data that is older than one year is stored on a large Serial ATA (SATA) RAID array that, while stable, does not perform quickly enough to withstand a heavy query workload. You can define three storage groups: one for the SSD storage (sg_hot), one for the FC and SAS storage (sg_warm), and the other for the SATA storage (sg_cold). You then take the following actions:

Assign the table space containing the data for the current quarter to the sg_hot storage group
Assign the table space containing the data for the previous three quarters to the sg_warm storage group
Assign the table space containing all older data to the sg_cold storage group

After the current quarter passes, you take the following actions:

Assign a table space for the new quarter to the sg_hot storage group
Move the table space for the quarter that just passed to the sg_warm storage group
Move the data for the oldest quarter in the sg_warm storage group to the sg_cold storage group

You can do all this work while the database is online.

Figure 1. Managing Sales data using multi-temperature data storage

The following steps provide more details on how to set up multi-temperature data storage for the sales data in the current fiscal year:

Create two storage groups to reflect the two classes of storage, a storage group to store hot data and a storage group to store warm data.
```
CREATE STOGROUP sg_hot ON '/ssd/path1', '/ssd/path2' DEVICE READ RATE 100 
         OVERHEAD 6.725;
CREATE STOGROUP sg_warm ON '/hdd/path1', '/hdd/path2';
```
These statements define an SSD storage group (sg_hot) to store hot data and an FC and SAS storage group (sg_warm) to store warm data.

Create four table spaces, one per quarter of data in a fiscal year, and assign the table spaces to the storage groups.

CREATE TABLESPACE tbsp_2010q2 USING STOGROUP sg_warm;
CREATE TABLESPACE tbsp_2010q3 USING STOGROUP sg_warm;
CREATE TABLESPACE tbsp_2010q4 USING STOGROUP sg_warm;
CREATE TABLESPACE tbsp_2011q1 USING STOGROUP sg_hot;

This association results in table spaces inheriting the storage group properties.

Set up your range partitions in your sales table.

CREATE TABLE sales (order_date DATE, order_id INT, cust_id BIGINT) 
PARTITION BY RANGE (order_date) 
(PART "2010Q2" STARTING ('2010-04-01') ENDING ('2010-06-30') in "tbsp_2010q2", 
 PART "2010Q3" STARTING ('2010-07-01') ENDING ('2010-09-30') in "tbsp_2010q3", 
 PART "2010Q4" STARTING ('2010-10-01') ENDING ('2010-12-31') in "tbsp_2010q4",
 PART "2011Q1" STARTING ('2011-01-01') ENDING ('2011-03-31') in "tbsp_2011q1");

The 2011Q1 data represents the current fiscal quarter and is using the sg_hot storage group.

After the current quarter passes, create a table space for a new quarter, and assign the table space to the sg_hot storage group.
```
CREATE TABLESPACE tbsp_2011q2 USING STOGROUP sg_hot;
```
Move the table space for the quarter that just passed to the sg_warm storage group. To change the storage group association for the tbsp_2011q1 table space, issue the ALTER TABLESPACE statement with the USING STOGROUP option.
```
ALTER TABLESPACE tbsp_2011q1 USING STOGROUP sg_warm;
```