DPSI question on z/os V8

Deepak Goyal

DPSI question on z/os V8
Hi All,

We have a very huge 229 partitioned table (around 600 million rows) in our
shop that have 5 NPIS defined on them. It's really very difficult to maintain the
table especially reorg. Doing the reorg on NPI's takes lot of time so we are
thinking of replacing the NPI's to DPSI. But would like to know the pros/cons
for that.

Appreciate your response.

Thanks

______________________________________________________________________

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



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: DPSI question on z/os V8
(in response to Deepak Goyal)
Pros:

*
DPSIs are partitioned so tend to be easier to manage (smaller datasets)
*
Under some circumstances you can get page range screening (if your SQL contains the partitioning key columns)
*
Partition level on-line reorgs do not have a BUILD" phase


Cons:

*
DPSIs cannot be unique
*
Because DPSIs are partitioned they may compromise your SQL performance (keys are now not "in order of the DPSI key" but are "in order of the DPSI key WITHIN the order ofthe partitoning key")

I am sure they may be others, but these are the key ones that come to mind at this time of night



Phil Grainger
CA


________________________________

From: DB2 Data Base Discussion List on behalf of Deepak Goyal
Sent: Fri 12/12/2008 21:05
To: [login to unmask email]
Subject: [DB2-L] DPSI question on z/os V8



Hi All,

We have a very huge 229 partitioned table (around 600 million rows) in our
shop that have 5 NPIS defined on them. It's really very difficult to maintain the
table especially reorg. Doing the reorg on NPI's takes lot of time so we are
thinking of replacing the NPI's to DPSI. But would like to know the pros/cons
for that.

Appreciate your response.

Thanks

______________________________________________________________________

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



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms




______________________________________________________________________

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



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Michael Kalena

Re: DPSI question on z/os V8
(in response to Phil Grainger)
One other thought that came up at the IBM Conference.

Consider if your queries will be able to limit the number of parts being
referenced. Which in turn means it can limit the number of DPSI
datasets to look at.

If the query is getting data from any/all partition and you have 229
DPSI datasets (229 partitions), the Optimizer would have to consider the
cost of looking at them all. This may cause it to use another index or
hurt performance vs. the NPI's performance.

This made sense when we heard it but we were thinking of all the good
benefits to maintenance/recovery (get rid of the 57 pack NPI) and now
are looking at all the queries.

Hope this helps.

Michael Kalena
973-793-2133
[login to unmask email]

DB2 Info Page at hBSC
(http://whsysops1.is.bear.com/db2/)


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Grainger, Phil
Sent: Friday, December 12, 2008 4:41 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DPSI question on z/os V8

Pros:

*
DPSIs are partitioned so tend to be easier to manage (smaller
datasets)
*
Under some circumstances you can get page range screening (if
your SQL contains the partitioning key columns)
*
Partition level on-line reorgs do not have a BUILD" phase


Cons:

*
DPSIs cannot be unique
*
Because DPSIs are partitioned they may compromise your SQL
performance (keys are now not "in order of the DPSI key" but are "in
order of the DPSI key WITHIN the order ofthe partitoning key")

I am sure they may be others, but these are the key ones that come to
mind at this time of night



Phil Grainger
CA


________________________________

From: DB2 Data Base Discussion List on behalf of Deepak Goyal
Sent: Fri 12/12/2008 21:05
To: [login to unmask email]
Subject: [DB2-L] DPSI question on z/os V8



Hi All,

We have a very huge 229 partitioned table (around 600 million rows) in
our
shop that have 5 NPIS defined on them. It's really very difficult to
maintain the
table especially reorg. Doing the reorg on NPI's takes lot of time so we
are
thinking of replacing the NPI's to DPSI. But would like to know the
pros/cons
for that.

Appreciate your response.

Thanks

______________________________________________________________________

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



The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms




______________________________________________________________________

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



The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms
Generally, this communication is for informational purposes only
and it is not intended as an offer or solicitation for the purchase
or sale of any financial instrument or as an official confirmation
of any transaction. In the event you are receiving the offering
materials attached below related to your interest in hedge funds or
private equity, this communication may be intended as an offer or
solicitation for the purchase or sale of such fund(s). All market
prices, data and other information are not warranted as to
completeness or accuracy and are subject to change without notice.
Any comments or statements made herein do not necessarily reflect
those of JPMorgan Chase & Co., its subsidiaries and affiliates.

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.
Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to UK legal entities.

______________________________________________________________________

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



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Muthuraj

Re: DPSI question on z/os V8
(in response to Michael Kalena)
Deepak,

When converting from NPI to DPSI, there mighe be some restrictions like 'Not
Unique'. But if you can convert it to DPSI, i would see there are many
advantages from Data Maintenance perception. but there will be problems
from performance point of view. I would recommend you to analyse the SQLs
which are using the Indexes. Sometimes you might have to rewrite the SQLs
to include the columns used in partitioning index to make sure that DPSI will
be used in place of NPI.

Regards,
Muthu

______________________________________________________________________

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



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Vanroose

Re: DPSI question on z/os V8
(in response to SUBSCRIBE DB2-L Muthuraj)
On Fri, 12 Dec, Phil Grainger wrote:

> Pros:
>* DPSIs are partitioned so tend to be easier to manage
> (smaller datasets)
> ...

Which implies an other (maybe less important) "Con":
* DB2 will have to open *multiple* (smaller) datasets (up to 229 in your
case) instead of one single dataset, especially for those queries that use
the index but have no partition-constraining predicate.

-- Peter Vanroose.

______________________________________________________________________

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



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Nick CIANCI

Re: DPSI question on z/os V8
(in response to Peter Vanroose)
One word of advise ,and I'm sure it's been said, make sure that anything
that uses the DPSI (static or Dynamic) specifies partitioning key (or at
least a decent part of it) in the query. Otherwise you may end up with up
to 229 (ie the number of partitions) index partitions being read, which
will end up being a performance hit!


Regards,
   Nick CIANCI




Deepak Goyal
<[login to unmask email]
.COM> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> [DB2-L] DPSI question on z/os V8


13/12/08 08:05 AM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-l.org>






Hi All,

We have a very huge 229 partitioned table (around 600 million rows) in our
shop that have 5 NPIS defined on them. It's really very difficult to
maintain the
table especially reorg. Doing the reorg on NPI's takes lot of time so we
are
thinking of replacing the NPI's to DPSI. But would like to know the
pros/cons
for that.

Appreciate your response.

Thanks

______________________________________________________________________

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



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

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



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms