Is your data hot, warm, or cold?

Editor's note: This article is an excerpt from the book DB2 10.1/10.5 for Linux, UNIX, and Windows Database Administration (Exams 611 and 311): Certification Study Guide.

The amount of data stored in enterprise data warehouse environments is growing at an exponential rate. At the same time, the end-user experience expectations are getting higher and higher. The challenge here is to store enormous amount of data without impacting application query performance and storage costs.

With DB2 10.1, you can classify the data warehouse data according to its temperature—Hot, Warm, or Cold. The temperature of the data is decided based on these factors:

• How often the data is accessed
• How critical the performance of the queries that access the data is
• How old the data is

You can configure the database so that frequently accessed data is stored on fast storage such as SSD drives, infrequently accessed data is stored on slightly slower storage such as SAS drives, and rarely accessed data is stored on slow storage such as SATA drives, as in the figure below.

This feature reduces storage costs because not all data is required to be stored on fast, expensive drives. It also helps achieve better end-user experience due to the ability to store recent data on fast drives.

The following steps provide more information about how to implement multi-temperature data storage for the sales data in the current financial year:

Step 1: Create three storage groups: SG_HOT to store frequently access data, SG_WARM to store infrequently accessed data, and SG_COLD to store occasionally accessed data:

CREATE STOGROUP SG_HOT ON '/data/hot/fs1' OVERHEAD 0.825

DEVICE READ RATE 512 DATA TAG 1

DB20000I The SQL command completed successfully.

CREATE STOGROUP SG_WARM ON '/data/warm/fs1' OVERHEAD 6.725

DEVICE READ RATE 100 DATA TAG 2

DB20000I The SQL command completed successfully.

CREATE STOGROUP SG_COLD ON '/data/cold/fs1' OVERHEAD 7.525

DEVICE READ RATE 70 DATA TAG 3

DB20000I The SQL command completed successfully.

Step 2: Create four table spaces to store quarter data and assign it to each respective storage group:

CREATE TABLESPACE TBSP3 USING STOGROUP SG_HOT

DB20000I The SQL command completed successfully.

CREATE TABLESPACE TBSP2 USING STOGROUP SG_WARM

DB20000I The SQL command completed successfully.

CREATE TABLESPACE TBSP1 USING STOGROUP SG_COLD

DB20000I The SQL command completed successfully.

CREATE TABLESPACE TBSP0 USING STOGROUP SG_COLD

DB20000I The SQL command completed successfully.

Step 3: Create a range partitioned table to store sales data based on the date:

CREATE TABLE SALES

(SALES_DATE DATE,

SALES_AMOUNT NUMERIC (5, 2))

IN TBSP0, TBSP1, TBSP2, TBSP3

PARTITION BY RANGE (SALES_DATE NULLS FIRST)

(STARTING '1/1/2014' ENDING '12/31/2014' EVERY 3 MONTHS)

DB20000I The SQL command completed successfully.

In the above example, 2014 Q1 data is stored in table space TBSP0, 2014 Q2 data in table space TBSP1, 2014 Q3 data in table space TBSP2, and 2014 Q4 data in table space TBSP3. You can also verify the storage allocation by executing the DESCRIBE DATA PARTITIONS command:

DESCRIBE DATA PARTITIONS FOR TABLE sales SHOW DETAIL

PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode Status

----------- ------------- --------- --------- ------------ ----------- ---------- ------

0          PART0       5        4        5           5           F

1          PART1       7        4        7           7           F

2          PART2       8        4        8           8           F

3          PART3       9        4      9           9           F

4 record(s) selected.

PartitionId Inclusive (y/n) Inclusive (y/n)

Low Value High Value

----------- - ------------------------------- - --------------------------

0 Y '2014-01-01' N '2014-04-01'

1 Y '2014-04-01' N '2014-07-01'

2 Y '2014-07-01' N '2014-10-01'

3 Y '2014-10-01' Y '2014-12-31'

4 record(s) selected.

Step 4: Re-adjust the temperature. At some point, new data for 2015 Q1 will be loaded into the database, which will take the higher priority for performance reasons. You can then associate the table space TBSP2 with the storage group SG_COLD and table space TBSP3 with the storage group SG_WARM, keeping the new table space TBSP4 on the fastest storage group—SG_HOT. To move the table spaces online from one storage group to another, use the ALTER TABLESPACE command. The set of commands look something like this:

CREATE TABLESPACE TBSP4 USING STOGROUP SG_HOT

DB20000I The SQL command completed successfully.

ALTER TABLE sales ADD PARTITION "PART4"

STARTING FROM ('2015-01-01') ENDING AT ('2015-04-01')

IN TBSP4 INDEX IN TBSP4

DB20000I The SQL command completed successfully.

ALTER TABLESPACE TBSP2 USING STOGROUP SG_COLD

DB20000I The SQL command completed successfully.

ALTER TABLESPACE TBSP3 USING STOGROUP SG_WARM

DB20000I The SQL command completed successfully.

Learn more with the book DB2 10.1/10.5 for Linux, UNIX, and Windows Database Administration (Exams 611 and 311): Certification Study Guide.