DB2 Partitioned Tables - Life made easy - Part 3

So all we are going to see in this post is some left-outs from the last 2 posts from this series

Please refer to my previous posts
DB2 Partitioned Tables - Life made easy - Part 1 &
DB2 Partitioned Tables - Life made easy - Part 2 to catch up this series .

Nevertheless of our discussions in previous posts we have seen the partition's being attached and detached from the base partitioned table (i.e., DB2INST1.PARTBLE), You can also do this Roll-In of data from the other tables also .

ALTER TABLE DB2INST1.PARTBLE
   ATTACH PARTITION PART JAN15 
   STARTING '1/1/2015' 
   ENDING '3/31/2015' IN TBSP2
FROM TABLE DB2INST1.EMPLOYEE

Of course the signature of the data in the table (DB2INST1.EMPLOYEE in our case) should match with the data in the table you are trying to Roll-in (DB2INST1.PARTBLE).

Run the set integrity command to make the data visible

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.

OK now how do we make the exception table , In-fact i have faced some difficulties while i was creating some exception tables , but finally got some help from the documentation , Here is how you do it

CREATE TABLE DB2INST1.EXCEPTION_PARTBLE AS
      (SELECT DB2INST1.PARTBLE.*,
        CURRENT TIMESTAMP AS TIMESTAMP,
        CAST ('' AS CLOB(32K)) AS MSG
      FROM DB2INST1.PARTBLE
      ) WITH NO DATA 

And you have an exception table with the same signature of your base table with 2 extra columns

CURRENT TIMESTAMP : to capture the timestamp of the error while doing the constraint checks and
MSG : to capture relative error messages

DB2 9.1 have some restrictions where 9.7 has overcome even that , Ya in 9.7 you can even partition you indexes and place split them to different tablespaces as we did for the table , Its awesome ,isn't it

Partitoning feature is a powerful feature is getting improvised version after version , to be more precise Table partitiong is equally flexible and to end with that start DBA's life is made easy

---

Resources

Wonderful developerWorks series Table Partitioning DB2 9

And Our very own friend Information center I cannot even imagine to pass my day without coming across this