|
View previous topic :: View next topic
|
| Author |
Message |
Auryn
New User
Joined: 11 Jan 2006
Posts: 85
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 |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
Ranges overlaps.
Can you check of use Multidimensional Clustering (MDC) here? |
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 85
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  |
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 85
Location: Lower Saxony (DE)
|
|
|
|
| no solution for this question so far? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
| No. Please talk to DBA's at your site otherwise raise a ticket with IBM. |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
I think, you have wrongly mentioned the sqlcode as -635. It should have been -636:
http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/n636.html
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.
. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
