Offline backup of all databases
Backup configuration of Database manager and all Databases :
db2 get dbm cfg > dbm_cfg.txt
db2set -all > db2set_all.txt
db2licm -l > db2licm.txt
db2level > db2level.txt
db2cfexp directory.txt backup
db2greg -dump -v > db2greg.txt
for db in `db2 list db directory | grep -p Indirect | grep alias | awk '{print $NF}'`; do db2look -d ${db} -td @ -a -e -x -l -o ddl_${db}.txt; db2 get db cfg for ${db} > dbcfg_${db}.txt; done
Check invalid objects :
db2 "select substr(objectschema,1,20) SCHEMA, substr(objectname,1,70) OBJECT, case objecttype when 'B' then 'Trigger' when 'F' then 'Routine' when 'R' then 'User datatype' when 'V' then 'View' when 'v' then 'Global var' when 'y' then 'Row perm.' when 2 then 'Col. mask' when 3 then 'Usage list' end as Type from syscat.invalidobjects with ur"
Take full backup of all databases
Stop all instances and associated services :
su - <instance> db2stop initctl stop db2fmcd
Rotate db2diag log :
db2diag -A
Stop DAS (if exists & running) :
su - <dasuser> db2admin stop
Stop Fault monitor :
<DB2DIR>/bin/db2fm -i <DB2INSTANCE> -D
Clean IPC :
<DB2INSTANCEHOME>/sqllib/bin/ipclean
Check if there aren't any libraries still loaded :
<INSTALL_PATH>/instance/db2prechk -b -d;echo $? #AIX /usr/bin/genld -l | grep -p db2 #Linux lsof +D <DB2DIR>
Install the fixpack :
</path/to/fixpack>/installFixpack -b <DB2DIR>
Update the DB catalogs (all Indirect databases !) :
db2updv9 -d <DBNAME> (9.1) db2updv97 -d <DBNAME> (9.7) db2updv10 -d <DBNAME> (10.x)
In case of errors, rebind the packages :
db2 connect to databasename db2 bind sqllib/bnd/@db2cli.lst blocking all grant public sqlerror continue db2 bind sqllib/bnd/db2schema.bnd blocking all grant public sqlerror continue db2 bind sqllib/bnd/@db2ubind.lst blocking all sqlerror continue grant public db2 commit db2 terminate #or db2rbind <DBNAME> -l </path/to/logfile> all
Check for inconsistent System catalog :
db2 "SELECT TABSCHEMA, TABNAME
FROM SYSCAT.DATAPARTITIONS
WHERE INDEX_TBSPACEID = 0 AND
TBSPACEID IS NOT NULL AND
TABSCHEMA NOT IN ('SYSIBM', 'SYSCAT', 'SYSSTAT',
'SYSIBMADM', 'SYSCATV82')
GROUP BY (TABSCHEMA, TABNAME)"
# run db2updv97 -d <DBNAME> -i
Check again invalid objects and try to recompile them :
db2 "select substr(objectschema,1,20) SCHEMA, substr(objectname,1,70) OBJECT, case objecttype when 'B' then 'Trigger' when 'F' then 'Routine' when 'R' then 'User datatype' when 'V' then 'View' when 'v' then 'Global var' when 'y' then 'Row perm.' when 2 then 'Col. mask' when 3 then 'Usage list' end as Type from syscat.invalidobjects with ur"
Recompile invalid objects :
db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()"
Post-fixpack script :
#!/bin/ksh
echo "[`date`] POST-FIXPACK ACTIONS STARTED !"
# BIND & RECOMPILE
for db in `db2 list db directory | grep -p Indirect | grep alias | awk '{print $NF}'`
do
echo "[`date`] UPDATING DB ${db} TO LATEST FIXPACK :"
db2updv97 -d ${db}
echo "------------------------"
echo "[`date`] REBINDING DB ${db} :"
db2 connect to ${db}
db2 bind ~/sqllib/bnd/@db2cli.lst blocking all grant public sqlerror continue
db2 bind ~/sqllib/bnd/db2schema.bnd blocking all grant public sqlerror continue
db2 bind ~/sqllib/bnd/@db2ubind.lst blocking all sqlerror continue grant public
db2 commit
db2 terminate
db2 connect to ${db}
db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()"
db2 "select substr(objectschema,1,20) SCHEMA, substr(objectname,1,70) OBJECT, case objecttype when 'B' then 'Trigger' when 'F' then 'Routine' when 'R' then 'User datatype' when 'V' then 'View' when 'v' then 'Global var' when 'y' then 'Row perm.' when 2 then 'Col. mask' when 3 then 'Usage list' end as Type from syscat.invalidobjects with ur"
db2 terminate
db2rbind ${db} -l ./rebind_${db}.log all
echo "------------------------"
done
echo "[`date`] POST-FIXPACK ACTIONS FINISHED !"