JDBC access DB2

Sample program using JDBC access DB2 database.

Prerequisite: installing DB2 JDBC driver and license JARs

The DB2 Universal JDBC Driver.

1. db2jcc.jar : includes functions in the JDBC 3.0 and earlier specifications, or
2. db2jcc4.jar : includes functions in JDBC 4.0 and later, as well as JDBC 3.0 and earlier specifications.

The DB2 JDBC License JARs

1. db2jcc_license_cisuz.jar: contains licenses for Linux, Unix, Windows®, IBM System i®, and IBM System z.
2. db2jcc_license_cu.jar. This alternate license is not sufficient to connect to DB2 running on z/OS.

Sample Java Code

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

public class DB2Test {
    public static void main(String[] argv) {
        try {
            Class.forName("com.ibm.db2.jcc.DB2Driver");
        }
        catch (ClassNotFoundException e) {
            System.out.println("Please include classpath where your DB2 Driver is located");
            e.printStackTrace();
            return;
        }
        System.out.println("DB2 driver is loaded successfully");
       
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset=null;
        boolean found=false;
        try {
            conn = DriverManager.getConnection("jdbc:db2:<dbname>","<username>","<passwd>");
            //or, if you want to connect remove DB2 server
            // conn = DriverManager.getConnection("jdbc:db2://<hostname>:<port>/<dbname>","<username>","<passwd>");
            if (conn != null)
            {
                System.out.println("DB2 Database Connected");
            }
            else
            {
                System.out.println("Db2 connection Failed ");
            }
            pstmt=conn.prepareStatement("select * from testtab");
            rset=pstmt.executeQuery();
            if(rset!=null)
            {
                while(rset.next())
                {
                    found=true;
                    System.out.println("a=[" + rset.getInt("a") + "]");
                    System.out.println("b=[" + rset.getString("b") + "]");
                }
            }
            if (found ==false)
            {
                System.out.println("No Information Found");
            }
        } catch (SQLException e) {
            System.out.println("DB2 Database connection Failed");
            e.printStackTrace();
            return;
        }
    }
}

Compile and Run

#!/bin/ksh

DB2CLIENT=/home/db2inst1

javac DB2Test.java
java -cp .:${DB2CLIENT}/sqllib/java/db2jcc.jar:${DB2CLIENT}/sqllib/java/db2jcc_license_cisuz.jar DB2Test

List Database/Node Configuration

$ db2 list database directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                       = <DBNAME>
 Database name                        = <DBNAME>
 Node name                            = <NODENAME>
 Database release level               = d.00
 Comment                              =
 Directory entry type                 = Remote
 Authentication                       = SERVER
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = <DBNAME>
 Database name                        = <DBNAME>
 Local database directory             = /home/db2inst1
 Database release level               = d.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

$ db2 list node directory    

 Node Directory

 Number of entries in the directory = 1

Node 1 entry:

 Node name                      = <NODENAME>
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = <HOSTNAME>
 Service name                   = <PORT>