Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

how to specifiy partition endings when 1st col is nullable?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Auryn

New User


Joined: 11 Jan 2006
Posts: 40
Location: Lower Saxony (DE)

PostPosted: Thu Apr 14, 2016 2:32 pm    Post subject: how to specifiy partition endings when 1st col is nullable?
Reply with quote

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
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Sat Apr 16, 2016 12:14 am    Post subject:
Reply with quote

Ranges overlaps.
Can you check of use Multidimensional Clustering (MDC) here?
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 40
Location: Lower Saxony (DE)

PostPosted: Wed Apr 20, 2016 6:55 pm    Post subject:
Reply with quote

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 icon_confused.gif
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 40
Location: Lower Saxony (DE)

PostPosted: Thu Oct 20, 2016 4:13 pm    Post subject:
Reply with quote

no solution for this question so far?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Oct 27, 2016 12:12 am    Post subject:
Reply with quote

No. Please talk to DBA's at your site otherwise raise a ticket with IBM.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Oct 27, 2016 1:20 am    Post subject: Reply to: how to specifiy partition endings when 1st col is nullable?
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing extra partition from PBG TS Jimy Carol DB2 3 Mon Jul 15, 2013 11:59 pm
No new posts how to handle nullable columns ( may ... cmsmoon DB2 18 Wed Jun 05, 2013 3:42 pm
No new posts Error while Creating Partition Table Furor DB2 2 Thu Oct 18, 2012 9:36 am
No new posts Getting first record of a particular ... Paresh0459 IMS DB/DC 4 Tue Dec 20, 2011 10:36 pm
No new posts Partition contains no data, but takes... dejunzhu DB2 4 Mon Jun 27, 2011 12:19 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us