Create partition table based upon substr of column



IBM's flagship relational database management system

Create partition table based upon substr of column

Postby aircraft74 » Thu Sep 02, 2010 10:46 pm

I have a need to create a 5 partitioned table where the limits of the partitions would be based upon the 5-7 positions of a char(10) defined column. I am limited to how the column names are defined...ie I need to keep the column defined as a char(10) and cannot split it. Is this possible?
I am running ZOS DB2 V9.

  CREATE TABLE owner.table
   (ID                            CHAR(10)      NOT NULL,
    BUSINESS                  CHAR(04)      NOT NULL,
    RECORD                    CHAR(02)      NOT NULL,
CONSTRAINT CONSTR1_PEND
     PRIMARY KEY (
       ID,
       BUSINESS)
  )
  PARTITION BY RANGE (ID(5:3))
   (PARTITION 1 ENDING AT ('099'),
    PARTITION 2 ENDING AT ('199'),
    PARTITION 3 ENDING AT ('299'),
    PARTITION 4 ENDING AT ('399'),
    PARTITION 5 ENDING AT ('499'))
 IN  POSSIBLE.STUFF;
aircraft74
 
Posts: 1
Joined: Fri May 21, 2010 6:29 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Create partition table based upon substr of column

 

Re: Create partition table based upon substr of column

Postby swd » Mon Sep 06, 2010 5:21 pm

What you are asking to do, to my knowledge cannot be done. However there is always a way around things. You could partition on a new column, lets call it ID_SUB (a substring of the ID column). When you populate the ID column, you can populate the ID_SUB column with the substring value from ID, so when you Insert into the table, your row will be inserted into the correct partition.

Have you read the documentation on partitioning for DB2 V9? You should be able to find out for sure whether you can do what you want. I am still on DB2 V8, so I haven't looked into the partitioning capabilities of V9 yet, but the manuals are freely available for you to read.
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post