Load utility vs reorg

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Yuri - 20 Feb 2006 17:06 GMT

We have an update sql that was running OK for several months completing
in under 2 minutes . Yesterday something changed and we had to kill this
update after it ran more than 6 hours. Update input table contained the
normal 100K rows and master table contained about 135 M rows.
We reorged the master table specifying the index we wanted DB2 to use in
the update, ran runstats and reran the update with the same results (had
to cancel it). Explain after reorg did not show the use of this index.
We then reloaded the master table and reran the update in under 10
seconds. Explain showed the use of the index.
We thought that reorg would fix the problem, but it didn't.
What is load doing differently? We don't have clustered index defined.
Thanks,
Yuri

Reply to this Message

Mark A - 20 Feb 2006 20:02 GMT

> We have an update sql that was running OK for several months completing in
> under 2 minutes . Yesterday something changed and we had to kill this
[quoted text clipped - 9 lines]
> Thanks,
> Yuri

Need to see your reorg statement and also whether you have a clustering
index defined on the table.

Reply to this Message

Yuri - 20 Feb 2006 20:09 GMT

>>We have an update sql that was running OK for several months completing in
>>under 2 minutes . Yesterday something changed and we had to kill this
[quoted text clipped - 12 lines]
> Need to see your reorg statement and also whether you have a clustering
> index defined on the table.

Mark,
we don't have clustering index defined. In reorg statement we specified
index that we wanted this table to be reorged by.

Reply to this Message

Mark A - 20 Feb 2006 20:14 GMT

> Mark,
> we don't have clustering index defined. In reorg statement we specified
> index that we wanted this table to be reorged by.

Did you reorg the index? Please post the reorg statement you used.

Reply to this Message

Yuri - 20 Feb 2006 21:46 GMT

>>Mark,
>>we don't have clustering index defined. In reorg statement we specified
>>index that we wanted this table to be reorged by.
>
> Did you reorg the index? Please post the reorg statement you used.

This is the statement:
reorg table crm.ps_bo use index crm.ps_bo_ind allow read access;
runstats on table crm.ps_bo with distribution and indexes all;

Reply to this Message

Mark A - 20 Feb 2006 22:15 GMT

> This is the statement:
> reorg table crm.ps_bo use index crm.ps_bo_ind allow read access;
> runstats on table crm.ps_bo with distribution and indexes all;

In version 7, reorging the table would reorg all the indexes also. In
version 8 there is a separate reorg index command, and I believe (but not
100% sure) that you need to run it to get the indexes reorged.

REORG INDEXES ALL FOR TABLE table-name

Reply to this Message

Bob [IBM] - 20 Feb 2006 22:32 GMT

>> This is the statement:
>> reorg table crm.ps_bo use index crm.ps_bo_ind allow read access;
[quoted text clipped - 5 lines]
>
> REORG INDEXES ALL FOR TABLE table-name

Unless you are doing an inplace reorganization, you do not need to
reorganize the indexes as they will be rebuilt with the standard
(classic) offline reorg. If you do an inplace reorg then only the data
is reorganized and then you can consider an index reorganization.

Bob

Reply to this Message

Mark A - 20 Feb 2006 22:52 GMT

> Unless you are doing an inplace reorganization, you do not need to
> reorganize the indexes as they will be rebuilt with the standard (classic)
> offline reorg. If you do an inplace reorg then only the data is
> reorganized and then you can consider an index reorganization.
>
> Bob

Any chance of getting a doc change? There is no mention of this that I can
see in the Command Reference.

Reply to this Message

Bob [IBM] - 21 Feb 2006 00:46 GMT

>> Unless you are doing an inplace reorganization, you do not need to
>> reorganize the indexes as they will be rebuilt with the standard (classic)
[quoted text clipped - 5 lines]
> Any chance of getting a doc change? There is no mention of this that I can
> see in the Command Reference.

The following is from the local Information Centre (installed on my
laptop) and on the web version of the Information Centre. Note the last
sentence:

REORG INDEXES/TABLE Command
...
Indexes might not be optimal following an in-place REORG TABLE
operation, since only the data object and not the indexes are
reorganized. It is recommended that you perform a REORG INDEXES after an
in place REORG TABLE operation. Indexes are completely rebuilt during
the last phase of a classic REORG TABLE, however, so reorganizing
indexes is not necessary.

Here is the link for reference: http://tinyurl.com/pycn3

I also had a look at the V8.2 Command Reference (downloaded from the
web) and the same statement is found in the REORG TABLE section towards
the end.

Bob

Reply to this Message

Yuri - 21 Feb 2006 17:08 GMT

>>> Unless you are doing an inplace reorganization, you do not need to
>>> reorganize the indexes as they will be rebuilt with the standard
[quoted text clipped - 26 lines]
>
> Bob

I still didn't get an answer to my original question - what is the
difference (if any) between reorg and load?
Yuri

Reply to this Message

Pierre Saint-Jacques - 21 Feb 2006 23:17 GMT

It's easy and plain english.
REORG reorganizes the data: As in reclaim empty rows and pages and also
optionally resequences the data.
LOAD is just that it loads data in the table either appending or replacing.
I t does not change or alter existing data and loads in whatever sequence it
comes in.
HTH, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

>
>>>> Unless you are doing an inplace reorganization, you do not need to
[quoted text clipped - 29 lines]
> difference (if any) between reorg and load?
> Yuri

Reply to this Message

Pierre Saint-Jacques - 27 Feb 2006 04:54 GMT

I think IBM used reverse logic in its docs.
One discovers that the INPLACE parm. forces DB2 to use one and only one of
two policies.
1) Use the cluster index to resequence if it exists. No space reclaim.
2) Use the specified in command index to resequence.No space reclaim.
3) If none of the above. Reclaim space. No resequence.

Therefore, classic off line reorg does both (???) id clustered index or
specified index.

Hope you're staying as confused as I am, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

>>
>> Unless you are doing an inplace reorganization, you do not need to
[quoted text clipped - 6 lines]
> Any chance of getting a doc change? There is no mention of this that I can
> see in the Command Reference.

Reply to this Message

Mark A - 27 Feb 2006 05:27 GMT

>I think IBM used reverse logic in its docs.
> One discovers that the INPLACE parm. forces DB2 to use one and only one of
[quoted text clipped - 7 lines]
>
> Hope you're staying as confused as I am, Pierre.

Personally, I think the doc on this subject is very poor. Once they
introduced separate commands for reorging tables and indexes, they should
explicitly say (toward the front of the doc, not in the usage notes) that a
table reorg will also reorg all indexes.

Reference to "classic" is completely inappropriate since not everyone has
used V7 previously (where there was only an "offline" table reorg which also
reorged the indexes).

Reply to this Message

Pierre Saint-Jacques - 28 Feb 2006 01:15 GMT

I quite agree with your last point.
Just to make sure we do stay confused:
One can do an offline (allow read only or no) reorg while using a temp space
to copy the table or to reorg in the containers themselves which the docs
also refer as in place???
But then if you want to reorg on line (allow read and write) you must
specify INPLACE parm nd cannot obviously use a temp space

??????

Regards, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

>>I think IBM used reverse logic in its docs.
>> One discovers that the INPLACE parm. forces DB2 to use one and only one
[quoted text clipped - 16 lines]
> used V7 previously (where there was only an "offline" table reorg which
> also reorged the indexes).

Reply to this Message