DB2 9 Application Development exam 733 prep, Part 7: Java programming

Learn about writing Java® code that interfaces with DB2 databases. This is Part 7 in a series of nine tutorials that you can use to help prepare for the IBM® DB2® 9 Family Application Development Certification exam (Exam 733).

Share:

Dirk deRoos (dirk.deroos@gmail.com), Information Architect, IBM

Dirk deRoos (BA, BCS) is the information architect for the DB2 Information Development team. He has recently authored DB2 and the Windows Developer (IBM developerWorks, 2005), coauthored The Official Guide to DB2 Version 8.1.2 (Prentice-Hall, 2003), and wrote chapters for DB2: The Complete Reference (Osborne/McGraw-Hill, 2001). He is a DB2 Certified Solutions Expert (Application Development, Business Intelligence). You can reach Dirk at dirk.deroos@gmail.com.



22 February 2007

Also available in Vietnamese

Before you start

About this series

The IBM Certified Application Developer certification confirms to others that you are an intermediate or advanced level DB2 for Linux, UNIX, and Windows application developer and shows that you have strong skills in all common programming tasks as well as embedded SQL programming, ODBC/CLI programming, .Net programming, or Java programming.

This series of nine free tutorials is designed to help you prepare for the DB2 9 Application Development for Linux, UNIX and Windows certification exam (Exam 733). Each tutorial includes a link to a free DB2 9 for Linux, UNIX, and Windows trial download. These tutorials provide a solid base for each section of the exam. However, you should not rely on these tutorials as your only preparation for the exam.

About this tutorial

In this tutorial, you will learn about writing Java code that interfaces with DB2 databases. You'll see how to:

  • Connect to a DB2 database from a Java application
  • Use JDBC to read and update data in a DB2 database
  • Use SQL for Java (SQLJ) to read and update data in a DB2 database
  • Troubleshoot a DB2 Java application

This tutorial provides you with the fundamental skills required to develop Java applications for DB2.

Prerequisites

To take the DB2 9 Family Application Development exam, you must have already passed the DB2 9 Family Fundamentals exam (Exam 730). You can use the DB2 Family Fundamentals tutorial series (see the Resources section) to prepare for that exam. It is a very popular series that has helped many people understand the fundamentals of the DB2 family of products.

Although not all materials discussed in the Family Fundamentals tutorial series are required to understand the concepts described in this tutorial, you should have a basic knowledge of:

  • DB2 instances
  • Databases
  • Database objects
  • DB2 security

You should also have basic knowledge of SQL and the Java platform before taking this tutorial. You can review the Resources section at the end of this tutorial for more information.

System requirements

You do not need a copy of DB2 to complete this tutorial. However, you can download a free copy of DB2 Express-C from the DB2 Express-C downloads page.

If you plan to develop Java applications for DB2, or you plan to try out this tutorial's sample code yourself, you need to complete the following tasks:

  1. Install DB2 9 for Linux, UNIX, and Windows.
  2. Create the sample database. You can do this from the First Steps Database Creation menu, or by running db2sampl from the DB2 Command Line Processor (CLP). The sample code in this article has been written to work with this database.
  3. Install the Java Runtime Environment, Java 2 Technology Edition Version 1.4.2.
  4. Update the PATH environment variable to include the jdk\bin directory of the Java SDK you have installed. Note that DB2 includes Java SDK Version 5, in the $DB2PATH\sqllib\java\jdk\bin directory ($DB2PATH/sqllib/java/jdk/bin in UNIX or Linux).

Development environments

Overview

There are many environments where you can develop your Java applications for use with DB2 databases. DB2 9.1 provides two options:

  • The DB2 Developer Workbench: A graphical integrated development environment.
  • The DB2 command line: A bare-bones environment where you can execute commands or scripts to build and run applications you have written in a text editor.

DB2 Developer Workbench

The DB2 Developer Workbench is an Eclipse-based development environment that provides the following capabilities, in addition to Eclipse's Java application development infrastructure:

  • Browsing and creating database objects
  • Creating, editing, debugging, deploying, and testing SQL stored procedures and user-defined functions
  • Developing SQLJ applications
  • Creating, editing, and running SQL statements
  • Creating, editing, and running XQuery expressions

The DB2 Developer Workbench is available as a free download (see the Resources section).

DB2 command line

From the DB2 command line, you can build and run your Java database applications using the javac, java, and sqljcustomize commands. The examples in this tutorial demonstrate how to build and run both JDBC and SQLJ applications from the DB2 command line.


Connect to a database

Overview

JDBC is a Java API through which Java programs can connect to relational databases and execute SQL statements. To enable Java applications to communicate with its databases, DB2 includes JDBC drivers.

In this section, you learn how to load the most appropriate JDBC driver for your application, and how to connect to a DB2 database.

JDBC drivers

A JDBC driver acts as an interface between a JDBC program and a database. DB2 includes two JDBC drivers: the DB2 JDBC Type 2 Driver, and the IBM DB2 Driver for JDBC and SQLJ.

  • DB2 JDBC Type 2 Driver: Also known as the app driver. Java applications that use this driver must run on a DB2 client, through which JDBC requests flow to a DB2 server. This driver is deprecated in DB2 9.1, and future releases of DB2 will not support or include this driver. It is recommended that you use the IBM DB2 Driver for JDBC and SQLJ instead of the DB2 JDBC Type 2 Driver. To load the DB2 JDBC Type 2 Driver, invoke the Class.forName() method with COM.ibm.db2.jdbc.app.DB2Driver as an argument.
  • IBM DB2 Driver for JDBC and SQLJ: It provides both Type 2 and Type 4 connectivity. You can use this driver for both applets and applications; it is the recommended driver for both. To load the IBM DB2 Driver for JDBC and SQLJ, invoke the Class.forName() method with com.ibm.db2.jcc.DB2Driver as an argument.

To use either of these JDBC drivers in your application, you need to import the Java packages that contain the JDBC API:

import java.sql.*;

All of the code examples and sample applications in this tutorial have been tested to work with the IBM DB2 Driver for JDBC and SQLJ.

Make a database connection

Once you've loaded the appropriate JDBC driver, you can connect to a database from your JDBC application. In JDBC applications, a database connection is represented by a Connection object. From a DriverManager object (available for use once the JDBC driver has been loaded), you can use the getConnection() method to acquire a Connection. In the following example, the IBM DB2 Driver for JDBC and SQLJ is loaded, and the DriverManager object generates a Type 2 database connection.

String url = "jdbc:db2:sample";
Class.forName("com.ibm.db2.jcc.DB2Driver");
Connection con = DriverManager.getConnection(url);

For the IBM DB2 Driver for JDBC and SQLJ, the type of database connection is determined by the URL passed to the DriverManager.getConnection() method. In URLs such as the following, where a domain name and port number of the database server are listed, the DriverManager object generates a Type 4 database connection.

String url = "jdbc:db2:sample://localhost:5021";

You can also create Connection objects using the DataSource interface. This interface is recommended if you are writing code that will be used with multiple database servers and JDBC drivers. The DriverManager interface is not portable, as it requires the application to specify a vendor-specific JDBC driver class name and driver URL. DataSource objects can be defined and managed on systems where your applications runs, using a tool such as WebSphere. Using such a tool, an administrator assigns logical names to DataSource objects and registers them in a Java Naming and Directory Interface (JNDI) directory. The Java application that uses the DataSource object can then refer to it by its logical name, and does not need any information about the underlying data source.

Database connection: Sample code

The sample application in this section demonstrates the following concepts:

  • Importing the Java packages that contain the JDBC API
  • Loading the IBM DB2 Driver for JDBC and SQLJ
  • Creating a Connection object
  • Using the DriverManager to open a Connection

This application represents a shell of a typical JDBC application -- the JDBC packages are imported, there is a database connection, and there is error handling logic in the try/catch blocks. To demonstrate that a database connection is actually made, the Connection object requests the name of the JDBC driver being used with the Connection.getMetaData().getDriverName() methods.

//ConnDb.java
import java.sql.*;

class ConnDb
{
  public static void main(String[] argv)
  {
    String url = "jdbc:db2:sample";
    Connection con;

    try
    {
      Class.forName("com.ibm.db2.jcc.DB2Driver");
      con = DriverManager.getConnection (url);

      System.out.println("JDBC driver name: " +
         con.getMetaData().getDriverName());

      con.close();
    }
    catch (ClassNotFoundException drvEx)
    {
      System.err.println("Could not load JDBC driver");
      System.out.println("Exception: " + drvEx);
      drvEx.printStackTrace();
    }
    catch(SQLException sqlEx)
    {
      while(sqlEx != null) {
        System.err.println("SQLException information");
        System.err.println("Error msg: " + sqlEx.getMessage());
        System.err.println("SQLSTATE: " + sqlEx.getSQLState());
        System.err.println("Error code: " + sqlEx.getErrorCode());
        sqlEx.printStackTrace();
        sqlEx=sqlEx.getNextException();
      }
    }
  }
}

To compile the above file (named ConnDb.java), execute the following command:

javac ConnDb.java

To run the compiled application, execute the following command:

java ConnDb

The application's output should look like:

JDBC driver name: IBM DB2 JDBC Universal Driver Architecture

Read and update data with JDBC

Overview

In this section, you'll learn how to use the Statement and PreparedStatement JDBC objects, which represent SQL statements in JDBC. You'll also learn how to use the JDBC ResultSet object, which is returned by Statement and PreparedStatement objects that contain SQL queries.

The Statement object

Statement objects are created using the Connection.createStatement() method. For example:

Statement stmt;
...
stmt = con.createStatement();

To execute an INSERT, UPDATE, or DELETE statement from a Statement object, pass a string with the statement to the Statement.executeUpdate() method. For example:

stmt.executeUpdate("DELETE FROM EMPLOYEE WHERE EMPNO = '000099'");

To execute a query using a Statement object, pass a string with the SELECT statement to the Statement.executeQuery() method, and retrieve the ResultSet object. For example:

ResultSet rs;
rs = stmt.executeQuery("SELECT EMPNO, LASTNAME FROM EMPLOYEE");

To parse a ResultSet object, you must first fetch each row using the ResultSet.next() method. Then, after each fetch, retrieve the column values using the methods applicable to the data type (for instance, ResultSet.getInt()).

rs = stmt.executeQuery("SELECT LASTNAME, BIRTHDATE FROM EMPLOYEE");
while (rs.next()) {
  System.out.println(rs.getString(1) + ", " + rs.getDate(2));
}

Statement object: Sample code

The following application demonstrates all the concepts discussed in the previous panel:

  • Creating a Statement object
  • Executing an INSERT, UPDATE, or DELETE statement
  • Executing an SQL query
  • Parsing a ResultSet object

This application inserts an employee record into the Employee table, and then runs a query against that table, requesting the employee number and last name for every record.

//StmtDb.java
import java.sql.*;

class StmtDb
{
  public static void main(String[] argv)
  {
    String url = "jdbc:db2:sample";
    Connection con;
    Statement stmt1, stmt2;
    ResultSet rs;
    String stmt1String =
       "INSERT INTO EMPLOYEE (EMPNO,FIRSTNME,MIDINIT,LASTNAME,EDLEVEL)" +
       " VALUES ('000099','MICHIEL','G','DEROOS',1)";
    String stmt2String = "SELECT EMPNO, LASTNAME FROM EMPLOYEE";

    try
    {
      Class.forName("com.ibm.db2.jcc.DB2Driver");
      con = DriverManager.getConnection(url);
      con.setAutoCommit(true);

      stmt1 = con.createStatement();
      stmt2 = con.createStatement();

      stmt1.executeUpdate(stmt1String);
      rs = stmt2.executeQuery(stmt2String);

      System.out.println("Employee #  Employee surname");
      while (rs.next()) {
        System.out.println(rs.getString(1) + "      " + rs.getString(2));
      }

      rs.close();
      stmt1.close();
      stmt2.close();
      con.close();

    }
    catch (ClassNotFoundException drvEx)
    {
      System.err.println("Could not load JDBC driver");
      System.out.println("Exception: " + drvEx);
      drvEx.printStackTrace();
    }
    catch(SQLException sqlEx)
    {
      while(sqlEx != null) {
        System.err.println("SQLException information");
        System.err.println("Error msg: " + sqlEx.getMessage());
        System.err.println("SQLSTATE: " + sqlEx.getSQLState());
        System.err.println("Error code: " + sqlEx.getErrorCode());
        sqlEx.printStackTrace();
        sqlEx=sqlEx.getNextException();
      }
    }
  }
}

To compile the above file (named StmtDb.java), execute the following command:

javac StmtDb.java

To run the compiled application, execute the following command:

java StmtDb

The output of this application should look like:

Employee #  Employee surname
000010      HAAS
000020      THOMPSON
000030      KWAN
000050      GEYER
000060      STERN
000070      PULASKI
000090      HENDERSON
000100      SPENSER
000110      LUCCHESSI
000120      O'CONNELL
000130      QUINTANA
000140      NICHOLLS
000150      ADAMSON
000160      PIANKA
000170      YOSHIMURA
000180      SCOUTTEN
000190      WALKER
000200      BROWN
000210      JONES
000220      LUTZ
000230      JEFFERSON
000240      MARINO
000250      SMITH
000260      JOHNSON
000270      PEREZ
000280      SCHNEIDER
000290      PARKER
000300      SMITH
000310      SETRIGHT
000320      MEHTA
000330      LEE
000340      GOUNOT
200010      HEMMINGER
200120      ORLANDO
200140      NATZ
200170      YAMAMOTO
200220      JOHN
200240      MONTEVERDE
200280      SCHWARTZ
200310      SPRINGER
200330      WONG
200340      ALONZO
000099      DEROOS

The PreparedStatement object

PreparedStatement objects are created using the Connection.prepareStatement() method. For example:

PreparedStatement stmt;
...
stmt = con.prepareStatement();

With a PreparedStatement object, you can dynamically prepare and execute SQL statements. By rebinding values to parameter markers in SQL statements, you can execute the same statement multiple times using different values. (A parameter marker is represented by a ?, and acts as a placeholder for a value to be assigned at run time.) For example:

pStmt = con.prepareStatement("UPDATE STAFF SET COMM=? WHERE ID=?");
pStmt.setDouble(1,710.53);
pStmt.setInt(2,350);
pStmt.executeUpdate();

pStmt.setDouble(1,710.53);
pStmt.setInt(2,350);
pStmt.executeUpdate();

For more on parameter markers, read the eighth article in this series (see the Resources section).

PreparedStatement object: Sample code

The following application demonstrates all the concepts discussed in the previous panel:

  • Creating a PreparedStatement object
  • Using parameter markers to dynamically assign values in SQL statements

This application parses through an array of names and job titles. For each array entry, it inserts a record in the staff table.

//pStmtDb.java
import java.sql.*;

class pStmtDb
{
  public static void main(String[] argv)
  {
    String url = "jdbc:db2:sample";
    Connection con;
    PreparedStatement pStmt;
    Statement sStmt;
    ResultSet rs;
    String stmtString = "INSERT INTO STAFF (ID,NAME,DEPT,JOB) VALUES (?,?,99,?)";
    String [][] staff = {{"Smyth","LW"},{"Hemsky","RW"},{"Horcoff","C"}};
    
    try
    {
      Class.forName("com.ibm.db2.jcc.DB2Driver");
      con = DriverManager.getConnection(url);
      con.setAutoCommit(false);

      pStmt = con.prepareStatement(stmtString);

      for (int i=0; i<3; i++) {
        pStmt.setInt(1,401+i);
        pStmt.setString(2,staff[i][0]);
        pStmt.setString(3,staff[i][1]);
        pStmt.executeUpdate();
      }
      
      con.commit();

      sStmt = con.createStatement();
      rs = sStmt.executeQuery("SELECT ID, NAME FROM STAFF WHERE DEPT = 99");

      System.out.println("Employee #  Employee surname");
      while (rs.next()) {
        System.out.println(rs.getString(1) + "         " + rs.getString(2));
      }

      rs.close();
      pStmt.close();
      sStmt.close();
      con.commit();
      con.close();
    }
    catch (ClassNotFoundException drvEx)
    {
      System.err.println("Could not load JDBC driver");
      System.out.println("Exception: " + drvEx);
      drvEx.printStackTrace();
    }
    catch(SQLException sqlEx)
    {
      while(sqlEx != null) {
        System.err.println("SQLException information");
        System.err.println("Error msg: " + sqlEx.getMessage());
        System.err.println("SQLSTATE: " + sqlEx.getSQLState());
        System.err.println("Error code: " + sqlEx.getErrorCode());
        sqlEx.printStackTrace();
        sqlEx=sqlEx.getNextException();
      }
    }
  }
}

To compile the above file (named pStmtDb.java), execute the following command:

javac pStmtDb.java

To run the compiled application, execute the following command:

java pStmtDb

The output of this application should look like the following:

Employee #  Employee surname
401         Smyth
402         Hemsky
403         Horcoff

Commit transactions

In the Statement and PreparedStatement code samples, two different approaches to committing transactions were used: autocommit and manual commit.

The approach used in the Statement sample was autocommit, where each statement was automatically committed. Autocommit is enabled from the Connection object, as follows:

con.setAutoCommit(true);

Unlike previous releases, the DB2 9 edition of the IBM DB2 Driver for JDBC and SQLJ will throw an exception if you execute Connection.Commit() or Connection.Rollback() when autocommit mode is enabled.

The approach used in the PreparedStatement sample was manual commit. With this approach, each statement is either manually committed or rolled back. If statements are not committed by the end of the application, they are automatically rolled back. Manual commit or rollback operations are performed from the Connection object, as follows:

con.commit();
...
con.rollback();

Issue distributed transactions

To issue distributed transactions from your Java applications, you need to use the Java Transaction API (JTA). (Distributed transactions, also called two-phase commit transactions, are transactions that update data in more than one database.) The JTA specification is the transaction management component of the Java 2 Platform, Enterprise Edition (J2EE) standard.

In DB2, distributed transactions are managed through the DB2XADataSource class, which implements the XADataSource interface from the javax.sql package.

For DB2 9, JTA support is provided in both the IBM DB2 Driver for JDBC and SQLJ and the DB2 JDBC Type 2 Driver. The following Java code creates an instance of the DB2XADataSource class using the IBM DB2 Driver for JDBC and SQLJ:

DB2XADataSource db2ds = new com.ibm.db2.jcc.DB2XADataSource();

Read and update data with SQLJ

Overview

The SQLJ API is an extension of JDBC, and it supports the static execution of SQL statements. Because DB2 supports SQLJ, Java developers can overcome a major limitation of JDBC, namely that JDBC can only execute SQL statements dynamically. Statically bound SQL statements typically run faster than dynamically bound statements, which gives SQLJ applications a significant performance advantage over JDBC applications.

In this section, you'll learn how to program with the SQLJ API by using connection contexts, issuing SQL statements in SQLJ executable clauses, and parsing result sets with iterators.

Tasks for developing SQLJ applications

At the beginning of an SQLJ application, you need to import the Java packages that contain the JDBC and SQLJ APIs:

import sqlj.runtime.*;
import java.sql.*;

SQLJ applications use the same JDBC drivers discussed in the JDBC drivers section.

Before SQLJ source code can be compiled, it needs to be transformed into Java code so that it can be compiled by a Java compiler. The sqlj command performs this transformation and also invokes the Java compiler. If you have SQL statements in your SQLJ source file, a file with the ending _SJProfile0.ser appears in your directory alongside the class file and sqlj file.

You can run your source code once you've translated and compiled it. However, the SQL statements in your application will not be statically bound, potentially resulting in poor performance. To statically bind the SQL statements in your application, you need to perform a profile customization of your application. You can perform this customization with the db2sqljcustomize tool.

To perform a profile customization of a compiled application, execute the following command:

db2sqljcustomize -url jdbc:db2://localhost:50000/sample
                 -user uid -password pwd
                    app_SJProfile0.ser

In this command, uid represents your user ID, pwd represents your password, and app represents the name of your application.

Some sample output from a successful run of db2sqljcustomize is shown below.

db2sqljcustomize -url jdbc:db2://localhost:50000/sample
                 -user me -password mypwd DbApp_SJProfile0.ser
[ibm][db2][jcc][sqlj]
[ibm][db2][jcc][sqlj] Begin Customization
[ibm][db2][jcc][sqlj] Loading profile: DbApp_SJProfile0
[ibm][db2][jcc][sqlj] Customization complete for profile DbApp_SJProfile0.ser
[ibm][db2][jcc][sqlj] Begin Bind
[ibm][db2][jcc][sqlj] Loading profile: DbApp_SJProfile0
[ibm][db2][jcc][sqlj] Driver defaults(user may override): BLOCKING ALL VALIDATE BIND 
   STATICREADONLY YES
[ibm][db2][jcc][sqlj] Fixed driver options: DATETIME ISO DYNAMICRULES BIND
[ibm][db2][jcc][sqlj] Binding package DBAPP01 at isolation level UR
[ibm][db2][jcc][sqlj] Binding package DBAPP02 at isolation level CS
[ibm][db2][jcc][sqlj] Binding package DBAPP03 at isolation level RS
[ibm][db2][jcc][sqlj] Binding package DBAPP04 at isolation level RR
[ibm][db2][jcc][sqlj] Bind complete for DbApp_SJProfile0

Establish a connection context

In SQLJ applications, SQL operations require a connection context instead of a JDBC Connection object. Each SQL statement is issued through #sql SQLJ clauses, and is explicitly or implicitly assigned a connection context. The assignment of a connection context associates the statement with a previously opened database connection.

Before establishing a database connection, you must first generate a connection context class. The following line of code generates a connection context class named Ctx:

#sql context Ctx;

To establish a database connection, load a JDBC driver using the Class.forName() method, and then construct an instance of the context class you generated earlier. At minimum, you need to pass the context constructor the URL of the database and a boolean indicating whether or not you want autocommit turned on. The following code snippet establishes a database connection in SQLJ:

String url = "jdbc:db2:sample";
Class.forName("com.ibm.db2.jcc.DB2Driver");
Ctx connCtx = new Ctx(url,true);

In this sample connection context connCtx, the IBM DB2 Driver for JDBC and SQLJ is being used to connect to the sample database on a DB2 server, and autocommit is on.

Issue SQL statements

A significant advantage of SQLJ over many other database APIs (including JDBC) is the simplicity and power of SQLJ's syntax. The following code example demonstrates an UPDATE statement that makes use of a host variable:

String staffJob = "Sales";
#sql [connCtx] {UPDATE STAFF SET COMM=400 WHERE JOB = :staffJob};

To write SQL statements that return result sets, you'll need to use iterators. Iterators are discussed later in the Use iterators section.

Basic SQLJ application: Sample code

The following application demonstrates all the concepts discussed thus far in this section:

  • Importing the Java packages that contain the JDBC and SQLJ APIs
  • Loading the IBM DB2 Driver for JDBC and SQLJ
  • Generating a connection context
  • Executing an SQL statement with a host variable

In this application, a SELECT statement is executed, and the scalar result is passed to a host variable named stname.

//DbApp.sqlj
import sqlj.runtime.*;
import java.sql.*;

#sql context Ctx;

class DbApp
{
  public static void main(String[] argv)
  {
    String url = "jdbc:db2:sample";
    String stname;

    try
    {
      Class.forName("com.ibm.db2.jcc.DB2Driver");

      Ctx connCtx = new Ctx(url,true);

      #sql [connCtx] {SELECT NAME INTO :stname FROM STAFF WHERE ID=10};

      System.out.println("The name of employee #10 is: " + stname);
      
      connCtx.close();
    }
    catch (ClassNotFoundException drvEx)
    {
      System.err.println("Could not load JDBC driver");
      System.out.println("Exception: " + drvEx);
      drvEx.printStackTrace();
    }
    catch(SQLException sqlEx)
    {
      while(sqlEx != null) {
        System.err.println("SQLException information");
        System.err.println("Error msg: " + sqlEx.getMessage());
        System.err.println("SQLSTATE: " + sqlEx.getSQLState());
        System.err.println("Error code: " + sqlEx.getErrorCode());
        sqlEx.printStackTrace();
        sqlEx=sqlEx.getNextException();
      }
    }
  }
}

To translate and compile the above file (named DbApp.sqlj), execute the following command:

sqlj DbApp.sqlj

To perform a profile customization of this compiled application, execute the following command:

db2sqljcustomize -url jdbc:db2://localhost:50000/sample
                 -user uid -password pwd
                 DbApp_SJProfile0.ser

In this command, uid represents your user ID, and pwd represents your password.

To run the compiled application, execute the following command:

java DbApp

The output of this application should look like this:

The name of employee #10 is: Sanders

Use iterators

To parse result sets in SQLJ applications, you need to use iterators. Iterators are the SQLJ version of SQL cursors. There are two main kinds of iterators you can use in SQLJ: named and positional.

Named iterators

Named iterators are declared with the names and data types of the columns in the result set. With named iterators, the order of the columns does not matter. Before you can use a named iterator in your application, you must generate an iterator class, keeping in mind the nature of the result set you want to parse. The following code snippet generates a named iterator class called NameIter; the iterator has two columns.

#sql iterator NameIter(String Name, int Id);

When issuing an SQL query, you should pass the result set to an instance of the iterator class you generated. For example:

NameIter nIter;
#sql [connCtx] nIter = {SELECT ID, NAME FROM STAFF WHERE DEPT = 20};

To retrieve column values from a named iterator, you can use the iterator methods named after the column names in the result set. To parse the named iterator, you use a loop structure; before each loop cycle, go to the next row by running the iterator.next() method. The following code snippet parses an instance of the NameIter named iterator.

while (nIter.next()) {
  System.out.println(nIter.Name() + ", ID #" + nIter.Id());
}

Positional iterators

Positional iterators are declared only with the data types of the columns in the result set. With positional iterators, the order of the columns matters. Before you can use a positional iterator in your application, you must generate an iterator class, keeping in mind the nature of the result set you want to parse. The following code snippet generates a positional iterator class called PosIter; the iterator has two columns.

#sql iterator PosIter(String, int);

When issuing an SQL query, pass the result set to an instance of the iterator class you generated. For example:

PosIter pIter;
#sql [connCtx] pIter = {SELECT ID, NAME FROM STAFF WHERE DEPT = 20};

To retrieve column values from a positional iterator, you need to fetch those values into host variables. Use the iterator.endFetch() method to determine if there are additional rows to fetch. The following code snippet parses an instance of the PosIter positional iterator.

#sql {FETCH :pIter INTO :nameHv, :idHv };
while (!pIter.endFetch()) {
  System.out.println(nameHv + ", ID #" + idHv);
  #sql {FETCH :pIter INTO :nameHv, :idHv };
}

Named and positional iterators: Sample code

The following application demonstrates all the concepts discussed in the previous panel:

  • Generating a named iterator
  • Generating a positional iterator
  • Parsing a result set using a named iterator
  • Parsing a result set using a positional iterator

In this application, a SELECT statement is executed twice. The result set from the first execution is parsed with a named iterator called nIter. The result set from the second execution is parsed with a positional iterator called pIter.

//Iter.sqlj
import sqlj.runtime.*;
import java.sql.*;

#sql context Ctx;
#sql iterator NameIter(String Name, int Id);
#sql iterator PosIter(String, int);

class Iter
{
  public static void main(String[] argv)
  {
    String url = "jdbc:db2:sample";
    String nameHv = null;
    int idHv = 0;
    NameIter nIter;
    PosIter pIter;

    try
    {
      Class.forName("com.ibm.db2.jcc.DB2Driver");

      Ctx connCtx = new Ctx(url,true);

      System.out.println("\nResult set from named iterator:");
      #sql [connCtx] nIter = {SELECT ID, NAME FROM STAFF WHERE DEPT = 20};
      while (nIter.next()) {
        System.out.println(nIter.Name() + ", ID #" + nIter.Id());
      }
      nIter.close();

      System.out.println("\nResult set from positional iterator:");
      #sql [connCtx] pIter = {SELECT NAME, ID FROM STAFF WHERE DEPT = 20};
      #sql {FETCH :pIter INTO :nameHv, :idHv };
      while (!pIter.endFetch()) {
        System.out.println(nameHv + ", ID #" + idHv);
        #sql {FETCH :pIter INTO :nameHv, :idHv };
      }
      pIter.close();

      connCtx.close();
    }
    catch (ClassNotFoundException drvEx)
    {
      System.err.println("Could not load JDBC driver");
      System.out.println("Exception: " + drvEx);
      drvEx.printStackTrace();
    }
    catch(SQLException sqlEx)
    {
      while(sqlEx != null) {
        System.err.println("SQLException information");
        System.err.println("Error msg: " + sqlEx.getMessage());
        System.err.println("SQLSTATE: " + sqlEx.getSQLState());
        System.err.println("Error code: " + sqlEx.getErrorCode());
        sqlEx.printStackTrace();
        sqlEx=sqlEx.getNextException();
      }
    }
  }
}

To translate and compile the above file (named Iter.sqlj), execute the following command:

sqlj Iter.sqlj

To perform a profile customization of this compiled application, execute the following command:

db2sqljcustomize -url jdbc:db2://localhost:50000/sample
                 -user uid -password pwd Iter.ser

In this command, uid represents your user ID, and pwd represents your password.

To run the compiled application, execute the following command:

java Iter

The output of this application should look like this:

Result set from named iterator:
Sanders, ID #10
Pernal, ID #20
James, ID #80
Sneider, ID #190

Result set from positional iterator:
Sanders, ID #10
Pernal, ID #20
James, ID #80
Sneider, ID #190

Troubleshooting

Overview

In this section, you learn how to use the troubleshooting tools available for JDBC applications. You will find the following information:

  • Examine basic error handling and reporting practices using SQLException objects
  • Use the JDBC trace facility
  • Discuss the JDBC error log

Error handling

To follow proper error handling practice in your Java programs, you wrap the logic in your JDBC applications in try/catch blocks. When errors occur in JDBC methods, they throw SQLException objects. Therefore, for every try block that contains a JDBC operation, the corresponding catch block should contain logic to handle an SQLException object.

The following example features a SELECT statement with type-incompatible operands in the WHERE clause: the JOB column of the STAFF table is of data type VARCHAR, not INTEGER.

try {
...
rs = stmt.executeQuery("SELECT ID, NAME FROM STAFF WHERE JOB = 99");
...
}
catch(SQLException sqlEx) {
  while(sqlEx != null) {
    System.err.println("SQLException information");
    System.err.println("Error msg: " + sqlEx.getMessage());
    System.err.println("SQLSTATE: " + sqlEx.getSQLState());
    System.err.println("Error code: " + sqlEx.getErrorCode());
    sqlEx=sqlEx.getNextException();
  }
}

The catch block contains a while loop, which facilitates the handling of multiple exceptions. At the end of the loop, there is a call of the SQLException.getNextException() method, which returns an exception if there is another exception to catch, or null if there are no additional exceptions.

The SELECT statement with the incompatible operands generates the following exceptions:

SQLException information
Error msg: DB2 SQL error: SQLCODE: -401, SQLSTATE: 42818, SQLERRMC: =
SQLSTATE: 42818
Error code: -401
SQLException information
Error msg: DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-401;42818;=
SQLSTATE: 56098
Error code: -727

This output differs depending on the JDBC driver you are using. The above output was generated by an application using the IBM DB2 Driver for JDBC and SQLJ.

All the complete code samples presented in this tutorial contain logic to handle SQLException objects.

IBM DB2 Driver for JDBC and SQLJ trace facility

To debug your JDBC and SQLJ applications, you can use the IBM DB2 Driver for JDBC and SQLJ trace facility. The recommended approach for collecting trace data is to activate the trace facility outside your application by editing properties in the IBM DB2 Driver for JDBC and SQLJ configuration properties file. However, you can also activate the trace facility from within your application. You can enable tracing for the duration of the application by specifying trace options when you load the driver, or you can explicitly call trace methods to turn tracing on and off during different portions of your application.

To use the IBM DB2 Driver for JDBC and SQLJ trace facility outside your application, add the following lines to a properties file:

db2.jcc.traceDirectory=Trace
db2.jcc.traceFile=extTrace
db2.jcc.traceFileAppend=true

For the purposes of this tutorial, these lines are added to a file named tut-trace.properties, which is in the same directory as the class files to be tested. As identified by the traceDirectory property, all trace files are written to a sub-directory called trace. This directory must exist for the trace files to be written. By specifying the tut-trace.properties properties file in the java command, the trace facility gathers information about the ConnDb class.

java -Ddb2.jcc.propertiesFile=tut-trace.properties ConnDb

To enable tracing from within an application, you can append trace-specific properties to the url parameter you use to get a database connection. For example:

String url = 
     "jdbc:db2:sample:traceFile=Trace/intTrace.txt;traceLevel=" +
     (com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS |
     com.ibm.db2.jcc.DB2BaseDataSource.TRACE_CONNECTS) + ";";

In this example, trace information is written to a file called intTrace.txt in the Trace sub-directory.

To turn tracing on and off within an application using a DriverManager, you specify the traceLevel in the url parameter you use to get a database connection, and the filename and path in the DriverManager.setLogWriter method. For example:

...
String url = 
     "jdbc:db2:sample:traceLevel=" +
     (com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS |
     com.ibm.db2.jcc.DB2BaseDataSource.TRACE_CONNECTS) + ";";
...
//turn on trace
((com.ibm.db2.jcc.DB2Connection) con).setJccLogWriter(printWriter, 
      com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL);
...
//database activity
...
//turn off trace
((com.ibm.db2.jcc.DB2Connection) con).setJccLogWriter(null);

Performance monitoring

The IBM DB2 Driver for JDBC and SQLJ includes a DB2SystemMonitor interface, which enables you to collect the following information about your application:

  • Core driver time: The sum of elapsed monitored API times.
  • Network I/O time: The sum of elapsed network I/O times.
  • Server time: The sum of all reported DB2 server elapsed times.
  • Application time: The sum of the application, JDBC driver, network I/O, and DB2 server elapsed times.

All the times collected are in microseconds.

The following code demonstrates how to create a DB2SystemMonitor interface, and collect and retrieve monitor data about the application:

...
com.ibm.db2.jcc.DB2SystemMonitor systemMonitor =
     ((com.ibm.db2.jcc.DB2Connection)con).getDB2SystemMonitor();
systemMonitor.enable(true);
//reset all counters
systemMonitor.start(com.ibm.db2.jcc.DB2SystemMonitor.RESET_TIMES);
...
//database activity
...
systemMonitor.stop();
System.out.println("Server elapsed time = " + systemMonitor.getServerTimeMicros());

Conclusion

Summary

This tutorial provides a working introduction to the Java database programming concepts you will be tested on in the DB2 9 Family Application Development Certification exam (Exam 733). To reinforce the ideas presented in each of the tutorial sections, do more than simply compile and run the sample code. Make your own modifications and enhancements.

To keep an eye on this series, bookmark the series page, DB2 9 application development exam 733 prep tutorials.

Resources

Learn

Get products and technologies

  • Download a free trial version of DB2 Enterprise 9.
  • DB2 Express-C: Now you can use DB2 for free. Download this no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  • DB2 Developer Workbench: Download this product for free.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=197194
ArticleTitle=DB2 9 Application Development exam 733 prep, Part 7: Java programming
publish-date=02222007