By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,617 Members | 1,751 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,617 IT Pros & Developers. It's quick & easy.

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

P: n/a
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="EMPLOYEE"

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

Jun 27 '08 #1
Share this Question
Share on Google+
16 Replies


P: n/a


Okonita via DBMonster.com wrote:
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
In what way does it fail?
/Lennart
>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="EMPLOYEE"

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
Jun 27 '08 #2

P: n/a
Hi,
It fails to select tables that certainly needs to be reorged - output file is
empty. I know this because when I run "db2 reorgchk current statistics on
table all", the list shows several tables and indexes that has '*' in either
F1 thru F8.

So, that leads to believe I am doing something wrong but what I don't know...
All I am looking for is the correct syntax for executing reorgcheck, grep '*'
and awk/print the find to the output file that I specify.

Can you help with the correct shell script? Will be much obliged...

Thanks
Lennart wrote:
>Hi all,
I am comming along with all this Linus/DB2/scripting business...I am no
[quoted text clipped - 4 lines]
>shown in the example below.
I have tried my hand at the following failing script

In what way does it fail?

/Lennart
>>and hope that gurus here
can throw me a lifeline of examples on how to script this. Here is what I
[quoted text clipped - 22 lines]
>Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

Jun 27 '08 #3

P: n/a
Okonita via DBMonster.com wrote:
#!/bin/ksh
DBLIST="EMPLOYEE"

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
REORGCHK, at least on my 9.5 FP1 box, outputs the table name and its
statistics on /separate/ lines. So simply grepping for *'s is not going
to work. It'd be possible to bodge something together with awk, but why
not do this properly ... ish ... ;-)

The following's got a lot more comments in it than I'd normally bother
with, but it should be pretty obvious as a result. Couple of
differences to the original script - the actual bit that reorg's the
tables is currently commented out. Test it, then uncomment it.
Secondly, I haven't bothered filtering the SYSIBM tables - after all,
why not reorg them too if they need it?

Also, the bit for reorg'ing the tables isn't set to do it in parallel
in the background (the trailing & in your original script). Unless
you've got an array with /lots/ of drives, I doubt you'll gain much by
trying to do it in parallel (which is not mention the problem with
using the same continually rewritten source script with multiple
background processes in your original script ;-).

Anyway - here's the script (it's Python: if this is a Linux box, you've
likely already got it, if it's some flavour of UNIX it shouldn't be
difficult to install from the source tarball).

#!/usr/bin/env python

from subprocess import Popen, PIPE, STDOUT

# List of database names to REORGCHK
databases = ['BOBIW']

for database in databases:
# Generate the SQL to run through thte CLP
sql = 'CONNECT TO %s;\n' % database
sql += 'REORGCHK CURRENT STATISTICS ON TABLE ALL;\n'
# Each element is a separate parameter
cmdline = ['db2', '-t', '+p']
# Start the CLP redirecting stdin et al. to pipes
p = Popen(cmdline, stdin=PIPE, stdout=PIPE, stderr=STDOUT)
# Pass the SQL to the CLP, read the output (the method returns
# a tuple of stdout, stderr but we've redirected stderr to
# stdout, so only grab the first element)
output = p.communicate(sql)[0]
# Parse the output
table = None
reorg = []
for line in output.split('\n'):
# Terminate once we reach index stats
if line == 'Index statistics:':
break
if line.startswith('Table:'):
# Grab the table name (7th character onwards on lines
# beginning with 'Table:'
table = line[7:]
elif table:
# Check the REORG column (last 3 chars on lines
# immediately following a 'Table:' line). If a * is
# present, add it to the reorg list
if '*' in line[-4:]:
reorg.append(table)
table = None
# Loop through the reorg list running the CLP with a REORG
# command for each table
for table in reorg:
print 'Reorganizing table %s' % table
# Same as above, run the CLP with pipes for std handles,
# pass the REORG command and print the output
#sql = 'CONNECT TO %s;\n' % database
#sql += 'REORG TABLE %s;\n' % table
#p = Popen(cmdline, stdin=PIPE, stdout=PIPE, stderr=STDOUT)
#print p.communicate(sql)[0]
Cheers,

Dave.
Jun 27 '08 #4

P: n/a
Ian
Okonita via DBMonster.com wrote:
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.
[...]
Any help will be highly appreciated...
Parsing REORGCHK output makes your job a lot harder, now that you can
use the REORGCHK_TB_STATS and REORGCHK_IX_STATS stored procedures.


Jun 27 '08 #5

P: n/a
lan,
Ok, but how about an example how to do that? I have problem because I am
relatively new at writing scripts and can use a working example...

Thanks

Ian wrote:
>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.
[...]
Any help will be highly appreciated...

Parsing REORGCHK output makes your job a lot harder, now that you can
use the REORGCHK_TB_STATS and REORGCHK_IX_STATS stored procedures.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

Jun 27 '08 #6

P: n/a
Dave,
Thanks for the example. I am new to shell scripting and Linux. I am only able
to deal with simple code at this time and your script eample is a little bit
complicated for me.

If you have someting simpler like I have in the initial post and strictly
kshell, I'll appreciate it. Later, I will have time to read about "python"
and learn about your code/script.

Please help if you can..

Thanks

Dave Hughes wrote:
>#!/bin/ksh
DBLIST="EMPLOYEE"
[quoted text clipped - 8 lines]
>done
exit 0

REORGCHK, at least on my 9.5 FP1 box, outputs the table name and its
statistics on /separate/ lines. So simply grepping for *'s is not going
to work. It'd be possible to bodge something together with awk, but why
not do this properly ... ish ... ;-)

The following's got a lot more comments in it than I'd normally bother
with, but it should be pretty obvious as a result. Couple of
differences to the original script - the actual bit that reorg's the
tables is currently commented out. Test it, then uncomment it.
Secondly, I haven't bothered filtering the SYSIBM tables - after all,
why not reorg them too if they need it?

Also, the bit for reorg'ing the tables isn't set to do it in parallel
in the background (the trailing & in your original script). Unless
you've got an array with /lots/ of drives, I doubt you'll gain much by
trying to do it in parallel (which is not mention the problem with
using the same continually rewritten source script with multiple
background processes in your original script ;-).

Anyway - here's the script (it's Python: if this is a Linux box, you've
likely already got it, if it's some flavour of UNIX it shouldn't be
difficult to install from the source tarball).

#!/usr/bin/env python

from subprocess import Popen, PIPE, STDOUT

# List of database names to REORGCHK
databases = ['BOBIW']

for database in databases:
# Generate the SQL to run through thte CLP
sql = 'CONNECT TO %s;\n' % database
sql += 'REORGCHK CURRENT STATISTICS ON TABLE ALL;\n'
# Each element is a separate parameter
cmdline = ['db2', '-t', '+p']
# Start the CLP redirecting stdin et al. to pipes
p = Popen(cmdline, stdin=PIPE, stdout=PIPE, stderr=STDOUT)
# Pass the SQL to the CLP, read the output (the method returns
# a tuple of stdout, stderr but we've redirected stderr to
# stdout, so only grab the first element)
output = p.communicate(sql)[0]
# Parse the output
table = None
reorg = []
for line in output.split('\n'):
# Terminate once we reach index stats
if line == 'Index statistics:':
break
if line.startswith('Table:'):
# Grab the table name (7th character onwards on lines
# beginning with 'Table:'
table = line[7:]
elif table:
# Check the REORG column (last 3 chars on lines
# immediately following a 'Table:' line). If a * is
# present, add it to the reorg list
if '*' in line[-4:]:
reorg.append(table)
table = None
# Loop through the reorg list running the CLP with a REORG
# command for each table
for table in reorg:
print 'Reorganizing table %s' % table
# Same as above, run the CLP with pipes for std handles,
# pass the REORG command and print the output
#sql = 'CONNECT TO %s;\n' % database
#sql += 'REORG TABLE %s;\n' % table
#p = Popen(cmdline, stdin=PIPE, stdout=PIPE, stderr=STDOUT)
#print p.communicate(sql)[0]

Cheers,

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

Jun 27 '08 #7

P: n/a
Okonita via DBMonster.com wrote:
Dave,
Thanks for the example. I am new to shell scripting and Linux. I am
only able to deal with simple code at this time and your script
eample is a little bit complicated for me.

If you have someting simpler like I have in the initial post and
strictly kshell, I'll appreciate it. Later, I will have time to read
about "python" and learn about your code/script.

Please help if you can..
"Strictly" ksh? What's strictly? No grep? No awk? I assume not as you
used them in the original ;-) Anyway...

#!/bin/ksh

DBLIST="EMPLOYEE"

rm -f reorgscript.sql
touch reorgscript.sql

for DBNAME in $DBLIST; do
echo "CONNECT TO $DBNAME;" >reorgscript.sql
db2 "CONNECT TO $DBNAME"
db2 -x "CALL SYSPROC.REORGCHK_TB_STATS('T', 'ALL')" | grep "\*" | awk
'{print "REORG TABLE " $1 "." $2 ";"}' >reorgscript.sql
echo "CONNECT RESET;" >reorgscript.sql
done

db2 -tvf reorgscript.sql
That should do the trick. Again, I haven't bothered filtering out
SYSIBM tables, or running reorgs in background tasks. It just builds a
script of everything that needs reorg'ing and then runs it.
Cheers,

Dave.
Jun 27 '08 #8

P: n/a
Hi Dave,

Yep, your code did the trick for tables by outputing just those tables that I
know for a fact needed reorg.

Not to dampen the spirit, I did notice that when I run the same code for
indexes, I get tables as entries instead of indexes. Here is what I did with
the code for indexes:

DBLIST="FSFNDM01"
rm -f reorgscript.sql
touch reorgscript.sql
for DBNAME in $DBLIST; do
echo "CONNECT TO $DBNAME;" >reorgscript_ix.msg
db2 "CONNECT TO $DBNAME"
db2 -x "CALL SYSPROC.REORGCHK_IX_STATS('S', 'DB2ADMIN')" | grep "\*" | awk '
{print "REORG INDEX " $1 "." $2 ";"}' >Reorgscript_Indexes.sql
echo "CONNECT RESET;" >reorgscript_ix.msg
done

The result show tables being outputed:

REORG INDEX DB2ADMIN.PAYR;
REORG INDEX DB2ADMIN.PAYR;
REORG INDEX DB2ADMIN.PAYR;
REORG INDEX DB2ADMIN.ALERT;
REORG INDEX DB2ADMIN.ALERTS_HISTORY;
REORG INDEX DB2ADMIN.CASE;
REORG INDEX DB2ADMIN.CASE_HISTORY;
REORG INDEX DB2ADMIN.ELIGIBILITY;
REORG INDEX DB2ADMIN.ELIGIBILITY;
REORG INDEX DB2ADMIN.LOCATION_ENTITIES;
REORG INDEX DB2ADMIN.LOCATION_ENTITIES;
REORG INDEX DB2ADMIN.LOCATION_ENTITIES;
REORG INDEX DB2ADMIN.LOCATION_ENTITIES;
REORG INDEX DB2ADMIN.TRANS_EVENTS;
REORG INDEX DB2ADMIN.TRANS_EVENTS;
REORG INDEX DB2ADMIN.TRANS_EVENTS;
REORG INDEX DB2ADMIN.TRANS_EVENTS;
REORG INDEX DB2ADMIN.TRANS_EVENTS;
REORG INDEX DB2ADMIN.TRANS_EVENTS;
REORG INDEX DB2ADMIN.TRANS_EVENTS;

All indexes in our system starts with 'X', for example XP1TRANS_EVENTS for
index on TRANS_EVENTS.

How can I fix this?

Thanks

Dave Hughes wrote:
>Dave,
Thanks for the example. I am new to shell scripting and Linux. I am
[quoted text clipped - 6 lines]
>>
Please help if you can..

"Strictly" ksh? What's strictly? No grep? No awk? I assume not as you
used them in the original ;-) Anyway...

#!/bin/ksh

DBLIST="EMPLOYEE"

rm -f reorgscript.sql
touch reorgscript.sql

for DBNAME in $DBLIST; do
echo "CONNECT TO $DBNAME;" >reorgscript.sql
db2 "CONNECT TO $DBNAME"
db2 -x "CALL SYSPROC.REORGCHK_TB_STATS('T', 'ALL')" | grep "\*" | awk
'{print "REORG TABLE " $1 "." $2 ";"}' >reorgscript.sql
echo "CONNECT RESET;" >reorgscript.sql
done

db2 -tvf reorgscript.sql

That should do the trick. Again, I haven't bothered filtering out
SYSIBM tables, or running reorgs in background tasks. It just builds a
script of everything that needs reorg'ing and then runs it.

Cheers,

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

Jun 27 '08 #9

P: n/a
Ian
Okonita via DBMonster.com 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)
reorganization 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.
Jun 27 '08 #10

P: n/a
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)
reorganization 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

Jun 27 '08 #11

P: n/a
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]

Jun 27 '08 #12

P: n/a
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

Jun 27 '08 #13

P: n/a
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.

Jun 27 '08 #14

P: n/a
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_stats
REORG TABLE DB2ADMIN.ALERTS_PART;
REORG TABLE DB2ADMIN.CASE_CLOSURE_DETAILS;
REORG TABLE DB2ADMIN.CASE_PART_STAT;
REORG TABLE DB2ADMIN.INVS_BCKGRND_SUMRY;
REORG TABLE DB2ADMIN.MPHONE_FRST;
REORG TABLE DB2ADMIN.MPHONE_LST;
REORG TABLE DB2ADMIN.NICKNAME;
REORG TABLE DB2ADMIN.PROVIDER_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_stats:

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.ADDRESS ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ADDRESS ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ALERTS ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.APPROVAL ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.APPROVAL ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ASSIGNMENT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ASSIGNMENT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.ASSIGNMENT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_CLOSURE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_CLOSURE_DETAILS ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_MASTER ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_OTHR_CNTCT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_PART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_PART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_PART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CASE_PART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.CA_MAIN ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.COUNTY_PROVIDER_CROSS_REF ALLOW WRITE
ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.DOC_NARRATIVE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.DOC_NARRATIVE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISODE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISODE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISODE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISODE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISODE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISODE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISODE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISODE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISODE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISODE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.EPISODE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INTAKE_PART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INVESTIGATION ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INVESTIGATION ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INVESTIGATION_CNTC ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INVESTIGATION_CNTC ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.INVESTIGATION_CNTC ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.MEDICAL_PROFILE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.MEDICATIONS 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_WORKER_ROLE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PAN_TEXT_EVENT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PAN_TEXT_EVENT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PERSON_AKA ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PROVIDER_ORG ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PROVIDER_ORG ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PROVIDER_ORG ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PROVIDER_PART ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.PROVIDER_PART_STAT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.RELATIONSHIP ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.RELATIONSHIP ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.TICKLER ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE DB2ADMIN.TICKLER 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

Jun 27 '08 #15

P: n/a
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_stats
REORG TABLE DB2ADMIN.ALERTS_PART;
REORG TABLE DB2ADMIN.CASE_CLOSURE_DETAILS;
REORG TABLE DB2ADMIN.CASE_PART_STAT;
REORG TABLE DB2ADMIN.INVS_BCKGRND_SUMRY;
REORG TABLE DB2ADMIN.MPHONE_FRST;
REORG TABLE DB2ADMIN.MPHONE_LST;
REORG TABLE DB2ADMIN.NICKNAME;
REORG TABLE DB2ADMIN.PROVIDER_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_stats:
REORGCHK_IX_STATS 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_STATS) doesn't
work too well.


Ian

Jun 27 '08 #16

P: n/a
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_stats:

REORGCHK_IX_STATS 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_STATS) doesn't
work too well.

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

Jun 27 '08 #17

This discussion thread is closed

Replies have been disabled for this discussion.