DB2 Upgrade/Migration

0) Install the new version on the system, in a new home path:

As root:

$INSTALL_PATH/server/db2_install
[root@appro01-rj server]# pwd
/repo/unpacked/DB2/server
[root@appro01-rj server]# ./db2_install 
DBI1324W  Support of the db2_install command is deprecated. For
      more information, see the DB2 Information Center.


 
Default directory for installation of products - /opt/ibm/db2/V10.5

***********************************************************
Install into default directory (/opt/ibm/db2/V10.5) ? [yes/no] 
no                 
Enter the full path of the base installation directory:

------------------------------------------------
/opt/ibm/BPM/v10.5/
 
 
Specify one of the following keywords to install DB2 products.

  SERVER 
  CONSV 
  EXP 
  CLIENT 
  RTCL 
 
Enter "help" to redisplay product names.

Enter "quit" to exit.

***********************************************************
SERVER
***********************************************************
Do you want to install the DB2 pureScale Feature? [yes/no] 
no
DB2 installation is being initialized.

 Total number of tasks to be performed: 48 

1) Backup configuration and all databases :

2) Check free space in syscat & sys temp tablespaces :

3) Make sure enough log space is available :

4) Convert Type-1 indexes to Type-2 : Identify Type-1 indexes for untyped tables and generate script :

<DB2INSTALL>/utilities/db2IdentifyType1 -d <DB> -o t1indexes_<DB>.sql

db2 "SELECT DISTINCT H.ROOT_SCHEMA, H.ROOT_NAME, 
     T.TBSPACEID, T.TABLEID
FROM SYSCAT.TABLES T, SYSCAT.INDEXES I, 
     SYSCAT.HIERARCHIES H, SYSCAT.NAMEMAPPINGS N
WHERE T.TYPE = 'H' AND T.TABSCHEMA = I.TABSCHEMA 
     AND T.TABNAME = I.TABNAME AND H.METATYPE='U' 
     AND H.ROOT_SCHEMA=N.LOGICAL_SCHEMA AND H.ROOT_NAME=N.LOGICAL_NAME
     AND T.TABSCHEMA=N.IMPL_SCHEMA AND T.TABNAME=N.IMPL_NAME"

Inspect typed tables :

db2 INSPECT CHECK DATABASE RESULTS KEEP inspect_<DBNAME>.log
db2inspf $INSTHOME/sqllib/db2dump/inspect_<DBNAME>.log inspect_<DBNAME>.out

# look in the inspect_<DBNAME>.out for :
Data phase start. Object: 4  Tablespace: 3
The index type is 1 for this table.

Add reorg indexes for each typed table into t1indexes_<DB>.sql :

REORG INDEXES ALL FOR TABLE table-name ALLOW WRITE ACCESS CONVERT

Run the script to convert all Type-1 indexes :

db2 -tvf t1indexes_<DB>.sql | tee -a t1indexes_<DB>.out

5) Verify DB for upgrade :

<DIRIMG>/db2/<OS>/utilities/db2ckupgrade/bin/db2ckupgrade <DB> -l db2ckupgrade_<DB>.log

• SQL1498W - Type-1 indexes must be converted.
• SQL0473N - User defined data types with system built-in data types must be dropped & recreated.
• SQL0553N - User created db objects in restricted schema names must be dropped & recreated.
• ADM4100W - All unfenced routines must be altered to fenced (alter_unfenced_database-name.lst)
• ADM4102W - NULL identifiers for column names, routine parameter names, or variable names (must be qualified or quoted in SQL statements)
• ADM4103W - Workload connection contains asterisks
• ADM4104E - XML Extender is enabled - must be first disabled and completely removed before migration.
• Check and fix any invalid flavor fields on SQLSPCS files using the fixtbspflvr tool. Details about this tool can be obtained from http://www.ibm.com/support.

6) Create full offline backup of all upgraded DBs

7) Upgrade the instance :

<NEWDB2DIR>/instance/db2iupgrade [ -u <fencedID> ] <instance> [ -k ]
# -k to keep the pre-upgrade edition of instance 

8) Upgrade HACMP Standby node Global registry with upgraded instance information :

<NEWDB2DIR>/bin/db2greg -delinstrec instancename=<old_instance_name>
<NEWDB2DIR>/instance/db2iset -a <upgradedinstance>

9) Start DB2 and veriy Diag log for errors

10) Upgrade databases under the instance :

db2 upgrade db <DB>

• SQL1704N - Find the error and take appropriate action according to the reason code.
• SQL1243W - Drop or rename SYSTOOLS.DB2LOOK_INFO.
• SQL1499W, ADM4100W (nfy) - External routines were redefined to FENCED NOT THREADSAFE. Use script alter_unfenced_database-name.db2 to change them to UNFENCED THREADSAFE.
• SQL1499W, ADM4101W (nfy) - System catalog tables need to collect statistics after upgrade.
• SQL1499W, ADM4102W (nfy) - NULL must be qualified or quoted in SQL statements.
• SQL1499W, ADM4106W (nfy) - DB2 XML Extender feature was not disabled.
• SQL1499W, ADM4105W (nfy) - Create new MQ Functions for XML with “enable_MQFunctions -xml”
• SQL1499W, ADM9516W (nfy) - Invalid indexes. Set indexrec TO restart, and RESTART DATABASE to rebuild the indexes.
• SQL0473N, SQL1700N - User defined data-types with restricted names. Database migration must be reversed to previous version and all those objects dropped/renamed.

11) Verify DB configuration and packages

12) Post-upgrade tasks :

• Rebind all packages

db2rbind <DB> -l rebind_<DB>.log all

• Migrate explain tables if created

db2exmig -d <DB> -e <SCHEMA>

• Convert XML Storage objects to 9.7 schemas

# list XML Storage objects
db2 "SELECT TABSCHEMA, TABNAME FROM SYSIBMADM.ADMINTABINFO 
WHERE XML_RECORD_TYPE=1 GROUP BY (TABSCHEMA, TABNAME)"

# recreate the object with
ADMIN_MOVE_TABLE()

• Verify temporary tablespaces
• Re-create Write-to-table event monitors :

#1. identify the event monitors
db2 "SELECT TABSCHEMA, TABNAME FROM SYSCAT.EVENTTABLES"

#2. rename or drop the table
db2 "rename/drop <TABLE> [to <NEWNAME>]"

#3. drop event monitor
db2 "DROP EVENT MONITOR"

#4. Re-create write-to-table event monitor
<TODO> - INSERT LINK !!!!

#5. Enable the event monitor
db2 "SET EVENT MONITOR <WRITE-TO-TABLE EVENT> 1"

1) Backup configuration and all databases :

db2support <directory> -d <DBNAME> -cl 0
db2 list packages for all show detail > packages.txt
db2audit describe > db2audit.txt
cp -R $INSTHOME/sqllib/function $INSTHOME/routine_backup
cp -r sqllib sqllib_preupgrade
db2set -all > db2set_all.txt
db2 get dbm cfg > dbmcfg.txt
db2 get db cfg for <DBNAME> > dbcfg_<DBNAME>.txt
db2 list db directory > db_dir.txt
db2 list node directory > node_dir.txt
db2cfexp <FILENAME>
db2look -d <DBNAME> -a -e -l -x -o ddl_<DBNAME>.sql
set | grep -i db2 > db2_env.txt
cp <nodelock> <nodelock>.bckp
cp <globalregistry> <globalregistry>.bckp

# instance & general config
db2set -all > db2set_all.txt
db2 get dbm cfg > dbmcfg.txt
env | grep -i db2 > env_db2.txt
db2audit describe > db2audit.txt
db2 list db directory > db_dir.txt
db2 list node directory > node_dir.txt
db2cfexp db2cfexp.txt backup
cp -r ~/sqllib/function routine_backup
db2greg -dump -v > db2greg_dump.txt
db2licm -l > db2licm.txt
db2level > db2level.txt

# db config
for db in `db2 list db directory | grep -p Indirect | grep alias | awk '{print $NF}'`; do db2 connect to ${db}; db2 get db cfg > dbcfg_${db}.txt; db2 list packages for all > packages_${db}.txt; db2 list packages for all show detail > packages_${db}_det.txt; db2 terminate; db2look -d ${db} -a -x -e -l -o ddl_${db}.sql; db2support . -d ${db} -cl 0; mv db2support.zip db2support_${db}.zip; db2 terminate; done

# license & db2 Global registry
cp /var/db2/global.reg .
cp nodelock nodelock.bckp

2) Check free space in syscat & sys temp tablespaces :

3) Make sure enough log space is available :

4) Convert Type-1 indexes to Type-2 :

<DB2INSTALL>/utilities/db2IdentifyType1 -d <DB> -o t1indexes_<DB>.sql
db2 -tvf t1indexes_<DB>.sql

5) Gather preupgrade data collection :

db2fodc -preupgrade -db <DBNAME>
db2support -preupgrade -d <DBNAME>

6) Check upgrade pre-requisities (from installation directory):

db2ckupgrade <DB> -l db2ckupgrade_<DB>.log

If DB is in rollforward state :

db2 "rollforward db <DB> to end of logs and stop"

If DB is in backup pending state :

db2 backup db <DB>

If Tablespace <TABLESPACE> is in QUIESCED_UPDATE , Member “0” state :

A) Identify which table is Quiescing the Tablespace :

db2 "select substr(st.tabschema,1,10) as tabschema,substr(st.tabname,1,10) as tabname, substr(sn.QUIESCER_STATE,1,10) as QUIESCER_STATE from syscat.tables st, sysibmadm.snaptbsp_quiescer sn where st.tableid=sn.quiescer_obj_id and st.tbspaceid=sn.quiescer_ts_id"

B) Reset the quiesce of the table :

db2 "quiesce tablespaces for table <TABLE> reset"

7) Run the db2ckupgrade again and db2stop :

db2ckupgrade <DB> -l db2ckupgrade_<DB>.log
db2stop
ipclean

8) As root install the DB2 10.x to new location

9) Upgrade instance :

db2iupgrade -u <FENCEDID> <INSTANCE>

10) Update standby node Global registry :

<NEWPATH>/bin/db2greg -delinstrec instancename=<OLDINSTANCE>
<NEWPATH>/instance/db2iset -a <UPGRADEDINSTANCE>

11) Start the instance

db2start

12) Upgrade all databases :

db2 upgrade db <DB>

13) Check invalid objects & Rebind packages :

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 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()"

db2rbind <DB> -l rebind_<DB>.txt all

14) Check for corrupted Triggers :

db2 "select name, schema, lib_id from sysibm.systriggers where lib_id not in (select lib_id from sysibm.syscodeproperties)"

15) Execute application tests and monitor diag log for errors

16) Remove previous DB2 installation copy if all OK

17) Add proper license for new DB2 level :

db2licm -a <licence>.lic

1) Backup DAD & DTD configuration :

db2 "EXPORT TO dtdfiles.del OF del LOBS TO dir-name
     MODIFIED BY lobsinsepfiles
     SELECT CONTENT FROM DB2XML.DTD_REF"
     
db2 "EXPORT TO dadfiles.del OF del LOBS TO dir-name
     MODIFIED BY lobsinsepfiles
     SELECT DAD FROM DB2XML.XML_USAGE"

2) Disable XML columns for XML Extender :

# list the columns
db2 "SELECT TABLE_SCHEMA, TABLE_NAME, COL_NAME 
    FROM DB2XML.XML_USAGE
    WHERE NOT TABLE_SCHEMA='DXX_COLL'"

# disable it
dxxadm disable_column db_name tab_name column_name
dxxDisableColumn() stored procedure.

3) Drop all references to XMLVARCHAR, XMLCLOB, and XMLFILE :

# list the references
db2 "SELECT TABSCHEMA, TABNAME, COLNAME
    FROM SYSCAT.COLUMNS 
    WHERE TYPESCHEMA='DB2XML' AND NOT TABSCHEMA='DB2XML'"
    
# drop the objects

4) Disable the XML Extender feature on database :

dxxadm disable_db <DB>

5) Uninstall the XML Extender feature :

<DB2DIR>/install/db2_deinstall -F XML_EXTENDER 

6) Update the instance to remove the uninstalled feature :

db2iupdate <instance>

7) Proceed with instance upgrade to DB2 9.7