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.