Need help w/.ksh script to REORCHK & output Recommended tables to a file

[Okonita via DBMonster.com]

Hi all,
I am comming along with all this Linus/DB2/scripting business...I am no
longer scared of it!! (LOL). But,
I need to create a .ksh script that does a REORGCHK and output only tables
recommended for reorg.
My goal is to reorgchk and run reorgs based on entries in this reorg file as
shown in the example below.
I have tried my hand at the following failing script and hope that gurus here
can throw me a lifeline of examples on how to script this. Here is what I
tried to do that is failing: (Actually, I found it someplace):

START SHELL CODE
#!/bin/ksh
DBLIST="EMPLOYE E"

for DB_NAME in $DBLIST
do
db2 connect to $DB_NAME
db2 reorgchk current statistics on table all | grep -v SYSIBM | grep '*' |
awk '/^DB2INST1/ && NF == 12 { print " REORG TABLE DB2INST1."$2 ";" }' >
ReorgTables.sql
/db2home/db2inst1/sqllib/bin/db2 -tvf ReorgTables.sql &
done
exit 0
END SHELL CODE

Any help will be highly appreciated...

Thanks

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

[Okonita via DBMonster.com]

Thanks lan for your continuing interest. I have this questions regarding
reorg:

(1) For DB2 (UDB inparticular), what is best practice in terms of (a)
Runstats, Reorgchk, Reorg, Runstats or (b) Reorgchk, Reorg, Runstats. What's
your take on either of the two reorg plan?

(2) It seems that if I run Reorgchk for tables and Reorgchk for indexes, each
producing files for input to reorg, I might reorg tables and indexes twice
because as you said, offline reorg of tables include reorg of indexes too. Is
there a way to avoid this? I hope you see what I am getting at here.

(3) Could you discuss the pros and cons of Offline and Inplace reorg? Why
would you chose one over the other?

Again, thanks for your contribution to my questions...
Ian wrote:

>Thank you lan. $3 and $4 did out only indexes and thats what I was looking
for.

Your last post certainly very educational about the way this tool/reorging
works. So, if both the table and indexes need reorging, doing reorg on the
table alone is sufficient?

Yes. Let me be clear though: REORG TABLE X does a "classic" (offline)
reorganizati on of the table, and all indexes are rebuilt as a part of
this.

REORG TABLE X INPLACE does an online reorg of the table. In this case,
indexes are not automatically rebuilt (they indexes are just updated as
RIDs change). So this is a case where an index reorg might help.

>How about where only indexes need reorg? If I reorg the index alone, how does
that affect the table that the index is based on?

Reorganizing an index has no effect on the base table.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

[Ian]

Okonita via DBMonster.com wrote:

Thanks lan for your continuing interest. I have this questions regarding
reorg:

(1) For DB2 (UDB inparticular), what is best practice in terms of (a)
Runstats, Reorgchk, Reorg, Runstats or (b) Reorgchk, Reorg, Runstats. What's
your take on either of the two reorg plan?

REORGCHK is not useful if you don't have valid statistics, so (b) would
be a better choice.

(2) It seems that if I run Reorgchk for tables and Reorgchk for indexes, each
producing files for input to reorg, I might reorg tables and indexes twice
because as you said, offline reorg of tables include reorg of indexes too. Is
there a way to avoid this? I hope you see what I am getting at here.

If you're doing offline reorgs, you probably don't need to bother with
separate index reorgs.

Realize that there are certain application design characteristics that
will result in the requirement to reorg indexes. If your application
does not follow these patterns, chances are you won't need to do this
kind of index maintenance.

(3) Could you discuss the pros and cons of Offline and Inplace reorg? Why
would you chose one over the other?

Offline means that the table is unavailable during reorg. Obviously,
this could present a problem for your users.

Again, thanks for your contribution to my questions...

You might consider letting DB2 handle all of this for you. DB2 has
some pretty smart routines built in to the engine to manage doing
runstats / reorgs so you don't have to think about it.

http://is.gd/Ait [DB2 Info Center]

[Okonita via DBMonster.com]

Yep, I wish I could leverage DB2 auto-maintenance but I have a very
unstructured environment that the only thing reasonably safe to do is manage
every aspect of our DB2 health upkeep.

Here's another question (I promise, this is the last on this thread:-)

(1) 2 Tables are identified as needing reorg both before and after runstats
was done on them. Reorg is then performed on the two tables and another
runstats done on these tables.
What puzzles me is that both tables still show that Reorg is needed. How can
that be?
Have you had any experience with this situation? Is there anything that can
be done?

Thanks
Ian wrote:

>Thanks lan for your continuing interest. I have this questions regarding
reorg:

(1) For DB2 (UDB inparticular), what is best practice in terms of (a)
Runstats, Reorgchk, Reorg, Runstats or (b) Reorgchk, Reorg, Runstats. What's
your take on either of the two reorg plan?

REORGCHK is not useful if you don't have valid statistics, so (b) would
be a better choice.

>(2) It seems that if I run Reorgchk for tables and Reorgchk for indexes, each
producing files for input to reorg, I might reorg tables and indexes twice
because as you said, offline reorg of tables include reorg of indexes too. Is
there a way to avoid this? I hope you see what I am getting at here.

If you're doing offline reorgs, you probably don't need to bother with
separate index reorgs.

Realize that there are certain application design characteristics that
will result in the requirement to reorg indexes. If your application
does not follow these patterns, chances are you won't need to do this
kind of index maintenance.

>(3) Could you discuss the pros and cons of Offline and Inplace reorg? Why
would you chose one over the other?

Offline means that the table is unavailable during reorg. Obviously,
this could present a problem for your users.

>Again, thanks for your contribution to my questions...

You might consider letting DB2 handle all of this for you. DB2 has
some pretty smart routines built in to the engine to manage doing
runstats / reorgs so you don't have to think about it.

http://is.gd/Ait [DB2 Info Center]

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

[Ian]

Okonita via DBMonster.com wrote:

(1) 2 Tables are identified as needing reorg both before and after runstats
was done on them. Reorg is then performed on the two tables and another
runstats done on these tables.
What puzzles me is that both tables still show that Reorg is needed. How can
that be?

Let's see the output from reorgchk for the tables that you're concerned
about.

[Okonita via DBMonster.com]

Ok, this goes to the heart of Q1 and Q2: (Caveat: I have been running the
index reorgs since 12noon today and I am not done as of 4pm.)

Output from Reorgchk_tb_sta ts
REORG TABLE DB2ADMIN.ALERTS _PART;
REORG TABLE DB2ADMIN.CASE_C LOSURE_DETAILS;
REORG TABLE DB2ADMIN.CASE_P ART_STAT;
REORG TABLE DB2ADMIN.INVS_B CKGRND_SUMRY;
REORG TABLE DB2ADMIN.MPHONE _FRST;
REORG TABLE DB2ADMIN.MPHONE _LST;
REORG TABLE DB2ADMIN.NICKNA ME;
REORG TABLE DB2ADMIN.PROVID ER_CHAR;
REORG TABLE DB2ADMIN.SCRTY_ GRP_PRFL;
REORG TABLE DB2ADMIN.SCRTY_ PRFL_RSRC;

Observe that few tables are reported for reorg versus many more indexes as
shown below. For, example, for ACCESS_REPORT, there are two indexes that
reorgchk says should be reorge'd hence two REORG INDEXES for ACCESS_REPORT. I
want to REORG ALL for a table once...

Output from Reorgchk_ix_sta ts:

REORG INDEXES ALL FOR TABLE DB2ADMIN.ACCESS _REPORT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ACCESS _REPORT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ADDRES S ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ADDRES S ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ALERTS ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.APPROV AL ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.APPROV AL ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ASSIGN MENT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ASSIGN MENT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ASSIGN MENT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_C LOSURE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_C LOSURE_DETAILS ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_M ASTER ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_O THR_CNTCT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_P ART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_P ART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_P ART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_P ART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CA_MAI N ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.COUNTY _PROVIDER_CROSS _REF ALLOW WRITE
ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.DOC_NA RRATIVE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.DOC_NA RRATIVE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISOD E ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISOD E ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISOD E ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISOD E ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISOD E ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISOD E ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISOD E ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISOD E ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISOD E ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISOD E ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISOD E ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INTAKE _PART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INVEST IGATION ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INVEST IGATION ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INVEST IGATION_CNTC ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INVEST IGATION_CNTC ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INVEST IGATION_CNTC ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.MEDICA L_PROFILE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.MEDICA TIONS ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.MENTAL _HEALTH_PROFILE ALLOW WRITE
ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.MPHONE _FRST ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.MPHONE _FRST ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.MPHONE _LST ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ONCALL _SCHEDULE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ORG_WO RKER_ROLE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PAN_TE XT_EVENT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PAN_TE XT_EVENT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PERSON _AKA ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PROVID ER_ORG ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PROVID ER_ORG ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PROVID ER_ORG ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PROVID ER_PART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PROVID ER_PART_STAT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.RELATI ONSHIP ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.RELATI ONSHIP ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.TICKLE R ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.TICKLE R ALLOW WRITE ACCESS;

The index reorgs is taking way too long because VERY LARGE tables are being
reorg'd many times and I need to find another way of doing indexes or
eliminating multiple reorgs of the same table because of multiple indexes
being reported for the same table. Am I making sense?

Some of the tables are in the millions of rows in the index section. What can
I do for relief? I am researching hard for options and the documentation that
I have reviewed don't say nothing about this. In fact, I have learned more
from this runnig thread than from the books - they tell you the code but no
reasoning behind it or why it done so...anyway, that is where I am stuck.

I appreciate some more help to resolve this...

Thanks

Ian wrote:

>(1) 2 Tables are identified as needing reorg both before and after runstats
was done on them. Reorg is then performed on the two tables and another
runstats done on these tables.
What puzzles me is that both tables still show that Reorg is needed. How can
that be?

Let's see the output from reorgchk for the tables that you're concerned
about.

--
Message posted via http://www.dbmonster.com

[Ian]

Okonita via DBMonster.com wrote:

Ok, this goes to the heart of Q1 and Q2: (Caveat: I have been running the
index reorgs since 12noon today and I am not done as of 4pm.)

Output from Reorgchk_tb_sta ts
REORG TABLE DB2ADMIN.ALERTS _PART;
REORG TABLE DB2ADMIN.CASE_C LOSURE_DETAILS;
REORG TABLE DB2ADMIN.CASE_P ART_STAT;
REORG TABLE DB2ADMIN.INVS_B CKGRND_SUMRY;
REORG TABLE DB2ADMIN.MPHONE _FRST;
REORG TABLE DB2ADMIN.MPHONE _LST;
REORG TABLE DB2ADMIN.NICKNA ME;
REORG TABLE DB2ADMIN.PROVID ER_CHAR;
REORG TABLE DB2ADMIN.SCRTY_ GRP_PRFL;
REORG TABLE DB2ADMIN.SCRTY_ PRFL_RSRC;

Observe that few tables are reported for reorg versus many more indexes as
shown below. For, example, for ACCESS_REPORT, there are two indexes that
reorgchk says should be reorge'd hence two REORG INDEXES for ACCESS_REPORT. I
want to REORG ALL for a table once...

Output from Reorgchk_ix_sta ts:

REORGCHK_IX_STA TS has one row per index, and tells you the index schema
(INDSCHEMA) and index name (INDNAME). So, logically, you would
generate:

REORG INDEX DB2ADMIN.ACCESS _RPT_IX1;
REORG INDEX DB2ADMIN.ACCESS _RPT_IX2;
As I said earlier: Chances are good that if you have a table with more
than 1 index, you're never going to get a "perfect" reorgchk for the
indexes on the table -- at a minimum, the cluster ratio will probably be
great on one indexes, and crap on the rest. Trying to automatically
pick indexes to reorg is a challenge, so using this kind of logic
usually (looking for a '*' in the output of REORGCHK_IX_STA TS) doesn't
work too well.


Ian

[Okonita via DBMonster.com]

Yes, it is going to be a challenge and I can thing of a solution albeit not
elegant.
Say do a quick load of the output of reorgchk to a table keyed on table_name,
turn around dump table back to a flat file and run reorg from that...for all
of the time I will spend monkeying around with this, you'll think my boss
will spring for a cheap tool...thank God I'm not doing this on a Prod
environment.

lan, here's to many thanks for the time and patience...I appreciate it very
much

Ian wrote:

>Ok, this goes to the heart of Q1 and Q2: (Caveat: I have been running the
index reorgs since 12noon today and I am not done as of 4pm.)

[quoted text clipped - 17 lines]

>>
Output from Reorgchk_ix_sta ts:

REORGCHK_IX_ST ATS has one row per index, and tells you the index schema
(INDSCHEMA) and index name (INDNAME). So, logically, you would
generate:

REORG INDEX DB2ADMIN.ACCESS _RPT_IX1;
REORG INDEX DB2ADMIN.ACCESS _RPT_IX2;

As I said earlier: Chances are good that if you have a table with more
than 1 index, you're never going to get a "perfect" reorgchk for the
indexes on the table -- at a minimum, the cluster ratio will probably be
great on one indexes, and crap on the rest. Trying to automatically
pick indexes to reorg is a challenge, so using this kind of logic
usually (looking for a '*' in the output of REORGCHK_IX_STA TS) doesn't
work too well.

Ian

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1