#DB2-8 The DB2Night Show #130: DB2’s GOT TALENT Top 7 Finalists Compete!

08 Apr

This post is based on the Multi-Temperature Storage Management Feature Introduced in DB2 10.1. I presented this topic at DB2Night Show on March 21, 2014. The theme for this episode was Storage. Since I am obsessed with the new fabulous features introduced in this version, without any second thought I chose this topic for this round of Finale.

This episode decided our progression for the Grand Finale and fortunately based on votes I qualified for the Grand Finale.
Here is the quick overview of the Multi-Temperature Storage Management Feature.

Storage Groups:

Storage groups are the logical groupings of the automatic storage paths. The storage paths grouped by a particular are identified by the same storage path characteristics like latency, overhead, transfer-rate etc. Once the storage group is created the, we can assign the Automatic Storage tablespaces to the respective storage group. This association of the tablespaces with the storage group is completely dynamic and we can change it whenever required. Here is the visual illustration of how storage groups are formed.





Multi-Temperature Storage Management:

In most of the OLTP applications, it is found that priority of the data is inversely proportional to the volume of the data. The data which is frequently access are usually low in the volume and analytical which is very huge in the amount are accessed rarely. This is the foundation on which entire concept of the Multi-Temperature is built.

Multi-Temperature Storage feature allows you to configure your database according to the priority of the data. It classifies the data as HOT, WARM and COLD according to the priority of the data. Once the data is classified we can put the HOT data on the fast storage devices so that they can be accessed quickly and the large analytical data on the slow and inexpensive storage devices. Once the HOT data cools down and is accessed less frequently, we can move the data to Slow storage dynamically.

Multi-Temperature Storage Scenario

Consider the above scenario where an organization implemented the Multi-Temperature Storage. The data of the current quarter which is getting accessed very frequently is considered as HOT and placed on the fast storage such as SSD (Solid State Drive). The data of the previous few quarters which is accessed less frequently is stored on the WARM storage device Like Fibre channel and is said to be WARM data. Finally the data which is rarely accessed such as the data of the last few years is stored on the SATA RAID drive. COLD data are usually very high in volume and are accessed only by the analytical queries. So we placed such large volume on the inexpensive storage devices. This way we can implement the multi-temperature storage feature for the real time applications. All the automatic tablespaces associated with the respective storage groups are having the same performance characteristics.

Here are the implementation steps for setting up the multi-temperature storage:

1) Creating the Storage Groups:

ON ‘/storage/v10/hot1′, ‘/storage/v10/hot2’

2) Create or Alter tablespaces to use Storage Group

i) Create Tablespace

                CREATE TABLESPACE tbspace7
                 PAGESIZE 1024
                 OVERHEAD [no-milliseconds / INHERIT]
                 TRANSFERRATE [no-milliseconds / INHERIT]
                 DATA TAG [INTEGER / NONE/ INHERIT]
                 USING STORAGE GROUP Sto_Grp_Hot;

ii) Alter the existing Tablespace to use the given storage group

                   ALTER TABLESPACE tbspace7
                      USING STOGROUP Sto_Grp_Hot;

NOTE: While altering the tablespace it is mandatory that it is AUTOMATIC storage tablespace.

3) For Non- Automatic Storage Tablespaces

For associating the non-automatic storage tablespaces with the storage group, we first need to alter the tablespace to the AUTOMATIC storage. Then need to perform the rebalance operation on the tablespaces.



Rebalance Operation allocate containers on the new storage path and rebalance the data from the existing containers into the new containers.

In case of the Automatic Storage tablespaces, rebalance operation in implicit and is done automatically. But in case of the non-automatic storage groups, we need to explicitly issue the rebalance command for rebalancing of the data in the new tablespaces.

NOTE: REBALANCE operation is mandatory in case of the NON-Automatic Storage tablespaces.


i) Stores the data based on the priority and provides the fast access to the frequently accessed data.
ii) Helps to extend the life of your older storage devices. We can store the COLD data in these older storage.
iii) The setup and administration is very simple and flexible.
iv) Help to manage your IT budget more efficiently by efficiently using expensive storage resources. This is very important from the organizational perspective.

Monitoring Tips and Support:

IBM provided the strong support for this new functionality in the form of system catalog views and table functions. Here are few important views that DBA will need for the monitoring purpose.

i) Views for getting the Storage Group Information

ii) Table Function to get the storage group information from the database

iii) Utility option for monitoring the storage group information in database

db2pd -storagegroup

iv) Table Function to check the status of tablespace rebalancing
Some of the best articles I came across while studying this topic.

i) IBM Information Management Article on DB2 10.1 New Features

ii) IBM Info Center

iii) I got one very helpful article on migrating your database to DB2 v10.1 Multi-temperature Storage Groups setup

I uploaded my presentation at the below link:


To view the complete episode and learn about some other very important and useful storage tips, download and view the complete episode from the below link:

Leave a comment

Posted by on April 8, 2014 in Databases, DB2, db2nightshow


Tags: , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: