====== DB2 Upgrade/Migration ======

===== Upgrade Server to DB2 9.7 (from 9.x, 8.x) =====

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

As root:

<sxh bash>

$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 
</sxh>


**1) Backup configuration and all databases :**
<sxh bash>
</sxh>

**2) Check free space in syscat & sys temp tablespaces :**
<sxh bash>
</sxh>

**3) Make sure enough log space is available :**
<sxh bash>
</sxh>

**4) Convert Type-1 indexes to Type-2 :**
Identify Type-1 indexes for untyped tables and generate script :
<sxh bash>
<DB2INSTALL>/utilities/db2IdentifyType1 -d <DB> -o t1indexes_<DB>.sql
</sxh>
Identify typed tables for Type-1 indexes :
<sxh bash>
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"
</sxh>

Inspect typed tables :
<sxh bash>
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.
</sxh>

Add reorg indexes for each typed table into t1indexes_<DB>.sql :
<sxh bash>
REORG INDEXES ALL FOR TABLE table-name ALLOW WRITE ACCESS CONVERT
</sxh>

Run the script to convert all Type-1 indexes : 
<sxh bash>
db2 -tvf t1indexes_<DB>.sql | tee -a t1indexes_<DB>.out
</sxh>

**5) Verify DB for upgrade :**
<sxh bash>
<DIRIMG>/db2/<OS>/utilities/db2ckupgrade/bin/db2ckupgrade <DB> -l db2ckupgrade_<DB>.log
</sxh>

  * 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 :**
<sxh bash>
<NEWDB2DIR>/instance/db2iupgrade [ -u <fencedID> ] <instance> [ -k ]
# -k to keep the pre-upgrade edition of instance 
</sxh>

**8) Upgrade HACMP Standby node Global registry with upgraded instance information :**
<sxh bash>
<NEWDB2DIR>/bin/db2greg -delinstrec instancename=<old_instance_name>
<NEWDB2DIR>/instance/db2iset -a <upgradedinstance>
</sxh>

**9) Start DB2 and veriy Diag log for errors**

**10) Upgrade databases under the instance :**
<sxh bash>
db2 upgrade db <DB>
</sxh>

  * 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
<sxh bash>
db2rbind <DB> -l rebind_<DB>.log all
</sxh>
  * Migrate explain tables if created
<sxh bash>
db2exmig -d <DB> -e <SCHEMA>
</sxh>
  * Convert XML Storage objects to 9.7 schemas
<sxh bash>
# 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()
</sxh>
  * Verify temporary tablespaces
  * Re-create Write-to-table event monitors :
<sxh bash>
#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"
</sxh>

===== Upgrade Server to DB2 10.x (from 9.5, 9.7) =====
**1) Backup configuration and all databases :**
<sxh bash>
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
</sxh>

<sxh bash>
# 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
</sxh>


**2) Check free space in syscat & sys temp tablespaces :**
<sxh bash>
</sxh>

**3) Make sure enough log space is available :**
<sxh bash>
</sxh>

**4) Convert Type-1 indexes to Type-2 :**
<sxh bash>
<DB2INSTALL>/utilities/db2IdentifyType1 -d <DB> -o t1indexes_<DB>.sql
db2 -tvf t1indexes_<DB>.sql
</sxh>

**5) Gather preupgrade data collection :**
<sxh bash>
db2fodc -preupgrade -db <DBNAME>
db2support -preupgrade -d <DBNAME>
</sxh>

**6) Check upgrade pre-requisities (from installation directory):**
<sxh bash>
db2ckupgrade <DB> -l db2ckupgrade_<DB>.log
</sxh>


If DB is in rollforward state : 
<sxh bash>
db2 "rollforward db <DB> to end of logs and stop"
</sxh>

If DB is in backup pending state : 
<sxh bash>
db2 backup db <DB>
</sxh>

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

A) Identify which table is Quiescing the Tablespace :
<sxh bash>
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"
</sxh>

B) Reset the quiesce of the table :
<sxh bash>
db2 "quiesce tablespaces for table <TABLE> reset"
</sxh>

**7) Run the db2ckupgrade again and db2stop :**
<sxh bash>
db2ckupgrade <DB> -l db2ckupgrade_<DB>.log
db2stop
ipclean
</sxh>

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

**9) Upgrade instance :**
<sxh bash>
db2iupgrade -u <FENCEDID> <INSTANCE>
</sxh>

**10) Update standby node Global registry :**
<sxh bash>
<NEWPATH>/bin/db2greg -delinstrec instancename=<OLDINSTANCE>
<NEWPATH>/instance/db2iset -a <UPGRADEDINSTANCE>
</sxh>

**11) Start the instance**
<sxh bash>
db2start
</sxh>

**12) Upgrade all databases :**
<sxh bash>
db2 upgrade db <DB>
</sxh>

**13) Check invalid objects & Rebind packages :**
<sxh bash>
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
</sxh>

**14) Check for corrupted Triggers :**
<sxh bash>
db2 "select name, schema, lib_id from sysibm.systriggers where lib_id not in (select lib_id from sysibm.syscodeproperties)"
</sxh>


**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 :**
<sxh bash>
db2licm -a <licence>.lic
</sxh>

===== Migrate XML Extender to DB2 9.7 (from 9.x, 8.x) =====
**1) Backup DAD & DTD configuration :**
<sxh bash>
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"
</sxh>

**2) Disable XML columns for XML Extender :**
<sxh bash>
# 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.
</sxh>

**3) Drop all references to XMLVARCHAR, XMLCLOB, and XMLFILE :**
<sxh bash>
# list the references
db2 "SELECT TABSCHEMA, TABNAME, COLNAME
    FROM SYSCAT.COLUMNS 
    WHERE TYPESCHEMA='DB2XML' AND NOT TABSCHEMA='DB2XML'"
    
# drop the objects
</sxh>

**4) Disable the XML Extender feature on database :**
<sxh bash>
dxxadm disable_db <DB>
</sxh>

**5) Uninstall the XML Extender feature :**
<sxh bash>
<DB2DIR>/install/db2_deinstall -F XML_EXTENDER 
</sxh>

**6) Update the instance to remove the uninstalled feature :**
<sxh bash>
db2iupdate <instance>
</sxh>

**7) Proceed with instance upgrade to DB2 9.7**