473,322 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Load utility vs reorg

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
13 4345
"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
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
"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
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
"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
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
"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
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
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
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
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
"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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

27
by: Raquel | last post by:
This question if for UDB on LUW. Suppose I take regular database backups of my database Monday: database backup <ts1> Tuesday: Tablespace reorged and imagecopied <ts2> Friday: database...
7
by: DB_2 | last post by:
Hello, I was trying to load a comma-separated text file to a DB2 table. I believe I have the syntax rigt for the LOAD command. My first question is, how do you actually run it? It is not a...
2
by: TomHorner | last post by:
I have several quick questions about reorg's, and a request (favor). 1. The documentation says that reorg "Reorganizes an index or a table" I cannot see how one would reorg ONE particular index...
1
by: hikums | last post by:
1. I did a reorgchk, and found that F2 on table and F4 on index indicates a reorg is necessary. 2. Did a reorg. 3. Again I do a reorgchk, the report is the same as in Step 1 4. I do a...
16
by: andy.standley | last post by:
Hi, we are running DB2 V8.2 (8.1.0.80) on redhat linux and I am trying to set the reorg to be online. I use the control center on the box - db2cc and then configure automatic maintenance wizard -...
9
by: mike_dba | last post by:
I am using DMS raw. I have a tablespace which is defined with 5 containers each allocated at 1.5 GB for a total of about 7.5 GB. I did a major deletion followed by a reorg and a runstats. I am...
1
by: huyuhui | last post by:
The following is a question of LOAD utility. Question: How does the DB2 enforce table check constraints for data added to table with the LOAD utility? A. With the BUILD phase of LOAD B. With the...
0
by: bwmiller16 | last post by:
Hi All - OSName: AIX NodeName: dr_aixdb01 Version: 5 Release: 3 dr_aixdb01$db2level DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL08027"
0
by: nithathomas | last post by:
Hi all, Please help me to load archive dataset back into the DB2 table: The archive dataset is in the form of a GDG which is created in the REORG job which has a delete condition. The DB2...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.