[MVS] Index Controlled Partitioning vs. Table Controlled Partitioning

Nick Smith

[MVS] Index Controlled Partitioning vs. 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


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Suresh Sane

Re: [MVS] Index Controlled Partitioning vs. Table Controlled Partitioning
(in response to Nick Smith)

Nick,

MAXVALUE is required on the last partition (or MINVALUE if descending).

I think this is logical carryover of the fact that limitkey on the last part is now checked (as of V* I think?) - earlier all values greater than the (n-1)th limitkey would end up in last part.

Thx
Suresh> Date: Thu, 15 Jan 2009 20:43:46 +0000> From: [login to unmask email]> Subject: [DB2-L] [MVS] Index Controlled Partitioning vs. Table Controlled Partitioning> To: [login to unmask email]> > 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> > > ______________________________________________________________________> > * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *> ______________________________________________________________________> > > > IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
_________________________________________________________________
Windows Live™ Hotmail®: Chat. Store. Share. Do more with mail.
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter_howitworks_012009

______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Peter Backlund

Re: [MVS] Index Controlled Partitioning vs. Table Controlled Partitioning
(in response to Suresh Sane)
Because the limit value which you had specified for your last partition is immaterial and treated as high value,
unless your tablespace is Large or has DSSIZE

If you want to add a partition now , you have to alter the high value to a lower value

Peter

Nick Smith wrote: 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 ______________________________________________________________________ * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events * ______________________________________________________________________ IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.10.8/1896 - Release Date: 2009-01-15 19:10
-- Attend IDUG 2009, North America -- 11-15 May in Denver, Colorado Attend IDUG 2009, Europe -- 5- 9 October in Rome, Italy Learn more at http://www.idug.org +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Rasundavagen 94 | Country Code (Sweden): 46 | | S-169 57 SOLNA | Skype: BacklundDB2 | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA

IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.

Steen Rasmussen

Re: [MVS] Index Controlled Partitioning vs. Table Controlled Partitioning
(in response to Peter Backlund)
Suresh is correct about DB2 checking the MAX VALUE (I believe it was DB2
V7) if your tablespace is defined as LARGE. If not defined as LARGE, the
high partition could still hold values higher than what was specified by
the LIMITKEY.

Once on DB2 V8 - Table Controlled Partitioning DOES honor the high
limitkey.



Steen Rasmussen
CA

Sr Engineering Services Architect



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Suresh Sane
Sent: Thursday, January 15, 2009 4:34 PM
To: [login to unmask email]
Subject: Re: [DB2-L] [MVS] Index Controlled Partitioning vs. Table
Controlled Partitioning



Nick,

MAXVALUE is required on the last partition (or MINVALUE if descending).


I think this is logical carryover of the fact that limitkey on the last
part is now checked (as of V* I think?) - earlier all values greater
than the (n-1)th limitkey would end up in last part.

Thx
Suresh

> Date: Thu, 15 Jan 2009 20:43:46 +0000
> From: [login to unmask email]
> Subject: [DB2-L] [MVS] Index Controlled Partitioning vs. Table
Controlled Partitioning
> To: [login to unmask email]
>
> 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
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
> IDUG.org was recently updated requiring members to use a new password.
You should have gotten an e-mail with the temporary password assigned to
your account. Please log in and update your member profile. If you are
not already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html



________________________________

Windows Live(tm) Hotmail(r): Chat. Store. Share. Do more with mail. See
how it works.
<http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter
_howitworks_012009>



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html