SQLJ Overview ------------- The function contained in this APAR is new to DB2 for OS/390 Version 5. The new function is delivered through an APAR to speed the availability of this key Java technology, providing it to customers faster than the normal DB2 for OS/390 product releases. This APAR contains the DB2 for OS/390 Version 5 implementation of the SQLJ: Embedded SQL for Java, Part0: Translator specifications that have been submitted to ANSI for standardization. This function includes an SQLJ Translator, a profile Customizer, the DB2 for OS/390 Version 5 SQLJ Runtime support and various utilities. The user externals are available in the DB2 for OS/390 Version 5 Application Programming Guide and Reference for Java, which may be downloaded in PDF format from http://www.software.ibm.com/data/db2/os390. That document describes the SQLJ grammar for constructing SQLJ clauses and the externals users need to translate, customize, and execute a Java program containing SQLJ clauses. Contents of the DB2 for OS/390 SQLJ Support ------------------------------------------- The DB2 for OS/390 support includes the following: o - Two Java jar files that are shipped with the DB2 for OS/390 SQLJ support. The jar files are installed into the /usr/lpp/db2/db2510/classes directory. The db2sqljclasses.zip jar file includes all of the DB2 for OS/390 Java class files that are needed to translate, customize, and execute a DB2 for OS/390 SQLJ application. In contrast, the db2sqljruntime.zip jar file contains ONLY the DB2 for OS/390 class files that are needed for execution of an SQLJ application. You should add the appropriate jar file to your CLASSPATH environment variable. o - Several dynamic load library (DLL) files that are shipped as part of the DB2 for OS/390 SQLJ support. These files are installed into the /usr/lpp/db2/db2510/lib directory. This directory should be added to both your LIBPATH and LD_LIBRARY_PATH environment variables. o - A sample SQLJ application is shipped in the /usr/lpp/db2/db2501/samples/sample02 directory. The sample consists of the source file, sample02.sqlj, and a readme file, sample02.readme. See the sample02.readme file for instructions about how to translate, customize, and execute the sample02.sqlj application. Porting Java applications from non-OS/390 platforms -------------------------------------------------- If you are porting existing SQLJ applications from a non-OS/390 platform, for example DB2 UDB 5.2 applications on NT, then you may need to modify your source code to load the DB2 for OS/390 Version 5 JDBC Driver for SQLJ Runtime support. Unless you are using the default ConnectionContext, you must also modify the URL values specified on either the JDBC Connection constructor or the SQLJ ConnectionContext constructor. Current SQLJ Restrictions ------------------------- This APAR is delivered in APARFIX mode and will not be closed in the near future. The SQLJ support contained in this APAR has several functional restrictions, all of which will be corrected when that the APAR is closed. The SQLJ support through this APAR includes these restrictions: o - JDBC 1.0 Support: While an SQLJ Part 0-compliant implementation includes JDBC 1.0, this is currently not supported. This means that a Java program cannot intermix dynamic SQL and static SQL. If you require dynamic SQL for an application, use the JDBC 1.0 support available for DB2 UDB OS/390. Applications that require static embedded SQL should use SQLJ. o - Character Set Support: Character data other than Latin-1, graphic, and mixed data are not supported. Java applications cannot insert or retrieve character data if the code page (CCSID) is other than 0500. Similarly, the application cannot insert or retrieve graphic or mixed data. o - Multiple Contexts/Concurrent Connections: Multiple contexts are not currently supported. Therefore, the number of concurrent connections supported from a single Java thread is one. To ensure that only one connection is active for a Java thread, SQLJ applications should explicitly close connections through the java.sql.Connection.close() method rather than relying on the Java garbage collection for closing connections. Any attempts to create a multiple concurrent Connections (ConnectionContexts) from a single Java thread can result in unpredictable failures. o - Connection Sharing: Connections and ConnectionContexts can only be used by a single Java thread. Connections and objects derived from Connections, cannot be shared between Java threads. A Java thread cannot use a connection that it did not create. o - AutoCommit: The ConnectionContext constructors do not support the autoCommit parameter. To create a ConnectionContext, which is disabled for autoCommit, first create the underlying JDBC Connection instance (autoCommit is ON), invoke the setAutoCommit method specifying a value of false, then pass the JDBC Connection to the ConnectionContext constructor. o - SQL token case sensitivity: In the current implementation, all SQL tokens must be written in uppercase. o - Multiple result sets returned from a Stored Procedure: Applications calling stored procedures that return multiple result set instances are able to access only the first result set. o - Iterator with clause: The WITH clause that can be specified on an iterator declaration is not supported. In particular, this means that Java applications cannot create iterators that have the 'holdability' attribute. Other SQLJ Notes ---------------- o - Any application that includes SQLJ clauses containing DB2 for OS/390 Version 5 specific SQL statements which specify output host variables, for example SET :hv = , must provide the output host variable modality explicitly in the SQLJ clause, for example, OUT :hv. o - Any iterator to be used for positional UPDATE/DELETE processing, that is, implements the sqlj.runtime.ForUpdate interface, must reside in a separate Java file, must be declared as public, and must be imported by the application using the iterator. o - In the event that an SQLException is thrown due to a DB2 for OS/390 Version 5 engine or SQLJ Runtime generated error, the SQLCODE, SQLSTATE, and error text are available through the getErrorCode, getSQLState, and getMessage methods respectively. o - If the application does not require connectivity to any DRDA Server, then the application can use the default ConnectionContext, which establishes a connection to the DB2 subsystem specified by the environment variable DB2SQLJSSID.