Embedded Structured Query Language in Java (SQLJ) support added in WebSphere Application Server Version 5.0.1

Embedded Structured Query Language in Java (SQLJ ) support added in WebSphere Application Server Version 5.0.1

WebSphere Application Server Version 5.0.1 introduces support for Structured Query Language Java (SQLJ ) with the DB2 universal Java Database Connectivity (JDBC) driver provider and the DB2 Legacy CLI-based Type 2 JDBC driver for DB2 Version 8 FixPak1 (FP1).

SQLJ is a set of programming extensions that allow a programmer, using the Java programming language, to embed statements that provide SQL (Structured Query Language) database requests.

Only the WebSphere Application Server Version 5.0.1 data source supports the DB2 universal JDBC driver provider. The WebSphere Application Server Version 4.0.x data source does not support this driver provider. In addition, the universal JDBC driver currently only supports a single phase data source.

Advantages of developing applications with SQLJ include improved performance and a shorter, more efficient development cycle:

You improve performance by using static SQL statements.
You reduce the development cycle because:
- You write less code with the simpler SQLJ syntax, which reduces the number of lines of code required to execute statements, and to set and retrieve parameters.
- You detect programming errors earlier in the development phase with the onlinecheck function, which performs data type and schema validation.

You might consider using SQLJ in situations where dynamic SQL is not needed, and where applications use DB2 as the backend database.

Refer to the following sections to convert existing JDBC applications to SQLJ in WebSphere Application Server:

Required drivers for SQLJ
Using SQLJ for Enterprise Java Bean (EJB) Container Managed Persistence (CMP) beans
Using SQLJ for Enterprise Java Bean (EJB) Bean Managed Persistence (BMP) entity beans, session beans, and Servlets

Required drivers for SQLJ

You need the following drivers to run SQLJ:

DB2 Legacy CLI-based JDBC driver
You must have the db2java.zip and db2jcc.jar in your classpath. These files are defined in the classpath of the Websphere Application Server Version 5.0.1 DB2 Legacy CLI-based JDBC Driver Provider.

DB2 Universal JDBC driver
You only need the db2jcc.jar file.

WebSphere Application Server driver
It must be WebSphere Application Server Version 5.0.1.

Using SQLJ for Enterprise Java Bean (EJB) Container Managed Persistence (CMP) beans

SQLJ for EJB Persistence requires DB2 Version 8 FP1. You need DB2 UDB Application Development Client Version 8 FP1 on the machine where you generate the deployed code, and DB2 UDB Server (Version 8 FP1 for UNIX and Windows, and Version 6 or Version 7 for z/OS and OS/390) on the machine containing the database for running the scenario.

SQLJ support requires Version 2.0 CMP Enterprise Java Beans. If you mix EJB 1.1 and 2.0 beans in an EJB 2.0 module, then only the EJB 2.0 beans are deployed to use SQLJ. The EJB 1.1 beans continue using JDBC for data access.

The deployment command for Enterprise Java Beans (EJBDeploy) only provides SQLJ support for the Version 2.0 CMP beans. It is the user's responsibility to manually translate and customize the profile on the database for the BMP entity beans, session beans, and servlet SQLJ applications.
See the Using SQLJ for Enterprise Java Bean (EJB) Bean Managed Persistence (BMP) entity beans, session beans, and Servlets section for more information.

WebSphere Application Server Version 5.0.1 provides tools for using SQLJ as the persistence mechanism for CMP Enterprise Java Beans. You can deploy the CMP beans to use SQLJ either by using the EJB deploy tool in WebSphere Studio Application Developer (WSAD), or by using the command utility ejbdeploy with the -sqlj option.

Note: You cannot deploy a CMP bean with the sqlj option from the administrative console or the Application Assembly Tool (AAT) in Version 5.01.

When you install an application comprised of CMP beans, you have the following choices:

For data access with SQLJ, deploy the CMP beans to use SQLJ before installing the application in WebSphere Application Server. Perform deployment within the WSAD environment, or by using the command utility ejbdeploy with the -sqlj option.
Do not deploy this bean again in the administrative console when you install the application. If you pre-deploy this bean with the -sqlj option and then re-deploy it from the administrative console when you install the application, this bean is then deployed with JDBC access.
For JDBC access, use the ejbdeploy command or the administrative console to deploy the CMP bean.
If the bean is not deployed, the installation of the application will fail.

Deploying an EAR file

Perfom the following steps to deploy your EAR file:

Ensure directory <was_home>/bin is in your PATH.

Verify the ws_ant.bat file is available on your system.
You need the ws_ant.bat file to run the rest of the commands.

Note: On Windows, the Apache Ant tool is ws_ant.bat On UNIX systems, the Ant tool is ws_ant.

Run the ejbdeploy command utility with the -sqlj option.
You must supply the location of the SQLJ translator sqlj.zip file with the -cp (classpath) option:
```
ejbdeploy d:\MyApplication.ear working d:\deployedMyApplication.ear -sqlj -dbvendor DB2UDB_V81 -cp "C:\PROGRA~1\IBM\SQLLIB\java\sqlj.zip;C:\PROGRA~1\IBM\SQLLIB\java\sqlj.zip "
```
Note: The ejbdeploy command will not access sqlj.zip from your system classpath.
The ejbdeploy command will generate an EAR file with the name you specify as, for example, deployedMyApplication.ear and an Ant script with the name <InputEarName>ear.xml, or in this example, deployedMyApplication.ear.xml.

If you create the EAR file using WebSphere Studio Application Developer (WSAD), you can associate SQLJ with a particular database backend in the mapping editor. If you use the mapping editor, you do not have to specify the -sqlj option in the ejbdeploy command.

Perform the customization step, described below.
If your application is not running in a clustered environment, go to the Ant tool properties description.
SQLJ profile customization

Perform the customization phase when the application is running in a clustered environment. Perform customization once, on a single host.

Customization consists of the following steps:
- 4-A. Run the DB2 SQLJ customizer, db2sqljcustomize, against the serialized profiles.
  One serialized profile exists for each EJB .jar file. You can find the serialized profile in the EJB .jar file. One example of a serialized profile file name is FS_TopDown1_SJProfile0.ser.
  
  When you run the DB2 SQLJ customizer with the "-automaticbind yes" default option against the serialized profiles, you create static SQL in the database, which is used at runtime. The customization phase creates four database packages that contain static SQL, one for each isolation level.
- 4-B. Include the customized profiles in the EJB .jar files installed on WebSphere Application Server.
  The customization step also updates the generated customized profiles. To make these updated customized profiles available to the generated code at runtime, include the profiles in the EJB .jar file, which is installed in WebSphere Application Server.
  
  Note: If you omit the customization step, the EJB applications will run, but they will not use the static SQL stored in the database, and you lose all the benefits of SQLJ.
- 4-C. Use the Ant script to make customization easier.
  When you run batch SQLJ ejbDeploy against an EAR file, it produces an Ant script. Use this script file to run the DB2 customizer against every serialized profile in every EJB .jar file in the associated EAR file. The script updates each EJB .jar file with a serialized profile, and replaces the .jar files in the EAR file with the modified versions. The Ant script is specific to the corresponding EAR file.
  
  Note: This script modifies the existing EAR file.
  
  The script also uses a set of default names for the packages created in the database. Change the names used by the script file to ensure the names for each customization profile do not conflict with existing package names in the database. Ant scripts, generated for different EAR files, by default use the same package names, and will overwrite existing packages unless the names are changed. Overwritten packages cause runtime errors.
- 4-D. Change the values of the database URL, and the database userid and password properties in the generated Ant script.
  The package names, database URLs, userids, and passwords are created in the script using Ant properties.
  
  The Ant script defines the following global properties:
  - for the database URL - db.url
  - for the user - db.user
  - for the password - db.password
- 4-E. Change the values used by the Ant script and the serialized profile by specifying new values in a property file.
  The Ant script uses the URL, userid, and password properties in the serialized profile to customize the profile. By default, the properties for the serialized profile are created from the global properties.
  
  The script for a particular EAR file reads properties from two files:
  - ejbdeploy.sqlj.properties
    The ejbdeploy.sqlj.properties file is common to all Ant scripts generated by the EJBDeploy command. Use the ejbdeploy.sqlj.properties file to specify global properties, like the database userid and password.
  - <ear-name>.properties
    The <ear-name> value is the name of the EAR file. The <ear-name>.properties file is specific to the Ant script for the EAR file. If you want your Ant script to use a another file instead of <ear-name>.properties, specify the script.property.file property when you run the script.
- 4-F. Use the DB2 Control Center to identify the packages installed in the database.
  Note: The DB2 SQLJ customizer requires a type 4 database URL in the form of:
```
jdbc:db2://<host-name>:<port>/<database-name>
```
  It also requires a userid and password. The value of the port is 50000, unless you changed it when you installed DB2.

Run the Ant script, specifying the properties target:
```
                      ws_ant -buildfile deployedMyApplication.ear.xml properties
```
This will create the properties file, deployedMyApplication.ear.properties. The deployedMyApplication.ear.properties file contains properties specifying default names for the packages corresponding to each serialized profile in the EAR file, as for example:
```
                pkg.MyEJB1.jar.DB2UDBNT_V8_1=PKG1_
                pkg.MyEJB2.jar.DB2UDBNT_V8_1=PKG2_
```
In this example, the EAR file contains two EJB .jar files: MyEJB1.jar and MyEJB2.jar.

Edit the generated properties file to change the package names.

You can also specify other properties in this file, such as the database URL, user, or password:

              url.MyEJB1.jar.DB2UDBNT_V8_1=jdbc:db2://localhost:50000/MyDB1
              user.MyEJB1.jar.DB2UDBNT_V8_1=dbuser
              password.MyEJB1.jar.DB2UDBNT_V8_1=dbpassword
              pkg.MyEJB1.jar.DB2UDBNT_V8_1=TEST
              url.MyEJB2.jar.DB2UDBNT_V8_1=jdbc:db2://localhost:50000/MyDB2
              user.MyEJB2.jar.DB2UDBNT_V8_1=dbuser  
              password.MyEJB2.jar.DB2UDBNT_V8_1=dbpassword
              pkg.MyEJB2.jar.DB2UDBNT_V8_1=WORK

Note: DB2 uses the first seven characters of the package name. The DB2 customizer uses this name to create four packages in the database. For example, if you specify the name TEST, the customizer will create packages called:
TEST1, TEST2, TEST3, TEST4

To bypass errors during a profile customization and to ensure a successful customization, use the following options to temporarily circumvent problems:
```
            -onlinecheck NO and -bindoptions "VALIDATE RUN"
```
However, you must understand what the problems are and fix them at the appropriate time.

Run the Ant script.
The DB2 db2jcc.jar file must be on the classpath. This file should have been added to the classpath environment variable when DB2 V8 FP1 was installed.

Specify a working directory for the Ant script.
The script will create and delete files and sub-directories in this directory. If the working directory contains existing files and directories with the same name as the files and directories used by the script, the script will erase the files and directories.

Use the following command to specify a working directory:
```
         ws_ant -Dwork.dir=tmp -buildfile MyApplication.ear.xml
```
The script creates and uses a directory called tmp as its working directory. If you want the script to use a different property file, set the script.property.file property when you run the script:
```
          ws_ant -Dwork.dir=tmp -Dscript.property.file=other.properties -buildfile MyApplication.ear.xml
```
The Ant script updates the original EAR file with the modified serialized profiles.

If you rerun the EJBDeploy command, rerun the Ant script.
Generate a new properties file if any of the following conditions are true:
- You changed the number of .jar files in the EAR file.
- You changed the names of the .jar files in the EAR file.
- You changed the database backend ids in any of the .jar files
Install the updated EAR file in WebSphere Application Server.

Create a jdbc provider and data source in WebSphere Application Server:
To create a JDBC provider, in the administrative console:
1. Click Resources > JDBC providers > NEW
2. Choose the default value for DB2 universal JDBC Driver provider.
3. Click Apply.
To create a data source under this JDBC provider, in the administrative console:
1. Scroll down to the Additional Properties panel.
2. Click DataSource > NEW
3. Define the Alias from the J2C Authentication Data Entries
4. Return to the data source panel
5. Enter the Alias in both the Component and Container managed authentication alias entries.
6. Specify the databaseName on the Custom Properties panel.
7. For a data source using the universal driver, specify the portNumber and serverName on the Custom Properties panel.

Install your application through the administrative console but do not re-deploy the EJB.
Note: If you check the EJBDeploy box, your application is redeployed to JDBC access.

Stop the server.

Start the server.

You are now ready to run your application.

Using SQLJ for Enterprise Java Bean (EJB) Bean Managed Persistence (BMP) entity beans, session beans, and Servlets

This section describes how JDBC applications, comprised of Bean Managed Persistence (BMP) beans or servlets, are converted to SQLJ applications. It also describes how the SQLJ applications are then deployed in WebSphere Application Server.

Follow these steps precisely and in the right order to ensure a correct conversion:

Create a backup copy of your .java file.
For example if your file is called MyServlet.java, copy MyServlet.java to MyServlet.java.bkup.

Rename your .java file to to a file name with a .sqlj extension.
For example, if your application is a servlet named MyServlet.java, rename MyServlet.java to MyServlet.sqlj.
Now when you run the sqlj tool in the next step, the .java file that it creates will have the same name as your old .java file, providing you with a seamless transition to the SQLJ technology.

Edit the .sqlj file to convert the JDBC syntax to SQLJ syntax.

Note: When using SQLJ in WebSphere Application Server, if you want WebSphere Application Server connection management to function properly, you must specify correct connection contexts.

For example, convert the following JDBC operation:


            Connection con = dataSource.getConnection();
            Statement stmt = con.createStatement();
            stmt.execute("INSERT INTO users VALUES (1, 'user1')");
            con.commit();

to the following SQLJ:


             // At the top of the file and just below the import statements, define Connection_Context
             #sql context Connection_context;
              .
              .
             Connection con = dataSource.getConnection();
              .
              .
             Connection_context ctx1 = new Connection_context(con);
              .
              .
             #sql [ctx1] {INSERT INTO users VALUES (1, 'user1')};
              .
              . 
             con.commit();
             ctx1.close();

Run the DB2 sqlj translator.
This tool creates a .java version of your .sqlj file as well as a .ser profile that is used later in the processing. Refer to the DB2 documentation for more information on this tool.
Package your EJB jar and deploy it in the usual manner.

After the EJB files are deployed, run the db2sqljcustomize tool to customize your .ser file.
One serialized profile exists for each EJB .jar file. You can find the profile in the EJB .jar file. One example of a serialized profile file name is MyBMPBeanProfile.ser.

When you run the DB2 SQLJ customizer against the serialized profiles, you create static SQL in the database, which is used at runtime. The customization phase creates four database packages that contain static SQL, one for each isolation level.

Configure your database.

Update your EJB jar with the .ser file.

Package your EJB jar and servlets, along with .ser files, into an .ear file.

Install the application in the usual manner.