DB2 for Linux on iSeries: Implementation Guide

88 DB2 UDB for Linux on iSeries: Implementation Guide

3.8.5 Sample JDBC program

This section introduces a sample program of JDBC. Example 3-7 shows the

various JDBC access methods including type 2 and type 4.

Example 3-7 Various JDBC access to OS/400 and Linux DB2

/*

TestJDBC.java a simple sample program of JDBC drivers provided by DB2 which

access Linux or OS/400 DB2

Parameters :

driver type (DB2 JDBC driver type. "Type2" or "Type4")

Connection ("OS400" means to connect to OS/400 via Type4.

"Linux" means to access the local Linux database.

"OS4002" means to access OS/400 using the universal

driver as Type2.

"Linux2" means to access the Local Linux database

using the universal driver as Type2.)

schema (The schema (OS/400 library) name)

user (OS/400 or DB2 instance user name.)

password (The password.)

Example : java TestJDBC Type4 OS400 SAMPLEDB01 DBDEMO password

*/

import java.io.*;

import java.sql.*;

public class TestJDBC

{

public static void main(String argv[])

{

try{

/*------------------------------------------*/

// JDBC driver selection

/*------------------------------------------*/

if(argv[0].equals("Type2")){

Class.forName("COM.ibm.db2.jdbc.app.DB2Driver"); // Type 2 driver

System.out.println("Type 2 JDBC driver selected.");

}

if(argv[0].equals("Type4")){

Class.forName("com.ibm.db2.jcc.DB2Driver"); // Type 4/2

universal driver

System.out.println("Type 4/2 universal JDBC driver selected.");

}

/*------------------------------------------*/

// Connection type and database selection

/*------------------------------------------*/

Chapter 3. Connectivity with DB2 UDB for iSeries 89

String url = null;

/*--- Type 2 driver ---*/

if(argv[0].equals("Type2")) {

// Type 2 to local Linux

if(argv[1].equals("Linux")) url = "jdbc:db2:SAMPLE"; 1

// Type 2 to OS/400 via DB2 Connect

if(argv[1].equals("OS400")) url = "jdbc:db2:OS400"; 2

}

/*--- Type 4 driver ---*/

if(argv[0].equals("Type4")) {

// Type 4 direct to local

if(argv[1].equals("Linux")) url = "jdbc:db2://localhost:50001/SAMPLE";

3

// Type 4 direct to OS/400

if(argv[1].equals("OS400")) url = "jdbc:db2://192.168.1.1:446/AS05"; 4

// Use Type 4 driver as type 2 to access local

if(argv[1].equals("Linux2")) url= "jdbc:db2:SAMPLE"; 5

// Use Type 4 driver as type 2 to access OS/400

if(argv[1].equals("OS4002"))url = "jdbc:db2:OS400"; 6

}

System.out.println("The URL string is : " + url);

/*------------------------------------------*/

// Access the table

/*------------------------------------------*/

Connection con = DriverManager.getConnection(url, argv[3], argv[4]);

System.out.println("Database connected.");

Statement stmt = con.createStatement();

String sql = "SELECT ID, NAME, DEPT, JOB, YEARS, SALARY FROM " + argv[2]

+ ".STAFF WHERE ID > 280";

ResultSet rs = stmt.executeQuery(sql);

while(rs.next())

{

String id = rs.getString("ID");

String name = rs.getString("NAME");

String dept = rs.getString("DEPT");

String job = rs.getString("JOB");

String years = rs.getString("YEARS");

90 DB2 UDB for Linux on iSeries: Implementation Guide

String salary = rs.getString("SALARY");

System.out.println(id + "\t" + name + "\t" + dept + "\t" + job + "\t" +

years + "\t" + salary);

}

stmt.close();

}catch(Exception e){

System.out.println("Exception : " + e);

}

}

}

Type 2 driver

The JDBC driver is COM.ibm.db2.jdbc.app.DB2Driver. The URL is

jdbc:db2:(DBalias), where the DBalias is the alias of the database, which is

cataloged in the database directory of the instance. In the Example 3-7, the URL

is as follows:

// Type 2 to local Linux

if(argv[1].equals("Linux")) url = "jdbc:db2:SAMPLE"; 1

// Type 2 to OS/400 via DB2 Connect

if(argv[1].equals("OS400")) url = "jdbc:db2:OS400"; 2

“SAMPLE” and “OS400” are the aliases of the Linux and OS/400 database,

respectively. In the latter case, DB2 UDB for iSeries is accessed via the DB2

Connect facility.

Type 4 driver

The JDBC driver is com.ibm.db2.jcc.DB2Driver. The URL is jdbc:db2:(Host

name or IP address):(DRDA port)/DBalias).

Type 4 driver connects to the

database directly. The URL includes the hostname or IP address of the server

where the actual DB2 instance resides and the port number of the DRDA service

is working. DBalias is the database alias (Linux) or the relational database name

(OS/400) of the DB2. In our case, the URL is as follows.

// Type 4 direct to local

if(argv[1].equals("Linux")) url = "jdbc:db2://localhost:50001/SAMPLE"; 3

// Type 4 direct to OS/400

if(argv[1].equals("OS400")) url = "jdbc:db2://192.168.1.1:446/AS05"; 4

For the Linux DB2, the database is in the same Linux server (localhost) on which

the program runs. The DRDA port number of the DB2 UDB for LUW is unique to