diff -u /home/system/db2inst1/nya/bin/db2filter.py ./db2filter.py
--- /home/system/db2inst1/nya/bin/db2filter.py 2007-01-12
15:23:34.000000000 +0100
+++ ./db2filter.py 2007-04-14 02:31:58.000000000 +0200
@@ -26,24 +26,31 @@
def main():
ignore = [ "DB20000I" ]
+ single = False
try:
- opts, args = getopt.getopt(sys.argv[1:], "i:")
+ opts, args = getopt.getopt(sys.argv[1:], "i:s")
except getopt.GetoptError:
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(lines, ignore)
lines = ''
+ if (single):
+ parse_line(lastline, 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.000000000 +0100
+++ ./pre_upgrade.py 2007-04-14 07:30:54.000000000 +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.000000000 +0100
+++ ./upgrade.sh 2007-04-14 10:07:54.000000000 +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=${logdir}/${db}_${date}.log
+logfile=${logdir}${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_rebind() {
+
+ 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','URVAL') \
+ 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.ROUTINESCHEMA) || '.' ||
rtrim(z.ROUTINENAME) \
+ from syscat.packages y \
+ inner join syscat.routinedep x \
+ on (x.BSCHEMA, x.BNAME) = (y.PKGSCHEMA, y.PKGNAME) \
+ inner join syscat.routines z \
+ on (x.ROUTINESCHEMA, x.ROUTINENAME) =
(z.ROUTINESCHEMA, 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${username}" = "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${username}" = "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=${PIPESTATUS[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_rebind
+ 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_rebind
+ 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 "-----------------------------------------------------"
