Results 1 to 3 of 3
  1. #1
    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

  2. #2
    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/

  3. #3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •