DB2 z/os v8 partitioned tables

Scott Hodgin

DB2 z/os v8 partitioned tables
I was playing around with converting my index controlled partitioned
tables to be table controlled. It appears that you can only have ONE
unique partitioned index, but many non-unique partitioned indexes. I've
looked through the Sql Reference, but can't find anything in black and
white saying this is a restriction, but when I try to create a second
unique partitioned index on a table, I get an error about multiple or
conflicting parameters.





Am I missing something?




Scott Hodgin

Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company

[login to unmask email]



_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Seibert

Re: DB2 z/os v8 partitioned tables
(in response to Scott Hodgin)
Hi Scott,



Can you post the DDL for both Create Index statements? And can you note
the partitioning columns?



Dave

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Hodgin, Scott
Sent: Wednesday, December 09, 2009 9:03 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/os v8 partitioned tables



I was playing around with converting my index controlled partitioned
tables to be table controlled. It appears that you can only have ONE
unique partitioned index, but many non-unique partitioned indexes. I've
looked through the Sql Reference, but can't find anything in black and
white saying this is a restriction, but when I try to create a second
unique partitioned index on a table, I get an error about multiple or
conflicting parameters.





Am I missing something?




Scott Hodgin

Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company

[login to unmask email]






The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.


IDUG - The Worldwide DB2 User Community! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >



_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Philip Sevetson

Re: DB2 z/os v8 partitioned tables
(in response to David Seibert)
Scott,
When IBM created Table partitioning, one unique index had to be permitted as the original, partitioning index on index-partitioned tablespaces was potentially unique. But DPSI's are not permitted to be unique. You seem to have kind of slid into the "legacy-accommodation gap," here. DB2 appears to be permitting your index to exist in the same way it permits legacy unique partitioning indexes to exist.

You might want to open an ETR and see whether your're going to encounter issues over whether the unique index is different from the partitioning scheme, if that's the case.

--Phil

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Hodgin, Scott
Sent: Wednesday, December 09, 2009 9:03 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/os v8 partitioned tables

I was playing around with converting my index controlled partitioned tables to be table controlled. It appears that you can only have ONE unique partitioned index, but many non-unique partitioned indexes. I've looked through the Sql Reference, but can't find anything in black and white saying this is a restriction, but when I try to create a second unique partitioned index on a table, I get an error about multiple or conflicting parameters.


Am I missing something?


Scott Hodgin
Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company

[login to unmask email]<mailto:[login to unmask email]>


________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Peter Vanroose

Re: DB2 z/os v8 partitioned tables
(in response to Philip Sevetson)
Only a partitioning, partitioned index can have the UNIQUE qualifier.
Suppose your table's first column is the partitioning column.
Creating a unique index on that column makes that index partitioning, hence
it is allowed to be partitioned.
Creating a unique index on the second column alone requires that index to be
non-partitioned.

*But* an index on columns 1 and 2 (in that order) is again partitioning,
hence such an index may be both unique and partitioned. Only DPSIs
(partitioned, but secondary hence non-partitioning) cannot be unique.

The following little example demonstrates the above:
CREATE TABLESPACE TESTPART NUMPARTS 3;
CREATE TABLE TEST_PARTITIONED
(K1 INT NOT NULL PRIMARY KEY,
K2 INT NOT NULL,
K3 INT NOT NULL)
IN TESTPART
PARTITION BY (K1)
(PARTITION 1 ENDING AT (10),
PARTITION 2 ENDING AT (20),
PARTITION 3 ENDING AT (30)) ;

CREATE UNIQUE INDEX I1 ON TEST_PARTITIONED(K1) PARTITIONED;
-- This index is both partitioning and partitioned, hence can be UNIQUE.
-- (It's even the primary key index, but that has nothing to do with
partitioning.)
CREATE UNIQUE INDEX I2 ON TEST_PARTITIONED(K1,K2) PARTITIONED;
-- Although only k1 is the partitioning key, an index on (k1,k2) is also
partitioning
CREATE UNIQUE INDEX I3 ON TEST_PARTITIONED(K1,K2,K3) ;
-- Although a partitioning index, i3 will not be partitioned (since not
explicitly asked for).

-- Looking in the catalog to verify the assumptions:
SELECT name, uniquerule, ibmreqd, indextype
FROM sysibm.sysindexes WHERE name IN ('I1','I2','I3')
name uniquerule ibmreqd indextype
I1 P L P
I2 U L P
I3 U N 2

SELECT ixname, partition
FROM sysibm.sysindexpart WHERE ixname IN ('I1','I2','I3')
ixname partition
I1 1
I1 2
I1 3
I2 1
I2 2
I2 3
I3 0

This is in correspondence with the list of created VSAM datasets:
TB000S.DSNDBD.DSNDB04.I1.I0001.A001
TB000S.DSNDBD.DSNDB04.I1.I0001.A002
TB000S.DSNDBD.DSNDB04.I1.I0001.A003
TB000S.DSNDBD.DSNDB04.I2.I0001.A001
TB000S.DSNDBD.DSNDB04.I2.I0001.A002
TB000S.DSNDBD.DSNDB04.I2.I0001.A003
TB000S.DSNDBD.DSNDB04.I3.I0001.A001
TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A001
TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A002
TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A003

-- Peter Vanroose,
ABIS Training & Consulting.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Peter Backlund

Re: DB2 z/os v8 partitioned tables
(in response to Peter Vanroose)
Peter,

in DB2 9 for z/OS the following two indexes are unique DPSIs

 create unique index i2 on test_partitioned(k2,k1) partitioned;

 create unique index i3 on test_partitioned(k3,k1) partitioned;

As long as the DPSI contains all columns from the partitioning key it can be unique.

There can also be multiple unique DPSI9

Best regards,

Peter

Peter Vanroose wrote: Only a partitioning, partitioned index can have the UNIQUE qualifier. Suppose your table's first column is the partitioning column. Creating a unique index on that column makes that index partitioning, hence it is allowed to be partitioned. Creating a unique index on the second column alone requires that index to be non-partitioned. *But* an index on columns 1 and 2 (in that order) is again partitioning, hence such an index may be both unique and partitioned. Only DPSIs (partitioned, but secondary hence non-partitioning) cannot be unique. The following little example demonstrates the above: CREATE TABLESPACE TESTPART NUMPARTS 3; CREATE TABLE TEST_PARTITIONED (K1 INT NOT NULL PRIMARY KEY, K2 INT NOT NULL, K3 INT NOT NULL) IN TESTPART PARTITION BY (K1) (PARTITION 1 ENDING AT (10), PARTITION 2 ENDING AT (20), PARTITION 3 ENDING AT (30)) ; CREATE UNIQUE INDEX I1 ON TEST_PARTITIONED(K1) PARTITIONED; -- This index is both partitioning and partitioned, hence can be UNIQUE. -- (It's even the primary key index, but that has nothing to do with partitioning.) CREATE UNIQUE INDEX I2 ON TEST_PARTITIONED(K1,K2) PARTITIONED; -- Although only k1 is the partitioning key, an index on (k1,k2) is also partitioning CREATE UNIQUE INDEX I3 ON TEST_PARTITIONED(K1,K2,K3) ; -- Although a partitioning index, i3 will not be partitioned (since not explicitly asked for). -- Looking in the catalog to verify the assumptions: SELECT name, uniquerule, ibmreqd, indextype FROM sysibm.sysindexes WHERE name IN ('I1','I2','I3') name uniquerule ibmreqd indextype I1 P L P I2 U L P I3 U N 2 SELECT ixname, partition FROM sysibm.sysindexpart WHERE ixname IN ('I1','I2','I3') ixname partition I1 1 I1 2 I1 3 I2 1 I2 2 I2 3 I3 0 This is in correspondence with the list of created VSAM datasets: TB000S.DSNDBD.DSNDB04.I1.I0001.A001 TB000S.DSNDBD.DSNDB04.I1.I0001.A002 TB000S.DSNDBD.DSNDB04.I1.I0001.A003 TB000S.DSNDBD.DSNDB04.I2.I0001.A001 TB000S.DSNDBD.DSNDB04.I2.I0001.A002 TB000S.DSNDBD.DSNDB04.I2.I0001.A003 TB000S.DSNDBD.DSNDB04.I3.I0001.A001 TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A001 TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A002 TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A003 -- Peter Vanroose, ABIS Training & Consulting. _____________________________________________________________________ * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal Technical atricles from world famous authors in DB2's most prestigious, peer reviewed magazine now on-line! _____________________________________________________________________ If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
-- +-------------------------------+---------------------------------+ | 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! | +-----------------------------------------------------------------+

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

Myron Miller

Re: DB2 z/os v8 partitioned tables
(in response to Peter Backlund)
So if I interpret what is being said by both Peter's, is that if I partition by one set of keys (and that key according to the documentation doesn't have to be unique), I can't have any other UNIQUE indexes on that table, period, end of discussion.

So if I have customer sales information and partition by date and region, I can't have another index that is unique by sold partno, and salesman ID.

That's interesting as I'm running V9 and I have a table that is partitioned by a generated key (address-id)that is the primary key and has another UNIQUE index on customer ID, plus some other totally different keys. And the table is clustered by the primary key. I won't argue that the table may not be that well designed. But it is running just fine with a second index on it that is no way related to the partitioning index. (and this is not the only table that has this situation, it's actually pretty common for us to have this case.)

Its not all unusual to have a unique key as the partitioning key and have other columns in the table that are totally and I do mean totally unrelated that in combination are REQUIRED to be unique in that table.

But if I'm interpreting what has been written, this situation is "now impossible and not allowed).

What about this am I not understanding? As I'm sure that there is something that I'm missing here.

Thanks

Myron




________________________________
From: Peter Backlund <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, December 10, 2009 5:27:32 AM
Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables

Peter,

in DB2 9 for z/OS the following two indexes are unique DPSIs

create unique index i2 on test_partitioned(k2,k1) partitioned;

create unique index i3 on test_partitioned(k3,k1) partitioned;

As long as the DPSI contains all columns from the partitioning key it
can be unique.

There can also be multiple unique DPSI9

Best regards,

Peter

Peter Vanroose wrote:
Only a partitioning, partitioned index can have the UNIQUE qualifier.
>Suppose your table's first column is the partitioning column.
>Creating a unique index on that column makes that index partitioning, hence
>it is allowed to be partitioned.
>Creating a unique index on the second column alone requires that index to be
>non-partitioned.
>
>*But* an index on columns 1 and 2 (in that order) is again partitioning,
>hence such an index may be both unique and partitioned. Only DPSIs
>(partitioned, but secondary hence non-partitioning) cannot be unique.
>
>The following little example demonstrates the above:
>CREATE TABLESPACE TESTPART NUMPARTS 3;
>CREATE TABLE TEST_PARTITIONED
>(K1 INT NOT NULL PRIMARY KEY,
> K2 INT NOT NULL,
> K3 INT NOT NULL)
>IN TESTPART
>PARTITION BY (K1)
>(PARTITION 1 ENDING AT (10),
> PARTITION 2 ENDING AT (20),
> PARTITION 3 ENDING AT (30)) ;
>
>CREATE UNIQUE INDEX I1 ON TEST_PARTITIONED(K1) PARTITIONED;
>-- This index is both partitioning and partitioned, hence can be UNIQUE.
>-- (It's even the primary key index, but that has nothing to do with
>partitioning.)
>CREATE UNIQUE INDEX I2 ON TEST_PARTITIONED(K1,K2) PARTITIONED;
>-- Although only k1 is the partitioning key, an index on (k1,k2) is also
>partitioning
>CREATE UNIQUE INDEX I3 ON TEST_PARTITIONED(K1,K2,K3) ;
>-- Although a partitioning index, i3 will not be partitioned (since not
>explicitly asked for).
>
>-- Looking in the catalog to verify the assumptions:
>SELECT name, uniquerule, ibmreqd, indextype
>FROM sysibm.sysindexes WHERE name IN ('I1','I2','I3')
> name uniquerule ibmreqd indextype
> I1 P L P
> I2 U L P
> I3 U N 2
>
>SELECT ixname, partition
>FROM sysibm.sysindexpart WHERE ixname IN ('I1','I2','I3')
> ixname partition
> I1 1
> I1 2
> I1 3
> I2 1
> I2 2
> I2 3
> I3 0
>
>This is in correspondence with the list of created VSAM datasets:
>TB000S.DSNDBD.DSNDB04.I1.I0001.A001
>TB000S.DSNDBD.DSNDB04.I1.I0001.A002
>TB000S.DSNDBD.DSNDB04.I1.I0001.A003
>TB000S.DSNDBD.DSNDB04.I2.I0001.A001
>TB000S.DSNDBD.DSNDB04.I2.I0001.A002
>TB000S.DSNDBD.DSNDB04.I2.I0001.A003
>TB000S.DSNDBD.DSNDB04.I3.I0001.A001
>TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A001
>TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A002
>TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A003
>
>-- Peter Vanroose,
> ABIS Training & Consulting.
>
>_____________________________________________________________________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
>_____________________________________________________________________
>
>http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
>Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
>magazine now on-line!
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
>
>

--

+-------------------------------+---------------------------------+
| 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! |
+-----------------------------------------------------------------+

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Peter Backlund

Re: DB2 z/os v8 partitioned tables
(in response to Myron Miller)
Myron,

my note was only describing DPSIs - which can be unique only if the partitioning key is part of the DPSI some way or other.

There is nothing preventing you from have any number of unique indexes, but they will have to be NPSIs (the same as old f-cking NPIs)

Peter

Myron Miller wrote:
So if I interpret what is being said by both Peter's, is that if I partition by one set of keys (and that key according to the documentation doesn't have to be unique), I can't have any other UNIQUE indexes on that table, period, end of discussion.

So if I have customer sales information and partition by date and region, I can't have another index that is unique by sold partno, and salesman ID. 

That's interesting as I'm running V9 and I have a table that is partitioned by a generated key (address-id)that is the primary key and has another UNIQUE index on customer ID, plus some other totally different keys.  And the table is clustered by the primary key.  I won't argue that the table may not be that well designed. But it is running just fine with a second index on it that is no way related to the partitioning index.  (and this is not the only table that has this situation, it's actually pretty common for us to have this case.)

Its not all unusual to have a unique key as the partitioning key and have other columns in the table that are totally and I do mean totally unrelated that in combination are REQUIRED to be unique in that table.

But if I'm interpreting what has been written, this situation is "now impossible and not allowed). 

What about this am I not understanding?  As I'm sure that there is something that I'm missing here.

Thanks

Myron


From: Peter Backlund <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, December 10, 2009 5:27:32 AM
Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables

Peter,

in DB2 9 for z/OS the following two indexes are unique DPSIs

 create unique index i2 on test_partitioned(k2,k1) partitioned;

 create unique index i3 on test_partitioned(k3,k1) partitioned;

As long as the DPSI contains all columns from the partitioning key it can be unique.

There can also be multiple unique DPSI9

Best regards,

Peter

Peter Vanroose wrote:
Only a partitioning, partitioned index can have the UNIQUE qualifier.
Suppose your table's first column is the partitioning column.
Creating a unique index on that column makes that index partitioning, hence
it is allowed to be partitioned.
Creating a unique index on the second column alone requires that index to be
non-partitioned.

*But* an index on columns 1 and 2 (in that order) is again partitioning,
hence such an index may be both unique and partitioned. Only DPSIs
(partitioned, but secondary hence non-partitioning) cannot be unique.

The following little example demonstrates the above:
CREATE TABLESPACE TESTPART NUMPARTS 3;                           
CREATE TABLE TEST_PARTITIONED                                    
(K1 INT NOT NULL PRIMARY KEY,                                    
 K2 INT NOT NULL,                                                
 K3 INT NOT NULL)                                
                
IN TESTPART                                                      
PARTITION BY (K1)                                                
(PARTITION 1 ENDING AT (10),                                     
 PARTITION 2 ENDING AT (20),                                     
 PARTITION 3 ENDING AT (30))   ;                                           
                    
CREATE UNIQUE INDEX I1 ON TEST_PARTITIONED(K1) PARTITIONED;
-- This index is both partitioning and partitioned, hence can be UNIQUE.
-- (It's even the primary key index, but that has nothing to do with
partitioning.)
CREATE UNIQUE INDEX I2 ON TEST_PARTITIONED(K1,K2) PARTITIONED;
-- Although only k1 is the partitioning key, an index on (k1,k2) is also
partitioning                                
CREATE UNIQUE INDEX I3 ON TEST_PARTITIONED(K1,K2,K3) ;
-- Although a partitioning index, i3 will not be partitioned (since not
explicitly asked
 for).

-- Looking in the catalog to verify the assumptions:
SELECT name, uniquerule, ibmreqd, indextype
FROM  sysibm.sysindexes WHERE name IN ('I1','I2','I3')
   name           uniquerule  ibmreqd indextype
   I1                  P                  L             P
   I2                  U                  L             P
   I3                  U                  N            2

SELECT ixname, partition
FROM  sysibm.sysindexpart WHERE ixname IN ('I1','I2','I3')
   ixname partition 
   I1           1
   I1           2
   I1           3
   I2           1
   I2           2
   I2           3
   I3           0

This is in correspondence with the list of created VSAM datasets:
TB000S.DSNDBD.DSNDB04.I1.I0001.A001  
TB000S.DSNDBD.DSNDB04.I1.I0001.A002  
TB000S.DSNDBD.DSNDB04.I1.I0001.A003  
TB000S.DSNDBD.DSNDB04.I2.I0001.A001       
TB000S.DSNDBD.DSNDB04.I2.I0001.A002      
 
TB000S.DSNDBD.DSNDB04.I2.I0001.A003       
TB000S.DSNDBD.DSNDB04.I3.I0001.A001       
TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A001 
TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A002 
TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A003 

--      Peter Vanroose,
        ABIS Training & Consulting.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *  http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html -  home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now
 on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

  

-- +-------------------------------+---------------------------------+ | 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! | +-----------------------------------------------------------------+

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.


-- +-------------------------------+---------------------------------+ | 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! | +-----------------------------------------------------------------+

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

Myron Miller

Re: DB2 z/os v8 partitioned tables
(in response to Peter Backlund)
Thanks Peter,
As I mentioned, I was totally confused here about the issues. I probably missed the statement that said it was about DPSI's.

Myron




________________________________
From: Peter Backlund <[login to unmask email]>
To: [login to unmask email]
Sent: Thu, December 10, 2009 1:24:15 PM
Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables

Myron,

my note was only describing DPSIs - which can be unique only if the
partitioning key is part of the DPSI some way or other.

There is nothing preventing you from have any number of unique indexes,
but they will have to be NPSIs (the same as old f-cking NPIs)

Peter

Myron Miller wrote:
>
>So if I interpret what is being said by both Peter's, is that if
>I partition by one set of keys (and that key according to the
>documentation doesn't have to be unique), I can't have any other UNIQUE
>indexes on that table, period, end of discussion.
>
>>So if I have customer sales information and partition by date and
>region, I can't have another index that is unique by sold partno, and
>salesman ID.
>
>>That's interesting as I'm running V9 and I have a table that is
>partitioned by a generated key (address-id)that is the primary key and
>has another UNIQUE index on customer ID, plus some other totally
>different keys. And the table is clustered by the primary key. I
>won't argue that the table may not be that well designed. But it is
>running just fine with a second index on it that is no way related to
>the partitioning index. (and this is not the only table that has this
>situation, it's actually pretty common for us to have this case.)
>
>>Its not all unusual to have a unique key as the partitioning key and
>have other columns in the table that are totally and I do mean totally
>unrelated that in combination are REQUIRED to be unique in that table.
>
>>But if I'm interpreting what has been written, this situation is "now
>impossible and not allowed).
>
>>What about this am I not understanding? As I'm sure that there is
>something that I'm missing here.
>
>>Thanks
>
>>Myron
>
>
>
>
________________________________
From: >Peter Backlund <[login to unmask email]>
>To: [login to unmask email]
>Sent: Thu, December
>10, 2009 5:27:32 AM
>Subject: Re: [DB2-L]
>DB2 z/os v8 partitioned tables
>
> Peter,
>
>>in DB2 9 for z/OS the following two indexes are unique DPSIs
>
>> create unique index i2 on test_partitioned(k2,k1) partitioned;
>
>> create unique index i3 on test_partitioned(k3,k1) partitioned;
>
>>As long as the DPSI contains all columns from the partitioning key it
>can be unique.
>
>>There can also be multiple unique DPSI9
>
>>Best regards,
>
>>Peter
>
>>Peter Vanroose wrote:
>
>Only a partitioning, partitioned index can have the UNIQUE qualifier.
>>Suppose your table's first column is the partitioning column.
>>Creating a unique index on that column makes that index partitioning, hence
>>it is allowed to be partitioned.
>>Creating a unique index on the second column alone requires that index to be
>>non-partitioned.
>>
>>*But* an index on columns 1 and 2 (in that order) is again partitioning,
>>hence such an index may be both unique and partitioned. Only DPSIs
>>(partitioned, but secondary hence non-partitioning) cannot be unique.
>>
>>The following little example demonstrates the above:
>>CREATE TABLESPACE TESTPART NUMPARTS 3;
>>CREATE TABLE TEST_PARTITIONED
>>(K1 INT NOT NULL PRIMARY KEY,
>> K2 INT NOT NULL,
>> K3 INT NOT NULL)
>>
>>IN TESTPART
>>PARTITION BY (K1)
>>(PARTITION 1 ENDING AT (10),
>> PARTITION 2 ENDING AT (20),
>> PARTITION 3 ENDING AT (30)) ;
>>
>>CREATE UNIQUE INDEX I1 ON TEST_PARTITIONED(K1) PARTITIONED;
>>-- This index is both partitioning and partitioned, hence can be UNIQUE.
>>-- (It's even the primary key index, but that has nothing to do with
>>partitioning.)
>>CREATE UNIQUE INDEX I2 ON TEST_PARTITIONED(K1,K2) PARTITIONED;
>>-- Although only k1 is the partitioning key, an index on (k1,k2) is also
>>partitioning
>>CREATE UNIQUE INDEX I3 ON TEST_PARTITIONED(K1,K2,K3) ;
>>-- Although a partitioning index, i3 will not be partitioned (since not
>>explicitly asked
>> for).
>>
>>-- Looking in the catalog to verify the assumptions:
>>SELECT name, uniquerule, ibmreqd, indextype
>>FROM sysibm.sysindexes WHERE name IN ('I1','I2','I3')
>> name uniquerule ibmreqd indextype
>> I1 P L P
>> I2 U L P
>> I3 U N 2
>>
>>SELECT ixname, partition
>>FROM sysibm.sysindexpart WHERE ixname IN ('I1','I2','I3')
>> ixname partition
>> I1 1
>> I1 2
>> I1 3
>> I2 1
>> I2 2
>> I2 3
>> I3 0
>>
>>This is in correspondence with the list of created VSAM datasets:
>>TB000S.DSNDBD.DSNDB04.I1.I0001.A001
>>TB000S.DSNDBD.DSNDB04.I1.I0001.A002
>>TB000S.DSNDBD.DSNDB04.I1.I0001.A003
>>TB000S.DSNDBD.DSNDB04.I2.I0001.A001
>>TB000S.DSNDBD.DSNDB04.I2.I0001.A002
>>
>>TB000S.DSNDBD.DSNDB04.I2.I0001.A003
>>TB000S.DSNDBD.DSNDB04.I3.I0001.A001
>>TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A001
>>TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A002
>>TB000S.DSNDBD.DSNDB04.TESTPART.I0001.A003
>>
>>-- Peter Vanroose,
>> ABIS Training & Consulting.
>>
>>_____________________________________________________________________
>>
>>* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
>>_____________________________________________________________________
>>
>>http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
>>Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
>>magazine now
>> on-line!
>>_____________________________________________________________________
>>
>>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
>>
>
>--
>
>+-------------------------------+---------------------------------+
>| 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!
> |
>+-----------------------------------------------------------------+
>
>________________________________

>The IDUG DB2-L Listserv is only part of your membership in IDUG.
>If you are not already an IDUG member, please
>register here.
>________________________________

>The IDUG DB2-L Listserv is only part of your membership in IDUG.
>If you are not already an IDUG member, please register here.

--

+-------------------------------+---------------------------------+
| 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! |
+-----------------------------------------------------------------+

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Onder CAGATAY

Re: DB2 z/os v8 partitioned tables
(in response to Myron Miller)

Hi Scott,

if you want to define 2 unique index in a table partitioned tablespace, i can say that's possible. We have one exemple of partitioned tablespace created with 2 unique index (defined as not partionned and NOT CLUSTER) and 1 DPSI (defined as CLUSTER and non unique, DPSI's are not permitted to be unique). But i don't know if it's possible to define 2 unique partitioned index with not cluster option. Any experience with that ?


Onder CAGATAY

HALKBANK
IT SPECIALIST (DBA)
e-mail: [login to unmask email]<mailto:[login to unmask email]>




________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Wednesday, December 09, 2009 8:34 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables

Scott,
When IBM created Table partitioning, one unique index had to be permitted as the original, partitioning index on index-partitioned tablespaces was potentially unique. But DPSI's are not permitted to be unique. You seem to have kind of slid into the "legacy-accommodation gap," here. DB2 appears to be permitting your index to exist in the same way it permits legacy unique partitioning indexes to exist.

You might want to open an ETR and see whether your're going to encounter issues over whether the unique index is different from the partitioning scheme, if that's the case.

--Phil

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Hodgin, Scott
Sent: Wednesday, December 09, 2009 9:03 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/os v8 partitioned tables

I was playing around with converting my index controlled partitioned tables to be table controlled. It appears that you can only have ONE unique partitioned index, but many non-unique partitioned indexes. I've looked through the Sql Reference, but can't find anything in black and white saying this is a restriction, but when I try to create a second unique partitioned index on a table, I get an error about multiple or conflicting parameters.


Am I missing something?


Scott Hodgin
Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company

[login to unmask email]<mailto:[login to unmask email]>


________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >


Bu elektronik posta ve ekleri sadece adreste belirtilen kisi veya kurulusun kullanimi icin gonderilmektedir. Bu
mesaj tarafiniza yanlislikla ulasirsa, lutfen gonderen kisiyi bilgilendiriniz ve mesaji sisteminizden siliniz. Mesajda
ve eklerinde yer alan bilgilerin her ne sekilde olursa olsun ucuncu kisiler ile paylasilmasi hukuki ve cezai
sorumluluk dogurabilir. T. Halk Bankasi A.S.'nin, bu mesaj ve eklerinin icerigi ve yayimi ile ilgili hicbir
sorumlulugu bulunmamaktadir.


This email and the attachments are sent to the individual or entity defined in the address field only. If you are
not the intended recipient or have received the message in error, please notify the sender and remove the
message from your system immediately. Sharing the information in the message or the attachments with the
3rd parties may cause legal rules and penalties to apply. T. Halkbankasi A.S. has no responsibility on the
submission of this message and the attachments.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Peter Vanroose

Re: DB2 z/os v8 partitioned tables
(in response to Onder CAGATAY)
Hi, Onder,

Since v8, "cluster" is no longer relevant w.r.t. partitioning or partitioned
indexes.
So you can create any number of UNIQUE, non-partitioned indexes (cluster or
not) on a partitioned table.
And you can create any number of UNIQUE, partitioned indexes (cluster or
not) <i>as long as they follow the partitioning</i>, i.e., as long as they
contain at least all the partitioning columns.

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Scott Hodgin

Re: DB2 z/os v8 partitioned tables
(in response to Peter Vanroose)
Yea, I was trying to partition _all_ my indexes on partitioned tables
(mostly for reorg performance reasons). We mostly use partitioning here
to break up our larger tables to smaller reorgable chunks.




Scott Hodgin

Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company

[login to unmask email]



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
[login to unmask email]
Sent: Thursday, December 17, 2009 3:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables





Hi Scott,



if you want to define 2 unique index in a table partitioned
tablespace, i can say that's possible. We have one exemple of
partitioned tablespace created with 2 unique index (defined as not
partionned and NOT CLUSTER) and 1 DPSI (defined as CLUSTER and non
unique, DPSI's are not permitted to be unique). But i don't know if it's
possible to define 2 unique partitioned index with not cluster option.
Any experience with that ?





Onder CAGATAY



HALKBANK

IT SPECIALIST (DBA)

e-mail: [login to unmask email]









________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson,
Phil
Sent: Wednesday, December 09, 2009 8:34 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables

Scott,

When IBM created Table partitioning, one unique index had to be
permitted as the original, partitioning index on index-partitioned
tablespaces was potentially unique. But DPSI's are not permitted to be
unique. You seem to have kind of slid into the "legacy-accommodation
gap," here. DB2 appears to be permitting your index to exist in the
same way it permits legacy unique partitioning indexes to exist.



You might want to open an ETR and see whether your're going to encounter
issues over whether the unique index is different from the partitioning
scheme, if that's the case.



--Phil



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Hodgin, Scott
Sent: Wednesday, December 09, 2009 9:03 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/os v8 partitioned tables



I was playing around with converting my index controlled partitioned
tables to be table controlled. It appears that you can only have ONE
unique partitioned index, but many non-unique partitioned indexes. I've
looked through the Sql Reference, but can't find anything in black and
white saying this is a restriction, but when I try to create a second
unique partitioned index on a table, I get an error about multiple or
conflicting parameters.





Am I missing something?




Scott Hodgin

Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company

[login to unmask email]





________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >



________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >

< http://www.halkcard.com.tr/channels/1.asp?id=314 >



*** Bu mail zararli icerige karsi, HALKBANK Antivirus ve e-posta Tarama
Sistemleri tarafindan taranmistir. ***

Bu elektronik posta ve ekleri sadece adreste belirtilen kisi veya
kurulusun kullanimi icin gonderilmektedir. Bu mesaj tarafiniza
yanlislikla ulasirsa, lutfen gonderen kisiyi bilgilendiriniz ve mesaji
sisteminizden siliniz. Mesajda ve eklerinde yer alan bilgilerin her ne
sekilde olursa olsun ucuncu kisiler ile paylasilmasi hukuki ve cezai
sorumluluk dogurabilir. T. Halk Bankasi A.S.'nin, bu mesaj ve eklerinin
icerigi ve yayimi ile ilgili hicbir sorumlulugu bulunmamaktadir.

This email and the attachments are sent to the individual or entity
defined in the address field only. If you are not the intended recipient
or have received the message in error, please notify the sender and
remove the message from your system immediately. Sharing the information
in the message or the attachments with the 3rd parties may cause legal
rules and penalties to apply. T. Halkbankasi A.S. has no responsibility
on the submission of this message and the attachments.


________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Robert Catterall

Re: DB2 z/os v8 partitioned tables
(in response to Scott Hodgin)
In case anyone is confused by the "yes you can" versus "no you can't"
concerning unique DPSIs, keep in mind that, as Peter Backlund pointed out,
the ability to create unique DPSIs is *a DB2 for z/OS V9 enhancement.*

*In a DB2 for z/OS V8* environment, a DPSI cannot be defined as unique,
regardless of whether or not it contains the table's "partition by" columns.
Try to create an index with PARTITIONED and UNIQUE specified, and you'll get
a -628 SQL code ("clauses are mutually exclusive") unless the index is a
partitioning index (meaning, it starts with the table's partition-by
columns, in the order specified on the CREATE TABLE statement).

*In a DB2 for z/OS V9* environment (*and this is true in Conversion Mode --
you don't have to be in NFM*), a non-partitioning index CAN be created with
both PARTITIONED and UNIQUE specified, as long as (and this was Peter B's
point) the index contains the table's partition-by columns (and if the
partition-by specification is multi-column, these columns do not have to be
in the partition-by order, so a table partitioned by COL1, COL2 can have a
unique DPSI on COL3, COL2, COL1, because COL1 and COL2 are the partition-by
columns).

Robert


2009/12/17 <[login to unmask email]>

>
> Hi Scott,
>
> if you want to define 2 unique index in a table partitioned tablespace,
> i can say that's possible. We have one exemple of partitioned
> tablespace created with 2 unique index (defined as not partionned and NOT
> CLUSTER) and 1 DPSI (defined as CLUSTER and non unique, DPSI's are not
> permitted to be unique). But i don't know if it's possible to define 2
> unique partitioned index with not cluster option. Any experience with that ?
>
>
>
> Onder CAGATAY
>
> HALKBANK
> IT SPECIALIST (DBA)
> e-mail: [login to unmask email]
>
>
>
>
> ------------------------------
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Sevetson,
> Phil
> *Sent:* Wednesday, December 09, 2009 8:34 PM
>
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] DB2 z/os v8 partitioned tables
>
> Scott,
>
> When IBM created Table partitioning, one unique index had to be permitted
> as the original, partitioning index on index-partitioned tablespaces was
> potentially unique. But DPSI’s are not permitted to be unique. You seem to
> have kind of slid into the “legacy-accommodation gap,” here. DB2 appears to
> be permitting your index to exist in the same way it permits legacy unique
> partitioning indexes to exist.
>
>
>
> You might want to open an ETR and see whether your’re going to encounter
> issues over whether the unique index is different from the partitioning
> scheme, if that’s the case.
>
>
>
> --Phil
>
>
> ------------------------------
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Hodgin,
> Scott
> *Sent:* Wednesday, December 09, 2009 9:03 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] DB2 z/os v8 partitioned tables
>
>
>
> I was playing around with converting my index controlled partitioned tables
> to be table controlled. It appears that you can only have ONE unique
> partitioned index, but many non-unique partitioned indexes. I’ve looked
> through the Sql Reference, but can’t find anything in black and white saying
> this is a restriction, but when I try to create a second unique partitioned
> index on a table, I get an error about multiple or conflicting parameters.
>
>
>
>
>
> Am I missing something?
>
>
>
>
> Scott Hodgin
>
> Database Administrator, MCITP
> South Carolina Farm Bureau Insurance Company
>
> [login to unmask email]
>
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
> < http://www.halkcard.com.tr/channels/1.asp?id=314 >
>
>
>
> *** Bu mail zararli icerige karsi, HALKBANK Antivirus ve e-posta Tarama
> Sistemleri tarafindan taranmistir. ***
>
> Bu elektronik posta ve ekleri sadece adreste belirtilen kisi veya kurulusun
> kullanimi icin gonderilmektedir. Bu mesaj tarafiniza yanlislikla ulasirsa,
> lutfen gonderen kisiyi bilgilendiriniz ve mesaji sisteminizden siliniz.
> Mesajda ve eklerinde yer alan bilgilerin her ne sekilde olursa olsun ucuncu
> kisiler ile paylasilmasi hukuki ve cezai sorumluluk dogurabilir. T. Halk
> Bankasi A.S.'nin, bu mesaj ve eklerinin icerigi ve yayimi ile ilgili hicbir
> sorumlulugu bulunmamaktadir.
>
> This email and the attachments are sent to the individual or entity defined
> in the address field only. If you are not the intended recipient or have
> received the message in error, please notify the sender and remove the
> message from your system immediately. Sharing the information in the message
> or the attachments with the 3rd parties may cause legal rules and penalties
> to apply. T. Halkbankasi A.S. has no responsibility on the submission of
> this message and the attachments.
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Dale Hardy

Re: DB2 z/os v8 partitioned tables
(in response to Robert Catterall)
Yes, you can have two unique partitioned indexes in DB2 V8.



We have a table partitioned on COL1, clustered on a primary key COL1,
COL2.



CREATE UNIQUE INDEX INDEX1 ON TABLEA

( COL1, COL2 )

CLUSTER

PARTITIONED

( PARTITION 01 USING STOGROUP ....



And a 2nd partitioned index defined as

CREATE UNIQUE INDEX INDEX2 ON TABLEA

( COL1, COL3, COL4, COL5

PARTITIONED

( PARTITION 01 USING STOGROUP .....



With no -628 SQL Code.



I believe the confusion is the use of the term DPSI itself. Yes, it is
true you cannot have a Unique DPSI in V8. But, you can have multiple
unique partitioned indexes as long as the lead column(s) are the same as
the ones in the PARTITION BY clause in the table definition.



So what is the correct descriptive name for INDEX2? A non-clustering
Data partitioned secondary index? NCDPSI?

I call it a DPSI even though I know that technically it isn't. It
doesn't have an intuitive catchy name. Does anybody know if there is an
official name for IND3X2 and what acronym should be?







From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Robert
Catterall
Sent: Thursday, December 17, 2009 12:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables



In case anyone is confused by the "yes you can" versus "no you can't"
concerning unique DPSIs, keep in mind that, as Peter Backlund pointed
out, the ability to create unique DPSIs is a DB2 for z/OS V9
enhancement.

In a DB2 for z/OS V8 environment, a DPSI cannot be defined as unique,
regardless of whether or not it contains the table's "partition by"
columns. Try to create an index with PARTITIONED and UNIQUE specified,
and you'll get a -628 SQL code ("clauses are mutually exclusive") unless
the index is a partitioning index (meaning, it starts with the table's
partition-by columns, in the order specified on the CREATE TABLE
statement).

In a DB2 for z/OS V9 environment (and this is true in Conversion Mode --
you don't have to be in NFM), a non-partitioning index CAN be created
with both PARTITIONED and UNIQUE specified, as long as (and this was
Peter B's point) the index contains the table's partition-by columns
(and if the partition-by specification is multi-column, these columns do
not have to be in the partition-by order, so a table partitioned by
COL1, COL2 can have a unique DPSI on COL3, COL2, COL1, because COL1 and
COL2 are the partition-by columns).

Robert


2009/12/17 <[login to unmask email]>



Hi Scott,



if you want to define 2 unique index in a table partitioned
tablespace, i can say that's possible. We have one exemple of
partitioned tablespace created with 2 unique index (defined as not
partionned and NOT CLUSTER) and 1 DPSI (defined as CLUSTER and non
unique, DPSI's are not permitted to be unique). But i don't know if it's
possible to define 2 unique partitioned index with not cluster option.
Any experience with that ?





Onder CAGATAY



HALKBANK

IT SPECIALIST (DBA)

e-mail: [login to unmask email]









________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson,
Phil
Sent: Wednesday, December 09, 2009 8:34 PM


To: [login to unmask email]

Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables

Scott,

When IBM created Table partitioning, one unique index had to be
permitted as the original, partitioning index on index-partitioned
tablespaces was potentially unique. But DPSI's are not permitted to be
unique. You seem to have kind of slid into the "legacy-accommodation
gap," here. DB2 appears to be permitting your index to exist in the
same way it permits legacy unique partitioning indexes to exist.



You might want to open an ETR and see whether your're going to encounter
issues over whether the unique index is different from the partitioning
scheme, if that's the case.



--Phil



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Hodgin, Scott
Sent: Wednesday, December 09, 2009 9:03 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/os v8 partitioned tables



I was playing around with converting my index controlled partitioned
tables to be table controlled. It appears that you can only have ONE
unique partitioned index, but many non-unique partitioned indexes. I've
looked through the Sql Reference, but can't find anything in black and
white saying this is a restriction, but when I try to create a second
unique partitioned index on a table, I get an error about multiple or
conflicting parameters.





Am I missing something?




Scott Hodgin

Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company

[login to unmask email]





________________________________

Error! Filename not specified. < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >



________________________________

Error! Filename not specified. < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >

Error! Filename not specified.
< http://www.halkcard.com.tr/channels/1.asp?id=314 >



*** Bu mail zararli icerige karsi, HALKBANK Antivirus ve e-posta Tarama
Sistemleri tarafindan taranmistir. ***

Bu elektronik posta ve ekleri sadece adreste belirtilen kisi veya
kurulusun kullanimi icin gonderilmektedir. Bu mesaj tarafiniza
yanlislikla ulasirsa, lutfen gonderen kisiyi bilgilendiriniz ve mesaji
sisteminizden siliniz. Mesajda ve eklerinde yer alan bilgilerin her ne
sekilde olursa olsun ucuncu kisiler ile paylasilmasi hukuki ve cezai
sorumluluk dogurabilir. T. Halk Bankasi A.S.'nin, bu mesaj ve eklerinin
icerigi ve yayimi ile ilgili hicbir sorumlulugu bulunmamaktadir.

This email and the attachments are sent to the individual or entity
defined in the address field only. If you are not the intended recipient
or have received the message in error, please notify the sender and
remove the message from your system immediately. Sharing the information
in the message or the attachments with the 3rd parties may cause legal
rules and penalties to apply. T. Halkbankasi A.S. has no responsibility
on the submission of this message and the attachments.



________________________________

Error! Filename not specified. < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >




--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

________________________________

< http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >



*****************************************************************
IF YOU RECEIVED THIS EMAIL IN ERROR, YOU SHOULD NOTIFY THE SENDER
BY REPLY EMAIL AND THEN DELETE IT (INCLUDING ANY ATTACHMENTS).
*****************************************************************

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Robert Catterall

Re: DB2 z/os v8 partitioned tables
(in response to Dale Hardy)
Hello, Dale.

I pointed out that specification of UNIQUE and PARTITIONED in a CREATE INDEX
statement would result in a -628 SQL code in a DB2 V8 environment "unless
the index is a partitioning index (meaning, it starts with the table's
partition-by columns, in the order specified on the CREATE TABLE
statement)."

Both of the indexes for which you provided DDL are partitioning indexes,
because in each case the key begins with the table's partition-by column (in
your case, COL1).

We don't need a new name for these indexes. They are called partitioning
indexes. If you want to get more specific, you can call them partitioned
partitioning indexes (a partitioning index need not be partitioned, though I
don't know why you'd create a partitioning index and not make it
partitioned).

Summary: in a DB2 V8 environment, you can have SEVERAL unique indexes, as
long as they are either non-partitioned or partitioned AND partitioning. In
a DB2 V8 environment, you cannot have ANY unique indexes that are
partitioned and non-partitioning (partitioned and non-partitioning = DPSI).
In a DB2 V9 environment, a partitioned index that is non-partitioning
(again, that's a DPSI) CAN be unique IF it includes the table's partition-by
columns.

I REALLY LIKE table-controlled partitioning (and DB2 V9 range-partitioned
universal tablespaces), but that DB2 advance did make discussion of
partitioned and partitioning indexes more complicated.

Robert


On Fri, Dec 18, 2009 at 10:13 AM, Hardy, Dale <[login to unmask email]> wrote:

> Yes, you can have two unique partitioned indexes in DB2 V8.
>
>
>
> We have a table partitioned on *COL1*, clustered on a primary key *COL1*,
> *COL2.*
>
>
>
> CREATE *UNIQUE* INDEX INDEX1 ON TABLEA
>
> ( *COL1*, COL2 )
>
> CLUSTER
>
> PARTITIONED
>
> ( PARTITION 01 USING STOGROUP ….
>
>
>
> And a 2nd partitioned index defined as
>
> CREATE *UNIQUE* INDEX INDEX2 ON TABLEA
>
> ( *COL1*, COL3, COL4, COL5
>
> *PARTITIONED *
>
> ( PARTITION 01 USING STOGROUP …..
>
>
>
> With no -628 SQL Code.
>
>
>
> I believe the confusion is the use of the term DPSI itself. Yes, it is
> true you cannot have a Unique DPSI in V8. But, you can have multiple unique
> partitioned indexes as long as the lead column(s) are the same as the ones
> in the PARTITION BY clause in the table definition.
>
>
>
> So what is the correct descriptive name for INDEX2? A non-clustering Data
> partitioned secondary index? NCDPSI?
>
> I call it a DPSI even though I know that technically it isn’t. It doesn’t
> have an intuitive catchy name. Does anybody know if there is an official
> name for IND3X2 and what acronym should be?
>
>
>
>
>
>
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Robert
> Catterall
> *Sent:* Thursday, December 17, 2009 12:08 PM
>
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] DB2 z/os v8 partitioned tables
>
>
>
> In case anyone is confused by the "yes you can" versus "no you can't"
> concerning unique DPSIs, keep in mind that, as Peter Backlund pointed out,
> the ability to create unique DPSIs is *a DB2 for z/OS V9 enhancement.*
>
> *In a DB2 for z/OS V8* environment, a DPSI cannot be defined as unique,
> regardless of whether or not it contains the table's "partition by" columns.
> Try to create an index with PARTITIONED and UNIQUE specified, and you'll get
> a -628 SQL code ("clauses are mutually exclusive") unless the index is a
> partitioning index (meaning, it starts with the table's partition-by
> columns, in the order specified on the CREATE TABLE statement).
>
> *In a DB2 for z/OS V9* environment (*and this is true in Conversion Mode
> -- you don't have to be in NFM*), a non-partitioning index CAN be created
> with both PARTITIONED and UNIQUE specified, as long as (and this was Peter
> B's point) the index contains the table's partition-by columns (and if the
> partition-by specification is multi-column, these columns do not have to be
> in the partition-by order, so a table partitioned by COL1, COL2 can have a
> unique DPSI on COL3, COL2, COL1, because COL1 and COL2 are the partition-by
> columns).
>
> Robert
>
>
> 2009/12/17 <[login to unmask email]>
>
>
>
> Hi Scott,
>
>
>
> if you want to define 2 unique index in a table partitioned tablespace,
> i can say that's possible. We have one exemple of partitioned
> tablespace created with 2 unique index (defined as not partionned and NOT
> CLUSTER) and 1 DPSI (defined as CLUSTER and non unique, DPSI's are not
> permitted to be unique). But i don't know if it's possible to define 2
> unique partitioned index with not cluster option. Any experience with that ?
>
>
>
>
>
>
> Onder CAGATAY
>
>
>
> HALKBANK
>
> IT SPECIALIST (DBA)
>
> e-mail: [login to unmask email]
>
>
>
>
>
>
>
>
> ------------------------------
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Sevetson,
> Phil
> *Sent:* Wednesday, December 09, 2009 8:34 PM
>
>
> *To:* [login to unmask email]
>
> *Subject:* Re: [DB2-L] DB2 z/os v8 partitioned tables
>
> Scott,
>
> When IBM created Table partitioning, one unique index had to be permitted
> as the original, partitioning index on index-partitioned tablespaces was
> potentially unique. But DPSI’s are not permitted to be unique. You seem to
> have kind of slid into the “legacy-accommodation gap,” here. DB2 appears to
> be permitting your index to exist in the same way it permits legacy unique
> partitioning indexes to exist.
>
>
>
> You might want to open an ETR and see whether your’re going to encounter
> issues over whether the unique index is different from the partitioning
> scheme, if that’s the case.
>
>
>
> --Phil
>
>
> ------------------------------
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Hodgin,
> Scott
> *Sent:* Wednesday, December 09, 2009 9:03 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] DB2 z/os v8 partitioned tables
>
>
>
> I was playing around with converting my index controlled partitioned tables
> to be table controlled. It appears that you can only have ONE unique
> partitioned index, but many non-unique partitioned indexes. I’ve looked
> through the Sql Reference, but can’t find anything in black and white saying
> this is a restriction, but when I try to create a second unique partitioned
> index on a table, I get an error about multiple or conflicting parameters.
>
>
>
>
>
> Am I missing something?
>
>
>
>
> Scott Hodgin
>
> Database Administrator, MCITP
> South Carolina Farm Bureau Insurance Company
>
> [login to unmask email]
>
>
>
>
> ------------------------------
>
> *Error! Filename not specified.* < http://www.idug.org >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
>
> ------------------------------
>
> *Error! Filename not specified.* < http://www.idug.org >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
> *Error! Filename not specified.* < http://www.halkcard.com.tr/channels/1.asp?id=314 >
>
>
>
> *** Bu mail zararli icerige karsi, HALKBANK Antivirus ve e-posta Tarama
> Sistemleri tarafindan taranmistir. ***
>
> Bu elektronik posta ve ekleri sadece adreste belirtilen kisi veya kurulusun
> kullanimi icin gonderilmektedir. Bu mesaj tarafiniza yanlislikla ulasirsa,
> lutfen gonderen kisiyi bilgilendiriniz ve mesaji sisteminizden siliniz.
> Mesajda ve eklerinde yer alan bilgilerin her ne sekilde olursa olsun ucuncu
> kisiler ile paylasilmasi hukuki ve cezai sorumluluk dogurabilir. T. Halk
> Bankasi A.S.'nin, bu mesaj ve eklerinin icerigi ve yayimi ile ilgili hicbir
> sorumlulugu bulunmamaktadir.
>
> This email and the attachments are sent to the individual or entity defined
> in the address field only. If you are not the intended recipient or have
> received the message in error, please notify the sender and remove the
> message from your system immediately. Sharing the information in the message
> or the attachments with the 3rd parties may cause legal rules and penalties
> to apply. T. Halkbankasi A.S. has no responsibility on the submission of
> this message and the attachments.
>
>
> ------------------------------
>
> *Error! Filename not specified.* < http://www.idug.org >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
>
>
>
> --
> Robert Catterall
> Catterall Consulting
> www.catterallconsulting.com
> ------------------------------
>
> [image: Image removed by sender. IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
> ***************************************************************** IF YOU
> RECEIVED THIS EMAIL IN ERROR, YOU SHOULD NOTIFY THE SENDER BY REPLY EMAIL
> AND THEN DELETE IT (INCLUDING ANY ATTACHMENTS).
> *****************************************************************
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: DB2 z/os v8 partitioned tables
(in response to Robert Catterall)
Hi Dale



For table controlled partitioning, they are both PARTITIONING indexes!!



In V8 (and beyond) a PARTITIONING index is one THAT SHARES the partitioning
key of the underlying table



A PARTITIONED index is one that just happens to be in partitions



A DPSI is an index that is PARTITIONED but does NOT share the tables
partitioning key



This is very different from V7 (and before) where a PARTITIONING index was
the index that CONTROLLED the partitioning!



Now, with table controlled partitioning, you can have:



NON-PARTITIONED NON-PARTITIONING indexes (aka NPIs or NPSIs)

PARTITIONED NON-PARTITIONING indexes (aka DPSIs)

PARTITIONED PARTITIONING indexes (such as the two you have listed below)

And

NON-PARTITIONED PARTITIONING indexes (though I am not sure I see why you
would want one of these)



Phil G

Grainger Database Solutions Ltd



_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Hardy, Dale
Sent: 18 December 2009 15:13
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables



Yes, you can have two unique partitioned indexes in DB2 V8.



We have a table partitioned on COL1, clustered on a primary key COL1,
COL2.



CREATE UNIQUE INDEX INDEX1 ON TABLEA

( COL1, COL2 )

CLUSTER

PARTITIONED

( PARTITION 01 USING STOGROUP ..



And a 2nd partitioned index defined as

CREATE UNIQUE INDEX INDEX2 ON TABLEA

( COL1, COL3, COL4, COL5

PARTITIONED

( PARTITION 01 USING STOGROUP ...



With no -628 SQL Code.



I believe the confusion is the use of the term DPSI itself. Yes, it is
true you cannot have a Unique DPSI in V8. But, you can have multiple unique
partitioned indexes as long as the lead column(s) are the same as the ones
in the PARTITION BY clause in the table definition.



So what is the correct descriptive name for INDEX2? A non-clustering Data
partitioned secondary index? NCDPSI?

I call it a DPSI even though I know that technically it isn't. It doesn't
have an intuitive catchy name. Does anybody know if there is an official
name for IND3X2 and what acronym should be?







From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Robert Catterall
Sent: Thursday, December 17, 2009 12:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables



In case anyone is confused by the "yes you can" versus "no you can't"
concerning unique DPSIs, keep in mind that, as Peter Backlund pointed out,
the ability to create unique DPSIs is a DB2 for z/OS V9 enhancement.

In a DB2 for z/OS V8 environment, a DPSI cannot be defined as unique,
regardless of whether or not it contains the table's "partition by" columns.
Try to create an index with PARTITIONED and UNIQUE specified, and you'll get
a -628 SQL code ("clauses are mutually exclusive") unless the index is a
partitioning index (meaning, it starts with the table's partition-by
columns, in the order specified on the CREATE TABLE statement).

In a DB2 for z/OS V9 environment (and this is true in Conversion Mode -- you
don't have to be in NFM), a non-partitioning index CAN be created with both
PARTITIONED and UNIQUE specified, as long as (and this was Peter B's point)
the index contains the table's partition-by columns (and if the partition-by
specification is multi-column, these columns do not have to be in the
partition-by order, so a table partitioned by COL1, COL2 can have a unique
DPSI on COL3, COL2, COL1, because COL1 and COL2 are the partition-by
columns).

Robert


2009/12/17 <[login to unmask email]>



Hi Scott,



if you want to define 2 unique index in a table partitioned tablespace,
i can say that's possible. We have one exemple of partitioned tablespace
created with 2 unique index (defined as not partionned and NOT CLUSTER) and
1 DPSI (defined as CLUSTER and non unique, DPSI's are not permitted to be
unique). But i don't know if it's possible to define 2 unique partitioned
index with not cluster option. Any experience with that ?





Onder CAGATAY



HALKBANK

IT SPECIALIST (DBA)

e-mail: [login to unmask email]









_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Wednesday, December 09, 2009 8:34 PM


To: [login to unmask email]

Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables

Scott,

When IBM created Table partitioning, one unique index had to be permitted as
the original, partitioning index on index-partitioned tablespaces was
potentially unique. But DPSI's are not permitted to be unique. You seem to
have kind of slid into the "legacy-accommodation gap," here. DB2 appears to
be permitting your index to exist in the same way it permits legacy unique
partitioning indexes to exist.



You might want to open an ETR and see whether your're going to encounter
issues over whether the unique index is different from the partitioning
scheme, if that's the case.



--Phil



_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Hodgin, Scott
Sent: Wednesday, December 09, 2009 9:03 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/os v8 partitioned tables



I was playing around with converting my index controlled partitioned tables
to be table controlled. It appears that you can only have ONE unique
partitioned index, but many non-unique partitioned indexes. I've looked
through the Sql Reference, but can't find anything in black and white saying
this is a restriction, but when I try to create a second unique partitioned
index on a table, I get an error about multiple or conflicting parameters.





Am I missing something?




Scott Hodgin

Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company

[login to unmask email]





_____

< http://www.idug.org > Error! Filename not specified.

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >



_____

< http://www.idug.org > Error! Filename not specified.

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >



< http://www.halkcard.com.tr/channels/1.asp?id=314 > Error! Filename not
specified.




*** Bu mail zararli icerige karsi, HALKBANK Antivirus ve e-posta Tarama
Sistemleri tarafindan taranmistir. ***

Bu elektronik posta ve ekleri sadece adreste belirtilen kisi veya kurulusun
kullanimi icin gonderilmektedir. Bu mesaj tarafiniza yanlislikla ulasirsa,
lutfen gonderen kisiyi bilgilendiriniz ve mesaji sisteminizden siliniz.
Mesajda ve eklerinde yer alan bilgilerin her ne sekilde olursa olsun ucuncu
kisiler ile paylasilmasi hukuki ve cezai sorumluluk dogurabilir. T. Halk
Bankasi A.S.'nin, bu mesaj ve eklerinin icerigi ve yayimi ile ilgili hicbir
sorumlulugu bulunmamaktadir.

This email and the attachments are sent to the individual or entity defined
in the address field only. If you are not the intended recipient or have
received the message in error, please notify the sender and remove the
message from your system immediately. Sharing the information in the message
or the attachments with the 3rd parties may cause legal rules and penalties
to apply. T. Halkbankasi A.S. has no responsibility on the submission of
this message and the attachments.



_____

< http://www.idug.org > Error! Filename not specified.

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >




--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____

< http://www.idug.org > Image removed by sender. IDUG - The Worldwide DB2
User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >

***************************************************************** IF YOU
RECEIVED THIS EMAIL IN ERROR, YOU SHOULD NOTIFY THE SENDER BY REPLY EMAIL
AND THEN DELETE IT (INCLUDING ANY ATTACHMENTS).
*****************************************************************

_____


< http://www.idug.org/db2-north-america-conference/index.html > IDUG - The
Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Peter Backlund

Re: DB2 z/os v8 partitioned tables
(in response to Phil Grainger)
Both indexes are "partitioning partitioned" indexes - indicated by indextype 'P' in sysibm.sysindexes
    partitioning as they start with the partitioning key
    partitioned based on the attribute partitioned

Both these indexes can be unique.

At most one index can have the attribute cluster

Peter

On 2009-12-18 16:13, Hardy, Dale wrote:

Yes, you can have two unique partitioned indexes in DB2 V8. 

 

We have a table partitioned on COL1, clustered on  a primary key  COL1, COL2.

 

  CREATE UNIQUE INDEX INDEX1 ON TABLEA

          ( COL1, COL2   )                        

  CLUSTER                                               

          PARTITIONED                           

        ( PARTITION 01 USING STOGROUP ….

 

And  a 2nd partitioned index defined as

CREATE UNIQUE INDEX INDEX2 ON TABLEA

      ( COL1, COL3, COL4, COL5

         PARTITIONED                    

       ( PARTITION 01 USING STOGROUP …..        

 

With no -628 SQL Code.                    

 

I believe the confusion is the use of the term DPSI itself.   Yes, it is true you cannot have a Unique DPSI in V8.  But, you can have multiple unique partitioned indexes as long as the lead column(s) are the same as the ones in the PARTITION BY  clause in the table definition.

 

So what is the correct descriptive name for INDEX2?  A non-clustering Data partitioned secondary index? NCDPSI? 

I call it a DPSI even though I know that technically it isn’t.   It doesn’t have an intuitive catchy name.  Does anybody know if there is an official name for IND3X2 and what acronym should be?

 

 

 

From: IDUG DB2-L [mailto:[login to unmask email]] On Behalf Of Robert Catterall
Sent: Thursday, December 17, 2009 12:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables

 

In case anyone is confused by the "yes you can" versus "no you can't" concerning unique DPSIs, keep in mind that, as Peter Backlund pointed out, the ability to create unique DPSIs is a DB2 for z/OS V9 enhancement.

In a DB2 for z/OS V8 environment, a DPSI cannot be defined as unique, regardless of whether or not it contains the table's "partition by" columns. Try to create an index with PARTITIONED and UNIQUE specified, and you'll get a -628 SQL code ("clauses are mutually exclusive") unless the index is a partitioning index (meaning, it starts with the table's partition-by columns, in the order specified on the CREATE TABLE statement).

In a DB2 for z/OS V9 environment (and this is true in Conversion Mode -- you don't have to be in NFM), a non-partitioning index CAN be created with both PARTITIONED and UNIQUE specified, as long as (and this was Peter B's point) the index contains the table's partition-by columns (and if the partition-by specification is multi-column, these columns do not have to be in the partition-by order, so a table partitioned by COL1, COL2 can have a unique DPSI on COL3, COL2, COL1, because COL1 and COL2 are the partition-by columns).

Robert
 

2009/12/17 <[login to unmask email]>

 

     Hi Scott,

 

    if you want to define 2 unique index in a table partitioned tablespace, i can say  that's possible.  We have one exemple of partitioned tablespace created with 2 unique index (defined as not partionned and NOT CLUSTER) and 1 DPSI (defined as CLUSTER and non unique, DPSI's are not permitted to be unique). But i don't know if it's possible to define 2 unique partitioned index with not cluster option. Any experience with that ?     

 

  

Onder CAGATAY

 

HALKBANK

IT SPECIALIST (DBA)

e-mail: [login to unmask email] 

 

 

 

 


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Wednesday, December 09, 2009 8:34 PM


To: [login to unmask email]

Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables

Scott,

When IBM created Table partitioning, one unique index had to be permitted as the original, partitioning index on index-partitioned tablespaces was potentially unique.  But DPSI’s are not permitted to be unique.  You seem to have kind of slid into the “legacy-accommodation gap,” here.  DB2 appears to be permitting your index to exist in the same way it permits legacy unique partitioning indexes to exist.

 

You might want to open an ETR and see whether your’re going to encounter issues over whether the unique index is different from the partitioning scheme, if that’s the case.

 

--Phil

 


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Hodgin, Scott
Sent: Wednesday, December 09, 2009 9:03 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/os v8 partitioned tables

 

I was playing around with converting my index controlled partitioned tables to be table controlled.  It appears that you can only have ONE unique partitioned index, but many non-unique partitioned indexes.  I’ve looked through the Sql Reference, but can’t find anything in black and white saying this is a restriction, but when I try to create a second unique partitioned index on a table, I get an error about multiple or conflicting parameters.

 

 

Am I missing something?

 


Scott Hodgin

Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company

[login to unmask email]

 

 

Error! Filename not specified.

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

 

Error! Filename not specified.

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

Error! Filename not specified.

 

*** Bu mail zararli icerige karsi, HALKBANK Antivirus ve e-posta Tarama Sistemleri tarafindan taranmistir. ***

Bu elektronik posta ve ekleri sadece adreste belirtilen kisi veya kurulusun kullanimi icin gonderilmektedir. Bu mesaj tarafiniza yanlislikla ulasirsa, lutfen gonderen kisiyi bilgilendiriniz ve mesaji sisteminizden siliniz. Mesajda ve eklerinde yer alan bilgilerin her ne sekilde olursa olsun ucuncu kisiler ile paylasilmasi hukuki ve cezai sorumluluk dogurabilir. T. Halk Bankasi A.S.'nin, bu mesaj ve eklerinin icerigi ve yayimi ile ilgili hicbir sorumlulugu bulunmamaktadir.

This email and the attachments are sent to the individual or entity defined in the address field only. If you are not the intended recipient or have received the message in error, please notify the sender and remove the message from your system immediately. Sharing the information in the message or the attachments with the 3rd parties may cause legal rules and penalties to apply. T. Halkbankasi A.S. has no responsibility on the submission of this message and the attachments.

 

Error! Filename not specified.

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

***************************************************************** IF YOU RECEIVED THIS EMAIL IN ERROR, YOU SHOULD NOTIFY THE SENDER BY REPLY EMAIL AND THEN DELETE IT (INCLUDING ANY ATTACHMENTS). *****************************************************************

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.716 / Virus Database: 270.14.113/2573 - Release Date: 12/18/09 08:35:00
-- +-------------------------------+---------------------------------+ | 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! | +-----------------------------------------------------------------+

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.