IBM Support

Weekly Tips from DB2 Experts: Demo of DB2 connectivity using type 2 Universal JDBC Driver

Technical Blog Post


Abstract

Weekly Tips from DB2 Experts: Demo of DB2 connectivity using type 2 Universal JDBC Driver

Body

In this demo, I have considered the following to demonstrate DB2 connectivity using type 2 Universal JDBC type 2 driver with 32-bit and 64 application:

 

- Downloaded IBM 32-bit JDK for Windows from the web: http://www.ibm.com/developerworks/java/jdk/

- Installed DB2 V9.7 FP5 64-bit Client on Windows 7
- DB2 V10.1 FP4 database server on Linux.

 

Used the following sample code:

import java.sql.Connection; 

import java.sql.DriverManager; 
import java.sql.*; 
import java.sql.SQLException; 
 
public class t2test
public static void main(String[] args)
{
try {
System.out.println("CONNECTING TO TEST"); 
Class.forName("com.ibm.db2.jcc.DB2Driver"); 
String url = "jdbc:db2:TEST:traceDirectory=C:\\temp;traceFile=jcctrc.log;traceFileAppend=false;traceLevel=-1;"; 
Connection con = DriverManager.getConnection(url,"db2admin,"xxxx"); 
con.close(); 
System.out.println("Completed"); 
 
catch (Exception ex)
 {
 System.out.println("Exception: " + ex);
 ex.printStackTrace();
}
}
}
 

===

DB2 client details:

C:\Program Files\IBM\SQLLIB\java\jdk\bin>db2level

DB21085I  This instance or install (instance name, where applicable: "DB2_01")
uses "64" bits and DB2 code release "SQL09079" with level identifier
"080A0107".
Informational tokens are "DB2 v9.7.900.250", "s131204", "IP23556", and Fix Pack
 
"9".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB~1" with DB2 Copy Name
"DB2COPY2".
 

Catalog node and database:

C:\Program Files\IBM\SQLLIB\java\jdk\bin>db2 catalog tcpip node node1 remote ts-test.lenexa.ibm.com server 50051
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is refreshed.

 

C:\Program Files\IBM\SQLLIB\java\jdk\bin>db2 catalog db test at node node1
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is refreshed.
 
Case1:

32-bit JVM used:

C:\temp\IBM_DevelopmentPackage_for_Eclipse_Win_X86_32_4.6.1\eclipseDevelopmentPackage\ibm_sdk70\bin>java -version
java version "1.7.0"
Java(TM) SE Runtime Environment (build pwi3270sr7fp1-20140712_01(SR7 FP1))
IBM J9 VM (build 2.6, JRE 1.7.0 Windows 7 x86-32 20140627_204598 (JIT enabled, A
OT enabled)
J9VM - R26_Java726_SR7_20140627_0924_B204598
JIT  - r11.b06_20140409_61252.04
GC   - R26_Java726_SR7_20140627_0924_B204598
J9CL - 20140627_204598)
JCL - 20140707_01 based on Oracle 7u65-b16

 

Environment info: (shows LIB - pointing to 32-bit libraries, CLASSPATH- pointing to the db2jcc.jar from client install AND PATH - pointing to 32-bit JDK)

 
C:\Program Files\IBM\SQLLIB\java\jdk\bin>set 

:

CLASSPATH=C:\Program Files\IBM\SQLLIB\java\db2java.zip;C:\Program Files\IBM\SQLLIB\java\db2jcc.jar;C:\Program Files\IBM\SQLLIB\java\sqlj.zip;C:\Program Files\IB

M\SQLLIB\java\db2jcc_license_cu.jar;C:\Program Files\IBM\SQLLIB\bin;C:\Program Files\IBM\SQLLIB\java\common.jar;.;.

:

LIB=C:\PROGRAMFILES\IBM\SQLLIB\LIB\Win32

:
Path=C:\temp\IBM_DevelopmentPackage_for_Eclipse_Win_X86_32_4.6.1\eclipseDevelopmentPackage\ibm_sdk70\bin;C:\Program Files\IBM\SQLLIB\BIN\..\db2tss\bin;C:\PROGRA~1\IBM\SQLLIB\BIN\..\db2tss\bin;C:\Program Files\IBM\SQLLIB\BIN;C:\Program Files\IBM\SQLLIB\FUNCTION;C:\Program Files (x86)\IBM\Java60\jre\bin;C:\windows\system
32;C:\windows;C:\windows\system32\wbem;C:\windows\system32\windowspowershell\v1.0\;c:\program files\ibm\infoprint select;c:\program files\thinkpad\bluetooth sof
tware\;c:\program files\thinkpad\bluetooth software\syswow64;c:\program files (x86)\ibm\personal communications\;c:\program files (x86)\ibm\trace facility\;c:\p
rogram files (x86)\common files\roxio shared\dllshared\;c:\program files (x86)\common files\roxio shared\dllshared\;c:\program files (x86)\common files\roxio sh
ared\13.0\dllshared\;c:\strawberry\c\bin;c:\strawberry\perl\site\bin;c:\strawberry\perl\bin;c:\program files\ibm\gsk8\lib64;c:\program files (x86)\ibm\gsk8\lib;
C:\Program Files (x86)\ooRexx;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;C:\Program Files (x86)\Lenovo\Access Connections\;C:\Program Files\nodejs\;.;C:\Program Files\IBM\SQLLIB\BIN\ICC64;C:\Program
Files\IBM\SQLLIB\BIN\ICC

 

C:\Program Files\IBM\SQLLIB\BIN>java com.ibm.db2.jcc.DB2Jcc -version
IBM DB2 JDBC Universal Driver Architecture 3.64.133
 
Compile Java program t2test.java:
C:\temp\IBM_DevelopmentPackage_for_Eclipse_Win_X86_32_4.6.1\eclipseDevelopmentPackage\ibm_sdk70\bin>javac t2test.java
 

Execute Java program:

C:\temp\IBM_DevelopmentPackage_for_Eclipse_Win_X86_32_4.6.1\eclipseDevelopmentPackage\ibm_sdk70\bin>java t2test
CONNECTING TO TEST
Exception in thread "main" com.ibm.db2.jcc.am.SqlException: [jcc][10389][12245][
3.64.133] Failure in loading native library db2jcct2, java.lang.UnsatisfiedLinkE
rror: db2jcct2 (C:\Program Files\IBM\SQLLIB\BIN\db2jcct2.dll is not a valid Win3
2 application. ):  ERRORCODE=-4472, SQLSTATE=null
      at com.ibm.db2.jcc.am.bd.a(bd.java:663)
      at com.ibm.db2.jcc.am.bd.a(bd.java:60)
      at com.ibm.db2.jcc.am.bd.a(bd.java:94)
      at com.ibm.db2.jcc.t2.a.a(a.java:37)
      at com.ibm.db2.jcc.t2.T2Configuration.<clinit>(T2Configuration.java:95)
      at java.lang.J9VMInternals.initializeImpl(Native Method)
      at java.lang.J9VMInternals.initialize(J9VMInternals.java:237)
      at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:436)
      at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:116)
      at java.sql.DriverManager.getConnection(DriverManager.java:582)
      at java.sql.DriverManager.getConnection(DriverManager.java:226)
      at t2test.main(t2test.java:13)
 

Failed with -4472 error. This is expected due to the below restriction documented in the following technote: /support/pages/node/399655

Type 2 connectivity requires a DB2 client (or server) installation and has a restriction in that the "bitness" of the java application must match with the DB2 client ( or server) instance.

So a 32bit java application using type 2 connectivity can only work with a 32bit DB2 client (or server) instance and a 64bit java application using type 2 connectivity can only work with a 64bit DB2 client (or server) instance for connectivity to a remote (or local) database. The bitnes of the remote database instance does not matter, only that of which is configured in the client application environment.

The JVM used for the application environment must load the correct library, depending on its bit type (32-bit or 64-bit). A 32-bit JVM can only load the 32-bit DB2 native libraries and a 64-bit JVM can only load the 64-bit DB2 native libraries.

 

 

Upon, switching to 64-bit JVM, application works fine, please see below test:

Case2:
 
C:\Program Files\IBM\SQLLIB\java\jdk\bin>set

:

CLASSPATH=C:\Program Files\IBM\SQLLIB\java\db2java.zip;C:\Program Files\IBM\SQIB\java\db2jcc.jar;C:\Program Files\IBM\SQLLIB\java\sqlj.zip;C:\Program Files\M\SQLLIB\java\db2jcc_license_cu.jar;C:\Program Files\IBM\SQLLIB\bin;C:\Programiles\IBM\SQLLIB\java\common.jar;.;.
:
LIB=C:\Program Files\IBM\SQLLIB\LIB
Path=C:\Program Files\IBM\SQLLIB\BIN\..\db2tss\bin;C:\PROGRA~1\IBM\SQLLIB\BIN\..\db2tss\bin;C:\Program Files\IBM\SQLLIB\BIN;C:\Program Files
\IBM\SQLLIB\FUNCTION;C:\Program Files (x86)\IBM\Java60\jre\bin;C:\windows\system32;C:\windows;C:\windows\system32\wbem;C:\windows\system32\windowspowershell\v1.
0\;c:\program files\ibm\infoprint select;c:\program files\thinkpad\bluetooth software\;c:\program files\thinkpad\bluetooth software\syswow64;c:\program files (x
86)\ibm\personal communications\;c:\program files (x86)\ibm\trace facility\;c:\program files (x86)\common files\roxio shared\dllshared\;c:\program files (x86)\c
ommon files\roxio shared\dllshared\;c:\program files (x86)\common files\roxio shared\13.0\dllshared\;c:\strawberry\c\bin;c:\strawberry\perl\site\bin;c:\strawber
ry\perl\bin;c:\program files\ibm\gsk8\lib64;c:\program files (x86)\ibm\gsk8\lib;C:\Program Files (x86)\ooRexx;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\
Common Files\Intel\WirelessCommon\;C:\Program Files (x86)\Lenovo\Access Connections\;C:\Program Files\nodejs\;.;C:\Program Files\IBM\SQLLIB\BIN\ICC64;C:\Program Files\IBM\SQLLIB\BIN\ICC
 
 
 
C:\Program Files\IBM\SQLLIB\BIN>java -version
java version "1.6.0"
Java(TM) SE Runtime Environment (build pwa6460sr14-20130705_01(SR14))
IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 Windows 7 amd64-64 jvmwa6460sr14-2013
0704_155156 (JIT enabled, AOT enabled)
J9VM - 20130704_155156
JIT  - r9_20130517_38390
GC   - GA24_Java6_SR14_20130704_1138_B155156)
JCL  - 20130618_01
 
C:\Program Files\IBM\SQLLIB\java\jdk\bin>javac t2test.java
 
C:\Program Files\IBM\SQLLIB\java\jdk\bin>java t2test
CONNECTING TO TEST
Completed   =======================> successful

 

So, from the above tests, we understand that a 64-bit java application using type 2 connectivity can only work with a 64-bit DB2 client instance (similarly for 32-bit applications, 32-bit Client instance is needed) for connectivity to a remote database. The bitness of the remote database instance does not matter.

Hope this helps! Please post your questions/feedback.

Thanks for reading!

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11141324