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

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

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