Deploying SQLJ applications
Use Structured Query Language for Java™ Enterprise Edition (SQLJ) to develop data access applications that connect to DB2®® databases. SQLJ is a set of programming extensions that enables you to use the Java programming language to embed statements that provide Structured Query Language (SQL) database requests.
About this task
Using SQLJ in your application provides the following benefits when it is compared to the
alternative of JDBC:
- Can improve performance if you use the DB2 customize and bind steps.
- Enable applications to run static SQL so that you can use more fine-grained security policies, such as PACKAGE level permissions, rather than dynamic SQL that JDBC offers.
Procedure
- Create a .sqlj file to perform database operations for your
application.
import java.sql.*; import javax.sql.*; import java.util.*; import javax.naming.; import sqlj.runtime.*; #sql context MyCtx; #sql iterator MyIter(String name); public class MyDB2SQLJProcedure { public static void myProcedure() throws Exception { DataSource ds = (DataSource) new InitialContext().lookup("java:comp/env/jdbc/myDS"); Connection con = ds.getConnection(); con.setAutoCommit(false); MyCtx ctx = new MyCtx(con); try { // Insert some data into the 'mytable' table. // Note that the java variables 'key' and 'val' get substituted. int key = 1; String val = "one"; #sql [ctx] { INSERT into mytable VALUES (:key, :val) }; #sql [ctx] { COMMIT }; MyIter iter; #sql [ctx] iter = { SELECT name FROM mytable WHERE id=1 }; iter.next(); System.out.println("Found value: " + iter.name()); iter.close(); #sql [ctx] { COMMIT }; } finally { con.setAutoCommit(true); // When SQLJ performs transactional operations during context close, it assumes autocommit is true ctx.close(); } } }
- Acquire the required drivers to transform SQLJ files and transforms .sqlj
files to .java files. You can specify an extra parameter, -db2optimize, to perform DB2 specific transformations, which might increase performance.
java -cp .:/sqlj/sqlj4.zip:/sqlj/db2jcc4.jar sqlj.tools.Sqlj -C-classpath=/sqlj/sqlj4.zip:/sqlj/db2jcc4.jar -db2optimize MyDB2SQLJProcedure.sqlj
When you run this command, you receive the following output files:- MyDB2SQLJProcedure.class
- MyDB2SQLJProcedure.java
- MyDB2SQLJProcedure_SJProfile0.ser
- MyDB2SQLJProcedure_SJProfileKeys.class
- MyCtx.class
- MyIter.class
- Optional: Run the DB2 SQLJ Customizer tool to customize and bind on
the previously generated .ser files and create static SQL in the database that
DB2 uses at runtime. The customization phase creates four
database packages that contain static SQL-- one for each isolation level.
java com.ibm.db2.jcc.sqlj.Customizer -url JDBC_URL -user USER_NAME -password PASSWORD [-rootpkgname PACKAGE_NAME] SERIALIZED_PROFILE1 SERIALIZED_PROFILE2...
Where:Example usage:- JDBC_URL is the JDBC URL that is used to access the DB2 system where your tables reside, with the following syntax:jdbc:db2://host-name:port/database-name
- USER_NAME is a valid user name for the DB2 system where your tables reside
- PASSWORD is the password for the specified USER_NAME
- PACKAGE_NAME is a valid partitioned data set (PDS) member name, up to seven characters long. Each of the four packages that are created by the profile customizer begin with this name and are appended with a number from 1 to 4. If you customize only one serialized profile, this value defaults to a shortened version of the serialized profile name and the -rootpkgname parameter is not required. If you customize more than one serialized profile with the same command, there is no default value and the -rootpkgname parameter is required.
- SERIALIZED_PROFILE# is the name of the serialized profile that you are customizing.
- To customize more than one serialized profile with the same command, list multiple files that are separated by spaces.
- Alternatively, you can specify the -rootpkgname parameter to customize more than one serialized profile with the same command.
java -cp .:/sqlj/sqlj4.zip:/sqlj/db2jcc4.jar com.ibm.db2.jcc.sqlj.Customizer -url jdbc:db2://localhost:50000/mydb -user dbuser1 -passworddbpass1 MyDB2SQLJProcedure_SJProfile0.ser
Example output:[jcc][sqlj] Begin Customization [jcc][sqlj] Loading profile: MyDB2SQLJProcedure_SJProfile0 [jcc][sqlj] Customization complete for profile MyDB2SQLJProcedure_SJProfile0.ser [jcc][sqlj] Begin Bind [jcc][sqlj] Loading profile:MyDB2SQLJProcedure_SJProfile0 [jcc][sqlj] Driver defaults(user may override): BLOCKING ALL VALIDATE BIND STATICREADONLY YES [jcc][sqlj] Fixed driver options: DATETIME ISO DYNAMICRULES BIND [jcc][sqlj] Binding package DB2SQL01 atisolation level UR [jcc][sqlj] Binding package DB2SQL02 at isolation level CS [jcc][sqlj] Binding package DB2SQL03 at isolation level RS [jcc][sqlj] Binding package DB2SQL04 at isolation level RR [jcc][sqlj] Bind complete for MyDB2SQLJProcedure_SJProfile0
- Update your application archive to include all of the files that are generated in step 2. Be
sure to maintain the directory structure in which the profiles exist. For example, if the MyDB2SQLJProcedure class has a fully qualified name
of com.company.MyDB2SQLJProcedure, the generated files should be placed in a
com/company/ folder in the appropriate application module.Note: The customized files must be placed in a location that is part of the application class path, and they must exist ahead of the serialized profiles that are not customized in your JAR file. If you decide to replace the serialized profiles in your JAR file, maintain the directory structure in which the profiles exist.
- For Java EE applications, enable the required features,
define a data source configuration, and apply the necessary shared library to your application
classloader. The jdbc-4.1 feature must be enabled in the
server.xml file to use SQLJ. If a data source is found by using JNDI, the
jndi-1.0 feature must also be enabled. See the following
example:
<server> <featureManager> <feature>jdbc-4.1</feature> <feature>jndi-1.0</feature> </featureManager> <application location="myapp.war"> <classloader commonLibraryRef="DB2Lib"/> </application> <library id="DB2Lib"> <fileset dir="C:/DB2/java" includes="db2jcc4.jar db2jcc_license_cisuz.jar"/> </library> <dataSource id="myDS" jndiName="jdbc/myDS"> <jdbcDriver libraryRef="DB2Lib"/> <properties.db2.jcc databaseName="mydb" serverName="localhost" portNumber="50000" user="dbuser1" password="dbpass1"/> </dataSource> </server>
- For OSGi applications, enable the jdbc-4.1 and wab-1.0
features to use your OSGi application. If your application uses JNDI, also enable the
jndi-1.0 feature. Create an <osgiLibrary> that references
your SQLJ library. Then, configure your application to reference the OSGi library that contains the
necessary SQLJ packages. See the following example of the server.xml file:
<server> <featureManager> <feature>jdbc-4.1</feature> <feature>jndi-1.0</feature> <feature>wab-1.0</feature> </featureManager> <osgiApplication name="myapp" location="myapp.eba"/> <osgiLibrary libraryRef="DB2Lib"/> <library id="DB2Lib"> <fileset dir="C:/DB2/java" includes="db2jcc4.jar db2jcc_license_cisuz.jar"/> </library> <dataSource id="myDS" jndiName="jdbc/myDS"> <jdbcDriver libraryRef="DB2Lib"/> <properties.db2.jcc databaseName="mydb" serverName="localhost" portNumber="50000" user="dbuser1" password="dbpass1"/> </dataSource> </server>
In the MANIFEST.MF of the application module for 'myapp', import the necessary SQLJ packages, which are provided by the <osgiLibrary> element:Import-Package: javax.annotation, javax.sql, javax.naming, com.ibm.db2.jcc, com.ibm.db2.jcc.sqlj, sqlj.runtime, sqlj.runtime.error, sqlj.runtime.profile, sqlj.runtime.profile.ref, sqlj.runtime.profile.util, sqlj.runtime.ref, sqlj.runtime.util
- It is possible to have WebSphere® Application Server Liberty cache a DB2
SQLJ context with each pooled connection. DB2 SQLJ context
caching can result in behavior that is more consistent with WebSphere Application Server traditional, such as helping with cleanup of resources
that are not closed by the application. It might also result in performance improvements on SQLJ
paths. To enable DB2 SQLJ context caching, you must configure
the data source property enableConnectionCasting with a value of
true. See the following
example:
<dataSource id="myDS" jndiName="jdbc/myDS" enableConnectionCasting="true">
Parent topic: Deploying data access applications to Liberty


http://www14.software.ibm.com/webapp/wsbroker/redirect?version=cord&product=was-libcore-mp&topic=twlp_deploy_sqlj
File name: twlp_deploy_sqlj.html