SQLj was born when IBM, Oracle, Compaq, Sybase, Informix, Sun Microsystems and others came together to develop a specification for embedding SQL directly in Java programs. What these companies created was a very simple model for Java code containing static SQL statements that make development more concise and legible. SQLj was also designed to allow vendors’ implementations of SQLj to be customized without rewriting the SQLj application. Since an SQLj program is a Java program, it is portable. So it can run everywhere as well as take advantage of the unique strengths of other database platforms through customization. Sounds great? I think so!

The SQLj specification is defined in three parts: Part 0—Embedded SQL is the SQLj language specification describing standard language syntax and semantics for embedding static SQL in Java. In 1998, this part of the SQLj specification became American National Standards Institute (ANSI) standard X3.135.10-1998. Part 1—SQL Routines describes standards for implementing database stored procedures and functions. Part 2—SQL Types describes standards on how to store Java datatypes and classes as objects in a database. Part 1 and Part 2 of the SQLj specification are currently going through the ANSI standards process.

It is important to note that SQLj and Java Database Connectivity (JDBC) complement each other. SQLj was designed to allow a static SQL interface into the database while JDBC provides a dynamic SQL interface. However, SQLj’s static nature provides benefits over JDBC such as improved productivity and robustness, which I will describe later on.

SQLj at Work

SQLj code is simply Java source code that encompasses a set of SQLj clauses containing SQL statements. These SQLj clauses begin with #sql and end with a semicolon(;). The SQLj clauses are not recognized by Java. That is why SQLj code must be translated.

The SQLj translator creates a standard Java source file from the SQLj source as well as at least one SQLj profile. The standard Java source file is a copy of the SQLj code but replaces the SQLj clauses with Java statements that are part of the SQLj runtime. The Java

source file produced by the SQLj translator can then be compiled by a Java compiler. The SQLj profile contains the SQL information contained in the SQLj clauses. At runtime, the SQL information in the SQLj profile is read and executed.

In order to develop and run an SQLj application on any platform, the following must be available.

• Java Virtual Machine (JVM) and Java Developer Kit

• JDBC driver

• SQLj Reference Implementation (contains SQLj translator and runtime)

SQLj uses JDBC for all database access whether it be during translation or runtime.

Benefits of SQLj

The design of SQLj is comprised of many benefits that application developers will love: productiveness, robustness, portability, and the ability to be customized.

Productivity

SQLj is compact in size. This alone allows for code to be much easier to write and maintain than JDBC. The following example demonstrates the clarity of SQLj over JDBC:

// SQLj
#sql { INSERT INTO

SANTAS_WISH_LIST :name, :address, :toy
};

// JDBC:
PreparedStatement stmt =

con.prepareStatement("INSERT INTO " +

"SANTAS_WISH_LIST ?, ?, ?");
stmt.setString(1, name);
stmt.setString(2, address);
stmt.setString(3, toy);

Porting existing embedded SQL applications to SQLj is easy as well, since most embedded SQL applications are very similar. Doing so allows you to take advantage of the strengths of Java (i.e., object-oriented programming, portability, automatic memory management, and threads).

Robustness

The SQLj code is rigorously checked by the SQLj translator. The SQLj translator has two phases of translation.

In the first phase, the SQLj translator invokes a SQLj parser to check the syntax of the SQLj clauses. Also, a Java parser is invoked to check the syntax of Java statements and any Java host variables or expressions within the SQLj clauses.

In the second phase, the semantics of the SQLj code are checked. The validity of Java types used in the SQL operations, such as result expressions, is checked. In this phase, there is the option to do online checking.

Online checking will connect to a database and check such things as the following:

• Compatibility between Java types and SQL types

• Existence of schema objects in SQLj statements

• Syntax of SQL Data Manipulation Language (DML) operations

These checks are done at development time, which can be a timesaver when it comes to testing your application.

Portability

A compiled SQLj application is a standard Java application and, thereby, follows the philosophy of Write Once, Run Anywhere. The application can run anywhere a database, Java Virtual Machine, JDBC driver, and a SQLj reference implementation are available.

In addition, SQLj allows the syntax and semantics of the embedded SQL statements to be location independent. There are no dependencies on the configuration under which SQLj is running. This lack of dependency makes it possible to implement SQLj programs that run on the client side, the database side, or in a middle tier.

Customizable

Part of the design of SQLj is to allow for customizations and optimizations from vendors’ database management systems. This ability is permitted by modifying the SQLj profiles produced during translation of the SQLj code through interfaces provided by the SQLj reference implementation. Since only the SQLj profile is modified, that means that the SQLj code itself is not changed. One way a SQLj profile may be customized is to transform the SQL text into a format that allows more efficient execution.

SQLj on the AS/400

SQLj support is provided for DB2 UDB for AS/400 as part of the AS/400 Developer Kit for Java. Support for embedded SQL was initially provided in OS/400 V4R4. In V4R5, the support for embedded SQL was enhanced by providing a set of SQLj development tools commonly used throughout other platforms that support DB2 UDB. Also, the performance of SQLj programs was significantly improved compared to V4R4.

SQLj Development Tools

OS/400’s SQLj environments provide five development tools: the SQLj translator, the DB2 SQLj profile customizer, the DB2 SQLj profile printer, the SQLj profile auditor installer, and the SQLj profile conversion tool. The SQLj translator, sqlj, replaces embedded SQL statements in the SQLj program with Java source statements and generates a serialized profile that contains information about the SQLj operations that are found in the SQLj program. The DB2 SQLj profile customizer, db2profc, precompiles the SQL statements stored in the generated profile and generates a package in the DB2 database. The DB2 SQLj profile printer, db2profp, prints the contents of a DB2 customized profile in plain text. The SQLj profile auditor installer, profdb, installs and uninstalls debugging class auditors into an existing set of binary profiles. The SQLj profile conversion tool, profconv, converts a serialized profile instance to Java class format.

Performance Improvement

The DB2 SQLj profile customizer can be used to enhance performance of your SQLj program. The customizer actually prepares the SQL statements contained in your SQLj program into a SQL package. The DB2 SQLj profile customizer will then replace the SQL statements in the SQLj profile with references to the associated statement in the SQL package. At runtime, the performance is improved since the SQL statements will not be syntax-hecked and compiled. Figure 1 (page 90) illustrates how the DB2 SQLj profile customizer fits into the SQLj-to-Java conversion process.

There are some restrictions to customization as well. You can only customize SQLj code that uses the native JDBC driver (com.ibm.db2. jdbc.app.DB2Driver) and connects to

the local database on the IBM iSeries 400. Note that the Java code has to run on the IBM iSeries 400 to use the customization benefits.

Also in V4R5, support for Java stored procedures was added. Now you can write your Java stored procedures in SQLj! Note, however, there is no JAR file support as described in SQL Routines specification, so your class files cannot be deployed with a JAR file.

An Example SQLj App

To illustrate the iSeries 400-based development of an SQLj application, I’m going to step through a simple SQLj application (see Figure 2, page 91).

The example attaches locally using OS/400’s native JDBC driver. Also, note that because this example used a stored procedure, V4R5 is required. The DisplayEmployeeData application performs four SQL processes:

1. Creates a table

2. Creates a Java stored procedure

3. Inputs data into the table by calling the Java stored procedure

4. Queries the table and displays the output

Note how the DefaultContext is created in the setup() method. As noted earlier, SQLj uses a JDBC driver for connecting to the database, which is evident by the JDBC connection being passed into the constructor of Default Context. The SQLj clauses implicitly use the DefaultContext. This example creates a Java stored procedure also written in SQLj, as shown in Figure 3.

There are two types of parameter styles for Java stored procedures: Java or DB2 General. There are specific conventions when coding either type. This example uses the Java parameter style, which has the following conventions:

• The Java method must be a public void static (non-instance) method.

• The parameters of the Java method must be SQL compatible types.

• A Java method may test for a SQL NULL value when the parameter is a nullable type (like String).

• Output parameters are returned by using single element arrays.

• The Java method may access the current database using the getConnection() method.

• The compiled class file must reside in the /QIBM/UserData/OS400/SQLLib/Function directory.

The Java stored procedure was written in SQLj, which also requires that the SQLj profile reside in /QIBM/UserData/OS400/SQLLib/ Function directory. More information on Java stored procedures can be found in the AS/400 DB2 UDB for AS/400 SQL Programming Concepts book.

The translation, compilation, and customization of SQLj source code on the iSeries 400 is literally as easy as 1-2-3:

1. Add several JAR files to your classpath (Figure 4).

2. Translate the SQLj source code to Java and compile, under the QShell environment, with the following:

sqlj

3. Customize the SQLj profile with the DB2 profile customizer:

db2profc

Note that more information on SQLj development can be found in the AS/400 Developer Kit for Java.

Choosing a Driver

Which iSeries 400 JDBC driver should you use in your SQLj application? SQLj requires a JDBC driver for database access. There are two JDBC drivers available on the IBM iSeries 400: the native JDBC driver and the AS/400 toolbox for Java’s JDBC driver.

The native JDBC driver is shipped as part of the AS/400 Developer Kit for Java. The driver is implemented by making native method calls to the SQL Call Level Interface (CLI). Consequently, the JDBC driver only runs on the iSeries 400 JVM. The class name to register is com.ibm.db2.jdbc.app.DB2Driver. The URL subprotocol is db2.

The toolbox JDBC driver is shipped as part of the AS/400 Toolbox for Java. It is implemented by making direct socket connections to the database host server. The toolbox has been certified 100% Pure Java and runs on any JVM. The class name to register is com.ibm.as400.access.AS400JDBCDriver. The URL subprotocol is as400.

SQLj will run using either JDBC driver. So, which one do you choose? The current advice to customers is this: When you are running on the IBM iSeries 400 directly, use the native JDBC driver. When your program runs from another JVM and will access the iSeries 400, use the AS/400 Toolbox JDBC driver. The best advice is to avoid tying (hard coding) your program to use a specific JDBC driver. Instead, make the JDBC driver configurable at runtime. Users can then specify whichever JDBC driver makes sense in their environment.

SQLj.close()

The support industrywide for SQLj is continually growing. The ANSI standards process for SQLj is continuing as well. Development tools are currently being created and enhanced. Together these items provide a great future for SQLj and for the people who use it. Application developers can run their applications on multiple platforms, which increases their profit potential. Users of these applications benefit from no longer needing to learn new applications that may be required as the database or machine they use to run their businesses changes.

REFERENCES AND RELATED MATERIALS

• AS/400 Developer Kit for Java JDBC Web page: www.as400.ibm.com/developer/jdbc

• DB2 UDB for AS/400 SQL Programming Concepts: http://as400bks.rochester.ibm.com

• IBM DB2 Java Enablement home page: www-4.ibm.com/software/data/db2/java/

• IBM Online Publications Web site: http://as400bks.rochester.ibm.com (Contains a link to AS/400/iSeries 400 Information Center, which contains links to the AS/400 Developer Kit for Java and the AS/400 Toolbox for Java)

• SQLj.org home page: www.sqlj.org

BINARY

CLASS

FILE

CUSTOMIZED

SQLj

PROFILE

SQL

PACKAGE

Figure 1: Your Java code that contains SQLj statements has the SQLj and Java code split into separate sources.

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

// Define an SQLJ iterator using name binding to columns
#sql iterator EmployeeDataIterator (String name, int number);

public class DisplayEmployeeData
{

// This method will create the:

// SQLJ default context

// Employee data table

// Java Stored Procedure

public static void setup() throws Exception

{

DefaultContext context = null;

Connection connection = null;

// Register the Native JDBC driver

Driver d = (Driver)(Class.forName(

“com.ibm.db2.jdbc.app.DB2Driver” ).newInstance());

DriverManager.registerDriver(d);

// Make a connection to the local database using the Natvie JDBC

connection = DriverManager.getConnection(“jdbc:db2:My400”);

// Set up the SQLJ default context.

// This will be used implicitly by the SQLJ clauses.

context = new DefaultContext(connection);

DefaultContext.setDefaultContext(context);

// Drop the employee table.

try

{

#sql { DROP TABLE EMPLOYEEDATA };

}

catch (Exception e)

{

}

// Create the employee data table

JAVA

COMPILER

DB2 SQLj

PROFILE

CUSTOMIZER

SQLj

TRANSLATOR JAVA

SOURCE

CODE

STANDARD

SQLj

PROFILE

SQLj

SOURCE

CODE

#sql { CREATE TABLE EMPLOYEEDATA (NAME VARCHAR(10), NUMBER INTEGER) };

// Drop the store procedure

try

{

#sql { DROP PROCEDURE INSERT_EMPLOYEEDATA };

}

catch (Exception e)

{

}

// Create the Java Stored Procedure

#sql { CREATE PROCEDURE INSERT_EMPLOYEEDATA(IN NAME VARCHAR(10),

IN NUMBER INTEGER)

LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME

‘EmployeeData!Insert’ };

}

// This method will call the Java Stored Procedure to insert data into the

// employee data table.

// A query will be run on the employee data and a SQLJ iterator will be used to

// iterate through the results.
public static void main (String args[]) throws Exception
{

try

{

// Perform set up

setup();

// Insert into employee data table by calling the stored procedure

String name = new String(“Scott”);

int number = 1234;

#sql { CALL INSERT_EMPLOYEEDATA(:name, :number) };

// Query the employee data and assign the SQLJ iterator

EmployeeDataIterator iter = null;

#sql iter = { SELECT NAME, NUMBER FROM EMPLOYEEDATA };

// Iterate throught the employee data and display the information

System.out.println(“NAME NUMBER”);

while (iter.next())

{

System.out.println(iter.name() + “ ” + iter.number());

}

}

catch (Exception e)

{

System.out.println(“Exception: “ + e);

}

}

}

Figure 2: SQL syntax is placed between the curly braces of a #sql clause.

import sqlj.runtime.*;
import sqlj.runtime.ref.*;

public class EmployeeData
{

// This method is invoked by the Java Store Procedure INSERT_EMPLOYEEDATA

// that was create in the setup method in DisplayEmployeeData.java

public static void Insert(String name, int number) throws Exception

{

#sql { INSERT INTO EMPLOYEEDATA VALUES(:name, :number) };

}

}

Figure 3: A Java stored procedure is wrappered with a simple Java class.

/QIBM/ProdData/Java400/ext/SQLj_classes.jar
/QIBM/ProdData/Java400/ext/translator.zip
/QIBM/ProdData/Java400/ext/runtime.zip
/IFS/path/To/Your/SQLj/source

Figure 4: Before you can use SQLj, you will have to add several JAR files to your classpath.

Also Read

TechTip: Read from and Write to an IFS File Using SQL

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL