Application Development Guide

SQLJ Programming

DB2 SQLJ support is based on the SQLJ ANSI standard. Refer to the DB2 Java Web site for a pointer to the ANSI Web site and other SQLJ resources. This chapter contains an overview of SQLJ programming and information that is specific to DB2 SQLJ support.

The following kinds of SQL constructs may appear in SQLJ programs:

• Queries; for example, SELECT statements and expressions.
• SQL Data Change Statements (DML); for example, INSERT, UPDATE, DELETE.
• Data Statements; for example, FETCH, SELECT..INTO.
• Transaction Control; for example, COMMIT, ROLLBACK, etc.
• Data Definition Language (DDL, also known as Schema Manipulation Language); for example, CREATE, DROP, ALTER.
• Calls to stored procedures; for example, CALL MYPROC(:x, :y, :z)
• Invocations of functions; for example, VALUES( MYFUN(:x) )

DB2 SQLJ Support

DB2 SQLJ support is provided by the DB2 Application Development Client. Along with the JDBC support provided by the DB2 client, DB2 SQLJ support allows you to create, build, and run embedded SQL for Java applications, applets, stored procedures and user-defined functions (UDFs). These contain static SQL and use embedded SQL statements that are bound to a DB2 database.

The SQLJ support provided by the DB2 Application Development Client includes:

• The SQLJ translator, SQLJ, which replaces embedded SQL statements in the SQLJ program with Java source statements and generates a serialized profile containing information about the SQL operations found in the SQLJ program. The SQLJ translator uses the sqllib/java/sqlj.zip file.
• The SQLJ run-time classes, available in sqllib/java/runtime.zip.
• The DB2 SQLJ profile customizer, db2profc, which precompiles the SQL statements stored in the generated profile and generates a package in the DB2 database.
• The DB2 SQLJ profile printer, db2profp, which prints the contents of a DB2 customized profile in plain text.
• The SQLJ profile auditor installer, profdb, which installs (or uninstalls) debugging class-auditors into an existing set of binary profiles. Once installed, all RTStatement and RTResultSet calls made during application run time are logged to a file (or standard output), which can then be inspected to verify expected behavior and trace errors. Note that only those calls made to the underlying RTStatement and RTResultSet call interface at run time are audited.
• The SQLJ profile conversion tool, profconv, which converts a serialized profile instance to class bytecode format. Some browsers do not yet have support for loading a serialized object from a resource file associated with the applet. As a work-around, you need to run this utility to perform the conversion.

For more information on the db2profc and db2profp commands, refer to the Command Reference. For more information on the SQLJ run-time classes, refer to the DB2 Java Web site .

DB2 SQLJ Restrictions

When you create DB2 applications with SQLJ, you should be aware of the following restrictions:

• DB2 SQLJ support adheres to standard DB2 Universal Database restrictions on issuing SQL statements.
• A positioned UPDATE and DELETE statement is not a valid sub-statement in a Compound SQL statement.
• The precompile option "DATETIME" is not supported. Only the date and time formats of the International Standards Organization are supported.
• The precompile option "PACKAGE USING package-name" specifies the name of the package that is to be generated by the translator. If a name is not entered, the name of the profile (minus extension and folded to uppercase) is used. Maximum length is 8 characters. Since the SQLJ profile name has the suffix _SJProfileN, where N is the profile key number, the profile name will always be longer than 8 characters. The default package name will be constructed by concatenating the first (8 - pfKeyNumLen) characters of the profile number and the profile key number, where pfKeyNumLen is the length of the profile key number in the profile name. If the length of the profile key number is longer than 7, the last 7 digits will be used without any warnings. For example:

      profile name                           default package name
      ---------------------                  --------------------
      App_SJProfile1                         App_SJP1
      App_SJProfile123                       App_S123
      App_SJProfile1234567                   A1234567
      App_SJProfile12345678                  A2345678
  

• When a java.math.BigDecimal host variable is used, the precision and scale of the host variable is not available during the translation of the application. If the precision and scale of the decimal host variable is not obvious from the context of the statement in which it is used, the precision and scale can be specified using a CAST.
• A Java variable with type java.math.BigInteger cannot be used as a host variable in an SQL statement.

Some browsers do not yet have support for loading a serialized object from a resource file associated with the applet. You will get the following error message when trying to load the applet Applt in those browsers:

   java.lang.ClassNotFoundException: Applt_SJProfile0

As a work-around, there is a utility which converts a serialized profile into a profile stored in Java class format. The utility is a Java class called sqlj.runtime.profile.util.SerProfileToClass. It takes a serialized profile resource file as input and produces a Java class containing the profile as output. Your profile can be converted using the following command:

   profconv Applt_SJProfile0.ser
 
     or
 
   java sqlj.runtime.profile.util.SerProfileToClass Applt_SJProfile0.ser

The class Applt_SJProfile0.class is created as a result. Replace all profiles in .ser format used by the applet with profiles in .class format.

For an SQLJ applet, you need both db2java.zip and runtime.zip files. If you choose not to package all your applet classes, classes in db2java.zip and runtime.zip into a single Jar file, put both db2java.zip and runtime.zip (separated by a comma) into the archive parameter in the "applet" tag. For those browsers that do not support multiple zip files in the archive tag, specify db2java.zip in the archive tag, and unzip runtime.zip with your applet classes in a working directory that is accessible to your web browser.