Build2 Phase for REORG SHRLEVEL Reference

Daniel Nardini

Build2 Phase for REORG SHRLEVEL Reference
In all the V9 presentations and documents, it is stated that the BUILD2 step
is eliminated for a online REORG (SHRLEVEL CHANGE). Is the BUILD2
step also eliminated for the REORG with SHRLVEL REFERENCE.? A
shadow copy of the NPI is built with SHRLEVEL REFERENCE but no
LOGAPPLY step is required. We can not find any explicit statement
regarding the elimination of the BUILD2 step of a REORG SHRLEVEL
REFERENCE although it would seem logical that it would work the same
way as a REORG SHRLEVEL CHANGE.

_____________________________________________________________________

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

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

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: Build2 Phase for REORG SHRLEVEL Reference
(in response to Daniel Nardini)
There is no BUILD2 phase for a partition-level online REORG, whether
executed with SHRLEVEL CHANGE or SHRLEVEL REFERENCE. As for an explicit
statement to this effect, you can check the DB2 for z/OS V9 Utility Guide
and Reference (available in PDF or HTML form at
http://www-01.ibm.com/support/docview.wss?rs=64&uid=swg27011656#manuals --
click on the "Manuals" link). In the section on REORG TABLESPACE, under the
heading "Execution phases of REORG TABLESPACE," you'll see that there is no
reference to a BUILD2 phase. There would be such a reference if there were a
BUILD2 phase for a REORG TABLESPACE PART SHRLEVEL REFERENCE.

Also, if the partitioned table has an NPI, there *will* be log apply
processing for the NPI if you run a REORG TABLESPACE PART SHRLEVEL REFERENCE
-- that's necessary because updates of partitions other than the one you're
REORGing will require that corresponding changes be made to the shadow copy
of the NPI.

Robert


On Tue, Dec 8, 2009 at 9:30 AM, Daniel Nardini <[login to unmask email]>wrote:

> In all the V9 presentations and documents, it is stated that the BUILD2
> step
> is eliminated for a online REORG (SHRLEVEL CHANGE). Is the BUILD2
> step also eliminated for the REORG with SHRLVEL REFERENCE.? A
> shadow copy of the NPI is built with SHRLEVEL REFERENCE but no
> LOGAPPLY step is required. We can not find any explicit statement
> regarding the elimination of the BUILD2 step of a REORG SHRLEVEL
> REFERENCE although it would seem logical that it would work the same
> way as a REORG SHRLEVEL CHANGE.
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
> meetings,
> Webcasts, Conferences- what is going on next?
> RUG leaders- get your events on the calendar today!
> _____________________________________________________________________
>
> 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
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* 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: Build2 Phase for REORG SHRLEVEL Reference
(in response to Robert Catterall)
Robert,
Thanks for the note about logapply on PART SHRLEVEL REFERENCE. I hadn't thought through the implications of the V9 changes for that.
--Phil Sevetson

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Robert Catterall
Sent: Tuesday, December 08, 2009 11:11 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Build2 Phase for REORG SHRLEVEL Reference

There is no BUILD2 phase for a partition-level online REORG, whether executed with SHRLEVEL CHANGE or SHRLEVEL REFERENCE. As for an explicit statement to this effect, you can check the DB2 for z/OS V9 Utility Guide and Reference (available in PDF or HTML form at http://www-01.ibm.com/support/docview.wss?rs=64&uid=swg27011656#manuals -- click on the "Manuals" link). In the section on REORG TABLESPACE, under the heading "Execution phases of REORG TABLESPACE," you'll see that there is no reference to a BUILD2 phase. There would be such a reference if there were a BUILD2 phase for a REORG TABLESPACE PART SHRLEVEL REFERENCE.

Also, if the partitioned table has an NPI, there will be log apply processing for the NPI if you run a REORG TABLESPACE PART SHRLEVEL REFERENCE -- that's necessary because updates of partitions other than the one you're REORGing will require that corresponding changes be made to the shadow copy of the NPI.

Robert

On Tue, Dec 8, 2009 at 9:30 AM, Daniel Nardini <[login to unmask email]<mailto:[login to unmask email]>> wrote:
In all the V9 presentations and documents, it is stated that the BUILD2 step
is eliminated for a online REORG (SHRLEVEL CHANGE). Is the BUILD2
step also eliminated for the REORG with SHRLVEL REFERENCE.? A
shadow copy of the NPI is built with SHRLEVEL REFERENCE but no
LOGAPPLY step is required. We can not find any explicit statement
regarding the elimination of the BUILD2 step of a REORG SHRLEVEL
REFERENCE although it would seem logical that it would work the same
way as a REORG SHRLEVEL CHANGE.

_____________________________________________________________________

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

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

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
Catterall Consulting
www.catterallconsulting.com < http://www.catterallconsulting.com >
________________________________

[ 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

Shery hepp

Re: Build2 Phase for REORG SHRLEVEL Reference
(in response to Philip Sevetson)
Hi Robert- we recently had experience with a reorg shrlevel NONE that
applies logs during the build2 phase as well. I can't figure that one
out- since there are NO updates to the table but IBM says its because
the index structure can change and that's why they need to log. Just
something to keep in mind if you have a large table that requires option
reference or none. We basically blew out the space for the logs in our
test system a couple of times because we were reorging a table that
contained 800 million rows- and we were the only ones running at the
time.



Regards, Shery



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Robert
Catterall
Sent: Tuesday, December 08, 2009 9:11 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Build2 Phase for REORG SHRLEVEL Reference



There is no BUILD2 phase for a partition-level online REORG, whether
executed with SHRLEVEL CHANGE or SHRLEVEL REFERENCE. As for an explicit
statement to this effect, you can check the DB2 for z/OS V9 Utility
Guide and Reference (available in PDF or HTML form at
http://www-01.ibm.com/support/docview.wss?rs=64&uid=swg27011656#manuals
-- click on the "Manuals" link). In the section on REORG TABLESPACE,
under the heading "Execution phases of REORG TABLESPACE," you'll see
that there is no reference to a BUILD2 phase. There would be such a
reference if there were a BUILD2 phase for a REORG TABLESPACE PART
SHRLEVEL REFERENCE.

Also, if the partitioned table has an NPI, there will be log apply
processing for the NPI if you run a REORG TABLESPACE PART SHRLEVEL
REFERENCE -- that's necessary because updates of partitions other than
the one you're REORGing will require that corresponding changes be made
to the shadow copy of the NPI.

Robert


On Tue, Dec 8, 2009 at 9:30 AM, Daniel Nardini <[login to unmask email]>
wrote:

In all the V9 presentations and documents, it is stated that the BUILD2
step
is eliminated for a online REORG (SHRLEVEL CHANGE). Is the BUILD2
step also eliminated for the REORG with SHRLVEL REFERENCE.? A
shadow copy of the NPI is built with SHRLEVEL REFERENCE but no
LOGAPPLY step is required. We can not find any explicit statement
regarding the elimination of the BUILD2 step of a REORG SHRLEVEL
REFERENCE although it would seem logical that it would work the same
way as a REORG SHRLEVEL CHANGE.

_____________________________________________________________________

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

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

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
Catterall Consulting
www.catterallconsulting.com

________________________________

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: Build2 Phase for REORG SHRLEVEL Reference
(in response to Shery hepp)
Hey, Shery.

Were you doing a partition-level REORG or a REORG of the entire tablespace?
I ask because you refer to "reorging a table that contained 800 million
rows" and "there are NO updates to the table." Both of these phrases suggest
that the entire tablespace was reorganized; however, you also refer to
"build2," and that suggests a partition-level REORG. Perhaps you did a
partition-level REORG for every partition in the tablespace, maybe via
several jobs running in parallel?

Robert


On Fri, Dec 11, 2009 at 10:29 AM, Hepp Shery C <[login to unmask email]>wrote:

> Hi Robert- we recently had experience with a reorg shrlevel NONE that
> applies logs during the build2 phase as well. I can’t figure that one out-
> since there are NO updates to the table but IBM says its because the index
> structure can change and that’s why they need to log. Just something to keep
> in mind if you have a large table that requires option reference or none. We
> basically blew out the space for the logs in our test system a couple of
> times because we were reorging a table that contained 800 million rows- and
> we were the only ones running at the time.
>
>
>
> Regards, Shery
>
>
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Robert
> Catterall
> *Sent:* Tuesday, December 08, 2009 9:11 PM
>
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] Build2 Phase for REORG SHRLEVEL Reference
>
>
>
> There is no BUILD2 phase for a partition-level online REORG, whether
> executed with SHRLEVEL CHANGE or SHRLEVEL REFERENCE. As for an explicit
> statement to this effect, you can check the DB2 for z/OS V9 Utility Guide
> and Reference (available in PDF or HTML form at
> http://www-01.ibm.com/support/docview.wss?rs=64&uid=swg27011656#manuals --
> click on the "Manuals" link). In the section on REORG TABLESPACE, under the
> heading "Execution phases of REORG TABLESPACE," you'll see that there is no
> reference to a BUILD2 phase. There would be such a reference if there were a
> BUILD2 phase for a REORG TABLESPACE PART SHRLEVEL REFERENCE.
>
>
> Also, if the partitioned table has an NPI, there *will* be log apply
> processing for the NPI if you run a REORG TABLESPACE PART SHRLEVEL REFERENCE
> -- that's necessary because updates of partitions other than the one you're
> REORGing will require that corresponding changes be made to the shadow copy
> of the NPI.
>
> Robert
>
>
> On Tue, Dec 8, 2009 at 9:30 AM, Daniel Nardini <[login to unmask email]>
> wrote:
>
> In all the V9 presentations and documents, it is stated that the BUILD2
> step
> is eliminated for a online REORG (SHRLEVEL CHANGE). Is the BUILD2
> step also eliminated for the REORG with SHRLVEL REFERENCE.? A
> shadow copy of the NPI is built with SHRLEVEL REFERENCE but no
> LOGAPPLY step is required. We can not find any explicit statement
> regarding the elimination of the BUILD2 step of a REORG SHRLEVEL
> REFERENCE although it would seem logical that it would work the same
> way as a REORG SHRLEVEL CHANGE.
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
> meetings,
> Webcasts, Conferences- what is going on next?
> RUG leaders- get your events on the calendar today!
> _____________________________________________________________________
>
> 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
> Catterall Consulting
> www.catterallconsulting.com
> ------------------------------
>
> [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 >
>



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

Shery hepp

Re: Build2 Phase for REORG SHRLEVEL Reference
(in response to Robert Catterall)
HI Robert, I was running a reorg on the whole tablespace since we were
changing the table partitioning from an integer to a date driven design.
Our systems programmer opened up an ETR with IBM early November and I
will include part of their explanation. I was surprised we were logging
due to the restriction on the tablespace and the fact that the reorg
specified log no and a copy was included. I guess the logging was in the
BUILD/SORTBLD phase- not specifically BUILD2. (my mistake on
terminology). I was not running with DISCARD, but the indexes were in a
restrictive state because of the alters on the table partitioning range.




Bottom line because of the logging of the indexes I will use a copy of
the table and unload/load to redistribute how the table is partitioned
rather than alter/reorg.



FROM IBM ETR 47007,227,000

Hi Rick, I got an update from development:

- the LOG NO parameter on REORG only dictates the logging done on the

data rows during the RELOAD phase - it has no impact on the index

logging done during the BUILD/SORTBLD phase. (True for V8 & V9)

- A REORG TABLESPACE SHRLEVEL NONE PART DISCARD on a partitioned

table space with NPIs will trigger the REORG to scan thru the NPI and

delete keys belonging to rows that were discarded, with following

behaviors:

* On V8, this is done via singleton key fetches/deletes, which can

be slow and CPU intensive.

* On V9, this is done via a new interface to delete all the NPI

* keys of a logical part in a single call, which helps improves

CPU of REORG. But as you observed, this might come at a cost of

increased logging because index structural changs are being logged and

there can be lots of indexpage splits, merges and collapsed when lots of

keys are being deleted/inserted all at once.

So in a nutshell, this is working as designed. We cannot revert back

to the V8 behavior without degrading performance of other existing

users.

The NPI scan only happens when either (1) DISCARD is specified and

rows are discarded or (2) indexes/data are in restrictive states prior

to the REORG (ie. REORP, RBDP). If the REORG does not satisfy any of

these conditions, then we should not see the increased logging in V9

due to the new behavior change.

...

I suspect it will be the NPI index again. According to what the

developer said previously, with V9 and NPIs, there might be increased

logging because index structural changes are being logged, and there

can be lots of index page splits,merges and collapsed when lots of keys

are being deleted/inserted all at once. We need to log these changes in

case they need to be rolled back for any reason. Its not so much the

data being loaded that is being logged as the need to log the structural

changes.







From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Robert
Catterall
Sent: Friday, December 11, 2009 1:54 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Build2 Phase for REORG SHRLEVEL Reference



Hey, Shery.

Were you doing a partition-level REORG or a REORG of the entire
tablespace? I ask because you refer to "reorging a table that contained
800 million rows" and "there are NO updates to the table." Both of these
phrases suggest that the entire tablespace was reorganized; however, you
also refer to "build2," and that suggests a partition-level REORG.
Perhaps you did a partition-level REORG for every partition in the
tablespace, maybe via several jobs running in parallel?

Robert



_____________________________________________________________________

* 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: Build2 Phase for REORG SHRLEVEL Reference
(in response to Shery hepp)
Thanks for the clarification, Shery.

Robert


On Fri, Dec 11, 2009 at 6:51 PM, Hepp Shery C <[login to unmask email]> wrote:

> HI Robert, I was running a reorg on the whole tablespace since we were
> changing the table partitioning from an integer to a date driven design. Our
> systems programmer opened up an ETR with IBM early November and I will
> include part of their explanation. I was surprised we were logging due to
> the restriction on the tablespace and the fact that the reorg specified log
> no and a copy was included. I guess the logging was in the BUILD/SORTBLD
> phase- not specifically BUILD2. (my mistake on terminology). I was not
> running with DISCARD, but the indexes were in a restrictive state because of
> the alters on the table partitioning range.
>
>
>
> Bottom line because of the logging of the indexes I will use a copy of the
> table and unload/load to redistribute how the table is partitioned rather
> than alter/reorg.
>
>
>
> FROM IBM ETR 47007,227,000
>
> Hi Rick, I got an update from development:
>
> - the LOG NO parameter on REORG only dictates the logging done on the
>
> data rows during the RELOAD phase - it has no impact on the index
>
> logging done during the BUILD/SORTBLD phase. (True for V8 & V9)
>
> - A REORG TABLESPACE SHRLEVEL NONE PART DISCARD on a partitioned
>
> table space with NPIs will trigger the REORG to scan thru the NPI and
>
> delete keys belonging to rows that were discarded, with following
>
> behaviors:
>
> * On V8, this is done via singleton key fetches/deletes, which can
>
> be slow and CPU intensive.
>
> * On V9, this is done via a new interface to delete all the NPI
>
> * keys of a logical part in a single call, which helps improves
>
> CPU of REORG. But as you observed, this might come at a cost of
>
> increased logging because index structural changs are being logged and
>
> there can be lots of indexpage splits, merges and collapsed when lots of
>
> keys are being deleted/inserted all at once.
>
> So in a nutshell, this is working as designed. We cannot revert back
>
> to the V8 behavior without degrading performance of other existing
>
> users.
>
> The NPI scan only happens when either (1) DISCARD is specified and
>
> rows are discarded or (2) indexes/data are in restrictive states prior
>
> to the REORG (ie. REORP, RBDP). If the REORG does not satisfy any of
>
> these conditions, then we should not see the increased logging in V9
>
> due to the new behavior change.
>
> ...
>
> I suspect it will be the NPI index again. According to what the
>
> developer said previously, with V9 and NPIs, there might be increased
>
> logging because index structural changes are being logged, and there
>
> can be lots of index page splits,merges and collapsed when lots of keys
>
> are being deleted/inserted all at once. We need to log these changes in
>
> case they need to be rolled back for any reason. Its not so much the
>
> data being loaded that is being logged as the need to log the structural
>
> changes.
>
>
>
>
>
>
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Robert
> Catterall
> *Sent:* Friday, December 11, 2009 1:54 PM
>
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] Build2 Phase for REORG SHRLEVEL Reference
>
>
>
> Hey, Shery.
>
>
> Were you doing a partition-level REORG or a REORG of the entire tablespace?
> I ask because you refer to "reorging a table that contained 800 million
> rows" and "there are NO updates to the table." Both of these phrases suggest
> that the entire tablespace was reorganized; however, you also refer to
> "build2," and that suggests a partition-level REORG. Perhaps you did a
> partition-level REORG for every partition in the tablespace, maybe via
> several jobs running in parallel?
>
> Robert
>
>
> ------------------------------
>
> [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 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