Joined: 11 Jan 2006 Posts: 83 Location: Lower Saxony (DE)
Hi there,
another question:
We’ve got a table that is – surprise, surprise – growing and growing over the last years and won’t stop growing in future: Today more than one mio rows, max. row length more than 8K, avg. row length more than 1K. So if we want to manage the increasing number of rows we’d like to partition the table (tablespace). So far so good.
The table contains one (self-referencing) foreign key constraint (Parent_ID DECIMAL(15, 0)) and we’d like to partition via this fk row. But only one eighth contains valid data, the other seven eighths ‘contain’ NULL.
Partitioning only via this fk column like
Code:
PARTITION BY ( Parent_ID)
( PARTITION 1 ENDING AT (999999999999999)
, PARTITION 2 ENDING AT ( MAXVALUE))
results the first eight with valid fk content in the first partition (as desired) but the seven eighths with Parent_ID NULL in a very large second partition. Not bad, but because of the lots of rows, this doesn’t bring a huge benefit in second partition.
Actually it’s not a big thing to add a second partitioning column. But how to declare the ending values? I.e.
Code:
PARTITION BY ( Parent_ID, Insert_Date )
( PARTITION 1 ENDING AT (999999999999999, MAXVALUE )
, PARTITION 2 ENDING AT ( MAXVALUE, ‘2010-12-31’)
, PARTITION 3 ENDING AT ( MAXVALUE, ‘2011-12-31’)
, PARTITION 4 ENDING AT ( MAXVALUE, ‘2012-12-31’)
, PARTITION 5 ENDING AT ( MAXVALUE, ‘2013-12-31’)
, PARTITION 6 ENDING AT ( MAXVALUE, ‘2014-12-31’)
, PARTITION 7 ENDING AT ( MAXVALUE, ‘2015-12-31’)
, PARTITION 8 ENDING AT ( MAXVALUE, ‘2016-12-31’)
, PARTITION 9 ENDING AT ( MAXVALUE, ‘2017-12-31’)
, PARTITION 10 ENDING AT ( MAXVALUE, MAXVALUE ))
is not allowed, results an SQL-Code -635 / SQL-State ’56.016’ :-(
Has anybody got an idea to solve this problem?
Thanks a lot for your helpful hints
Joined: 11 Jan 2006 Posts: 83 Location: Lower Saxony (DE)
Rohit Umarjikar wrote:
Ranges overlaps.
Can you check of use Multidimensional Clustering (MDC) here?
Sorry, but I had forgotten to say I'm talking about DB2 v11 Mainframe z/OS, not v9.7 LUW.
So - as far as I could see MDC tables are not yet supported here
And, this is where you are going wrong (assuming ascending order Since you are using MAXVALUE at both places i.e. as start value and end value for date column):
Quote:
Once MAXVALUE is specified, all subsequent columns must be MAXVALUE.
I am not good at this but probably you can make your hard-coded dates in descending order till MINVALUE to see if that helps.
Or make use of MINVALUE in Parent_id column? Play with some MINVALUE/MAXVALUE in both columns and try to do ascending/descending to see if anything changes.
