DB2 Migration to Version 8 – Part 2

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

Marin Komadina
Marin Komadina
Marin was born June 27, 1968 in Zagreb, Croatia. He graduated in 1993 form The Faculty for Electrotechnology and Computer Sciences, University of Zagreb in Croatia. He started his professional career as a System specialist and DBA for the Croatian company Informatika System. His most important project was the development and implementation of the enterprise, distributed point of sales solution, based on the Oracle technology. In 1999, Marin became the company CTO, where he played an active role in company development and technical orientation. After Informatika System, Marin worked as an IT Manager Assistant for the Austrian international retail company "Segro," on location in Graz (Austria) and Zagreb (Croatia). He was responsible for the company's technical infrastructure and operational support. Segro used IBM technology, OS/400 operating system and DB2 database. In 1998, Marin joined the international telecommunication company VIPNet GSM that was a part of greater concern, Mobilkom Austria& Western Wireless Int. USA. After one year, Marin took over the IT System Manager position, where he managed many multi-platform, telecommunication projects and was leading the IT system department. In 2001, Marin started to work in Germany as a senior system architect. He is currently working for German banks on different banking projects.

Latest Articles