473,396 Members | 1,918 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,396 software developers and data experts.

Reorg table/index questions

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 - is there a way
to do this? More importantly, would you WANT to do this?

2. We plan on doing inplace reorg's due to the 24x7 nature of our
database servers. Are there any "undocumented" issues with these for
V8.1, FP2 or FP6?

3. When you do a table reorg, the manual says that one needs to
"complete all database operations and release all locks before invoking
REORG TABLE." I thought the whole idea of in inplace reorg was that
the application could go on without interruption. Can I start an
inplace reorg without interrupting the users? Is this statement in the
manual true for inplace reorgs?

Request:

Does anyone have a "canned" script for running reorg's? I hate to
reinvent the wheel :-) ! I checked the IDUG and IBM sites, but could
not find any "sample" scripts. I would prefer to tweak an existing
script rather than create one from scratch.

Design:

I plan to use the output of the reorgchk command to drive the reorg
script, doing only those tables that are marked as needing a reorg. If
possible, I also hope to do this for those individual indexes that are
marked as needing a reorg. I understand that it is best to do an
"index all" reorg after a table reorg, so I may add this step after
each table reorg to the script.

Of course, this design depends on how long all this takes. Our
databases are not "huge" (they average about 2 GB), but I have no idea
how long these reorgs will take until I test them out.

Thanks in advance!

Nov 12 '05 #1
2 8262
Reading of your Command Ref. will help, I guarantee it!

Point-1) This refers to the ability to address the reclaiming of space
in the index leaf pages or the pages themselves where "pseudo deletes"
are present.
V8 indexes are Type 2 and when you do deletes, the key+rid combo is
"marked" to be deleted. This can be done at reorg time (reorgchk will
indicate it), on one or all indexes for CLEANUP option.
It has absolutely no bearing on reorging (cleaning up, sorting or
whatever). It's only to deal with cleaning up pages for pseudo deletes
and reclaiming empty pages or pages full of pseudo deletes.
The last option on the cmd. is to convert ALL indexes from Type1 to
Type2. You'd use this after a migration from V7 (Type1) indexes to V8
and you need to do an online reorg of the table. This requires all
indexes of the table to be Type2. So convert.... reorg ... INPLACE....

Point 2) Hopefully someone will respond to this as I'm not experienced
enough in "gotchas" for that command at V8 level.
HTH, Pierre.
TomHorner wrote:
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 - is there a way
to do this? More importantly, would you WANT to do this?

2. We plan on doing inplace reorg's due to the 24x7 nature of our
database servers. Are there any "undocumented" issues with these for
V8.1, FP2 or FP6?

3. When you do a table reorg, the manual says that one needs to
"complete all database operations and release all locks before invoking
REORG TABLE." I thought the whole idea of in inplace reorg was that
the application could go on without interruption. Can I start an
inplace reorg without interrupting the users? Is this statement in the
manual true for inplace reorgs?

Request:

Does anyone have a "canned" script for running reorg's? I hate to
reinvent the wheel :-) ! I checked the IDUG and IBM sites, but could
not find any "sample" scripts. I would prefer to tweak an existing
script rather than create one from scratch.

Design:

I plan to use the output of the reorgchk command to drive the reorg
script, doing only those tables that are marked as needing a reorg. If
possible, I also hope to do this for those individual indexes that are
marked as needing a reorg. I understand that it is best to do an
"index all" reorg after a table reorg, so I may add this step after
each table reorg to the script.

Of course, this design depends on how long all this takes. Our
databases are not "huge" (they average about 2 GB), but I have no idea
how long these reorgs will take until I test them out.

Thanks in advance!


--
Pierre Saint-Jacques - Reply to: sescons at attglobal dot net
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.
Nov 12 '05 #2
For point 3, you can kick off the inplace reorg while users are active
on the table...however it will not begin processing until it can get
exclusive access to the table (exclusive access is only held/needed for
a brief period of time).

TomHorner wrote:
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 - is there a way
to do this? More importantly, would you WANT to do this?

2. We plan on doing inplace reorg's due to the 24x7 nature of our
database servers. Are there any "undocumented" issues with these for
V8.1, FP2 or FP6?

3. When you do a table reorg, the manual says that one needs to
"complete all database operations and release all locks before invoking
REORG TABLE." I thought the whole idea of in inplace reorg was that
the application could go on without interruption. Can I start an
inplace reorg without interrupting the users? Is this statement in the
manual true for inplace reorgs?

Request:

Does anyone have a "canned" script for running reorg's? I hate to
reinvent the wheel :-) ! I checked the IDUG and IBM sites, but could
not find any "sample" scripts. I would prefer to tweak an existing
script rather than create one from scratch.

Design:

I plan to use the output of the reorgchk command to drive the reorg
script, doing only those tables that are marked as needing a reorg. If
possible, I also hope to do this for those individual indexes that are
marked as needing a reorg. I understand that it is best to do an
"index all" reorg after a table reorg, so I may add this step after
each table reorg to the script.

Of course, this design depends on how long all this takes. Our
databases are not "huge" (they average about 2 GB), but I have no idea
how long these reorgs will take until I test them out.

Thanks in advance!

Nov 12 '05 #3

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...
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 -...
3
by: andy.standley | last post by:
Hi, I posted something a while ago about not being able to setup online reorgs - the db2 control center only offers online runstats. So I started adding tasks to reorg each table. This works fine,...
2
by: EpsilonAurigae | last post by:
does anyone have an idea as to why the reorgchk utility says that a particular table needs to be reorged even after running the reorg command on that particular table. answer is appreciated.
13
by: Yuri | last post by:
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...
9
by: Mikael | last post by:
Must admit, I am new to DB2: Seems I can not issue command REORG TABLESPACE: Commands Entered: REORG TABLESPACE SBO_DB.TS_SBO_SAFE; SQL0104N An unexpected token "TABLESPACE" was found...
2
by: dunleav1 | last post by:
I have a many row and many column table that is in a 16K page size. I have four indexes on the table. I am running row compression on the table. The table does not have a primary key. The table...
0
by: Roger | last post by:
I changed my classic reorgs to inplace reorg on my siebel database last weekend and ran into the log space getting full issue . This is one of the command on my script : reorg table SIEBEL.ASSET...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

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.