Use Structured Query Language in Java (SQLJ) for Enterprise Java Bean (EJB) container-managed persistence (CMP) beans

Use Structured Query Language in Java (SQLJ) for Enterprise Java Bean (EJB) container-managed persistence (CMP) beans

WAS 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 Rational Application Developer (RAD), or by using the command utility ejbdeploy with the -sqljoption.

Overview

Structured Query Language in Java (SQLJ) for EJB Persistence requires DB2 V8 FixPak1 or later. We need DB2 UDB Application Development Client V8 FixPak1 (or later) on the machine where you generate the deployed code, and DB2 UDB Server (V8 FixPak1 or later for Unix and Windows, and V6 or V7 for DB2 on z/OS and OS/390) on the machine containing the database for running the scenario.
SQLJ support requires V2.x CMP Enterprise Java Beans. If you mix EJB 1.1 and 2.x beans in an EJB 2.x module, then only the EJB 2.x 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 V2.0 CMP beans. It is your responsibility to manually translate and customize the profile on the database for the Bean Managed Persistence (BMP) entity beans, session beans, and servlet SQLJ applications. See the Use SQLJ for Enterprise Java Bean (EJB) Bean Managed Persistence (BMP) entity beans, session beans, and Servlets article for more information. 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 WAS. Perform deployment within the RAD environment, or by using the command utility ejbdeploy with the -sqlj option.
Do not deploy this bean again in the console when you install the application. If you pre-deploy this bean with the -sqlj option and then redeploy it from the console when you install the application, this bean deploys with JDBC access.
You cannot deploy a CMP bean with the sqlj option from the console or the assembly tool.

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 fails.

Procedure
Deploy the EAR file.

Verify that the was_home/bin directory is in your PATH statement.

Launch the Apache Ant tool; use it for the rest of the commands.

The tool is ws_ant.bat.
The tool is ws_ant.

Run the ejbdeploy command utility with the -sqlj option.
Perform the following SQLJ profile customization steps if your application is running in a clustered environment.
You must supply the location of the SQLJ translator sqlj.zip file with the -cp (class path) 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 "
The ejbdeploy command does not access sqlj.zip from your system class path. 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 Rational Application Developer, 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.
If your application is not running in a clustered environment, go to the Ant tool properties description.
Perform customization once on a single host.
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.

Include the customized profiles in the EJB .jar files installed on WAS.
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.
If you omit the customization step, the EJB applications run, but do not use the static SQL stored in the database, and you lose all the benefits of SQLJ.

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 program 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.
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 use the same package names by default, and overwrite existing packages unless you change the names. Overwritten packages cause run-time errors.

Change the values of the database URL, and the database user and password properties in the generated Ant script.
The package names, database URLs, users, and passwords are created in the script using Ant properties.
The Ant script defines the following global properties for the:

Database URL - db.url

User - db.user

Password - db.password

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, user, 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, such as the database user 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.
Use the DB2 Control Center to identify the packages installed in the database. 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 user and password. The value of the port is 50000, unless you change it when you install DB2.
Run the Ant script, specifying the properties target:
ws_ant -buildfile deployedMyApplication.ear.xml properties
This script creates 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.
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
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 
You can also specify other properties in this file, such as the database URL, user, or password.
Use the following db2sqljcustomize options to temporarily circumvent profile customization problems.
These options bypass errors during a profile customization and ensure a successful customization:
-onlinecheck NO and -bindoptions "VALIDATE RUN"
However, 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 class path. This file should have been added to the class path environment variable when DB2 V8 FixPak1 was installed.
Specify a working directory for the Ant script.
The script will create and delete files and subdirectories 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 change the number of .jar files in the EAR file.

You change the names of the .jar files in the EAR file.

You change the database backend ids in any of the .jar files

Install the updated EAR file in WAS.

Create a JDBC provider and data source in WebSphere Application Server.
Generate a new properties file if any of the following conditions are true:

You change the number of .jar files in the EAR file.

You change the names of the .jar files in the EAR file.

You change the database backend ids in any of the .jar files

Generate a new properties file if any of the following conditions are true:

You change the number of .jar files in the EAR file.

You change the names of the .jar files in the EAR file.

You change the database backend IDs in any of the .jar files

Install your application through the console but do not redeploy the EJB. 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.
Embedded Structured Query language in Java (SQLJ) support
Exceptions pertaining to data access

Related tasks

Applying access intent policies to beans
Developing data access applications

Related Reference

Data access bean types