Establishing a DB2 JDBC Connection

Recently I was forced outside my comfort zone and asked to vet various open source BI tools. I was a report developer in a past life, a database administrator supporting datamarts at various employers, and even supported Cognos backend databases. I thought my past experience gave me an edge when it came to evaluating BI tools.

I was immediately humbled when I had to create a simple JDBC connection in various tools I was evaluating. The connection just would not test successfully. Was the connection hamstrung by a Java configuration on my Mac? Could a read only database be a source? Was there a problem with the newly established floating IP? Did I have poor JDBC syntax?

Each troubleshooting iteration returned the digital equivalent of “You Shall Not Pass”! I eventually came to the conclusion that I needed to get back to basics. I needed to remove variables like a GUI or floating IP and complete a simple JDBC connection from my laptop command line.

Being relatively new to the development side of database administration, I’ve never had a need to really play with java or set up a JDBC connection. I had a lot of theory but little practice and found getting enough help from one location to be difficult. Eventually I was successful in getting guidance but was frustrated that the details I needed were not in one location. I am publishing my experience to help another wayward DBA.

Frenemies – Java and Mac

Java and Mac will begrudgingly play together. Macs often have an unusual version of Java where files and classes are filed in unexpected locations. Sometimes this can cause confusion with (or flat out break) software expecting a certain path to be in place. Once configured properly, Java on a Mac seems pretty solid. For the purpose of this article, we are just going to check that Java exists and can be called by command line.

1. Under “System Preferences”, there should be a Java icon. Java is not installed if this icon is missing.   There is a good installation resource on Java’s website if you need it – “How do I install Java for my Mac?”
2. In a terminal window, test to see if Java is loading into your environment.
   
   
3. If java –version returns an error, check your .profile or .bash_profile. I added the following line so Java would be available to my environment upon start.

   export JAVA_HOME=$(/usr/libexec/java_home)

   Sometimes a .profile will not load if .bash_profile exists. I use my .profile so I added the following lines to my .bash_profile . This allowed entries in my .profile to be executed as well.

   if [ -f ~/.profile ]; then
    . ~/.profile
   fi
   

JAR Files

A .jar file is a package file containing various classes and technical prerequisites to accomplish a task. To connect to a DB2 database you will call on one of two .jar files – db2jcc.jar (deprecated) or db2jcc4.jar (latest specifications).

If DB2 is installed on your laptop, these files can be found in a Java subdirectory of DB2 such as /opt/IBM/db2/V10.5/java . To be certain of the Java subdirectory location, look at the value assigned to JDK_PATH under the database manager configuration (DBM CFG).

If DB2 is not installed, you can download the proper files at IBM Support’s DB2 JDBC Driver Version and Downloads page.

Compiling and Executing a .java File

If you are a complete novice with Java, the first concept is understanding that code needs to be compiled. You cannot create a .java file in a text editor and expect to execute it. Use the “javac” command to compile code.

javac DB2ConnectionTest.java

To execute a .java file, use the “java” command. This will compile your code and create a “.class” file in your current directory. There are various flags and paths that can be used.

`java -cp "/path/to/JDBC/Driver:." DB2ConnectionTest`

The “-cp” flag points to the location of db2jcc.jar and db2jcc4.jar files. “:.” means “look in the current directory as well”. This directory will be where your .java connection test file and new .class file are located

Testing a Connection via a .java File

I discovered a code example that could natively check a JDBC connection. The original code was written by an unknown author at Justexample.com. Working with a friend (Jon Gnagy), I made a slight adjustment to code so I could execute it properly. This is the code that specifically worked for me.

/*
 Original example taken from "Just Example - Connect to DB2 in Java" (http://www.justexample.com/wp/connect-db2-java/), license unknown
 Comments, notes, and minor edits made by M. Krafick - Aug 6, 2017
 
 Purpose: Simple pass/fail of a DB2 JDBC connection string
 
 Pre-Req:
 - Make sure your Java environment is being loaded via .profile or .bash_profile
 - On Mac, I added this in my .profile: "export JAVA_HOME=$(/usr/libexec/java_home)"
 
 Usage Notes: 
 - Swap out String URL with Database Name and Port, User, Password as needed.
 - Save as "DB2ConnectionTest.java"
 - Compile with "javac DB2ConnectionTest.java"
 - Run with `java -cp "/path/to/JDBC/Driver:." DB2ConnectionTest`
 Notice you are directing class path (-cp) to where db2jcc.jar and db2jcc4.jar are located
 This is followed by :. which means look in current directory as well, this is where your .java file is saved 
*/

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

public class DB2ConnectionTest {

public static void main(String[] args) {
 String jdbcClassName="com.ibm.db2.jcc.DB2Driver";
 String url="jdbc:db2://servername:port/DBNAME";
 String user="UserID";
 String password="Password";

Connection connection = null;
 try {
 //Load class into memory
 Class.forName(jdbcClassName);
 //Establish connection
 connection = DriverManager.getConnection(url, user, password);

} catch (ClassNotFoundException e) {
 e.printStackTrace();
 } catch (SQLException e) {
 e.printStackTrace();
 }finally{
 if(connection!=null){
 System.out.println("Connected successfully.");
 try {
 connection.close();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
 }

}

}

I need to recognize a good friend and co-worker Jon Gnagy. Not only did he guide me through the last bit of troubleshooting and execution, he developed a more dynamic version of the same code. His version will execute the same test with variables instead of my hardcoded equivalent.

Want to see another take on the command with SSL? Check out comments from /u/dogmashah on the DB2 Subreddit.

Finally, both versions of the JDBC connection test code (my DB2ConnectionTest.java and Jon’s DB2Test.java) can be found via my GitHub account. I’ve been slowly moving various scripts and SQL that I find useful to this public account. Any scripts, SQL, or code you find there is free for you to use. Please note, I am not the strongest SQL/KSH writer in the world. You will find better ways to approach much of what you find there but the current versions work for me. Feel free to pull code you need but use at your own risk, no warranty is implied.

Michael Krafick is an IBM Champion and occasional contributor to db2commerce.com. He has been a DB2 LUW DBA for over 15 years in data warehousing, transactional OLTP environments, and e-commerce databases. He was recently inducted into the IDUG “Speaker Hall of Fame” and given the IBM “DB2 Community Leader Award” in 2015. Michael also has extensive experience in setting up monitoring configurations for DB2 Databases as well as preparing for high availability failover, backup, and recovery. He can be reached at “Michael.Krafick (at) icloud (dot) com”. Linked-in Profile: http://www.linkedin.com/in/michaelkrafick. Twitter: @mkrafick Reddit: https://www.reddit.com/r/DB2/

• Author Details
• Twitter

Michael Krafick

https://www.youtube.com/discoverdb2

michael.krafick@gmail.com

Michael Krafick is an aspiring Zamboni driver and well known twig-eater. During the day he is a Sr. Database Engineer for a Fortune 500 company. He is a frequent contributor to datageek.blog, an IBM champion, member of the DB2 Technical Advisory Board, and IDUG speaker Hall of Fame inductee. With over 18 years experience in DB2 for LUW, Mike works hard to educate and mentor others.

My Tweets