Triton Db2 Geek

Confessions of a DB2 geek

IBM Gold Consultant Program and IBM Premier business Partner

DB2 for z/OS Connectivity Testing

December 3rd, 2012 - by

 

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.

 

 

Comments

« »

Tag Archives