Customize and bind SQLJ profiles with the db2sqljcustomize
tool before you install the SQLJ application in the application server.
Before you begin
To perform this task, you must have SQLJ application that
has been deployed, but the application should not be installed in
the application server. If the application is already installed in
the application server, you will need to reinstall the application
after you customize the profiles. You also need serialized profiles
for the SQLJ application.
For SQLJ applications that use container-managed
persistence, you can deploy the application in two ways:
- Deploy the SQLJ application in the application server. See the
topic on deploying SQLJ applications that use container-managed persistence
(CMP) for more information.
- Deploy SQLJ applications with the ejbdeploy tool. See the topic
on deploying SQLJ applications that use container-managed persistence
(CMP) with the ejbdeploy tool.
For SQLJ application that use bean-managed persistence, see the
topic on deploying SQLJ applications that use bean-managed persistence,
servlets, or sessions beans.
About this task
To take advantage of SQLJ applications in the application
server, you need to customize the SQLJ profiles. The customization
process augments the profiles with information that is specific to
the DB2® database. The database uses this information
at run time. By default, four DB2 packages are created in the
database; one package is created for each isolation level.
The application
server supports customizing and binding the SQLJ profiles in the administrative
console or with scripting:
- For administrative console support, read the topic on customizing
and binding profiles for Structured Query Language in Java (SQLJ) applications.
- For scripting support, see the topic on the application management
command group for the AdminTask object.
Procedure
- Make sure the necessary database tables exist, as described
in the topic on deploying data access applications.
- Transfer the serialized profiles to the environment on
which you installed your application. Alternatively, use the Java jar command to extract
the serialized profiles from the JAR file in your installed EAR directory.
- Add the location for the SQLJ profiles and the application's
JAR file to your environment's class path.
- Make sure the necessary database tables exist, as described
in the topic on deploying data access applications.
- Optional: If your application is not running
in a clustered environment, you can use the Ant script to make customization
easier. If you run a batch SQLJ customization against an
EAR file with the ejbdeploy tool, the tool produces an Ant script
that is named application_name.ear.xml. You can
use this script file to run the DB2 customizer
program against the serialized profiles in all of the enterprise bean
JAR files for the associated EAR file. The script updates each enterprise
bean's JAR file with a serialized profile and replaces the JAR files
in the existing EAR file with the modified versions.
![[AIX Solaris HP-UX Linux Windows]](../images/dist.gif)
The
tool is:
- Change the values of the database URL, and the database
user and password properties in ejbdeploy.sqlj.properties. This
file is a common file to all Ant scripts that are generated by the
ejbdeploy command. The ejbdeploy.sqlj.properties script defines the
global properties for:
- Database URL - db.url
- User - db.user
- Password - db.password
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.
- Run the Ant script, specifying the properties target. For example:
ws_ant -buildfile application_name.ear.xml properties
This script creates the properties file,
application_name.ear.properties.
The
application_name.ear.properties file contains
properties that specify the default names for the packages corresponding
to each serialized profile in the EAR file. This is a sample properties
file:
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
- Use the DB2 Control Center to identify the
packages that are 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.
- Change the names that are used by the script file to
ensure that the names for each customization profile do not conflict
with existing package names that are in the database. Ant
scripts that are generated for different EAR files use the same package
names by default, and the script will overwrite existing packages
unless you change the names. Overwritten packages can cause errors
at run time.
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 DB2 customizer will create packages called TEST1,
TEST2, TEST3, and TEST4.
- Run the Ant script. The Ant script updates
the original EAR file with the modified serialized profiles.
Avoid trouble: Verify that you have db2jcc.jar in the class path.
This file should have been added to the class path environment variable
when DB2 V8 FixPak1 was installed.
gotcha
A sample Ant command looks like this:ws_ant -Dwork.dir=tmp
-Dscript.property.file=other.properties
-buildfile application_name.ear.xml
where:- -buildfile specifies the XML file to create.
- -Dscript.property.file specifies a different
properties file. This parameter is optional. If you want your Ant
script to use a another file instead of application_name.ear.properties,
specify the Dscript.property.file property when you run the script.
- -Dwork.dir specifies a temporary working
directory for the 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 or overwrite
the files and directories. This script creates and uses a directory
called tmp as its working directory.
- Proceed to installing
the application in the application server..
- Run the db2sqljcustomize tool to customize the SQLJ profiles
that correspond to each enterprise bean's JAR file. When
you generate your deployment code, serialized profiles (files with
a .ser extension) that are specific to your application are created.
These profiles exist in the same directory as your SQLJ files, and
the files must be customized to the environment before they can be
used. When you run the DB2 SQLJ customizer against the
serialized profiles, you create static SQL in the database that DB2 will
use at run time. The customization phase creates four database packages
that contain static SQL, one for each isolation level.
- Optional: Consider using the SQLJ customizer
tool to enable context caching for your application's data source
connections. DB2 V8.1 fix pack 6 provides the
new caching option with the db2sqljcustomize tool called db2optimize.
You can run this option if your application uses the explicit connection
context instead of the default context.
Avoid trouble: - SQLJ context caching support requires the DB2 with IBM® JCC
driver or Version 2.2 or later of the DB2 Universal
JDBC Driver with APAR PQ87786 applied.
- If you want to enable context caching for an application or BMP
bean that caches connections across transaction boundaries, you cannot
use shareable connections. Use the get/use/close pattern of connection
usage when you invoke the db2optimize option, or an object closed
exception occurs. The following code gives an example of incorrect
connection usage for context caching:
utx.begin();
cons =ds.getConnection(
request.getParameter("db.user"),
request.getParameter("db.password"));
cmctx1 = new CM_context(cons);
#sql [cmctx1] {DELETE FROM cmtest WHERE id=1};
utx.commit();
//The next statement verifies the result:
#sql [cmctx1] cursor1 = {SELECT id, name FROM cmtest WHERE id=1};
In
this case, the Select statement elicits an object
closed exception. To prevent the exception from occurring, close the
connection before committing the transaction. Then get a new connection
and a new context before running the Select statement.
gotcha
The following example code demonstrates
proper syntax for running the option on the serialized profile: sqlj -db2optimize SQLJTransactionTest.sqlj
db2sqljcustomize -url jdbc:db2://localhost:50000/dbname -user USER_NAME -password PASSWORD
SQLJTransactionTest_SJProfile0.ser
- Run the db2sqljcustomize tool to customize the SQLJ
profiles. After you successfully run the db2sqljcustomize
command, customized profiles exist in the directory from which you
issued the command. If you run the db2sqljcustomize command from the
directory that contains the serialized profiles that were not customized,
the customized versions will overwrite previous versions that have
the same file names.
The recommended syntax for
running the db2sqljcustomize command is:
db2sqljcustomize -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.
- 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, separated by spaces.
- Alternatively, you can specify the -rootpkgname parameter
to customize more than one serialized profile with the same command.
Note: The following options provide more control
over the customization process:
- -automaticbind yes specifies to run the DB2 SQLJ
customizer against the serialized profiles to create static SQL in
the database that the database will use at run time. The customization
phase creates four database packages that contain static SQL, one
for each isolation level.
- -onlinecheck NO and -bindoptions "VALIDATE RUN" specifies
settings to bypass errors during a profile customization and ensure
a successful customization.
- Update the JAR file for the enterprise beans with the serialized
profiles.
- Use the jar command to replace the
serialized profiles in your JAR file with the customized profiles.
Avoid trouble: 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.
gotcha
- Package the JAR file for the enterprise bean, servlets,
and serialized profiles into an enterprise archive (EAR) file.
- Install the application in the application
server.