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, 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, 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 console or :

• For 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 (AdminTask).

1. Make sure the necessary database tables exist, as described in the topic on deploying data access applications.

2. 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 the installed EAR directory.

3. Add the location for the SQLJ profiles and the application's JAR file to the environment's class path.

4. Make sure the necessary database tables exist, as described in the topic on deploying data access applications.

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

   (dist) The tool is:

   • ws_ant

   • ws_ant.bat

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

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

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

   4. Change the names 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 specified the name TEST, the DB2 customizer will create packages called TEST1, TEST2, TEST3, and TEST4.

   5. Run the Ant script. The Ant script updates the original EAR file with the modified serialized profiles.

      Avoid trouble: 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. 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. Optional. If we want the 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.

   6. Proceed to installing the application in the application server..

6. Run the db2sqljcustomize tool to customize the SQLJ profiles that correspond to each enterprise bean's JAR file. When you generate the 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 the 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, we create static SQL in the database that DB2 will use at run time. The customization phase creates four database packages containing static SQL, one for each isolation level.

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

      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.

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

      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

   2. 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 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 the tables reside.

      • USER_NAME is a valid user name for the DB2 system where the 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 you 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 containing 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.

7. Update the JAR file for the enterprise beans with the serialized profiles.

8. Use the jar command to replace the serialized profiles in the 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 the JAR file. If we decide to replace the serialized profiles in the JAR file, maintain the directory structure in which the profiles exist.gotcha

9. Package the JAR file for the enterprise bean, servlets, and serialized profiles into an EAR file.

10. Install the application in the application server.

    Avoid trouble: Do not select Deploy enterprise beans during the application installation process in the console. If we redeploy the enterprise beans from the console, you will lose the customization changes that we have made.gotcha

Related concepts

Exceptions pertaining to data access

Related tasks

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

Application management (AdminTask)

Data access bean types