Multi-temperature data feature
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.
Multi-temperature storage provides the ability to assign priority to data (hot, warm, cool, cold) and dynamically assign it to different classes of storage. This is carried out by the introduction of storage groups, which is a new layer of abstraction between logical table spaces and physical storage (containers).
Storage groups allow the flexibility to implement multi-temperature data management in automatic storage table spaces. After you create storage groups that map to the different classes of storage in your database management system, you can assign automatic storage table spaces to those storage groups, based on which table spaces have hot, warm, or cold data.
The following example illustrates the use of storage groups with multi-temperature data. As shown in the figure, solid-state drives (SSD) are used to hold data for the current quarter, and enough Fibre Channel-based (FC) and Serial Attached SCSI (SAS) drives are used to hold data for the previous three quarter. The data that is older than one year is stored on a large Serial ATA (SATA) RAID array that does not perform quickly enough to withstand a heavy query workload.
Figure 9. Usage of storage groups with multi-temperature data
You can dynamically reassign a table space to a different storage group as the
data changes or your business direction changes. The
TABLESPACE statement can be used to do this:
ALTER TABLESPACE tbSpc USING STOGROUP sg_target.
ALTER TABLESPACE statement is committed,
containers are allocated on the new storage group's storage paths, the existing
containers residing in the old storage groups are marked as drop pending, and an
REBALANCE operation is initiated. This
operation allocates containers on the new storage path and rebalances the data
from the existing containers into the new containers. The number and size of the
containers to create depend on both the number of storage paths in the target
storage group and on the amount of free space on the new storage paths. The old
containers are dropped, after all the data is moved.
DB2 Workload Manager (WLM) gives users the ability to prioritize incoming work based on what data is accessed (a data-centric approach). Users can assign a data tag attribute (a value from 0 to 9) to a storage group or tablespace. The data tag can be used by WLM to determine how to treat the work.
WLM can use data tags predictively. The Optimizer gathers a list of data tags for all table spaces used by an SQL statement at compile time. The data tag can influence the initial placement of the statement into a service class. WLM can also use data tags reactively. At runtime, based on the tag of a table space being accessed, a statement can be remapped into a lower-priority subclass.
The following steps provide more details on how to set up multi-temperature data storage for the example shown above:
- Create three storage groups to reflect the three classes of storage, a
storage group to store hot data, a storage group to store warm data, and a
storage group to store cold data.
CREATE STOGROUP sg_hot ON '/hot/fs1' DATA TAG 1 CREATE STOGROUP sg_warm ON '/warm/fs1', '/warm/fs2' DATA TAG 5 CREATE STOGROUP sg_cold ON '/cold/fs1', '/cold/fs2', '/cold/fs3' DATA TAG 9
Data tags values assigned to storage groups sg_hot and sq_warm to indicate the business priority of the data stored in these storage groups to the WLM.
- Create four table spaces, one per quarter of data in the year, and assign
the table spaces to the storage groups. Data tags represent business
priority to be used by the optimizer.
CREATE TABLESPACE tbsp_2012q1 USING STOGROUP sg_warm CREATE TABLESPACE tbsp_2012q2 USING STOGROUP sg_warm CREATE TABLESPACE tbsp_2012q3 USING STOGROUP sg_warm DATA TAG 3 CREATE TABLESPACE tbsp_2012q4 USING STOGROUP sg_hot
This association results in table spaces inheriting the storage group properties. Table spaces tbsp_2012q1 and tbsp_2012q2 will inherit the data tag value of 5 from the sg_warm storage group, but table space tbsp_2012q3 will not inherit the data tag value from the storage group because another data tag value has been assigned to the table space. Table space for data tbsp_2012q4 will inherit the data tag value of 1 from the sh_hot storage group.
- Set up your range partitions in your table:
Listing 20. Set up range partitions
CREATE TABLE ... PARTITION BY RANGE (date_column) (PART "2012Q1" STARTING ('2012-01-01') ENDING ('2012-03-31') in "tbsp_2012q1", PART "2012Q2" STARTING ('2012-04-01') ENDING ('2012-06-30') in "tbsp_2012q2", PART "2012Q3" STARTING ('2012-07-01') ENDING ('2012-09-30') in "tbsp_2012q3", PART "2012Q4" STARTING ('2012-10-01') ENDING ('2012-12-31') in "tbsp_2012q4"
The 2012Q4 data represents the current quarter and is using the sg_hot storage group.
- After the current quarter passes, create a table space for the new quarter
and assign the table space to the sg_hot storage group:
CREATE TABLESPACE tbsp_2013q1 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_2012q4 table space, issue the
ALTER TABLESPACEstatement with the
USING STOGROUPoption. To fix the business priority for the altered tablespace, the
DATA TAGoption will be used. Additionally, table space tbsp_2012q3 will be altered to change the data tag value from 3 to 5:
ALTER TABLESPACE tbsp_2012q4 USING STOGROUP sg_warm DATA TAG 3 ALTER TABLESPACE tbsp_2012q3 DATA TAG 5
- Finally, move the table space for the older quarter assigned to
sg_warm storage group to sg_cold storage group. To change
the storage group association for the tbsp_2012q1 table space, issue
ALTER TABLESPACEstatement with the
ALTER TABLESPACE tbsp_2012q1 USING STOGROUP sg_cold.