Customize and binding SQLJ profiles with the db2sqljcustomize tool

Network Deployment (Distributed operating systems), v8.0 > Develop and deploying applications > Develop Data access resources > Deploy SQLJ applications > Customize and binding profiles for Structured Query Language in Java (SQLJ) applications

Customize and binding SQLJ profiles with the db2sqljcustomize tool

Customize and bind SQLJ profiles with the db2sqljcustomize tool before you install the SQLJ application in the application server. To perform this task, 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. To take advantage of SQLJ applications in the application server, 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 admin 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 .

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 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, 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. We 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.
The tool is:

(AIX) (Solaris) 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 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. 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. 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 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 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 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, 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.
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 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.

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 you 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 admin console. If you redeploy the enterprise beans from the admin console, you will lose the customization changes that we have made.
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
Develop data access applications

Related

Application management command group
Data access bean types

+
Search Tips | Advanced Search