Customizing and binding SQLJ profiles with the db2sqljcustomize tool

Customize and binding SQLJ profiles with the db2sqljcustomize tool

Customize and bind SQLJ profiles with the db2sqljcustomize tool before we install the SQLJ application in the application server.

To perform this task, we 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, we will need to reinstall the application after you customize the profiles. We also need serialized profiles for the SQLJ application.
For SQLJ applications that use container-managed persistence, we 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.

To take advantage of SQLJ applications in the application server, we 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 .

Tasks
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 we installed the 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 the environment's class path.
Make sure the necessary database tables exist, as described in the topic on deploying data access applications.
Optional: If the application is not running in a clustered environment, we can use the Ant script to make customization easier. If we 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. 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.
The tool is:

(AIX) (HPUX) (Solaris) (Linux) ws_ant
(Windows) ws_ant.bat
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 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 
Create 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 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 we change it when we install DB2.
Change the names used by the script file to ensure that 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 the script will overwrite existing packages unless we 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 we 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.
Verify that we 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.. 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. Optional. If we want your Ant script to use a another file instead of application_name.ear.properties, specify the Dscript.property.file property when we 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. Create 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 we generate the deployment code, serialized profiles (files with a .ser extension) specific to the 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 we run the DB2 SQLJ customizer against the serialized profiles, we 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 the application's data source connections. DB2 V8.1 fix pack 6 provides the new caching option with the db2sqljcustomize tool called db2optimize. We can run this option if the application uses the explicit connection context instead of the default context.
SQLJ context caching support requires the DB2 with IBM JCC driver or v2.2 or later of the DB2 Universal JDBC Driver with APAR PQ87786 applied.
To enable context caching for an application or BMP bean that caches connections across transaction boundaries, we 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.
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 we issued the command. If we run the db2sqljcustomize command from the directory containing 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 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 created by the profile customizer begin with this name and are appended with a number from 1 to 4. If we 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 we 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 we are customizing.

To customize more than one serialized profile with the same command, list multiple files, separated by spaces.
Alternatively, we can specify the -rootpkgname parameter to customize more than one serialized profile with the same command.

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.
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 we decide to replace the serialized profiles in your JAR file, maintain the directory structure in which the profiles exist.
Package the JAR file for the enterprise bean, servlets, and serialized profiles into an EAR file.
Install the application in the application server.
Do not select Deploy enterprise beans during the application installation process in the administrative console. If we redeploy the enterprise beans from the administrative console, we will lose the customization changes that we have made.
Related:

Exceptions pertaining to data access
Customize and binding profiles for Structured Query Language in Java (SQLJ) applications
Deploy SQLJ applications that use container-managed persistence (CMP)
Deploy SQLJ applications that use bean-managed persistence, servlets, or sessions beans
Rational Application Developer: Developing SQLJ applications
Deploy SQLJ applications
Developing data access applications
Application management
Data access bean types