Connect to DB2 using Java

/** Basic information **/
For Java Program DB2 provides two application programming interfaces (APIS): JDBC and sqlj.

JDBC:
The JDBC driver is divided into the old/CLI driver <db2java.zip> and the new Universal JDBC driver <db2jcc. Jar>.
JDBC is a dynamic SQL interface unrelated to the supplier. It provides data access to your applications through standardized Java methods.
JDBC is similar to DB2 CLI because you do not need to pre-compile the application Code And you do not need to bind the software package to the DB2 database.
As a vendor-independent standard, JDBC applications provide more portability-an essential advantage of today's heterogeneous business infrastructure.
During the execution of the JDBC application, the driver verifies the SQL statement for the currently connected DB2 database server.
Any issues during the access will be reported to the application as a Java exception together with the corresponding sqlstate and sqlcode.
Sqlj:
Sqlj is a standard development model used to access data from Java applications.
The sqlj API is defined in the SQL 1999 specification.

The new universal JDBC Driver provides support for both JDBC and sqlj APIs in one implementation.
JDBC and sqlj can operate on each other in the same application.
Sqlj provides unique capabilities to use static SQL statements for development and access control at the DB2 package level.

/** JDBC connection method analysis **/
The JDBC driver architecture can be divided into four types: type1, type2, type3, and type4.

Type1:
The driver is based on the JDBC-ODBC bridge.
Therefore, ODBC drivers can be used together with such JDBC drivers (provided by Sun.
IBM does not support the type 1 driver, so it is not a recommended environment.

Type2:
Drivers rely on Operating System-specific libraries (shared libraries) to communicate with RDBMS.
The application will mount this JDBC driver, and the driver will use the shared library to communicate with the DB2 server.
DB2 UDB for Linux, UNIX, and windowsv8.1 provide two different types of 2 drivers:
<1> the old/cli jdbc driver is provided in the db2java.zip file.
The implementation package name is com. IBM. db2.jdbc. App. db2driver.
The driver is currently used for J2EE authentication.
Its alias "app driver" comes from a concept and its package name,
This idea is that the driver will execute local connections through the local DB2 UDB client of the remote database.
<2> the common JDBC driver is provided in the file db2jcc. jar.
The implementation package name is com. IBM. db2.jcc. db2driver.
This driver is a new feature in DB2 UDB for Linux, UNIX, and Windows v8.1.
In the initial implementation (v8.1), this driver was used to directly connect to the DB2 server using the type 4 driver architecture.
In DB2 v8.1.2, you can use this driver in the Type 2 architecture.
One of the main reasons for using this driver in the Type 2 architecture is for local application performance and distributed transaction support.
The general JDBC Type 2 driver uses com. IBM. db2.jcc. db2xadatasource and COM. IBM. db2.jcc. db2connectionpooldatasource to support distributed transactions and connection pools.

Note: The old/CLI type 2 driver will not be enhanced in future versions.

Type3:
The driver is a pure Java implementation. It must communicate with the DB2 JDBC Applet Server (DB2 JDBC Applet Server) to access DB2 data.
This type of driver is designed to enable Java applet to access the DB2 data source.
It is often referred to as the "Network (net) driver", which is named according to its package name com.ibm.db2.jdbc.net. DB2 v8.1 supports network drivers and can be used in JDBC applications.
To ensure that the db2java.zip driver is always at the same maintenance level as the DB2 Applet Server.
If the driver is used in the applet, this is not an issue because the browser downloads the corresponding db2java.zip file during the application execution period.
Many customers use type3 drivers instead of type2 drivers to avoid necessary DB2 Client installation and required DB2 catalog database commands, the latter is used to create the database directory information required to use the old/CLI driver for Type 2 connection.
Currently, WebSphere Application Server and other J2EE servers do not support the IBM type 3 driver because the driver does not support distributed transactions (JTA ).
In future versions, the Type 3 driver will not be enhanced.

We recommend that you use the common JDBC Type 4 driver instead of the Type 3 Driver.


Type4:
A driver is a JDBC driver used only for Java. It is directly connected to the database server.
DB2 UDB for Linux, UNIX, and Windows v8.1 introduce a type 4 driver called "Universal JDBC driver.
The common JDBC driver is provided in the file db2jcc. jar.
The implementation package name is com. IBM. db2.jcc. db2driver.
Note that the generic type 2 and generic type 4 drivers have the same implementation class name.
There are two ways to differentiate which driver DB2 will instantiate:
Use the connection feature to determine whether the connection uses the Shared Library (type2), or whether the driver starts a direct connection (type4) from the Java application ).

Important: for DB2 UDB v8.1.2, the general JDBC driver requires that the classpath have the license JAR file and the db2jcc. jar file.
The following is the required license JAR file:
Cloudscape network server v5.1: db2jcc_license_c.jar
DB2 UDB V8 for Linux, UNIX, and Windows Servers: db2jcc_license_su.jar
DB2 UDB for iseries and Z/OS Server (provided together with DB2 connect and DB2 Enterprise Server Edition): db2jcc_license_cisuz.jar

 

 

**************************************** ************************

Driver type: db2java.zip, db2jcc. Jar
Note: If you use db2java.zip and the Web server uses tomcat, change db2java.zip to db2java. jar, it is best to decompress the ZIP file and use the jar command to package it, and directly change the file type (haha, according to the JAR file strictly speaking, this is not in line with the syntax <less descriptive file: manifest. mf>, you can use it)
In general, if db2java.zip is used, the DB2 client needs to be installed. If db2jcc. jar is used to connect directly through the network, the DB2 client does not need to be installed. (if it is used on type2, the client must be installed)

Type2:
Use <db2java.zip>:
JDBC. driverclassname = com. IBM. db2.jdbc. App. db2driver
JDBC. url = JDBC: DB2: databasename


If your tool uses myeclipse and tomcat plugin, copy db2jdbc. DLL to % java_home %/bin. Otherwise, it will not work.
<This is not the case if another user uses the db2java.zip driver. I don't know. I have never tried it. I have time to try it.>

Use <db2jcc. Jar>:
JDBC. driverclassname = com. IBM. db2.jcc. db2driver
JDBC. url = JDBC: DB2: databasename

Type3:
Driver: db2java.zip
JDBC. driverclassname = com.ibm.db2.jdbc.net. db2driver
JDBC. url = JDBC: DB2: // ip: 6789/dbname
Note: Run db2jstrt 6789 on the database. (In this example, the db2jd process is started. 6789 is the default port on which the server listens to the jdbc2 connection, you can also set it to another non-conflicting port .)

Type4:
Driver: db2jcc. Jar
The database character set must be set to UTF-8.
JDBC. driverclassname = com. IBM. db2.jcc. db2driver
JDBC. url = JDBC: DB2: // ip: Port/dbname

============================

Import java. SQL .*;

Public class dbtest
...{

Public static void main (string [] ARGs)
...{
Try
...{
Class. forname ("com. IBM. db2.jcc. db2driver"). newinstance ();

String url = "JDBC: DB2: // 172.16.123.126: 60000/DOL ";
String user = "dolown ";
String Password = "dolown ";
System. Out. println ("try ");
Connection conn = drivermanager. getconnection (URL, user, password );
System. Out. Print ("done! OK !!! ");

Preparedstatement PS = conn. preparestatement ("select dealermarket_name from tr_everybusiness_total ");
& Nbsp; resultset rsw.ps.exe cutequery ();
While (Rs. Next ())
...{
System. Out. println ("user_no =" + Rs. getstring ("dealermarket_name "));
}
Conn. Close ();
} Catch (exception sqle)
...{
System. Out. Print (sqle );

}

}

}
 
 

Environment: Eclipse + ibm jdk (Be sure to use IBM instead of sun, otherwise the error "com. IBM. db2.jcc. C. disconnectexception: encoding not supported!" will be reported !!")

Ibmjdk (eclipse with JDK ):

Https://www14.software.ibm.com/webapp/iwm/web/reg/download.do? Source = idpe & s_tact = 105agx05 & s_cmp = JDK & lang = en_us & s_pkg = win220 & CP = UTF-8

You must register an IBM account before downloading it,

When running the program, you must first load db2's driver db2java.zip and db2jcc. jar to the project.

There are a lot of examples of connecting to DB2 on the Internet. I tried a lot and got an error. I finally found it was a JDK problem. I 'd like to post it here to save you some time.

ArticleSource: feino Network (www.firnow.com): http://dev.firnow.com/course/3_program/java/javashl/200857/114846_2.html

==============================

 

 

 

Code

  Import  Java. SQL.  *  ;

  Public     Class  Testdb2 {

 Public     Static     Void  Main (string [] ARGs ){
  Try  {
Class. forname (  "  Com. IBM. db2.jcc. db2driver  "  ). Newinstance ();
String URL  =     "  JDBC: db2. // 192.168.0.108: 50001/Donghai "  ;
String user  =     "  Db2admin  "  ;
String Password  =     "  Lina  "  ;
System. Out. println (  "  Try  " );
Connection Conn  =  Drivermanager. getconnection (URL, user, password );
System. Out. Print (  "  Done! OK !!!  "  );

Statement stmt  =  Conn. createstatement ();
Resultset rs  =  Stmt.exe cutequery (  "  Select * from student  "  );
 While  (Rs. Next ()){
  Int  ID  =  Rs. getint (  "  ID  "  );
String name  =  Rs. getstring (  "  Name  "  );
System. Out. println (ID  +    "  ,  "     +  Name );
}
Stmt. Close ();
Conn. Close ();
}  Catch  (Exception E1 ){
E1.printstacktrace ();
}
}
}