DB2 Partitioned Tables - Life made easy - Part 2

Continuing from my first post of this series DB2 Partitioned Tables - Life made easy - Part 1

Go through that post to get an idea of the table structure created and partitions used in this exercise .

We now go through some workout examples of the following categories

  1. Attaching a partition
  2. Detaching a partition
  3. Roll in a partition
  4. Roll out a partition 
Detaching / Roll out a partition : 

Before detaching a partition we just go through the total no. of records available in the partition table 
db2 "select count (ID) from DB2INST1.PARTBLE"
 
1
-----------
       1042
 
  1 record(s) selected.

So we will now detach the DEC13 partition from our table and see the no. of records but before that lets see the no. of records available only for DEC13 partition 

db2 "select count (ID) from DB2INST1.PARTBLE where date(GDATE)<'01/01/2014'"
 
1
-----------
         65
 
  1 record(s) selected.

Fire the command below to detach DEC13 partition and save it is a new table DB2INST1.PARTBLE_DEC13
db2 "alter table DB2INST1.PARTBLE detach partition DEC13 into DB2INST1.PARTBLE_DEC13"

You can check your tables in the respective schema , you will be with a new table DB2INST1.PARTBLE_DEC13
      db2 "select count (ID) from DB2INST1.PARTBLE"                                                                                              
      1
      -----------
      977
      1 record(s) selected.

Now you can see the 65 records from the DEC13 partition have been removed

See ... Its so simple , no purging activity , no generation of logs and no more logs getting full during purging(delete) activity

I know you are running with some doubts , this post may not answer all of them but at-least to the immediate one ... HOw do I get my data back in the base table from the detached table ???? and there comes our next section attaching a partition

Attaching / Roll in a partition :

You can attach the detached DEC13 partition to the table DB2INST1.PARTBLE_DEC13 with the command below 

db2 "alter table DB2INST1.PARTBLE attach partition DEC13 starting '12/01/2013' ending '12/31/2013' from DB2INST1.PARTBLE_DEC13"

SQL3601W  The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state.  SQLSTATE=01586

The table is kept in set integrity pending state... which can be brought out with the command below
db2 "set integrity for DB2INST1.PARTBLE allow write access  immediate checked for exception in DB2INST1.PARTBLE use DB2INST1.EXCEPTION_PNR_GUESTDETAILS"

DB20000I  The SQL command completed successfully.

Now query the count of the table and you will have the data back

db2 "select count (ID) from DB2INST1.PARTBLE"
 
1
-----------
       1042
 
  1 record(s) selected.

I personally prefer partitioned tables if the table is single without any relations or referential constraints and if you can foresee data to fill in table faster

Note : Always use partitions you make to fall in different tablespace to utilize maximum performance delivered by partitioned tables and also make sure you keep your indexes in separate tablespace

In my next series of this post we will see some more options we can make use of when considering partitioned tables.

Posted by