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
PKGINST: SUNWj2rt
NAME: JDK 1.2 run time environment
CATEGORY: system
ARCH: sparc
VERSION: 1.2.2,REV=2001.09.10.12.37
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
|
Solaris
|
Java
Version
|
|
|
2.6, 7, 8
|
1.1.8
|
|
|
2.6, 7, 8
|
1.2.2
|
|
|
2.6, 7, 8
|
1.3
|
|
Version 8
|
7, 8, 9
|
1.3
|
|
|
7, 8, 9
|
1.4
|
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
PKGINST: SUNWj3rt
NAME: JDK 1.3 run time environment
CATEGORY: system
ARCH: sparc
VERSION: 1.3.1,REV=2001.08.09.00.00
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
CLASSPATH=/home/db2inst1/sqllib/java/db2java.zip:/home/db2inst1/sqllib/java/
db2jcc.jar:/home/db2inst1/sqllib/function:/home/db2inst1/sqllib/java/
db2jcc_license_cisuz.jar:/home/db2inst1/sqllib/java/db2jcc_license_cu.jar:.
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-11.23.03.324770
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.
» 
IE 7
Firefox 2.0
Comment and Contribute
