Wednesday, October 5, 2016

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.

No comments: