Partitioned data on DB2 Z/os



IBM's flagship relational database management system

Partitioned data on DB2 Z/os

Postby p19689 » Mon Mar 18, 2013 2:38 pm

Hi, I'm trying to understand the data distribution based on limit keys. This was created before v8 (DB2 Z/os) and based on index based partitioning. The DDLextract is as below.

CREATE UNIQUE INDEX creator1.index1
ON creator1.DEPT_SALES_FCAST
(SALES_YEAR ASC,
SALES_AREA ASC,
COL3 ASC,
COL4 ASC,
COL5 ASC)

CLUSTER
(PART 1 VALUES(2014 'DA02'),
PART 2 VALUES(2014, 'DB04'),
PART 3 VALUES(2014, 'F004'),
PART 4 VALUES(2014, '0014')
PART 5 VALUES(2014, 'Z010')
PART 6 VALUES(2014))
BUFFERPOOL BP15
CLOSE YES
COPY NO
DEFINE YES;
COMMIT;

1)What DATA goes to partition 6 ? It does not contain value for sales_area. what would be the logic behind PART 6 not having value for SALES_AREA ?
2)If sales_area has value 'G004' then will it goes to the partition 3. I assumed limit key F004 can accommodate G004 values. How to work it out different alphabetic values (on column SALES_AREA) go to which partition please.
Thanks for your help in advance.
p19689
 
Posts: 65
Joined: Fri Oct 01, 2010 1:13 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Partitioned data on DB2 Z/os

Postby pmartyn » Fri Mar 29, 2013 10:37 pm

1) Partition 6 is a 'catch-all' for data not eligible to go into the other partitions.
2) Those other values you see for the other partitions are ENDING AT values for each partition or the max value for each partition

You a re correct about this being 'old school'. Since V8 partitioning is done at the table level, not the index.
pmartyn
 
Posts: 42
Joined: Thu Feb 28, 2013 7:11 pm
Has thanked: 5 times
Been thanked: 3 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post