Dec
03
2012 Posted by James Gill

DB2 for z/OS Connectivity Testing

 

One of the challenges that customers face is validating the DDF configuration of a new service before making it available to mid-range application servers. In an ideal world, we’d all have DB2 Connect available to bash a quick configuration together and use the command line processor to connect and run some simple SQL as an IVP.

 

If you are one of the many sites that don’t have this (or you gave your DB2 subsystem a LOCATION name longer than 8 characters!) then the following might be useful.

 

JDBC connectivity has been shipped with DB2 for z/OS for quite a while now. The Type 4 drivers were delivered with DB2 V8 and these can be used with a simple Java application within Unix System Services (USS) in z/OS, i.e.

 

// parms:

// 1. ip address/url name

// 2. port number

// 3. location name to use

// 4. userid to connect to DDH0 with

// 5. password for userid

// 6. (optional) driver type – 2 or 4 (default)

//

import java.sql.*;

import java.util.Properties;

//import com.ibm.db2.jcc.DB2BaseDataSource;

 

public class chkjdbc

{

public static Connection con;

 

public static void main(String argv[]) throws Exception

{

String server = “”;

String port = “”;

String location = “”;

String userid = “”;

String password = “”;

String jdbctype = “”;

System.out.println(” “);

System.out.println(“chkjdbc”);

System.out.println(“=======”);

System.out.println(“Testing JDBC connectivity to DB2 for z/OS.”);

System.out.println(” “);

if ((argv.length != 5) & (argv.length != 6)) {

System.out.println(“Parms:”);

System.out.println(” “);

System.out.println(“1. ip address / ip name of database server”);

System.out.println(“2. port number of DB2 on database server”);

System.out.println(“3. location name of the target DB2”);

System.out.println(“4. userid to connect to DB2 with”);

System.out.println(“5. password for userid”);

System.out.println(“6. (optional) driver type – 2 or 4 (default)”);

System.out.println(” “);

} else {

try

{

server = argv[0];

port = argv[1];

location = argv[2];

userid = argv[3];

password = argv[4];

if (argv.length == 6) {

jdbctype = argv[5];

} else {

jdbctype = “4”;

}

 

// construct the URL

String url = “jdbc:db2://”+server+”:”+port+”/”+location;

if (jdbctype.equals(“2”)) {

url = “jdbc:db2:”+location;

}

 

Class.forName(“com.ibm.db2.jcc.DB2Driver”).newInstance();

 

Properties mfprop = new Properties();

mfprop.setProperty(“driverType”,jdbctype);

mfprop.setProperty(“clientProgramName”,”chkjdbc”);

mfprop.setProperty(“loginTimeout”,”5″);

mfprop.setProperty(“readOnly”,”true”);

mfprop.setProperty(“resultSetHoldability”,new String(“” + com.ibm.db2.jcc.DB2BaseDataSource.CLOSE_CURSORS_AT_COMMIT + “”));

mfprop.setProperty(“securityMechanism”,new String(“” + com.ibm.db2.jcc.DB2BaseDataSource.ENCRYPTED_USER_AND_PASSWORD_SECURITY + “”));

mfprop.setProperty(“user”,userid);

mfprop.setProperty(“password”,password);

 

System.out.println(“Connecting type “+jdbctype+” jdbc with url : “+url);

con = DriverManager.getConnection(url, mfprop);

 

System.out.println(“Connected – running SQL”);

Statement stmt = con.createStatement();

 

// issue sql query

String sql = “SELECT CURRENT MEMBER,CURRENT SERVER,CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1″;

ResultSet rs = stmt.executeQuery(sql);

System.out.println(” “);

System.out.println(“Results:”);

while (rs.next())

{

String cmb = rs.getString(1);

String csv = rs.getString(2);

String cts = rs.getString(3);

System.out.println(”  MEMBER    : ” + cmb.trim());

System.out.println(”  SERVER    : ” + csv.trim());

System.out.println(”  TIMESTAMP : ” + cts.trim());

}

System.out.println(” “);

System.out.println(“*** end ***”);

 

// tidy up nicely

rs.close();

stmt.close();

con.close();

}

catch(Exception e)

{

e.printStackTrace();

}

}

}

}

 

 

Compiled with the following script / environment in USS:

 

export JAVA_HOME=/usr/lpp/java/J6.0.1

export JDBC=/usr/lpp/db2a10/jdbc

export APP_HOME=/u/myid

 

export PATH=£PATH:£JAVA_HOME/bin:£JAVA_HOME/bin/j9vm

 

export LIBPATH=/lib:/usr/lib:£APP_HOME:”£{JAVA_HOME}”/bin

export LIBPATH=”£LIBPATH”:”£{JAVA_HOME}”/lib/s390

export LIBPATH=”£LIBPATH”:”£{JAVA_HOME}”/lib/s390/j9vm

export LIBPATH=”£LIBPATH”:”£{JAVA_HOME}”/bin/classic

export LIBPATH=”£LIBPATH”:”£{JCC_HOME}”/lib

 

export CLASSPATH=£CLASSPATH:£JDBC/classes/db2jcc.jar

export CLASSPATH=£CLASSPATH:£JDBC/classes/db2jcc_license_cisuz.jar

export CLASSPATH=£CLASSPATH:£APP_HOME

 

cd £APP_HOME

javac chkjdbc.java

 

Note that this build is against a V10 installation (JDBC=/usr/lpp/db2a10/jdbc), but this works just as well with DB2 V8 and V9.

Note also that the JDBC drivers are currently (according to the trace messages!) verified against Java 1.4, 1.5 and 1.6 – NOT 1.7.

 

Run with the same environment in USS or JZOS batch Java tool ($JAVA_HOME/mvstools/…) as above:

 

java chkjdbc <ipaddr> <port> <location> <userid> <password> { <jdbctype> }

 

An example – our old V8 service produces the following DSNL004I messages when DDF starts:

 

DSNL004I  -DB2T DDF START COMPLETE  884

LOCATION  DB2TSYSTEM

LU        ADCD.DB2TLU

GENERICLU -NONE

DOMAIN    192.168.200.1

TCPPORT   5024

RESPORT   5025

 

To test this, we run:

 

cd £APP_HOME

java chkjdbc 192.168.200.1 5024 DB2TSYSTEM myid mypassword

 

This produces the following:

 

chkjdbc

=======

Testing JDBC connectivity to DB2 for z/OS.

 

com.ibm.net.SocketKeepAliveParameters

Connecting type 4 jdbc with url : jdbc:db2://192.168.200.1:5024/DB2TSYSTEM

Connected   running SQL

 

Results:

MEMBER    :

SERVER    : DB2TSYSTEM

TIMESTAMP : 2012-12-03 14:41:40.35917

 

*** end ***

 

Data sharing services will populate the MEMBER output field as well.

 

Okay, this is all well and good, but what do we do if it doesn’t work? Well, the IBM Data Server Driver can help us out there as well, as we can turn on diagnostic tracing in the default properties file. We haven’t needed this so far, but as we pick up our Java class from a directory in the CLASSPATH (rather than a JAR), we can create it in the same place, i.e.

 

£APP_HOME/DB2JccConfiguration.properties, or

/u/myid/DB2JccConfiguration.properties

 

Put the following entries in this file to get diagnostic tracing:

 

db2.jcc.traceDirectory=/u/myid/traceout

db2.jcc.traceLevel=-1

 

These set the output path to the /u/myid/traceout directory, and turn on all traces. Note that the trace file will be overwritten each time you run the Java.

 

Finally, what with Java being portable, the class should be just as usable on a PC as on z/OS – but remember that you will need the driver and license JAR files, which are typically available with higher end DB2 LUW configurations, or with DB2 Connect.

 

 

« | »
Have a Question?

Get in touch with our expert team and see how we can help with your IT project, call us on +44(0) 870 2411 550 or use our contact form…