WebSphere Application Server 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.
About this task
Structured Query Language in Java (SQLJ) for EJB Persistence requires
DB2 Version 8 FixPak1 or later. You need DB2 UDB Application Development
Client Version 8 FixPak1 (or later) on the machine where you generate the
deployed code, and DB2 UDB Server (Version 8 FixPak1 or later for Unix and
Windows, and Version 6 or Version 7 for DB2 on z/OS and OS/390) on the machine
containing the database for running the scenario.
SQLJ support requires
Version 2.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 Version 2.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 Using 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 WebSphere Application Server. 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 administrative console when you install the application. If you pre-deploy
this bean with the -sqlj option and then redeploy it from
the administrative console when you install the application, this bean deploys
with JDBC access.
Note: You cannot deploy a CMP bean with the sqlj option
from the administrative 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.
- 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 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.
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, 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 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 WebSphere Application Server.
- 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 administrative 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.