Reorg using Shrlevel Change

William Moss

Reorg using Shrlevel Change
My environment is DB2 V5.1, OS/390 2.8, and data sharing.

I have been testing online reorg with shrlevel change with a disorganized
table (67% cluster ratio) and 7.4 million rows. The reorg runs for 66
minutes with the reload phase running for 60 minutes. Omegamon shows 48
minutes of wait for synchronous I/O during the reload phase. Does any one
know what is causing all of this wait for I/O ? Is it the mapping table ?
There is no activity against this table during the reorg.

After the online reorg completed, I reloaded the table with the disorganized
data and ran a reorg with shrlevel none. It ran in 6 minutes. I don't
understand the large difference in the run times between the two reorgs.

Thanks in advance,

Bill Moss

DBA
Aetna/US Healthcare



Ray H Janes

Re: Reorg using Shrlevel Change
(in response to William Moss)
One guess. Do a -dis util(*) on it while it is doing the waiting. See if it is
doing log apply. If so, it also gives you counts as to how much it has to do.
Also, if it is in switch phase. We have a lot of multi-table tablespace. Each
index has a physical file. So switch might be re-naming 100+ datasets TWICE.
You can go to ispf 3.4 and watch the shadow dataset slowly go away and the
temporary one's appear. then also go away while the rename to the online name
occurs. Sort of fun.




"Moss, William" <[login to unmask email]> on 08/24/2000 03:28:24 PM

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

To: [login to unmask email]
cc: (bcc: Ray H Janes/C/Dallas/Mobil-Notes)
Subject: Reorg using Shrlevel Change





My environment is DB2 V5.1, OS/390 2.8, and data sharing.

I have been testing online reorg with shrlevel change with a disorganized
table (67% cluster ratio) and 7.4 million rows. The reorg runs for 66
minutes with the reload phase running for 60 minutes. Omegamon shows 48
minutes of wait for synchronous I/O during the reload phase. Does any one
know what is causing all of this wait for I/O ? Is it the mapping table ?
There is no activity against this table during the reorg.

After the online reorg completed, I reloaded the table with the disorganized
data and ran a reorg with shrlevel none. It ran in 6 minutes. I don't
understand the large difference in the run times between the two reorgs.

Thanks in advance,

Bill Moss

DBA
Aetna/US Healthcare








Michael Ebert

Re: Reorg using Shrlevel Change
(in response to Ray H Janes)
Wrong. It was stated that the problem was in the RELOAD phase, and that there
was no other tablespace activity. It's the building of the mapping table index.
See my email dated 18.8.00, "Re: DB2 v5.1 Reorg shrlevel change on a single
partition of a 10 partition table", and many other postings re OLR.
<sigh>
I sometimes wish people would READ their emails...
</sigh>

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




From: Ray H Janes <[login to unmask email]> on 24/08/2000 20:33 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: |
|--------->
>--------------------------------------------------------------------------->
|Re: Reorg using Shrlevel Change |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|







One guess. Do a -dis util(*) on it while it is doing the waiting. See if it is
doing log apply. If so, it also gives you counts as to how much it has to do.
Also, if it is in switch phase. We have a lot of multi-table tablespace. Each
index has a physical file. So switch might be re-naming 100+ datasets TWICE.
You can go to ispf 3.4 and watch the shadow dataset slowly go away and the
temporary one's appear. then also go away while the rename to the online name
occurs. Sort of fun.




"Moss, William" <[login to unmask email]> on 08/24/2000 03:28:24 PM

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

To: [login to unmask email]
cc: (bcc: Ray H Janes/C/Dallas/Mobil-Notes)
Subject: Reorg using Shrlevel Change





My environment is DB2 V5.1, OS/390 2.8, and data sharing.

I have been testing online reorg with shrlevel change with a disorganized
table (67% cluster ratio) and 7.4 million rows. The reorg runs for 66
minutes with the reload phase running for 60 minutes. Omegamon shows 48
minutes of wait for synchronous I/O during the reload phase. Does any one
know what is causing all of this wait for I/O ? Is it the mapping table ?
There is no activity against this table during the reorg.

After the online reorg completed, I reloaded the table with the disorganized
data and ran a reorg with shrlevel none. It ran in 6 minutes. I don't
understand the large difference in the run times between the two reorgs.

Thanks in advance,

Bill Moss

DBA
Aetna/US Healthcare













Ray H Janes

Re: Reorg using Shrlevel Change
(in response to Michael Ebert)
Thank you for pointing out my mis-reading of the question. Next time I have a
GUESS, I'll keep it to myself and allow the good Dr. to answer it.




[login to unmask email] on 08/25/2000 02:44:09 AM

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

To: [login to unmask email]
cc: (bcc: Ray H Janes/C/Dallas/Mobil-Notes)
Subject: Re: Reorg using Shrlevel Change





Wrong. It was stated that the problem was in the RELOAD phase, and that there
was no other tablespace activity. It's the building of the mapping table index.
See my email dated 18.8.00, "Re: DB2 v5.1 Reorg shrlevel change on a single
partition of a 10 partition table", and many other postings re OLR.
<sigh>
I sometimes wish people would READ their emails...
</sigh>

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




From: Ray H Janes <[login to unmask email]> on 24/08/2000 20:33 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: |
|--------->
>--------------------------------------------------------------------------->
|Re: Reorg using Shrlevel Change |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|







One guess. Do a -dis util(*) on it while it is doing the waiting. See if it is
doing log apply. If so, it also gives you counts as to how much it has to do.
Also, if it is in switch phase. We have a lot of multi-table tablespace. Each
index has a physical file. So switch might be re-naming 100+ datasets TWICE.
You can go to ispf 3.4 and watch the shadow dataset slowly go away and the
temporary one's appear. then also go away while the rename to the online name
occurs. Sort of fun.




"Moss, William" <[login to unmask email]> on 08/24/2000 03:28:24 PM

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

To: [login to unmask email]
cc: (bcc: Ray H Janes/C/Dallas/Mobil-Notes)
Subject: Reorg using Shrlevel Change





My environment is DB2 V5.1, OS/390 2.8, and data sharing.

I have been testing online reorg with shrlevel change with a disorganized
table (67% cluster ratio) and 7.4 million rows. The reorg runs for 66
minutes with the reload phase running for 60 minutes. Omegamon shows 48
minutes of wait for synchronous I/O during the reload phase. Does any one
know what is causing all of this wait for I/O ? Is it the mapping table ?
There is no activity against this table during the reorg.

After the online reorg completed, I reloaded the table with the disorganized
data and ran a reorg with shrlevel none. It ran in 6 minutes. I don't
understand the large difference in the run times between the two reorgs.

Thanks in advance,

Bill Moss

DBA
Aetna/US Healthcare


















Jim Ruddy

Re: Reorg using Shrlevel Change
(in response to Ray H Janes)
The long elapsed time in the unload and reload phases is caused by the
disorganization and the way the mapping table was built in V5. The design
was changed in V6 to correct this. Time to upgrade to V6?

Jim Ruddy
IBM DB2 Development



John Arbogast

Re: Reorg using Shrlevel Change
(in response to Jim Ruddy)
If you enjoy applying PTFs like crazy.....

On Fri, 25 August 2000, Jim Ruddy wrote:

>
> The long elapsed time in the unload and reload phases is caused by the
> disorganization and the way the mapping table was built in V5. The design
> was changed in V6 to correct this. Time to upgrade to V6?
>
> Jim Ruddy
> IBM DB2 Development
>
>
>



Michael Ebert

Re: Reorg using Shrlevel Change
(in response to John Arbogast)
Hi Jim,

from a lecture I learned about a couple of REORG enhancements (runtime
determination whether to run REORG, inline RUNSTATS, parallel Index build,
parallel VSAM rename, improved LOGAPPLY) but nothing about improvements in the
RELOAD phase. Can you specify? As for going to V6: we have had enormous problems
with abending OLRs crashing DB2 (and many other things). That's better now, but
we have postponed using V6 in production with no new date set.

To me, the obvious way to improve OLR would be: in the RELOAD phase, instead of
inserting keys into the mapping table index (out of order), pass the keys to
DFSORT. BUILD the mapping index using the normal utility code from the sort
output (this would be done in parallel to the tablespace index BUILD, so no
additional elapsed time). This would make CPU and elapsed time nearly
independent of clusterratio, and would have little overhead as compared to
normal REORG (whereas now, the CPU and elapsed times are much higher than for
SHRLEVEL NONE reorg, and increase dramatically with decreasing clusterratio). A
faster RELOAD would also mean less log records to apply, which will shorten the
LOGAPPLY phase, which means still less log records... of course, during LOG
APPLY, you collect a couple of thousand log records first, sort them in memory
by old RID, then go to the index to retrieve the index entries, sort the result
set by new RID, then apply to the shadow TS to optimise the log application
(instead of doing it one log record at a time).

I'm curious as to why it wasn't done like this in the first place. The current
solution most likely required new code to be developed (for the "phantom"
mapping table), while this solution should make use of existing code. Maybe
there is a deep reason?

Any company using this algorithm is put under moral obligation to pay me a hefty
bonus!

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




From: Jim Ruddy <[login to unmask email]> on 25/08/2000 15:32 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: |
|--------->
>--------------------------------------------------------------------------->
|Re: Reorg using Shrlevel Change |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|







The long elapsed time in the unload and reload phases is caused by the
disorganization and the way the mapping table was built in V5. The design
was changed in V6 to correct this. Time to upgrade to V6?

Jim Ruddy
IBM DB2 Development








Jim Ruddy

Re: Reorg using Shrlevel Change
(in response to Michael Ebert)
When we put together the first presentations on V6 enhancements we did not
mention that we had changed part of the architecture of OLR to process the
mapping table exactly as you describe. The change did not improve the
availability of the data but we were thoroughly chastised by the QPP
customers for not telling them of the big improvement in overall elapsed
time.

It is easy for us to forget that OLR was originally planned for V6 and we
moved the delivery up a release. We discovered the performance problem with
poorly clustered data too late in V5 development to change the design of
populating the mapping table. We missed this during design because we were
so intensely focused on shadow datasets and the LOG, SWITCH, and BUILD2
phases that we missed something that in hindsight seems so simple. Using
parallel index build in V6 allows the mapping table index to be built in
parallel with the other indexes.

Jim Ruddy
IBM DB2 Development



Isaac Yassin

REORG SHRLEVEL CHANGE
(in response to Jim Ruddy)
Hi,

For the mapping table you can have one small permanent segmented TS with
many TBs in it.
What's changes the size during the reorg is only the index on the table (one
index per table).
So you can define the TS as priqty 720 (1 cyl) and each index as priqty 720
secqty 72000 (or what ever you find is better for you ).



Isaac Yassin
DBMS & IT Consultant
[login to unmask email]