If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Table Controlled Partitioning

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jun 2008
Posts: 16
Table Controlled Partitioning

Hello Listers:

I have just created or rather changed an Index Controlled Partitioning to Table Controlled Partitioning by dropping partitioning index. And I expected the limit keys to be as below.

Code:
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+-
TSNAME DBNAME LIMITKEY
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+-
TABLESPC DATABASE 'FM '
TABLESPC DATABASE 'ZZZ'


For, my CREATE INDEX statement was like this before I decided to drop, in order to change PARTIONING TO Table Controlled...

Code:
CREATE TYPE 2 UNIQUE INDEX TST.TESTINDX
ON TST.TEST_TABLE

(COL_FIRST ASC
,COL_SECOND ASC
.
.
.
,COL_LAST ASC )
CLUSTER
(PARTITION 1 ENDING AT ('FM ')
USING STOGROUP SYSDEFLT
PRIQTY 720
SECQTY 720
ERASE NO
FREEPAGE 0
PCTFREE 15
,PARTITION 2 ENDING AT ('ZZZ')
USING STOGROUP SYSDEFLT
PRIQTY 720
SECQTY 720
ERASE NO
FREEPAGE 0
.
.
.


After dropping this INDEX I received a warning 20272 CODE (this is expected) saying the tablespace has been converted to Table-Controlled Partitioning instead of Index-Controlled Partitioning.

Now, I try to look at the limit keys in the catalog table here is what I see.

Code:
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+-
TSNAME DBNAME LIMITKEY
---------+---------+---------+---------+---------+---------+---------+--
---------+---------+---------+---------+---------+---------+---------+-
TABLESPC DATABASE 'FM '
TABLESPC DATABASE MAXVALUE,MAXVALUE,M


Does anyone have an idea why is it MAXVALUE,MAXVALUE... instead of 'ZZZ'???

DB2 V8 NFM - z/OS

Thanks,
Nick Knight
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,126
Let's call this a "bug", or actually a "documented feature".
According to the documentation, when you drop the partitioning index, the ending value for the last partition changes to "MAXVALUE", which is effectively an x'FFFFFF'.
Actually, this fixes a mismatch in earlier versions of DB2, since the ending value of the last partition was not enforcing; now it is, so "MAXVALUE" is actually the correct representation of the old mismatching behaviour.
You can always restore the original end value with
Code:
ALTER TABLE TST.TEST_TABLE ALTER PARTITION 2 ENDING AT ('ZZZ')
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jun 2008
Posts: 16
Pete,

It worked like a gem! I talked a few other ppl and learned that it has to be a LARGE dataset if you want to change the LIMITKEY value (perhaps, I misunderstood). Now, I try your way for a non-large dataset and yes, it works... Thanks much.

However, I am not sure if I agree with you when you say it is a "documented feature". But, I agree with the former term "a bug". To me, it looks like an "extra step/effort" to restore the LIMITKEY value.

Nick
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On