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.

3 comments:

Coepd BA Trainings said...

We at Coepd declared Data Science Internship Programs (Self sponsored) for professionals who want to have hands on experience. We are providing this program in alliance with IT Companies in COEPD Hyderabad premises. This program is dedicated to our unwavering participants predominantly acknowledging and appreciating the fact that they are on the path of making a career in Data Science discipline. This internship is designed to ensure that in addition to gaining the requisite theoretical knowledge, the readers gain sufficient hands-on practice and practical know-how to master the nitty-gritty of the Data Science profession. More than a training institute, COEPD today stands differentiated as a mission to help you "Build your dream career" - COEPD way.

http://www.coepd.com/AnalyticsInternship.html

Muoro Infotech said...

Hi, Thank you for sharing such useful information. I have gone through your blog post it's such a useful and nice information to know about. If you are looking to know more about the Big Data Management and to get to know about the latest trends just move to www.muoro.io

logistic-solutions said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
Big Data Analytics Consulting for Hadoop
sas strategic consulting services