Db2 reorg online

Srirengan Venkatesh Kumar

Db2 reorg online
Hi,

We are OS390 2.3, DB2 V5.1

I have two questions regarding db2 online reorg

1. I was attempting to reorg a table of 14mil records (online reorg with
shrlevel change). I cancelled the job after about 90 mins. The time I
cancelled the job the utility was in reload phase and the count was about
4.2 mil. My question is that why is DB2 online reorg taking so long. I
compared with BMC reorg of the same table when it was about 8 mil records
and took only about 17 mins (shrlevel none). There are no structure changes
since and the participating dasd is the same. There is enough CPU available
in the system to process.

2. This question is related to the above. How can I tune DB2 reorg .
Following is the settings I have at the sprc macro. Is this enough??
sprmubs=128, sprmulb = 10240, sprmulf.=1

Please let me know.

Thanks



Eric Pearson

Re: Db2 reorg online
(in response to Srirengan Venkatesh Kumar)
Why are you so concerned about the elapsed time?
That is not the problem Online Reorg attempts to solve.
BMC, Platinum, CDB (and others) minimize the required outage
by more efficient I/O etc. They still have an outage (17 minutes in your
case).
Online Reorg does not seek to minimize the outage by reducing elapsed time,
but by reducing the time data is not available. The 'not available' time
is typically a few seconds or so (maybe even a minute) of RO during the
last log apply, and a few seconds (1-5 seconds per dataset) while
the datasets and shadow datasets are being renamed/deleted. Your
net result is typically a shorter application outage than you get
with the blazing fast OEM utilities.

We have converted most of our reorgs for critical applications
from Platinum/CA (which was much faster than IBM) to IBM Online Reorg and
now
have better availability than before. Yes, we now use more CPU. Yes, we now
do a bit more I/O. Yes, we now tie up tape drives longer. And yes, we now
have
a happier user community.

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Srirengan Venkatesh Kumar
[mailto:[login to unmask email]
Sent: Tuesday, December 19, 2000 6:08 AM
To: [login to unmask email]
Subject: Db2 reorg online


Hi,

We are OS390 2.3, DB2 V5.1

I have two questions regarding db2 online reorg

1. I was attempting to reorg a table of 14mil records (online reorg with
shrlevel change). I cancelled the job after about 90 mins. The time I
cancelled the job the utility was in reload phase and the count was about
4.2 mil. My question is that why is DB2 online reorg taking so long. I
compared with BMC reorg of the same table when it was about 8 mil records
and took only about 17 mins (shrlevel none). There are no structure changes
since and the participating dasd is the same. There is enough CPU available
in the system to process.

2. This question is related to the above. How can I tune DB2 reorg .
Following is the settings I have at the sprc macro. Is this enough??
sprmubs=128, sprmulb = 10240, sprmulf.=1

Please let me know.

Thanks








Alan Smith

Db2 reorg online
(in response to Eric Pearson)
I gather that there have been problems in V5 with the build of the mapping
table/index for shrlevel change if the tablespace is badly clustered. In V5
the mapping rows are inserted, in V6 they are loaded, which is supposed to
improve things.

Alan Smith


> ------------------------------
>
> Date: Tue, 19 Dec 2000 19:07:57 +0800
> From: Srirengan Venkatesh Kumar
> <[login to unmask email]>
> Subject: Db2 reorg online
>
> Hi,
>
> We are OS390 2.3, DB2 V5.1
>
> I have two questions regarding db2 online reorg
>
> 1. I was attempting to reorg a table of 14mil records (online reorg with
> shrlevel change). I cancelled the job after about 90 mins. The time I
> cancelled the job the utility was in reload phase and the count was about
> 4.2 mil. My question is that why is DB2 online reorg taking so long. I
> compared with BMC reorg of the same table when it was about 8 mil records
> and took only about 17 mins (shrlevel none). There are no structure changes
> since and the participating dasd is the same. There is enough CPU available
> in the system to process.
>
> 2. This question is related to the above. How can I tune DB2 reorg .
> Following is the settings I have at the sprc macro. Is this enough??
> sprmubs=128, sprmulb = 10240, sprmulf.=1
>
> Please let me know.
>
> Thanks