Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 25, 2004

DB2 Migration to Version 8 - Part 2 - Page 2

By Marin Komadina

JVM and JDBC Driver

JDBC driver functionality depends on locally installed JVM software. The installed Java software on the test SUN Solaris machine was version 1.2.2:

# >>  pkginfo -l SUNWj2rt
      NAME:  JDK 1.2 run time environment
  CATEGORY:  system
      ARCH:  sparc
   VERSION:  1.2.2,REV=2001.
   BASEDIR:  /usr
    VENDOR:  Sun Microsystems, Inc.
      DESC:  Java virtual machine and 
	       core class libraries
    PSTAMP:  sola010910123743
  INSTDATE:  Jul 23 2002 09:30
   HOTLINE:  Please contact your local 
      service provider
    STATUS:  completely installed
     FILES:      192 installed pathnames
                   4 shared pathnames
                  16 directories
                  27 executables
               54464 blocks used (approx)

# java -version
java version "1.2.2"
Solaris VM (build Solaris_JDK_1.2.2_10, 
   native threads, sunwjit)

drwxr-xr-x   7 root     bin          512 
  Aug 12  2002 j2se -> version 1.3.1  
lrwxrwxrwx   1 root     other          9 
  Aug 12  2002 java -> ./java1.2  
  -> version 1.2.2 
drwxrwxr-x   6 root     bin          512 
  Aug 12  2002 java1.1  
drwxr-xr-x   7 root     bin          512 
  Aug 12  2002 java1.2  
Listing 6: Installed Java software information

The DB2 instance owner had java version 1.2.2 configured for the usage. Other installed java versions are 1.1 and 1.3.1. IBM java comp ability matrix gives us following details:

Version 7


Java Version

2.6, 7, 8


2.6, 7, 8


2.6, 7, 8


Version 8

7, 8, 9


7, 8, 9


Table 1: IBM Java Compatibility Matrix

The DB2 V8 database requests a Java version of 1.3 or 1.4. The recommendation from IBM says:

"With the advent of DB2 8.1 came the new Universal JDBC driver, which can be used either in a Type 2 architecture or a Type 4 architecture. In a Type 2 mode, the Universal JDBC driver provides local application performance gains (because it avoids using TCP/IP protocol to communicate to the DB2 server). In Type 2 mode, there is also full support for distributed transactions. Therefore, if the application and the DB2 server are on the same machine, we recommend that you use the Universal JDBC driver in Type 2 mode. The driver is located in the file db2jcc.jar."

Standard JDBC driver  type 2 and type 3
>> ls -lrt /opt/IBM/db2/V8.1/java/db2java.zip
-r--r--r--   1 bin      bin      1522386 Feb 17 17:42 /opt/IBM/db2/V8.1/java/db2java.zip

Universal JDBC driver type 4 and type 2 
>> ls -lrt /opt/IBM/db2/V8.1/java/db2jcc.jar
-r--r--r--   1 bin      bin      1017444 Feb 17 17:42 /opt/IBM/db2/V8.1/java/db2jcc.jar
Listing 7: Installed V8 Java drivers

I changed the default Java installation on the machine to version 1.3.1. The DB2 instance owner adopted the profile, including a pointer to the latest JDBC driver.

# pkginfo -l SUNWj3rt
      NAME:  JDK 1.3 run time environment
  CATEGORY:  system
      ARCH:  sparc
   VERSION:  1.3.1,REV=2001.
   BASEDIR:  /usr
    VENDOR:  Sun Microsystems, Inc.
      DESC:  Java virtual machine and core class libraries
    PSTAMP:  re450m08140310
  INSTDATE:  Aug 12 2002 11:55
   HOTLINE:  Please contact your local service provider
    STATUS:  completely installed
     FILES:      212 installed pathnames
                   5 shared pathnames
                  52 directories
                  51 executables
               78337 blocks used (approx)

>> java -version
java version "1.3.1_01"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.1_01)
Java HotSpot(TM) Client VM (build 1.3.1_01, mixed mode)

>> set | grep CALSSPATH 
Listing 8: Actual Java software information

Explain Tables V8

After migration, during a post-migration functionality test, a new error message appeared:

>> db2advis -d artist -i test_2.sql
execution started at timestamp 2004-06-23-
  found [1] SQL statements from the input file

SQL0220N  The Explain table "DB2INST1.EXPLAIN_INSTANCE", column "EXPLAIN_OPTION" 
does not have the proper definition or is missing.  SQLSTATE=55002

DB2 Workload Performance Advisor tool is finished.
Listing 9: DB2 advisor post migration test

All necessary explain tables were there, belonging to the DB2INST1 user. Searching for the solution, I found that the problem lies in the explain table format. Explain plan tables V7 are incompatible with explain plan tables V8. IBM documentation emphasizes a new format for the explain tables, but this was not mentioned in the migration documentation. The explain table structure and the data conversion to V8 format was made using the db2exmig utility.

>> db2exmig -d artist -e db2inst1 
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1999, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Migration Tool
Connecting to the Database.
Connect to Database Successful.
Binding package - Bind was Successful.
Examining the columns for table DB2UDB1.EXPLAIN_INSTANCE
Examining the columns for table DB2UDB1.EXPLAIN_STATEMENT
Examining the columns for table DB2UDB1.EXPLAIN_ARGUMENT
Examining the columns for table DB2UDB1.EXPLAIN_OBJECT
Examining the columns for table DB2UDB1.EXPLAIN_OPERATOR
Examining the columns for table DB2UDB1.EXPLAIN_PREDICATE
Examining the columns for table DB2UDB1.EXPLAIN_STREAM
Examining the columns for table DB2UDB1.ADVISE_INDEX
Examining the columns for table DB2UDB1.ADVISE_WORKLOAD
Found all tables OK. Start migration.
Old tables renamed...begin creating new explain tables.
Begin insert into new explain tables.
Copying to Explain table EXPLAIN_INSTANCE from EMINS_001087983210
Copying to Explain table EXPLAIN_STATEMENT from EMSTA_001087983210
Copying to Explain table EXPLAIN_ARGUMENT from EMARG_001087983210
Copying to Explain table EXPLAIN_OBJECT from EMOBJ_001087983210
Copying to Explain table EXPLAIN_OPERATOR from EMOPR_001087983210
Copying to Explain table EXPLAIN_PREDICATE from EMPRD_001087983210
Copying to Explain table EXPLAIN_STREAM from EMSTR_001087983210
Copying to Explain table ADVISE_INDEX from ADIND_001087983210
Copying to Explain table ADVISE_WORKLOAD from ADWKL_001087983210
Exec Imm was Successful.
Check all tables where copied properly.
All tables confirmed to have been copied properly.
Dropping old Explain table EMINS_001087983210 (EXPLAIN_INSTANCE)
Dropping old Explain table EMSTA_001087983210 (EXPLAIN_STATEMENT)
Dropping old Explain table EMARG_001087983210 (EXPLAIN_ARGUMENT)
Dropping old Explain table EMOBJ_001087983210 (EXPLAIN_OBJECT)
Dropping old Explain table EMOPR_001087983210 (EXPLAIN_OPERATOR)
Dropping old Explain table EMPRD_001087983210 (EXPLAIN_PREDICATE)
Dropping old Explain table EMSTR_001087983210 (EXPLAIN_STREAM)
Dropping old Explain table ADIND_001087983210 (ADVISE_INDEX)
Dropping old Explain table ADWKL_001087983210 (ADVISE_WORKLOAD)
Migration of the explain tables to Version 8.1 format has been sucessful.
******** WARNING **********
Because the 'NOT LOGGED INITITALLY' option was used in migrating the explain tables
it is recommended that a backup of the explain tables be taken as soon as possible,
if your backup procedures include the backuping up of the explain tables.
Listing 10: Explain table's conversion to V8 format

Having the explain tables converted to the V8 format, db2advis worked correctly.

DB2 Archives