[DB2-L] [MVS] Index Controlled Partitioning vs. Table Controlled Partitioning

Walter Janißen

[DB2-L] [MVS] Index Controlled Partitioning vs. Table Controlled Partitioning
Nick

And you also notice, that all columns of the partitioning clause are taken as candidates for limitkes, which means, other indexes, starting with COL1 (for which you have provided limitkeys) will be either NPIS or DSPIs. If one of these have to be unique, they must be NPIs.

So you should think about the number of columns, which you use for partitioning.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40198 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996

-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Nick Smith
Gesendet: Donnerstag, 15. Januar 2009 21:44
An: [login to unmask email]
Betreff: [DB2-L] [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

______________________________________________________________________

* 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

Nick Smith

Re: [MVS] Index Controlled Partitioning vs. Table Controlled Partitioning
(in response to Walter Janißen)
Thank you everybody for providing different suggestions. This group is
GREAT.

After giving the DSSIZE, I am now seeing what I would expect to see for the
LIMITKEY.

PARTITION TSNAME DBNAME LIMITKEY
---------+---------+---------+---------+---------+---------+---------
1 VLNTY DBCUST 'FM '
2 VLNTY DBCUST 'YYY'
3 VLNTY DBCUST 'ZZZ'

Thanks,
Nick

On Thu, Jan 15, 2009 at 3:59 PM, Peter Backlund <[login to unmask email]>wrote:

> 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 < 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* < 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

Adrian Collett

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

I beg to differ....

With Index-controlled partitioning the value of the last partition is NOT
enforced. Hence even if you specify 'ZZZ' as the highest value you could
actually have rows in the partition with value higher than 'ZZZ' like '999'
for example or x'FFFFFF'.

However, in V8 with TCP the limit key of the last partition IS
enforced....so when converting from ICP to TCP DB2 MUST change the limit-key
in the last partition to MAXVALUE to be consistent with the rows that
"could" be in the partition...if it did not behave in this way then the last
partition would have to be placed in a restrictive state.....

As of V8 you shouldn't need to specify DSSIZE to enforce the specified limit
for the last partition... so, I'm sure that if you just alter the limit key
of the last partition back to 'ZZZ' you'll get what you wanted without
having to specify DSSIZE.

Hope this helps.

Ciao,
Adrian

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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

Philip Sevetson

Re: [MVS] Index Controlled Partitioning vs. Table Controlled Partitioning
(in response to Adrian Collett)
Correction. With Index-controlled partitioning _and non-LARGE
Tablespaces_ (the only kind there were before DB2V7), the high-partition
limitkey is not enforced. The minute you use LARGE (and, I _suspect_
but am not sure, a non-default DSSIZE), the high-partition limitkey is
enforced.

--Phil Sevetson

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Adrian Collett
Sent: Saturday, January 17, 2009 6:25 AM
To: [login to unmask email]
Subject: Re: [DB2-L] [MVS] Index Controlled Partitioning vs. Table
Controlled Partitioning

Nick,

I beg to differ....

With Index-controlled partitioning the value of the last partition is
NOT
enforced. Hence even if you specify 'ZZZ' as the highest value you could
actually have rows in the partition with value higher than 'ZZZ' like
'999'
for example or x'FFFFFF'.

However, in V8 with TCP the limit key of the last partition IS
enforced....so when converting from ICP to TCP DB2 MUST change the
limit-key
in the last partition to MAXVALUE to be consistent with the rows that
"could" be in the partition...if it did not behave in this way then the
last
partition would have to be placed in a restrictive state.....

As of V8 you shouldn't need to specify DSSIZE to enforce the specified
limit
for the last partition... so, I'm sure that if you just alter the limit
key
of the last partition back to 'ZZZ' you'll get what you wanted without
having to specify DSSIZE.

Hope this helps.

Ciao,
Adrian

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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


=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * 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