Java – JDBC program to connect IBM DB2 database running on Mainframe z/OS system

August 18, 2017 SJ JDBC 0

In this article, we will learn and list down the steps to connect IBM DB2 database running on Mainframe z/OS system in Java and finally executing a simple query to test whether connected database works as expected

Connect IBM DB2 database :

We will divide this article into 2 parts

  1. IBM DB2 database
  2. JDBC application to connect IBM DB2 database

1. Set-up IBM DB2 Database :

Before working with JDBC API to interact with database (to be specific IBM DB2 database for this example), we need to set up IBM DB2 database and create required things like

  • Create database
  • Create table (inside newly created database)
  • Insert few sample records (inside newly created table)
  • For this example, we will create database called “benchresources”

1.1 Create database :

CREATE DATABASE benchresources;

Next step is to create new table and insert couple of records

1.2 Create table command :

CREATE TABLE benchresources.PLAYER (
  PLAYER_ID INT(6) NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(50) NOT NULL,
  AGE INT(3) NOT NULL,
  MATCHES INT(3) NOT NULL,
  PRIMARY KEY (PLAYER_ID)
);

1.3 Insert new records :

INSERT INTO PLAYER(NAME, AGE, MATCHES) VALUES ('Sachin Tendulkar',41,200);
INSERT INTO PLAYER(NAME, AGE, MATCHES) VALUES ('Shane Warne',44,145);
INSERT INTO PLAYER(NAME, AGE, MATCHES) VALUES ('Kevin Pietersen',34,104);
INSERT INTO PLAYER(NAME, AGE, MATCHES) VALUES ('Shahid Afridi',35,27);
INSERT INTO PLAYER(NAME, AGE, MATCHES) VALUES ('Brian Lara',45,131);
INSERT INTO PLAYER(NAME, AGE, MATCHES) VALUES ('Graeme Smith',34,117);
INSERT INTO PLAYER(NAME, AGE, MATCHES) VALUES ('Mahela Jayawardene',37,145);

1.4 Query table to view inserted records :

  • All done with IBM DB2 database part, except querying to fetch all rows
Select * from PLAYER;
  • See below screen-capture to see new records

2. Java JDBC application :

  • As we are completed set up & ready with IBM DB2 database
  • next step is to figure out essential things required to query database
  • from Java application using JDBC API

2.1 Pre-requisite :

  • Download db2jcc, db2jcc_license_cu & db2jcc_license_cisuz jars to be included in the project classpath
  • Loading DB2 driver class (com.ibm.db2.jcc.DB2Driver)
  • Database URL formation (server IP address, port number, database name)
  • Username
  • Password

2.2 To download required JARS :

2.3 Important parameters to connect IBM DB2 database :

  • Server name/IP –> localhost
  • Port number –> 50002
  • Database name –> benchresources
  • Username –> test
  • Password –> test@123

2.4 JDBC program to connect and query Mainframe database/table :

  • Once we are ready with above listed things
  • Then we can go ahead and code an example to connect IBM DB2 database
  • Finally querying database

 IbmDb2DatabaseConnectionInJava.java

package in.bench.resources.ibm.db2.example;

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

public class IbmDb2DatabaseConnectionInJava {

	public static void main(String[] args) {

		// local variables
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;

		// Step 1: Loading or
		// registering IBM DB2 JDBC driver class
		try {
			Class.forName("com.ibm.db2.jcc.DB2Driver");
		}
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in"
					+ " loading or registering IBM DB2 JDBC driver");
			cnfex.printStackTrace();
		}

		// Step 2: Opening database connection
		try {

			// Step 2.A: Create and
			// get connection using DriverManager class
			connection = DriverManager.getConnection(
					"jdbc:db2://localhost:50002/benchresources",
					"test",
					"test@123");

			// Step 2.B: Creating JDBC Statement
			statement = connection.createStatement();

			// Step 2.C: Executing SQL and retrieve data into ResultSet
			resultSet = statement.executeQuery("SELECT * FROM PLAYER");

			System.out.println("ID\tName\t\t\tAge\tMatches");
			System.out.println("==\t================\t===\t=======");

			// processing returned data and printing into console
			while(resultSet.next()) {
				System.out.println(resultSet.getInt(1) + "\t" +
						resultSet.getString(2) + "\t" +
						resultSet.getInt(3) + "\t" +
						resultSet.getInt(4));
			}

		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
		finally {
			// Step 3: Closing database connection
			try {
				if(null != connection) {
					// cleanup resources, once after processing
					resultSet.close();
					statement.close();

					// and then finally close connection
					connection.close();
				}
			}
			catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
	}
}

Output:

ID	Name			Age	Matches
==	================	===	=======
1	Sachin Tendulkar	43	200
2	Shane Warne		45	145
3	Kevin Pietersen	36	104
4	Shahid Afridi		36	27
5	Brian Lara		46	131
6	Graeme Smith		36	117
7	Mahela Jayawardene	38	145

Related Articles:

References :

Happy Coding !!
Happy Learning !!

Java 8 - Connect to MS Access database using JDBC

Related posts:

  1. Java – Creating database using JDBC Statement interface
  2. Java – Dropping a table using JDBC Statement interface
  3. Java – Deleting a record using JDBC PreparedStatement interface
  4. Java – Metadata of database using JDBC DatabaseMetaData interface