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

  1. 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();
            }
         }
    }
  2. 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
  3. 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:
    • 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.
    Example usage:
      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    
  4. 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.
  5. 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>
  6. 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
  7. 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">

Icon that indicates the type of topic Task topic



Timestamp icon Last updated: Saturday, 3 December 2016
http://www14.software.ibm.com/webapp/wsbroker/redirect?version=cord&product=was-nd-mp&topic=twlp_deploy_sqlj
File name: twlp_deploy_sqlj.html