Storage groups - A comprehensive solution of handling your data

Monday, 20 January 2014 Storage groups manohar viswanatha

Presumptions :

In this post we assume 3 mount points namely C:\ , D:\ , E:\ .

C:\ & D:\ are considered to be SSD's and

E:\ is considered to be a RAID device or normal HDD

I have gone through Ember's recent post A Few Differences When Moving From 9.7 to 10.1 where I came across storage groups - a new way of handling physical data .I just thought of giving it a try and went through the db2 v10.1 documentation where the feature was introduced , let me tell you db2 is getting rock-steady version by version

By default when you install db2 10.1 a storage group is created with the name IBMSTOGROUP.We have been provided with a new catalog table SYSCAT.STOGROUPS where in you can query the storage groups . So if you query it after a fresh installation here is what you see

select substr(SGNAME,1,40) as SGNAME from syscat.stogroups

SGNAME
----------------------------------------
IBMSTOGROUP

  1 record(s) selected.

You can create storage path with the following syntax

create stogroup FASTDATA on 'C:\','D:\'

Let us see some important default options it takes while we execute the above command

• OVERHEAD : If not mentioned it takes 6.725 milliseconds
• DEVICE READ RATE : If not mentioned it takes 100 megabytes per second
• SET AS DEFAULT : set the current storage group as a default choice

So executing the above command is very much equal to

create stogroup FASTDATA on 'C:\','D:\' OVERHEAD 6.725 DEVICE READ RATE 100

Now when creating the tablespace mention the storage space where it should reside on

create tablespace FASTBSP using stogroup FASTDATA

So now what ever the table I create in tablespace FASTBSP will get split between C:\ and D:\ partitions , in other words the data get's REBALANCED between partitions C:\ and D:\

For Ex:

CREATE TABLE DEPARTMENT1      
(DEPTNO     SMALLINT      NOT NULL,
 DEPTNAME   VARCHAR(36)   NOT NULL,      
 MGRNO      CHAR(6),      
 MALLINT    NOT NULL,     
 LOCATION   CHAR(30)) in FASTBSP

So now the table resides in the storage group FASTDATA i.e., on partitions C:\ and D:\

Say in future if you want to move the FASTBSP tablespace to another storage group , lets say E:\ , here is how you do it

Before that let's create another storage group on mount point E:\

create stogroup SLOWDATA on 'E:\'

create tablespace SLOWTBSP using stogroup SLOWDATA

Just ALTER the tablespace as shown below

alter tablespace FASTBSP using stogroup SLOWDATA

So its just like recycling your old data to slow storagegroup and keeping your fast storage as always fast.It's simple , to be frant it's dead simple if you have a proper architecture.

Monitoring Storage Paths :

As you have come through the post we have create 2 extra storage groups FASDATA and SLOWDATA in addition to db2's default storage group IBMSTOGROUP

Querying the table SYSCAT.STOGROUPS we come to know the ID's and names as shown below

select substr(SGNAME,1,40) as SGNAME,SGID from syscat.stogroups

SGNAME                                   SGID
---------------------------------------- -----------
IBMSTOGROUP                                        0
FASTDATA                                           1
SLOWDATA                                           2

  3 record(s) selected.

Also the db2pd utility is provided with 2 options for the storagegroups mentioned below

• -storagepaths
• -storagegroups [<Storage Group ID>]

Let us see the information it shows

db2pd -storagegroups 1 -db sample

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:23:52 -- Date 201
4-01-20-14.13.42.566000

Storage Group 1 Configuration :
Address            SGID  Default  DataTag    Name
0x000000FA1580A0A0 1     No       0          FASTDATA

Storage Group 1 Statistics:
Address            SGID  State      Numpaths  NumDropPen
0x000000FA1580A0A0 1     0x00000000 2         0

Storage Group 1 Paths :
Address            SGID  PathID    PathState    PathName
0x000000FA1580A720 1     1024      NotInUse     C:
0x000000FA1580A1C0 1     1025      NotInUse     D:

Now if you provide the -storagepaths with the db2pd utility the below is what you get

db2pd -storagepaths -storagegroups 1 -db sample

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:25:19 -- Date 201
4-01-20-14.15.09.078000

Storage Group Configuration:
Address            SGID  Default  DataTag    Name
0x000000FA157C3060 0     Yes      0          IBMSTOGROUP
0x000000FA1580A0A0 1     No       0          FASTDATA
0x000000FA1582AE00 2     No       0          SLOWDATA

Storage Group Statistics:
Address            SGID  State      Numpaths  NumDropPen
0x000000FA157C3060 0     0x00000000 1         0
0x000000FA1580A0A0 1     0x00000000 2         0
0x000000FA1582AE00 2     0x00000000 1         0

Storage Group Paths:
Address            SGID  PathID    PathState    PathName
0x000000FA157C3180 0     0         InUse        C:
0x000000FA1580A720 1     1024      NotInUse     C:
0x000000FA1580A1C0 1     1025      NotInUse     D:
0x000000FA1582AF20 2     2048      InUse        C:

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:25:19 -- Date 201
4-01-20-14.15.09.205000

Storage Group 1 Configuration :
Address            SGID  Default  DataTag    Name
0x000000FA1580A0A0 1     No       0          FASTDATA

Storage Group 1 Statistics:
Address            SGID  State      Numpaths  NumDropPen
0x000000FA1580A0A0 1     0x00000000 2         0

Storage Group 1 Paths :
Address            SGID  PathID    PathState    PathName
0x000000FA1580A720 1     1024      NotInUse     C:
0x000000FA1580A1C0 1     1025      NotInUse     D:

You can go through the Information center through this link DB2 Storage groups

Have a Nice Day