Upgrade DB2 ESE from 9.7 to 10.5

# v10.5fp7_aix64_server_t.tar 10.5.0.7 media

# as user db2inst1, make sure no invalid objects exist

db2 connect to shinpbdb
db2 connect to shibordb
db2 connect to shinetdb
db2 "SELECT SUBSTR(OBJECTSCHEMA,1,10) OBJECTSCHEMA,
SUBSTR(OBJECTNAME,1,30) OBJECTNAME, 
SUBSTR(ROUTINENAME, 1,30) ROUTINENAME, 
OBJECTTYPE, INVALIDATE_TIME, 
LAST_REGEN_TIME 
FROM SYSCAT.INVALIDOBJECTS"

db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(NULL, NULL, NULL)"

# as user root

cd /install
tar -xvf v10.5fp7_aix64_server_t.tar
cd server_t/
./db2prereqcheck -s -v 10.5.0.7

# recommended minimum AIX TL and SP
# 7100-01-10
# 7100-02-05
# 7100-03-03
# 7100-04-00
#
# /tmp at least 2GB
# minimum “XL C/C++ Runtime” level: “12.1.0.0” , lslpp -l | grep -i xlc

# backup db as user db2inst1

db2 list db directory
db2 backup db xxx compress
db2ckbkp [backup.image]

# backup current configuration information as user db2inst1

mkdir /home/db2inst1/`date +%Y%m%d`
db2support /home/db2inst1/`date +%Y%m%d` -d [db_name] -cl 0
# if there are more than one db that you want to run db2support against, you might want to rename db2support.zip before running it the 2nd time

The -cl 0 parameter collects the database system catalog, database and database manager configuration parameters settings, DB2 registry variables settings. The information collected is stored in the db2support.zip compressed zip file under the output directory. A summary report in HTML format is included. In the db2supp_opt.zip file that is also included, you should check the optimizer.log file to verify that the collection of information was performed successfully.

# backup packages, audit policies and external routines

db2 connect to [dbname]
db2 LIST PACKAGES FOR ALL SHOW DETAIL > /home/db2inst1/`date +%Y%m%d`/all_pckg_before_upgrade.dbname.txt
db2 get db cfg for dbname show detail > /home/db2inst1/`date +%Y%m%d`/db_cfg_before_upgrade.dbname.txt

# backup dbm cfg, db cfg and db2set variables

db2 get dbm cfg > /home/db2inst1/`date +%Y%m%d`/dbm_cfg_before_upgrade.txt
db2set -all > /home/db2inst1/`date +%Y%m%d`/db2set_before_upgrade.txt
cp -R $INSTHOME/sqllib/function /home/db2inst1/`date +%Y%m%d`/routine_backup
db2audit describe > /home/db2inst1/`date +%Y%m%d`/audit_db2ins1.cfg
set | grep DB2 > /home/db2inst1/`date +%Y%m%d`/env_variables_before_upgrade.txt

# check free space in all container paths, log file space

db2 connect to [dbname]

db2 "SELECT SUBSTR(TBSP_NAME,1,15) NAME, MEMBER, TBSP_TYPE TYPE,
TBSP_AUTO_RESIZE_ENABLED AUTO_RESIZE, TBSP_TOTAL_PAGES TOTAL_PGS,
TBSP_USED_PAGES USED_PGS, TBSP_FREE_PAGES FREE_PGS,
TBSP_PAGE_SIZE PG_SZ, TBSP_EXTENT_SIZE EXTENT_SZ,
TBSP_PREFETCH_SIZE PREFETCH_SZ
FROM TABLE(MON_GET_TABLESPACE(NULL,-2)) AS T
WHERE TBSP_CONTENT_TYPE IN ('ANY','SYSTEMP')"

db2pd -d dbname -tablespace # if tablespace containers for SYSCATSPACE and TEMPSPACE1 are PATH, we can proceed

# you may need to enable archive log for db

mkdir -p /home/db2inst1/db2log/shibordb/
mkdir -p /home/db2inst1/db2log/shinetdb/
db2 UPDATE DB CFG FOR shibordb using LOGARCHMETH1 DISK:/home/db2inst1/db2log/shibordb
db2 UPDATE DB CFG FOR shinetdb using LOGARCHMETH1 DISK:/home/db2inst1/db2log/shinetdb
db2 UPDATE DB CFG FOR shinpbdb using LOGSECOND -1
db2 UPDATE DB CFG FOR shibordb using LOGSECOND -1
db2 UPDATE DB CFG FOR shinetdb using LOGSECOND -1

# check dbm diaglevel is 3

db2 get dbm cfg | grep DIAGLEVEL

# Taking a DB2 server offline for upgrade

db2licd -end
db2 list applications
db2 force applications all
db2stop

# install 10.5 software as user root

cd /install/server_t
./db2_install
# input yes, install into default directory
# input SERVER, install ESE
# input no, don't install pureScale

# upgrade instance as user root

cd /opt/IBM/db2/V10.5/instance
./db2iupgrade -u db2fenc1 db2inst1

# upgrade databases as user db2inst1

db2start
db2 UPGRADE DATABASE shinpbdb
db2 UPGRADE DATABASE shibordb
db2 UPGRADE DATABASE shinetdb
db2 activate db shinpbdb
db2 activate db shibordb
db2 activate db shinetdb

db2 update db cfg for shinpbdb using STMTHEAP AUTOMATIC
db2 update db cfg for shibordb using STMTHEAP AUTOMATIC
db2 update db cfg for shinetdb using STMTHEAP AUTOMATIC

# restore secondary logfile space

db2 UPDATE DB CFG FOR shinpbdb using LOGSECOND 20
db2 UPDATE DB CFG FOR shibordb using LOGSECOND 20
db2 UPDATE DB CFG FOR shinetdb using LOGSECOND 20

db2 deactivate db shinpbdb
db2 deactivate db shibordb
db2 deactivate db shinetdb
db2 activate db shinpbdb
db2 activate db shibordb
db2 activate db shinetdb

# rebind all invalid packages in each database

db2 connect to shinpbdb
db2 connect to shibordb
db2 connect to shinetdb
db2 "SELECT SUBSTR(OBJECTSCHEMA,1,10) OBJECTSCHEMA,
SUBSTR(OBJECTNAME,1,30) OBJECTNAME, 
SUBSTR(ROUTINENAME, 1,30) ROUTINENAME, 
OBJECTTYPE, INVALIDATE_TIME, 
LAST_REGEN_TIME 
FROM SYSCAT.INVALIDOBJECTS"

db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(NULL, NULL, NULL)"

db2rbind shinpbdb -l rebind.logfile.shinpbdb all
db2rbind shibordb -l rebind.logfile.shibordb all
db2rbind shinetdb -l rebind.logfile.shinetdb all

# run stats

./runstats.sh shinpbdb
./runstats.sh shibordb
./runstats.sh shinetdb

# update license info as user db2inst1

jar -xvf ESE_-_PVU_OPTION_-_QSG_ACT10.5.0..zip
cd /tmp/ese_c/db2/license/
db2licm -a db2ese_c.lic
db2licm -l

# update AUTHENTICATION parameter

db2 update dbm cfg using AUTHENTICATION SERVER_ENCRYPT

# revoke public privilege

db2 connect to shinpbdb
db2 "select * from syscat.dbauth where grantee='PUBLIC'"
db2 "grant connect, createtab, bindadd, implicit_schema on database to user etluser"
db2 revoke connect, createtab, bindadd, implicit_schema on database from public

db2 connect to shinetdb
db2 "grant connect, createtab, bindadd, implicit_schema on database to user shidba"
db2 revoke connect, createtab, bindadd, implicit_schema on database from public

# Optionaly, upgrade db2 clients to 10.5
# check JDBC driver and database version compatibility

# How to find IBM Data Server Driver for JDBC and SQLJ version
java com.ibm.db2.jcc.DB2Jcc -version

# Reference:

http://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_c0024897.html
JDBC driver and database version compatibility

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_cjvintro.html
Supported drivers for JDBC and SQLJ

http://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_rjv00010.html
DB2 for Linux, UNIX, and Windows and IBM Data Server Driver for JDBC and SQLJ levels

http://www-01.ibm.com/support/docview.wss?&uid=swg21363866
DB2 JDBC Driver Versions and Downloads

http://www-01.ibm.com/support/docview.wss?uid=swg21633449
Upgrading to DB2 Version 10.5 roadmap

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.qb.upgrade.doc/doc/c0023662.html
Upgrade to DB2 Version 10.5

http://www-01.ibm.com/support/docview.wss?uid=swg21165448
Known issues for DB2 for Linux, UNIX and Windows on AIX 5.3, 6.1, 7.1,and 7.2

http://www-01.ibm.com/support/docview.wss?uid=swg27038033#111Ent
System requirements for IBM DB2 for Linux, UNIX, and Windows

http://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.qb.client.doc/doc/r0009731.htmlSupported combinations of clients, drivers and server levels

http://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.qb.upgrade.doc/doc/c0007192.html
Best practices for upgrading DB2 servers

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.qb.upgrade.doc/doc/c0023410.html
Upgrade database applications, routines, tools, and scripts

http://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.qb.upgrade.doc/doc/r0022380.html
DB2 server behavior changes

http://scn.sap.com/community/db2-for-linux-unix-windows/blog/2016/05/17/db2-upgrade-from-97-to-105
DB2 upgrade from 9.7 to 10.5

https://www.ibm.com/support/knowledgecenter/SSTRGZ_10.2.1/com.ibm.swg.im.iis.db.repl.sqlmig.doc/topics/iiyrsmig10.5.html
Migrating to SQL Replication Version 10.5 (Linux, UNIX, Windows)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s