Access MS SQL server from iSeries using JDBC

A sample Java code is presented to demonstrate how to start a Microsoft SQL server job from the iSeries.

A sample Java code is presented to demonstrate how to start a Microsoft SQL server job from the iSeries -- using the JDBC connection. Two JDBC drivers are used, which can be added to the iSeries in order to access MS SQL Server from iSeries.

The Java sample can be used in cases where you have to run two procedures sequentially, the first on the iSeries and the second on the MS SQL server. The gain is that we treat the two procedures as one merged without any needed synchronization functions.

The two type 4 JDBC drivers, which can query SQL statements from the iSeries to the MS SQL server, are the Microsoft SQL server 2000 JDBC driver for 2000 series, or alternatively the jTDS JDBC driver which is an open source 100% pure Java JDBC 2.0 driver for the MS SQL Server series 6.5, 7.x and 2000.

Both JDBC drivers, being type 4, don't need any kind of installation. Just add the .jar files to your existing classpath and you have finished.

You can download the jTDS driver from here and from the Microsoft site the MSSQLServer.tar for UNIX-based systems (search to find the MSSQLServer.tar file). The .jar files are named jtds-0.5.jar and mssqlserver.jar, msbase.jar and msutil.jar correspondingly. In order to find the three .jar files of Microsoft driver, you must first unzip the mssqlserver.tar and then unzip the msjdbc.tar, which is contained into the mssqlserver.tar (using Winzip or a similar software).

Preparation steps:

1. Create on iSeries the folder /mssql
2. Upload the three MS SQL .jar files into the folder /mssql
3. Upload the jtds-0.5.jar into the folder /mssql
4. Upload the Java sample on iSeries into the folder /mssql
5. Edit the Java sample and select which of the two drivers you want to use and modify the driver manager component, filling the IP address, the userid and the password of MS SQL server. Also, fill the correct name of the job you want to execute with the sp_start_job procedure.
6. Compile the StartJob.java using the crtjavacl CL program
7. Run the StartJob.class using the startjobcl CL program


   
/* CRTJAVACL: Compiles the StartJob.java     */
 PGM
 ADDENVVAR  ENVVAR(CLASSPATH) +
 VALUE('/mssql/jtds-0.5.jar:+
        /mssql/mssqlserver.jar:+
        /mssql/msbase.jar:+
        /mssql/msutil.jar:.')
 MONMSG MSGID(CPFA980)
 CD DIR('/mssql')
 QSH CMD('javac StartJob.java')
 ENDPGM
 
 /* STARTJOBCL : Runs The StartJob.class      */
 PGM
 CD DIR('/mssql')
 RUNJVA  CLASS(StartJob) +
 CLASSPATH('/mssql/jtds-0.5.jar:+
            /mssql/mssqlserver.jar:+
            /mssql/msbase.jar:+
            /mssql/msutil.jar:.')
 ENDPGM

// StartJob.java: Starts the MS SQL Server Job
import java.sql.*;

public class StartJob {

  static Connection con  = null;
       
  public static Connection getConnection() {
  if (con != null) {return con;}
    try {
        Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        con = DriverManager.getConnection ("jdbc:microsoft:sqlserver://xxx.xxx.xxx.xxx;DatabaseName=MSDB","userid","pwd");
       // Class.forName("net.sourceforge.jtds.jdbc.Driver");
       //con = DriverManager.getConnection ("jdbc:jtds:sqlserver://xxx.xxx.xxx.xxx/MSDB;;","userid","pwd");
    }  catch( Exception e) {
          System.out.println("Error retrieving connection:" + e);
          return null;
    } 
    return con; 
  }

  public static void main(String[] args) {                     
  con = getConnection();
  if (con == null) {
     System.out.println("MS SQL Server is not available.");
     return;
  }
  try {
       Statement stmt1 = con.createStatement();
       // Check to see if the Job has status Idle (4)
       String sql1="exec sp_help_job @job_name = 'JobName' , @execution_status = 4";
       ResultSet rs1=stmt1.executeQuery(sql1);
       if (rs1.next()) { 
          Statement stmt2 = con.createStatement();
       // Execute the Job 
          String sql2="exec sp_start_job @job_name = 'JobName'";
          stmt2.executeUpdate(sql2); 
          stmt2.close();
       } else {
       System.out.println("Job hasn't the status Idle");
       }
       stmt1.close();
  } catch (SQLException e) {
    System.out.println("SQL error : " + e);
  } catch (Exception e) {
    System.out.println("SQL error : " + e);
  } 
  finally {
    try {
      if (con != null) {con.close();}
    } catch (SQLException e) {
      System.out.println("SQL error : " + e);
    } 
  }
  System.exit(0);
  }
}


==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.

This was first published in December 2002

Dig deeper on iSeries CL programming

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close