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.
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
- 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.
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.
This association results in table spaces inheriting the storage group properties.CREATE TABLESPACE TbSpc11 USING STOGROUP sg_warm; CREATE TABLESPACE TbSpc12 USING STOGROUP sg_warm; CREATE TABLESPACE TbSpc13 USING STOGROUP sg_warm; CREATE TABLESPACE TbSpc14 USING STOGROUP sg_hot;
- Set up your range partitions in your sales table.
The Q12011 data represents the current fiscal quarter and is using theCREATE TABLE sales (order_date DATE, order_id INT, cust_id BIGINT) PARTITION BY RANGE (order_date) (PART "Q22010" STARTING ('2010-04-01') ENDING ('2010-06-30') in "TbSpc11", PART "Q32010" STARTING ('2010-07-01') ENDING ('2010-09-30') in "TbSpc12", PART "Q42010" STARTING ('2010-10-01') ENDING ('2010-12-31') in "TbSpc13", PART "Q12011" STARTING ('2011-01-01') ENDING ('2011-03-31') in "TbSpc14");
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 TbSpc15 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 TbSpc14 table space, issue the ALTER TABLESPACE statement with
the USING STOGROUP
option.
ALTER TABLESPACE TbSpc14 USING STOGROUP sg_warm;
- Create a new partition for the next quarter in TbSpc15.
ALTER TABLE sales ADD PART Q22011 STARTING ('2011-04-01') ENDING ('2011-06-30') IN TbSpc15;