Java JDBC Application to connect to IBM BigSQL or IBM DB2 in a Kerberos Cluster

This blog provides the complete code to connect to the IBM BigSQL Database in a Kerberos Cluster. The same code works for IBM DB2 also.

In the below code, we do not pass the database User/Passowd to connect to Database. Here, we use the Kerberos Authentication to connect to Database.

1) Java - JDBC Code

public class KerberosTest {

       public static void main(String[] args) {

            

             if (null != args && args.length != 2) {

                    throw new IllegalArgumentException(

                                 "Invalid arguments. " +

                                 "Specify headnode hostname/ip " +

                                 "& database port");

             }

             String ServerName = args[0];

             int PortNumber = Integer.parseInt(args[1]);

             String DatabaseName = "bigsql";

             java.util.Properties properties = new java.util.Properties();

            

             // 11 is the integer value for kerberos

             properties.put("securityMechanism", new String("11"));

            

             // Provide the Kerberos Principal

             properties.put("KerberosServerPrincipal",

                           "biadmin/btestserver.ibm.com@IBM.COM");

             String url = String.format("jdbc:db2://%s:%d/%s", ServerName,

                           PortNumber, DatabaseName);

             java.sql.Connection con = null;

             try {

                    Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();

             } catch (Exception e) {

                    System.out.println("Error: " +

                                 "failed to load Db2 jcc driver.");

             }

             try {

                   

                    System.out.println("url: " + url);

                   

                    con = java.sql.DriverManager.

                                 getConnection(url, properties);

                   

                    java.sql.Statement s2 = con.createStatement();

                    try {

                           s2.executeUpdate("drop table t1");

                           s2.executeUpdate("drop table tbint");

                           System.out.println("Drop Hadoop & DB2 " +

                                        "tables successfull!!!");

                          

                    } catch (Exception e) {

                           System.out.println("drop is failing");

                    }

                    try {

                           // Create DB2 Table

                           s2.executeUpdate("create table t1 (c1 int)");

                          

                           // Create BigSQL Table

                           s2.executeUpdate(

                                        "create hadoop table " +

                                        "if not exists "+

                                        "tbint " +

                                        "(col1 INT, col2 INT, col3 INT)");

                           System.out.println(

                                        "Created Hadoop & DB2 tables " +

                                        "successfully!!!");

                          

                    } catch (Exception e) {

                           System.out.println("create is failing");

                    }

                    // Insert to DB2 Table

                    String str = "insert into t1 values (100)";

                    s2.executeUpdate(str);

                    // Query the DB2 Table

                    java.sql.PreparedStatement ps = con

                                 .prepareStatement("select * from t1");

                    java.sql.ResultSet rs = ps.executeQuery();

                    while (rs.next()) {

                           System.out.println(rs.getString(1));

                    }

                    // Insert to BIGSQL Table

                    str = "insert into tbint values(1,2,3),(1,2,3),(1,2,3)";

                    s2.executeUpdate(str);

                    // Query the BIGSQL Table

                    ps = con.prepareStatement("select * from tbint");

                    rs = ps.executeQuery();

                    while (rs.next()) {

                           System.out.printf("%s,%s,%s", rs.getString(1),

                                        rs.getString(2), rs.getString(3));

                           System.out.println();

                    }

                    con.close();

             } catch (Exception e) {

                    e.printStackTrace();

             }

       }

}

To complie the above code, ensure the DB2 jars - db2jcc_license_cu.jar, db2jcc.jar & db2jcc4.jar in the classpath.

2) Testing the JDBC Application in Kerberos Cluster

Do the Kinit and get the valid ticket.

[biadmin@btestserver ~]$
[biadmin@btestserver ~]$ date
Wed Oct  5 16:50:18 PDT 2016
[biadmin@btestserver ~]$
[biadmin@btestserver ~]$
[biadmin@btestserver ~]$ /opt/ibm/biginsights/jdk/jre/bin/kinit -k -t /opt/ibm/biginsights/conf/security/keytabs/biadmin.keytab biadmin/btestserver.ibm.com
Done!
New ticket is stored in cache file /home/biadmin/krb5cc_biadmin
[biadmin@btestserver ~]$
[biadmin@btestserver ~]$
[biadmin@btestserver ~]$ klist -c krb5cc_biadmin
Ticket cache: FILE:krb5cc_biadmin
Default principal: biadmin/btestserver.ibm.com@IBM.COM

Valid starting     Expires            Service principal
10/05/16 16:50:22  10/06/16 16:50:22  krbtgt/IBM.COM@IBM.COM
[biadmin@btestserver ~]$
[biadmin@btestserver ~]$


Run the Java code.

[biadmin@btestserver ~]$
[biadmin@btestserver ~]$ java -jar /opt/nisan/BigSQL_jdbc_kerberos.jar "btestserver.ibm.com" 51000
url: jdbc:db2://btestserver.ibm.com:51000/bigsql
Drop Hadoop & DB2 tables successfull!!!
Created Hadoop & DB2 tables successfully!!!
100
1,2,3
1,2,3
1,2,3
[biadmin@btestserver ~]$

3) Verifying the records inserted to the table

[biadmin@btestserver ~]$
[biadmin@btestserver ~]$ su bigsql
Password:
[bigsql@btestserver biadmin]$
[bigsql@btestserver biadmin]$ db2 "connect to bigsql"

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.6.2
 SQL authorization ID   = BIGSQL
 Local database alias   = BIGSQL

[bigsql@btestserver biadmin]$
[bigsql@btestserver biadmin]$ db2 "select * from biadmin.t1"
C1
-----------
        100

  1 record(s) selected.

[bigsql@btestserver biadmin]$
[bigsql@btestserver biadmin]$ db2 "select * from biadmin.tbint"
COL1        COL2        COL3
----------- ----------- -----------
          1           2           3
          1           2           3
          1           2           3

  3 record(s) selected.

[bigsql@btestserver biadmin]$

I will be covering the JDBC Application to connect to Apache Hive in my next blog.