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”,
Product is installed at “/opt/IBM/db2/V8.1”.
and DB2 code release “SQL08010”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 P5227850db2 => select substr(IMPLEMENTATION,1,40)
PKGNAME SCHEMA PROCNAME
—————————————- ——————– —————————-
P5227850!pgsjmp ARTIST SOFTVERSION1 record(s) selected.
db2 => select PROCEDURE_ID,PROCNAME from syscat.procedures
PROCEDURE_ID PROCNAME
———— —————————————————————————–
1 SOFTVERSION1 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 directorydb2 => select substr(IMPLEMENTATION,1,40)
PKGNAME SCHEMA PROCNAME
—————————————- ——————– —————————-
P5227850!pgsjmp ARTIST SOFTVERSION1 record(s) selected.
-> database definition exist
db2 => select PROCEDURE_ID,PROCNAME from syscat.procedures
PROCEDURE_ID PROCNAME
———— —————————————————————————–
1 SOFTVERSION1 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/*.*
$ 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
