Considering SQLJ for Your DB2 V8 Java Applications

Learn why SQLJ is the language of choice for developers interested in security, performance, and simplicity. Code samples are included.

Connie Tsui, Staff Software Analyst , IBM Toronto Laboratory

Connie Tsui is a Staff Software Analyst with the DB2 Solution Integration Team at the IBM Toronto Lab. She earned her Bachelor of Computer Science Degree from the University of Toronto. Her current focus is DB2 and WebSphere® integration.



13 February 2003

Also available in Japanese

Introduction

There are two standard ways to access relational data using JavaTM: SQLJ and JDBCTM. Why should you consider SQLJ for your IBM® DB2® Universal DatabaseTM (UDB) applications? The answer is that SQLJ is the language of choice for application programmers concerned with security, performance, and simplicity. This article provides you some background on SQLJ, discusses the advantages of SQLJ over JDBC and highlights some new and improved SQLJ features provided by DB2 UDB Version 8.1.


Background on SQLJ

In April 1997, an informal and open group of database vendors started to meet regularly to exchange ideas about how to use static SQL statements and constructs in the Java programming language. The key participants included IBM, Oracle, Compaq, Informix®, Sybase, Cloudscape and Sun Microsystems. The group named the specification they were working on JSQL. After the discovery that JSQL was a trademarked term, JSQL was renamed to SQLJ. In December 1997, Oracle provided a reference implementation of embedded SQL in Java to the other members. This reference implementation could run on any platform that supported the JDK 1.1, and it was vendor-neutral. In December 1998, the specification for embedding SQL in Java was fully developed and accepted as ANSI standard Database Language - SQL, Part 10 Object Language Bindings (SQL/OLB) ANSI x3.135.10-1998. This specification has commonly been referred to as Part 0 of the SQLJ specifications. It is now known as SQL/OLB (Object Language Bindings).

The SQLJ specification currently consists of two parts:

  • SQL/OLB: Embedded SQL in Java
    This part of the standard specifies the syntax and semantics of embedding SQL in Java methods, as well as mechanisms to ensure binary portability of resulting SQLJ applications. This is the topic described in this article.
  • SQL/JRT: SQL Routines and Types using the Java Programming Language
    This part of the standard covers the following:
    • Specification for calling Java static methods as SQL stored procedures and user-defined functions. It defines SQL extensions for installing Java classes in an SQL system, for invoking static methods of Java classes in SQL as SQL functions and stored procedures, for obtaining specified output values of parameters, and for returning SQL result sets.
    • Specification for using Java classes as SQL user-defined data types. It defines SQL extensions for using Java classes as data types in SQL.

Terminology: When we use the term SQLJ in the remainder of this article, we are referring only to SQL/OLB.


The SQLJ programming environment

The SQLJ environment consists of two phases: development and runtime. This section introduces you to the components involved for each phase and the relationship among the components.

Developing SQLJ applications

Developing applications using SQLJ involves three components: the translator, the profile customizer and the profile binder. The utilities providing the functionalities to support the three components are: sqlj, db2sqljcustomize, and db2sqljbind, respectively. Here is an overview of the process as illustrated in Figure 1:

  1. First, invoke the SQLJ translator (sqlj) to read in your SQLJ source file and check for proper SQLJ syntax in the program. The translator generates a Java source file and zero or more SQLJ profiles, and optionally compiles the generated Java source into byte codes (by default) if there are no errors. The generated Java source file replaces embedded SQL with calls to the SQLJ runtime, which executes the SQL operations.
  2. Second, call the SQLJ profile customizer (db2sqljcustomize) to create a DB2 customization for the generated serialized profile. The customizer optionally (by default) performs an online check for SQL statements that can be dynamically prepared. The online check performs syntax, semantic, and schema validation. It also optionally (by default) calls the SQLJ profile binder to bind the DB2 packages.
  3. If you choose not to perform the automatic bind during profile customization, you can invoke the SQLJ profile binder (db2sqljbind) separately to bind the previously customized SQLJ profile to the database.
  4. To view the profile contents, whether it's customized or not, you can use the SQLJ profile printer (db2sqljprint) to print out the profile contents in text format.
Figure 1. SQLJ development environment
SQLJ development environment

Executing SQLJ applications

The SQLJ runtime relies on a JDBC driver to obtain a database connection in order to access the database. Uncustomized SQLJ applications can run with any JDBC 2.0 drivers. You only want to run an uncustomized application for testing purposes during development. To run customized SQLJ applications, you can establish a database connection using the V8 CLI-based JDBC type 2 driver, the Universal JDBC drivers (type 2 or type 4). In this section, we only describe the runtime environment for customized SQLJ applications.

When you run an SQLJ application, the SQLJ runtime reads information about your SQL operations from your customized profile, and executes statements in the DB2 package that matches the package key information stored in the customization (package name, package consistency token, and collection name).

Figure 2. SQLJ runtime environment
SQLJ runtime environment

Advantages of SQLJ over JDBC

Both the SQLJ and JDBC specifications describe how to access relational database using Java. This section discusses their differences from the following aspects:

Table 1 summarizes the differences between SQLJ and JDBC that we describe in this section.

Standards and SQL specification level

SQLJ is the implementation of ISO/IEC 9075-10:2000 Information technology -- Database languages -- SQL -- Part 10: Object Language Bindings (SQL/OLB). SQLJ is not part of the J2EE platform.

JDBC is a required component of the J2SE 1.4 and J2EE 1.4 platform specifications. It has been a core part of the Java Software Development Kit (JDK) since Version 1.1. It is included in the java.sql package. A JDBC driver must support at least Entry SQL-92 statements, with some extensions defined in the specification.

Security

The security authorization model implemented in SQLJ is one primary reason for users to consider using SQLJ. With static SQL, security privileges are assigned to the package creator and stored in DB2 packages.

With customized DB2 SQLJ, the SQL is executed statically; therefore the SQL statements are executed using the package owner's privileges. Any other users who run SQLJ applications must be granted the EXECUTE privilege on its packages. That is, users who are authorized to run programs do not necessarily have the right to SELECT, UPDATE, DELETE, or INSERT against the same tables or views that the program queries or is modifying unless an appropriate privilege is explicitly granted.

SQL statements within JDBC applications are executed with the privileges of the person who connects to the database and executes the application. Therefore, users require privilege to access tables.

Performance

SQLJ allows SQL statements to be embedded in a Java program, similar to how SQL-92 allows SQL statements to be embedded in C, COBOL, FORTRAN and other programming languages. However, SQLJ applications can be run dynamically or statically depending on whether the SQLJ profiles are customized. SQLJ applications are precompiled and the path length of SQL statements is optimized when the packages are stored in the DB2 database. SQLJ applications executed statically can lead to better performance than JDBC.

If you want to take advantage of static execution, which we recommend, you must customize the profiles using the SQLJ profile customizer.

JDBC provides for the dynamic execution of SQL statements. Any exceptions due to syntax or semantic errors are raised at the time the application runs.

Static or dynamic SQL statement processing can be validated using the DB2 UDB monitors. The two ways of monitoring are snapshot monitoring and event monitoring. A snapshot monitor provides information regarding database activity at a specific point in time. An event monitor records the occurrence of specific milestones of DB2 UDB events. Listing 1 below is an excerpt of an event monitor sample output generated from a JDBC program. The "Type: Dynamic" tells you that the statement SELECT job FROM staff WHERE name = ? was dynamically executed.

Listing 1. Event monitor sample output generated from a JDBC program

10) Statement Event ... 
  Appl Handle: 23 
  Appl Id: G91AA377.G576.00F306261BF2 
  Appl Seq number: 0001 
 
  Record is the result of a flush: FALSE 
  ------------------------------------------- 
  Type     : <font color="#0000FF">Dynamic</font> 
  Operation: Prepare 
  Section  : 1 
  Creator  : NULLID 
  Package  : SYSSH200 
  Consistency Token  : SYSLVL01 
  Package Version ID  : 
  Cursor   : SQL_CURSH200C1 
  Cursor was blocking: FALSE 
  Text     : SELECT job FROM staff WHERE name = ?

Listing 2 is an excerpt of an event monitor sample output generated from a SQLJ program. The "Type: Static" and "Package: SRQT402" in the output tells you that the statement was executed statically against the package SRQT402.

Listing 2. Event monitor sample output generated from a SQLJ program

10) Statement Event ... 
  Appl Handle: 12 
  Appl Id: G91ABD18.G47D.00F306C01D63 
  Appl Seq number: 0001 
 
  Record is the result of a flush: FALSE 
  ------------------------------------------- 
  Type     : <font color="#0000FF">Static</font> 
  Operation: Execute 
  Section  : 1 
  Creator  : NULLID 
  Package  : <font color="#0000FF">SRQT402</font> 
  Consistency Token  : SARoQCAp 
  Package Version ID  : 
  Cursor   : 
  Cursor was blocking: FALSE

Note: Some statements will execute correctly for customized SQLJ programs that will not execute correctly for uncustomized SQLJ programs. An example is an UPDATE/DELETE WHERE CURRENT OF on a scrollable cursor. In general, if there is a functionality not supported by the underlying JDBC driver, then it is not supported in an uncustomized SQLJ program.

Performance tip:
For singleton select query, you can reduce network activity by using the SELECT INTO syntax provided by SQLJ, compared to manipulating arbitrarily large JDBC ResultSets.

Figure 3 compares the SQLJ and JDBC syntax for a singleton select query.

Figure 3. Retrieve a single row with SQLJ vs. JDBC

SQLJ syntax:

#sql [conCtx] { SELECT job INTO :job FROM staff WHERE name = :name };

JDBC syntax:
 
PreparedStatement pstmt = con.prepareStatement( 
                  "SELECT job FROM staff WHERE name = ? FETCH FIRST 1 ROW ONLY" ); 
             ResultSet rs = pstmt.executeQuery(); 
             if ( rs.next() ) 
                job = rs.getString(1); 
             else 
                job = null; 
             pstmt.close();

Syntax

Figure 3 hints at the simplicity of the SQLJ syntax over that of JDBC. The simplicity of SQLJ is appealing for many Java developers. SQLJ modules are typically more concise and easier to write than JDBC modules. This implies a shorter development cycle and lower development and maintenance costs. Figure 4 shows you how simple SQLJ can be to insert a single row of data to the database. If you already have existing embedded SQL applications written in other languages (such as. C or COBOL), you can easily migrate your applications to Java using SQLJ.

Figure 4. Insert a single row with SQLJ vs. JDBC

SQLJ syntax:

sql [conCtx] { INSERT INTO sales VALUES(:date, :salesperson, :region, :sales) };

JDBC syntax:

PreparedStatement pstmt = con.prepareStatement
( "INSERT INTO sales VALUES (?, ?, ?, ?)" ); 
 
// set input parameter 
pstmt.setObject(1, date); 
pstmt.setString(2, salesperson); 
pstmt.setString(3, region); 
pstmt.setInteger(4, sales); 
pstmt.executeUpdate(); 
pstmt.close();

SQLJ and JDBC interoperability

The SQLJ language lets you have JDBC statements in your SQLJ applications. To facilitate the interaction between JDBC and SQLJ, SQLJ provides a way to share the SQLJ connections and JDBC connections within the same application, and to obtain a JDBC result set from an SQLJ iterator, or vice versa.

When do you need to use JDBC within your SQLJ application?
You need to use JDBC for dynamic operations; that is, when the SQL operations are not known at the time the program is written. Listing 3 illustrates the use of JDBC to execute a dynamic query (the name in the WHERE clause is not known at development time) within a SQLJ program and how to turn a JDBC result set into an SQLJ iterator.

Unlike SQLJ, JDBC does not recognize SQLJ syntax, and SQL statements cannot be embedded into JDBC applications.

Listing 3. Turn a JDBC result set into an SQLJ iterator

Public class ResultSetInterop 
{ 
   #sql public static iterator Employees (String name, double salary); 
   public static void main(String[] argv) throws SQLException 
   { 
      // the code for creating the SQLJ connection context (conCtx) and  
      // the Connection object (con) is omitted 
      // create a JDBC statement object to execute a dynamic query 
      Statement stmt = con.createStatement();  
      String query = "SELECT name, salary FROM staff WHERE "; 
      query += argv[0]; 
      ResultSet rs = stmt.executeQuery(query); 
      Employees SalReport; 
      // turn a JDBC result set to an SQLJ interator using the CAST statement 
      #sql [conCtx] SalReport = { CAST :rs }; 
 
      while (SalReport.next()) { 
         System.out.println( SalReport.name() + " earns " + SalReport.salary() ); 
      } 
      SalReport.close(); 
      stmt.close();   
   } 
}

Type and schema checking

SQLJ, like Java, is strongly typed. SQLJ syntax is checked by the SQLJ translator when the SQLJ source file is converted to a Java source file. This is similar to other DB2 precompilers. Also, iterator data type conversion is done during Java compilation at the translation phase. For instance, the Java compiler prevents an interator column of type double (such as employee salary) from being used where a double is not allowed. Therefore, the assignment String hv = employees.salary(); generates an error during compile time. In addition, online checking is done during profile customization so that programming errors can be caught earlier.

JDBC does not have the capability of doing syntax or semantic checking before run time. Any exceptions due to syntax or semantic errors are raised at the time the application runs.

Notes:

  • In Version 8.1, the online checking is performed during profile customization as opposed to the translation phase in past releases.
  • There are some SQLJ errors that won't be caught until run time. In addition, statements that cannot be dynamically prepared are not online checked.

Summary of differences

Table 1 summarizes the differences between SQLJ and JDBC.

Table 1. Comparing SQLJ and JDBC

SQLJJDBC
StandardsISO/ANSI (not part of J2EE)Sun (part of J2EE)
SQL specification levelSQL-1999N/A (must support at least Entry Level SQL-92 )
SecurityStrongAverage
PerformanceFaster (static access plan created during development)Slower (dynamic access plan created during applications program execution)
SyntaxHigh (compact)Low (cumbersome)
SQLJ and JDBC interoperabilityYesN/A
Type and schema checkingStrong (Performed during development)Weak (Performed during runtime)

What's new in Version 8.1

DB2 UDB Version 8.1 delivers a newly designed SQLJ driver, which has new features. The new SQLJ driver is based on an open distributed protocol, known as Distributed Relational Database ArchitectureTM (DRDA®). It is supported by the CLI-based JDBC drivers (type 2 and type 3) and the new Universal JDBC drivers (type 2 and type 4) introduced in V8.1.

Major enhancements of SQLJ can be summarized as follows:

New SQLJ utilities and runtime

SQLJ in DB2 UDB Version 8.1 implements pure Java SQLJ utilities and runtime, with new options and option formats. The new runtime leads to a much better performance than Version 7.

The SQLJ translator, sqlj, in Version 8.1 always compiles the generated Java source by default. In Version 7, the compile option did not work with some JDKs and you had to compile the Java file manually.

The new profile printer in Version 8.1, db2sqljprint, no longer requires you to provide a URL, and it provides detailed information about the SQL statement to be executed, such as DB2 statement type, section number, and DB2 result set metadata information.

Elimination of platform-specific files

The SQLJ profile customizer in Version 8.1 has new serialized profile format, which eliminates the use of DBRM files and bind files (.bnd files). The new format is fully portable to all platforms. It contains all the information needed for all BIND operations, and users can deploy on any server platform without re-customizing the serialized profiles on the target system (UNIX®, Windows®, OS/390® and z/OSTM).

New features in Version 8

Major features added in DB2 UDB V8.1 include the following:

Create SQLJ connection contexts using DataSources

With the Version 8.1 SQLJ, you can use the JDBC DataSource interface for creating a SQLJ connection. Also, the implementation for default connection contexts has changed. To obtain the default connection context, the SQLJ runtime does a JNDI lookup for jdbc/defaultDataSource. If nothing is registered, a null context exception will be thrown when the driver attempts to access the context. Therefore, you have to either register a jdbc/defaultDataSource with JNDI, or set the default context by calling DefaultContext.setDefaultContext(userctxt).

Recommendation: Use an explicit connection context on the SQLJ clause.

Listing 4. Create SQLJ connection contexts using datasources

// Create connection context class Ctx with the new dataSource keyword 
#sql public static context Ctx with (dataSource="jdbc/sampledb"); 
 
String userid, password; 
String empname; 
... 
// Create connection context object conCtx for the connection to jdbc/sampledb 
Ctx conCtx = new Ctx(userid, password); 
#sql [conCtx] { SELECT lastname INTO :empname FROM emp WHERE empno = '000010' }; 
... 
conCtx.close();

Scrollable iterators

A scrollable iterator lets you to move forward, backward, or to a specific row. Like a scrollable cursor in JDBC, a scrollable iterator can be insensitive or sensitive.

  • An insensitive iterator means that changes to the underlying table after the iterator is opened are not visible to the iterator. Insenstive iterators are read-only.
  • A sensitive iterator means that changes that the iterator or other processes make to the underlying table are visible to the iterator. For example, the code in Listing 5 demonstrates how to use a named iterator to retrieve the employee number and last name from all rows from the employee table in reverse order.

Listing 5. Using scrollable iterators

//  Declare a scrollable iterator. 
#sql  iterator ScrollIter implements sqlj.runtime.Scrollable with 
(sensitivity = SENSITIVE) 
  (String EmpNo, String LastName); 
{ 
  ScrollIter scrlIter;                                               
  #sql [conCtx] scrlIter={ SELECT empno, lastname FROM emp };                 
 scrlIter.afterLast();                                               
  while (scrlIter.previous())                                      
  { 
    System.out.println(scrlIter.EmpNo() + " " + scrlIter.LastName());    
  } 
  scrlIter.close();                                                  
}

Batch updates

Batch updates allow statements to be grouped together and then sent as a batch to the database for execution using a single round trip. You can include the following types of statements in a batch update:

  • Searched INSERT, UPDATE, or DELETE statements
  • CREATE, ALTER, DROP, GRANT, or REVOKE statements
  • CALL statements with input parameters only

Unlike JDBC, SQLJ allows heterogeneous batches that contain statements with input parameters or host expressions. You can therefore combine instances of the same statement, different statements, statements with input parameters or host expressions, and statements with no input parameters or host expressions in the same SQLJ statement batch.

Recommendation: Call executeBatch() explicitly before turning off batching or ending the use of an ExecutionContext that has bad batch mode turned on. This will ensure all statements that have been batched will get executed.

The code fragment in Listing 6 shows you how to give raises to all managers by performing updates in a batch.

Listing 6. Performing a batch update

#sql iterator getMgr(String);      
{ 
  getMgr deptIter;            
  String mgrnum = null;         
  int raise = 400;               
   int currentSalary; 
      String url = null, username = null, password = null; 
 
  testContext conCtx = new testContext (url, username, password, false); 
  // Acquire execution context.   
  // All statements that execute in a batch must use this execution context. 
  ExecutionContext exeCtx = new ExecutionContext();      
  // Invoke ExecutionContext.setBatching (true) to create a batch.                
  exeCtx.setBatching(true);                                               
 
  #sql [conCtx] deptIter =  { SELECT mgrno FROM dept }; 
  #sql {FETCH :deptIter INTO :mgrnum}; 
  while (!deptIter.endFetch())  
 {     
    #sql [conCtx] {  
      SELECT SALARY INTO :currentSalary FROM emp WHERE empno = :mgrnum}; 
    #sql [conCtx, exeCtx]                                            
      { UPDATE emp SET SALARY = :(currentSalary+raise) WHERE empno =:mgrnum }; 
    #sql { FETCH :deptIter INTO :mgrnum }; 
  } 
  exeCtx.executeBatch();   
  exeCtx.setBatching(false);                                              
                                                
  #sql [conCtx] {COMMIT};                                                
  deptIter.close();       
  exeCtx.close(); 
  conCtx.close();    
 
}

Summary

The DB2 implementation of SQLJ offers distinct advantages over JDBC. Simpler syntax and type and schema checking at precompile time greatly reduce development cost. SQLJ also provides the flexibility to embed JDBC statements in SQLJ applications. This means a single application can take advantage of the benefits of both SQLJ and JDBC. When security and performance are critical to your Java applications, SQLJ is the right choice.

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, WebSphere
ArticleID=14388
ArticleTitle=Considering SQLJ for Your DB2 V8 Java Applications
publish-date=02132003