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
