IBM Mainframe Forum

by **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.

Select all

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;

by **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.

IBM Mainframe Forum

Create partition table based upon substr of column

Create partition table based upon substr of column

Re: Create partition table based upon substr of column