Load utility vs reorg

Yuri

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

Feb 20 '06 #1

Mark A

"Yuri" <yu**@prodigy.net> wrote in message
news:jG*****************@newssvr33.news.prodigy.co m...

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

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

Feb 20 '06 #2

Yuri

Mark A wrote:

"Yuri" <yu**@prodigy.net> wrote in message
news:jG*****************@newssvr33.news.prodigy.co m...

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

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.

Feb 20 '06 #3

Mark A

"Yuri" <yu**@prodigy.net> wrote in message
news:Ol****************@newssvr33.news.prodigy.com ...

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.

Feb 20 '06 #4

Yuri

Mark A wrote:

"Yuri" <yu**@prodigy.net> wrote in message
news:Ol****************@newssvr33.news.prodigy.com ...

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;

Feb 20 '06 #5

Mark A

"Yuri" <yu**@prodigy.net> wrote in message
news:VM*****************@newssvr33.news.prodigy.co m...

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

Feb 20 '06 #6

Bob [IBM]

Mark A wrote:

"Yuri" <yu**@prodigy.net> wrote in message
news:VM*****************@newssvr33.news.prodigy.co m...

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

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

Feb 20 '06 #7

Mark A

"Bob [IBM]" <Bo********@gmail.com> wrote in message
news:45************@individual.net...

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.

Feb 20 '06 #8

Bob [IBM]

Mark A wrote:

"Bob [IBM]" <Bo********@gmail.com> wrote in message
news:45************@individual.net...

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.

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

Feb 21 '06 #9

Yuri

Bob [IBM] wrote:

Mark A wrote:

"Bob [IBM]" <Bo********@gmail.com> wrote in message
news:45************@individual.net...

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.

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

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

Feb 21 '06 #10

Pierre Saint-Jacques

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.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Yuri" <yu**@prodigy.net> a écrit dans le message de news:
bO****************@newssvr33.news.prodigy.com...

Bob [IBM] wrote:

Mark A wrote:

"Bob [IBM]" <Bo********@gmail.com> wrote in message
news:45************@individual.net...

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.

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

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

Feb 21 '06 #11

Pierre Saint-Jacques

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.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Mark A" <no****@nowhere.com> a écrit dans le message de news:
C6********************@comcast.com...

"Bob [IBM]" <Bo********@gmail.com> wrote in message
news:45************@individual.net...

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.

Feb 27 '06 #12

Mark A

"Pierre Saint-Jacques" <se*****@invalid.net> wrote in message
news:rG********************@weber.videotron.net...

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.

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

Feb 27 '06 #13

Pierre Saint-Jacques

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.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Mark A" <no****@nowhere.com> a écrit dans le message de news:
da********************@comcast.com...

"Pierre Saint-Jacques" <se*****@invalid.net> wrote in message
news:rG********************@weber.videotron.net...

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.

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

Feb 28 '06 #14