DB2 10.5 for Linux, UNIX, and Windows

Scenario: Moving table spaces to different storage devices

This scenario uses multi-temperature storage to set up a database system that uses service classes of different priorities, each class using a different type of storage device.

Assume there are two storage devices: disk and SSD. You can set up a system to run short queries, based on the estimated cost, in a high-priority service class and large queries, based on the estimated cost, in a low-priority service class. In the low-priority service class, the number of large queries that can run concurrently is throttled. Initially, the table space data is on SSD storage, but as the data ages, you move it to slower storage.

To set up your database to use multi-temperature storage:

Create the storage group SSDGROUP for SSD and the storage group DISKGROUP for disk.

CREATE STOGROUP SSDGROUP on '/db2/ssdsystem' DEVICE READ RATE 350;
CREATE STOGROUP DISKGROUP on '/db2/disksystem'  DEVICE READ RATE 70;

Create the initial table space, initially in the SSDGROUP storage group.

CREATE TABLESPACE Q1_2010_TBSPC MANAGED BY AUTOMATIC STORAGE
  USING SSDGROUP PAGESIZE 8K

Create a service superclass that contains two subclasses: one for short queries and one for long queries:
```
CREATE SERVICE CLASS SC_SUPER;
CREATE SERVICE CLASS SC_HIGH UNDER SC_SUPER SOFT CPU SHARES 5000;
CREATE SERVICE CLASS SC_LOW UNDER SC_SUPER HARD CPU SHARES 2000;
```
Note: You can alternatively use AIX® WLM or Linux WLM integration to limit the impact of low-priority work.
Map all user work to the SC_SUPER service superclass by altering the SYSDEFAULTUSERWORKLOAD workload:
```
ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD SERVICE CLASS SC_SUPER
```

Create a work class set to isolate long queries from short queries based on the estimated cost:

CREATE WORK CLASS SET WLM_WCS
 (WORK CLASS WLM_DML_SHORT WORK TYPE DML FOR TIMERONCOST FROM 1 to 1000,
  WORK CLASS WLM_DML_LONG WORK TYPE DML FOR TIMERONCOST FROM 1001 to UNBOUNDED)

Create a work action set that maps the short queries to the high-priority service class and the long queries to the low-priority service class:

CREATE WORK ACTION SET WLM_WAS for SERVICE CLASS SC_SUPER 
  USING WORK CLASS SET WLM_WCS
    (WORK ACTION WLM_MAP_HIGH_WA ON WORK CLASS WLM_DML_SHORT
                      MAP ACTIVITY TO SC_HIGH,
     WORK ACTION WLM_MAP_LOW_WA ON WORK CLASS WLM_DML_LONG
                      MAP ACTIVITY TO SC_LOW)

Create a threshold named LIMITLOW that limits the number of concurrent long activities to five:

CREATE THRESHOLD LIMITLOW FOR SERVICE CLASS SC_LOW
  UNDER SC_SUPER ACTIVITIES ENFORCEMENT DATABASE
  WHEN CONCURRENTDBCOORDACTIVITIES > 5 CONTINUE

Create a new table space to hold the data for the new quarter and move the data from last quarter from SSD storage to disk storage:

CREATE TABLESPACE Q2_2010_TBSPC MANAGED BY AUTOMATIC STORAGE
  USING SSDGROUP PAGESIZE 8K;
ALTER TABLESPACE Q1_2010_TBSPC USING STOGROUP DISKGROUP;

Note: If the transfer rate of the SSD device is slower than what is noted in the catalog table, the estimated cost of queries using the device increases, and the queries using that device are mapped to the low-priority service class.