DB2 Partitioned Tables - Life made easy - Part 1

Why partitioned tables ???

Well this cannot be said in a single statement

• It makes the DBA's life lot more easier ?

Sure it does , If you are a DBA and you opted partitioned table , you need not worry of the purging activity . If the data is growing beyond your foresight , decided to keep only 3 or 6 or 9 months of active data in table and you have partitioned your table , simply detach the old partition and you are done .

• Query will be processed faster than ever ? 

Obviously , Say you have a table partitioned for every 3 months and same table without any partitions , Now both the tables have 30 million records , in the latter db2 has to scan all the table to get your result whereas in the former db2 has an advantage to take of every 3 months split , Once you update the statistics db2 goes directly to that respective 3 months slot and collects the data . So how large the table , its only 3 months data for db2 .

• Faster Roll in / Roll out of data ?

Yes , Rolling-in & Rolling-out of data is very quick , happens very fast 

• Index created for table in SMS tablespace can be placed in separate tablespace ? 

Yes , if you have partitioned your table you can place your index in different tablespace even though you create table in SMS tablespace .

Now let us go through some basic stuff here 

How the partitioned table looks

Here the table PARTBLE is a partitioned

CREATE TABLE DB2INST1.PARTBLE
(
ID INTEGER NOT NULL PRIMARY KEY ,
NAME VARCHAR (50) NOT NULL,
G_ID INTEGER,
MAILID VARCHAR (50),
MOBILENO VARCHAR (25),
PASSWORD VARCHAR (50),
GDATE DATE,
CREATEDDATE TIMESTAMP)
INDEX IN TBS3
PARTITION BY RANGE (GDATE)
 (PART DEC13 STARTING '12/1/2013' ENDING '12/31/2013' IN TBSP1,
PART JAN14 STARTING '1/1/2014' ENDING '3/31/2014' IN TBSP2,
PART APR14 STARTING '4/1/2014' ENDING '6/30/2014' IN TBSP1,
PART JUL14 STARTING '7/1/2014' ENDING '9/30/2014' IN TBSP2,
PART OCT14 STARTING '10/1/2014' ENDING '12/31/2014' IN TBSP1,
)

Partitioned till 2014 every 3 months

DEC13,JAN14,APR14..... are Partition names

So now describe data partitons of table we created now

db2 "describe data partitions of table DB2INST1.PARTBLE show detail"

PartitionId Inclusive (y/n)                   Inclusive (y/n)
              Low Value                         High Value
----------- - ------------------------------- - -------------------------------
          0 Y '2013-12-01'                    Y '2013-12-31'
          1 Y '2014-01-01'                    Y '2014-03-31'
          2 Y '2014-04-01'                    Y '2014-06-30'
          3 Y '2014-07-01'                    Y '2014-09-30'
          4 Y '2014-10-01'                    Y '2014-12-31'
 
 
PartitionId PartitionName                   TableSpId   PartObjId   LongTblSpId AccessMode
                                                                                  Status
----------- ------------------------------- ----------- ----------- ----------- - ------
          0 DEC13                                     9           6           9 N A
          1 JAN14                                     4          74           4 F
          2 APR14                                     9           7           9 F
          3 JUL14                                     4          75           4 F
          4 OCT14                                     9           8           9 F

We would be going into some examples and workouts in my next post ...i.e., part 2 of this series .