DB2 Migration to Version 8 - Part 2

August 25, 2004

As it was demonstrated in my last article, an error free DB2 V7 to V8 migration procedure is compromised of database software and catalog tables migration. The user data was not migrated, leaving the user tables and indexes intact. The series of post migration tests have been done prior to database handover to production. This article presents select issues that appeared during post migration testing.

This article covers:

The Migration Status
Database Routines
JVM and JDBC Driver
Explain Tables V8
Index Type 2
Conclusion

The Migration Status

The test-case database was a DB2 V8 database instance, running on a SUN Solaris 8 operating system, under db2inst1 instance owner. The DB2 V8 instance hosted a single node (partition) database, "ARTIST."

$ db2level
DB21085I  Instance "db2udb1" uses "32" bits 
   and DB2 code release "SQL08010" 
with level identifier "01010106".
Informational tokens are "DB2 v8.1.0.0", 
   "s021023", "", and FixPak "0".
Product is installed at "/opt/IBM/db2/V8.1".

Listing 1: DB2 level information

For detailed information of pre-migration and migration steps, reference my last article, DB2 Migration to Version 8.

Following the migration procedure, additional logs and diagnostic files were created at the database log destination. A short explanation of these logs follows:

db2imdbd.dmp - system database directory migration dump file
db2imnod.dmp - node migration dump file
db2eventlog.000 - node diagnostic files, created as result of inspect check
db2inst1.nfy administration notification log
db2diag.log database diagnostic log

$ pwd 
/home/db2inst1/sqllib/db2dump$

$ ls -alrt
-rw-r-----   1 db2inst1  db2dba   2101976 Jun 28 15:17 db2imdbd.dmp
-rw-r-----   1 db2inst1  db2dba   2101976 Jun 28 15:17 db2imnod.dmp
-rw-r-----   1 db2inst1  db2dba   5242044 Jun 29 10:47 db2eventlog.000
-rw-rw-rw-   1 db2inst1  db2dba  13345933 Jun 29 10:47 db2diag.log
-rw-rw-rw-   1 db2inst1  db2dba  13345933 Jun 29 10:47 db2inst1.nfy

Listing 2: IBM log and diagnostic files

The listed files are part of the new IBM FFDC (First Failure Data Capture) system. Starting with DB2 V8, the database diagnostic concept changed. When errors occur, DB2 automatically starts collecting diagnostic
information, generating log files as defined by DBM DIAGPATH parameter.

Database Routines

Preceding the migration procedure, I made a backup of one stored routine, (procedure) SOFTVERSION, located under the user schema ARTIST. The stored procedure is extracted and saved using "get routine" commands.

DB2 V7 
######
$ cd /home/db2inst1/sqllib/function/routine/sqlproc/ARTIST
$ ls -lrt
drwxrwxr-x   5 db2inst1  db2dba       512 Jun 14 15:52 ARTIST
$ cd ARTIST
$ ls -lrt
-rw-r-----   1 db2inst1  db2dba     56125 May 28 10:22 P5227850.sqc
-rw-r-----   1 db2inst1  db2dba        96 May 28 10:22 P5227850.scm
-rw-r-----   1 db2inst1  db2dba      1534 May 28 10:22 P5227850.log
-rw-r-----   1 db2inst1  db2dba     75237 May 28 10:22 P5227850.c
-rwxr-xr-x   1 db2inst1  db2dba     15944 May 28 10:22 P5227850

db2 => select substr(IMPLEMENTATION,1,40) 
   AS PKGNAME,substr(PROCSCHEMA,1,20) 
   AS SCHEMA,substr(PROCNAME,1,30)
   as PROCNAME from syscat.procedures
PKGNAME                                  SCHEMA               PROCNAME
---------------------------------------- -------------------- ---------------------------- 
P5227850!pgsjmp                           ARTIST               SOFTVERSION

  1 record(s) selected.

db2 => select PROCEDURE_ID,PROCNAME from syscat.procedures

PROCEDURE_ID PROCNAME
------------ -----------------------------------------------------------------------------
1 SOFTVERSION

1 record(s) selected.

db2 => get routine into ARTIST_SOFTVERSION from procedure artist.SOFTVERSION
DB20000I  The GET ROUTINE command completed successfully.

Listing 3: Extracting stored procedure SOFTVERSION before migration

The stored routine, SOFTVERSION, has the database package P5227850 associated with it. A post migration check proved that the user stored routine did not migrate correctly. The situation with SOFTVERSION routine after migration:

DB2 V8 
######
$ cd /home/db2inst1/sqllib/function/routine/sqlproc
$ ls
--> procedure did not migrated, no compiled files of routines, empty directory

db2 => select substr(IMPLEMENTATION,1,40) 
   AS PKGNAME,substr(PROCSCHEMA,1,20) 
   AS SCHEMA,substr(PROCNAME,1,30)
   as PROCNAME from syscat.procedures
PKGNAME                                  SCHEMA               PROCNAME
---------------------------------------- -------------------- ---------------------------- 
P5227850!pgsjmp                          ARTIST               SOFTVERSION

  1 record(s) selected.

-> database definition exist

db2 => select PROCEDURE_ID,PROCNAME from syscat.procedures

PROCEDURE_ID PROCNAME
------------ -----------------------------------------------------------------------------
1 SOFTVERSION

1 record(s) selected.

Listing 4: Checking for stored procedure SOFTVERSION after migration

The migration procedure migrated the package and routine definition; however, the compiled files were not copied into new V8 subdirectories. IBM has published document explaining this problem (HIPER APAR IY43787):

"On migration of instances from DB2 UDB V7.2 to DB2 UDB V8.1, Java stored procedures and user defined functions (UDFs) are not migrated as expected.The MIGRATE DATABASE command does not move subdirectories under sqllib/function/jar due to missing logic in the copy_UDFs() migration script. This is a known limitation in DB2 Version 7.2 and will be resolved in a future FixPak for DB2 Version 8.

Workaround:

Move the subdirectories and jar files manually from the old sqllib directory (renamed sqllib_v6 or sqllib_v7 by migration) or applay 8.1 Fixpak 3 before migrating instance or databases."

Workaround instructions demanded a file copy of all V7 compiled files from the old V7 to the new V8 code database directory (/home/db2inst1/sqllib/function/routine/sqlproc/ARTIST). Finally, all database packages needed to be manually rebind.

$ cp /home/db2inst1/sqllibV7/function/routine/sqlproc/ARTIST/*.*  
   /home/db2inst1/sqllib/function/routine/sqlproc/ARTIST

$ db2rbind artist -l logfile.out all
 Rebind done successfully for database 'ARTIST'.

$ db2 "select PKGNAME, EXPLICIT_BIND_TIME, LAST_BIND_TIME from SYSCAT.PACKAGES"
PKGNAME  EXPLICIT_BIND_TIME         LAST_BIND_TIME
-------- -------------------------- --------------------------
P5227850 2002-08-05-10.52.28.023400 2003-01-08-18.21.27.822195

Listing 5: Manually rebinding all database packages

Go to page: 1 2 3 Next

DB2 Migration to Version 8 - Part 2

The Migration Status

Database Routines

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos