diff

[Lennart]

diff -u /home/system/db2inst1/nya/bin/db2filter.py ./db2filter.py
--- /home/system/db2inst1/nya/bin/db2filter.py 2007-01-12
15:23:34.000000 000 +0100
+++ ./db2filter.py 2007-04-14 02:31:58.000000 000 +0200
@@ -26,24 +26,31 @@

def main():
ignore = [ "DB20000I" ]
+ single = False
try:
- opts, args = getopt.getopt(s ys.argv[1:], "i:")
+ opts, args = getopt.getopt(s ys.argv[1:], "i:s")
except getopt.GetoptEr ror:
sys.exit(-1)
for o, a in opts:
if (o == "-i"):
args = a.split(',')
ignore += args
+ if (o == "-s"):
+ single = True

inf = sys.stdin
lines = ''
+ lastline = ''
for line in inf:
+ lastline = line
m = re.match('^\S*$ ', line)
if (m == None):
lines += line
else:
parse_line(line s, ignore)
lines = ''
+ if (single):
+ parse_line(last line, ignore)

if __name__ == "__main__":
main()
diff -u /home/system/db2inst1/nya/bin/pre_upgrade.py ./pre_upgrade.py
--- /home/system/db2inst1/nya/bin/pre_upgrade.py 2007-01-12
15:23:34.000000 000 +0100
+++ ./pre_upgrade.py 2007-04-14 07:30:54.000000 000 +0200
@@ -136,7 +136,8 @@
print line

if (current_state < 2):
- print """values 'Upgrade aborted. Database version already >=
%s' @
+ print """
+ values 'Upgrade aborted. Database version already >= %s' @
-- generate error, exit(4)
drop index sysibm.XXX @
""" % (cv)
diff -u /home/system/db2inst1/nya/bin/upgrade.sh ./upgrade.sh
--- /home/system/db2inst1/nya/bin/upgrade.sh 2007-03-12
12:08:08.000000 000 +0100
+++ ./upgrade.sh 2007-04-14 10:07:54.000000 000 +0200
@@ -1,7 +1,6 @@
#!/bin/sh

-# source helpers
-. db_helpers
+declare -i rc

############### ############### ###############
# Parse cmdline
@@ -50,7 +49,7 @@
mkdir -p ${logdir}
db=`echo ${DB} | tr a-z A-Z`
date=`date "+%Y-%m-%d_%H:%M:%S"`
-logfile=${logdi r}/${db}_${date}.l og
+logfile=${logd ir}${db}_${date }.log

export PATH=${bindir}: ${PATH}

@@ -74,7 +73,7 @@
* upgrade $db aborted `date +%Y%m%d%H%M%S`
*************** *************** *************** *******
EOF
- db2 terminate
+ db2 terminate /dev/null 2>&1;
exit 1
}

@@ -86,7 +85,7 @@
* upgrade $db finished `date +%Y%m%d%H%M%S`
*************** *************** *************** *******
EOF
- db2 terminate
+ db2 terminate /dev/null 2>&1;
exit 0
}

@@ -105,6 +104,55 @@
abort "ERROR: wrong usage";
}

+runstats_and_r ebind() {
+
+ cnt=0
+ echo "---------------------------------------------" | tee -a
${logfile}
+ echo "Doing runstats for tables with old statistics" | tee -a
${logfile}
+ echo "---------------------------------------------" | tee -a
${logfile}
+
+ for t in `db2 -x "select rtrim(tabschema ) || '.' || tabname \
+ from syscat.indexes i \
+ where tabschema in ('NYA','STUDERA ','HUBBLE','URV AL') \
+ and exists ( \
+ select 1 from syscat.tables t \
+ where (i.tabschema, i.tabname) = (t.tabschema, t.tabname) \
+ and t.type in ('S','T') \
+ ) \
+ and (stats_time + 24 hours < current timestamp or \
+ nleaf = -1 or nlevels = -1)"`; do
+ db2 -s -l ${logfile} "runstats on table $t and indexes
all" | ./db2filter.py -s 2>&1
+ cnt=`expr $cnt + 1`
+ done
+ echo ""
+ echo ""
+ echo "Updated statistics for $cnt tables" | tee -a ${logfile}
+ echo ""
+
+ cnt=0
+ echo "-------------------------------------" | tee -a ${logfile}
+ echo "Rebinding invalid procedures/packages" | tee -a ${logfile}
+ echo "-------------------------------------" | tee -a ${logfile}
+
+ for p in `db2 -x "select \
+ rtrim(z.ROUTINE SCHEMA) || '.' ||
rtrim(z.ROUTINE NAME) \
+ from syscat.packages y \
+ inner join syscat.routined ep x \
+ on (x.BSCHEMA, x.BNAME) = (y.PKGSCHEMA, y.PKGNAME) \
+ inner join syscat.routines z \
+ on (x.ROUTINESCHEM A, x.ROUTINENAME) =
(z.ROUTINESCHEM A, z.SPECIFICNAME) \
+ where y.valid <'Y' \
+ or z.valid <'Y'"`; do
+ db2 -s -l ${logfile} "call
sysproc.rebind_ routine_package ('P','$p','ANY' )" | ./db2filter.py -i
"Return Status = 0" 2>&1
+ cnt=`expr $cnt + 1`
+ done
+ echo ""
+ echo ""
+ echo "Rebinded $cnt procedures/packages" | tee -a ${logfile}
+ echo ""
+}
+
+
############### ############### ###############
# Validation of input
############### ############### ###############
@@ -116,6 +164,10 @@
[ "x${usernam e}" = "x" ] && usage
[ "x${passwd} " = "x" ] && usage

+echo "-----------------" | tee -a ${logfile}
+echo "Preparing upgrade" | tee -a ${logfile}
+echo "-----------------" | tee -a ${logfile}
+
db=`echo ${DB} | tr a-z A-Z`
if [ "x${usernam e}" = "x" ]; then
db2 connect to ${db} /dev/null 2>&1;
@@ -127,6 +179,7 @@
abort "ERROR: Unable to connect to ${db}"
fi

+
# create function to cmp versions
pushd ${basedir}/function/ /dev/null 2>&1;
dos2unix GET_DB_VERSION. sql /dev/null 2>&1;
@@ -165,25 +218,40 @@
pushd /tmp /dev/null 2>&1
dos2unix current_upgrade .sql /dev/null 2>&1;
popd /dev/null 2>&1
-db2 -s +c -l ${logfile} -td@ -f /tmp/current_upgrade .sql
-rc=$?

-echo ""
-echo "-----------------------------------------------------"
+echo "" | tee -a ${logfile}
+echo "-----------------" | tee -a ${logfile}
+echo "Executing upgrade" | tee -a ${logfile}
+echo "-----------------" | tee -a ${logfile}
+
+db2 -x -s +c -l ${logfile} -td@ -f /tmp/current_upgrade .sql |
../db2filter.py -i "Return Status = 0"
+rc=${PIPESTATU S[0]}
+
+echo "" | tee -a ${logfile}

if [ $rc -ge 4 ]; then
- echo "ERROR: Transactions where rolled back due to error"
- echo " db2 exit with $rc, see ${logfile} for details"
- db2 rollback /dev/null 2>&1;
+ echo "-----------------------------------------------------" | tee
-a ${logfile}
+ echo "ERROR: Transactions where rolled back due to error" | tee
-a ${logfile}
+ echo " db2 exit with $rc, see ${logfile} for details" | tee
-a ${logfile}
+ echo "-----------------------------------------------------" | tee
-a ${logfile}
+ db2 rollback /dev/null 2>&1;
+ abort
elif [ $rc -gt 0 ]; then
- echo "INFO: There where warnings but all transactions where
committed succesfully."
- echo " db2 exit with $rc, see ${logfile} for details"
db2 commit /dev/null 2>&1;
+ runstats_and_re bind
+ echo "-----------------------------------------------------" | tee
-a ${logfile}
+ echo "INFO: There where warnings but all transactions where
committed succesfully." | tee -a ${logfile}
+ echo " db2 exit with $rc, see ${logfile} for details" | tee
-a ${logfile}
+ echo "-----------------------------------------------------" | tee
-a ${logfile}
+ success
else
- echo "INFO: All transactions where committed succesfully."
db2 commit /dev/null 2>&1;
+ runstats_and_re bind
+ echo "-----------------------------------------------------" | tee
-a ${logfile}
+ echo "INFO: All transactions where committed succesfully." | tee
-a ${logfile}
+ echo "-----------------------------------------------------" | tee
-a ${logfile}
+ success
fi

-db2 connect reset /dev/null 2>&1;
+#db2 connect reset /dev/null 2>&1;

-echo "-----------------------------------------------------"

[Lennart]

On Apr 14, 11:26 am, Lennart <erik.lennart.j [e-mail] mwrote:

Sorry, completely wrong address

/Lennart

[...]