z/OS DB2 connectivity using Java client

Connecting to a DB2 instance hosted on z/OS(Mainframe) requires a series of configuration and coding steps. This blog post explains the steps:

· Request EXECUTE privileges on package ‘NULLID.SYSLH200’ for user login id

· Get the below information from SDSF log for DB2 startup job ‘DB8’. This data will be used while configuring the Java client.

DB8GMSTR Log

Java Reference

DOMAIN

Host Name

LOCATION

DB2 Server Name

TCPPORT

TCP Port Number

·        Step#1 : Execute following SQL through SPUFI

CREATE PROCEDURE EMPDTLS1
    (
         IN  IN_EMP_NAME   CHAR(25) ,
         OUT OUT_PRO_NAME  CHAR(25)
    )
    EXTERNAL NAME SPEMPTAB
    LANGUAGE COBOL
    PARAMETER STYLE GENERAL
    MODIFIES SQL DATA
    WLM ENVIRONMENT DB8GENVC
    COLLID COLLID11

·        Step#2 : COBOL source for the stored procedure with external name SPEMPTAB

·        Step#3 : Compile and bind
EXEC SQL
   INCLUDE EMPTAB
END-EXEC.
LINKAGE SECTION.
01 E-NAME               PIC X(25).
01 P-NAME               PIC X(25).
PROCEDURE DIVISION USING E-NAME, ROJ-NAME.
EXEC SQL
   SELECT PROJ_NAME INTO :ROJ-NAME FROM ID0011.EMP_TABLE
        WHERE EMP_NAME = :E-NAME
   END-EXEC.

o   Compile this program and store load module in DSN810.RUNLIB.LOAD

o   DSN810.RUNLIB.LOAD is the library the WLM DB8GENVC looks into for load modules

o   Package bind the DBRM to a collection (like COLLID11)

o   This collection name should match with the one used in Stored Procedure CREATE statement

·        Java client requirements

o   IBM JDBC Type-4 Drivers are needed (db2jcc.jar and db2jcc_license_cisuz.jar). These are typically found in any DB2 Server or Client installations. You also get them from z/OS HFS path – /VERSYSB/usr/lpp/db2910_jdbc/classes

o   The user machine should have access to z/OS host IP address and DB2 server TCP port

·        Sample java client code is mentioned below

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

public class Test {
	static String hostIP = "IP Address";
	static String hostTCP = "446";
	static String hostLocName = "Location Name";
	static String uName = "user id";
	static String uPswd = "password";
	static String stProc = "EMPDTLS1(?,?)";
	static String scName = "schema name";

	public static void main(String[] args) {
		String emp_id, emp_name, proj_name;
		try {
			// load the DB2 Driver
			Class.forName("com.ibm.db2.jcc.DB2Driver");
			System.out.println("Step 1 : Class loaded");

			// establish a connection to DB2
			String jdbcURL = "jdbc:db2://" + hostIP + ":" + hostTCP + "/"
					+ hostLocName;
			Connection db2Conn = DriverManager.getConnection(jdbcURL, uName,
					uPswd);

			System.out.println("Step 2 : Connection established");

			// Use a statement to gather data from the database
			Statement st = db2Conn.createStatement();
			String myQuery = "SELECT * FROM ID001.EMP_TABLE";
			// **** Execute the query
			ResultSet resultSet = st.executeQuery(myQuery);
			System.out.println("Step 3: Query execution is complete");

			while (resultSet.next()) {
				emp_id = resultSet.getString("emp_id");
				emp_name = resultSet.getString("emp_name");
				proj_name = resultSet.getString("proj_name");
				System.out.println(emp_id + " : " + emp_name + " : "
						+ proj_name);
			}

			st = db2Conn.createStatement();

			// **** Invoke a Stored Procedure
			String callStmt = "{call " + scName + "." + stProc + "}";
			CallableStatement csmt = db2Conn.prepareCall(callStmt);
			csmt.setString(1, "ALPHA");
			csmt.registerOutParameter(2, java.sql.Types.CHAR);
			boolean flag = csmt.execute();
			System.out.println("Step 3 : Stored Procedure Executed");
			System.out.println("SP Output : " + csmt.getString(2));
			// Clean up resources

			resultSet.close();
			st.close();
			db2Conn.close();
		} catch (ClassNotFoundException cnfe) {
			cnfe.printStackTrace();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
	}
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s