Online Reorg with NPI

Bill (GLIC) Johnson

Online Reorg with NPI
All, We are DB2 V6 in a data sharing environment. We are implementing online
reorg and are looking for guidance or ideas from anyone that has experienced
the same issues. If we have a partitioned tablespace(8 partitions) and have
1 or more NPI's defined to it, is it better to online reorg at the partition
level or at the tablespace level? Lets say for examples sake that each
partition contains 7-8 million rows with a large amount of update activity.
Any gotchas that we need to look out for using either scenario? Would the
build2 phase and switch phase of the partition reorg take longer than the
switch phase of the tablespace reorg? Is there an easy way to calculate
those phases without actually running the 2 scenarios and comparing? What
about changing all NPI's to PI's?
Thanks in advance,
Bill Johnson DBA
Allstate Insurance Co.



Dan Sullivan

Re: Online Reorg with NPI
(in response to Bill (GLIC) Johnson)
With that many rows I would think you would want to keep it at the
partition level.



Michael Ebert

Re: Online Reorg with NPI
(in response to Dan Sullivan)
With NPIs, you're more or less required to use full-TS REORG. The assumption is
that you have NPIs because you need them. If you're doing a partition-level
REORG, then in the BUILD2 phase (which takes a long time), the logical partition
will be in a UT restricted state, and any transactions that need access will
fail immediately (no wait period). Thus you're defeating the purpose of OLR.
Also the NPI(s) won't be reorged by a partition-level OLR, so you need an
additional job for this.
With a full-TS REORG in V5, the SWITCH phase used to take a long time. In V6 (I
heard) this has been improved using a parallelised SWITCH phase, and there's a
new FASTSWITCH option for instantaneous renames (or is it V7??), so this
unavailability period should no longer be such a big issue.
There have been lots of postings concerning OLR in the past. Search the
archives.
Note: all my experience has been with V5 OLR. There's supposed to be lots of
improvements in V6. But I still think the conclusion above is valid.
As for changing NPIs to PIs: a partitioned TS requires exactly one PI, so you
can't do that. It would be nice if indexes that have the PI columns as a prefix
could be partitioned as well, but that's not available yet (unless there has
been a big change when I wasn't looking)...

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



From: "Johnson, Bill" <[login to unmask email]> on 04/01/2001 15:52 GMT

Please respond to DB2 Data Base Discussion List <[login to unmask email]>





|--------->
| |
|--------->
>--------------------------------------------------------------------------->
| |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|
|--------->
|To: |
|--------->
>--------------------------------------------------------------------------->
|[login to unmask email] |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
| |
>-------------------------------------------|
|--------->
|cc: |
|--------->
>--------------------------------------------------------------------------->
| (bcc: Michael Ebert/MUC/AMADEUS) |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
| |
>-------------------------------------------|
|--------->
| |
|--------->
>--------------------------------------------------------------------------->
| |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|
|--------->
|Subject: |
|--------->
>--------------------------------------------------------------------------->
|Online Reorg with NPI |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|




All, We are DB2 V6 in a data sharing environment. We are implementing online
reorg and are looking for guidance or ideas from anyone that has experienced
the same issues. If we have a partitioned tablespace(8 partitions) and have
1 or more NPI's defined to it, is it better to online reorg at the partition
level or at the tablespace level? Lets say for examples sake that each
partition contains 7-8 million rows with a large amount of update activity.
Any gotchas that we need to look out for using either scenario? Would the
build2 phase and switch phase of the partition reorg take longer than the
switch phase of the tablespace reorg? Is there an easy way to calculate
those phases without actually running the 2 scenarios and comparing? What
about changing all NPI's to PI's?
Thanks in advance,
Bill Johnson DBA
Allstate Insurance Co.



Rob Wright

Re: Online Reorg with NPI
(in response to Michael Ebert)
We hit a problem with online reorg that is possibly fixed with UQ49427 - we
haven't had time to test this yet. Basically, we had a segmented tablespace with
> 2GB, non-partitioned. During the online reorg, we got the following error
messages:

DUMP00 TITLE=XXXX,ABND=04E-00C200A4,U=SIBAR1 ,C=XYR00.610.BMC
-DSNB1RTR,M=DSNTFRCV,LOC=DSNBBM .DSNB5COM+1F46
DUMP TAKEN TIME=12.35.47 DATE=12/19/2000

DSNP002I -XXXX DSNPXTN0 - DEFINE FAILED FOR 012
YYYYYYYY.DSNDBC.<DBNAME>.AP001028.S0001.A002.
RC=00D70003
CONNECTION-ID=UTILITY, CORRELATION-ID=XXXXREOG,
LUW-ID=*
DSNP007I -XXXX DSNPXTN0 - EXTEND FAILED FOR 014
YYYYYYYY.DSNDBD.<DBNAME>.AP001028.S0001.A002.
RC=00D70003
CONNECTION-ID=UTILITY, CORRELATION-ID=XXXXREOG,
LUW-ID=*
DSNT500I -XXXX DSNUGRAR RESOURCE UNAVAILABLE 015
REASON 00D70003
TYPE 00000220
NAME YYYYYYYY.DSNDBC.<DBNAME>.AP001028.S0001.A002

As usual, try this out in development before Prod.....

Regards
Rob Wright



Eric Pearson

Re: Online Reorg with NPI
(in response to Rob Wright)
Bill,
If you have NPI and do partition level reorg, Online Reorg will
have a BUILD2 phase for the NPIs. During this BUILD2 phase you will no
have application access to the table. If there are no NPI,
partition-level Online Reorg is fine. The BUILD2 phase is *way* longer
than the usual Drain, Last Log Apply, SWITCH sequence of unavailability.

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Johnson, Bill [mailto:[login to unmask email]
Sent: Thursday, January 04, 2001 10:52 AM
To: [login to unmask email]
Subject: Online Reorg with NPI


All, We are DB2 V6 in a data sharing environment. We are implementing online
reorg and are looking for guidance or ideas from anyone that has experienced
the same issues. If we have a partitioned tablespace(8 partitions) and have
1 or more NPI's defined to it, is it better to online reorg at the partition
level or at the tablespace level? Lets say for examples sake that each
partition contains 7-8 million rows with a large amount of update activity.
Any gotchas that we need to look out for using either scenario? Would the
build2 phase and switch phase of the partition reorg take longer than the
switch phase of the tablespace reorg? Is there an easy way to calculate
those phases without actually running the 2 scenarios and comparing? What
about changing all NPI's to PI's?
Thanks in advance,
Bill Johnson DBA
Allstate Insurance Co.