Okonita via DBMonster.com wrote:
#!/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
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(s ql)[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(ta ble)
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(s ql)[0]
Cheers,
Dave.
