Customize SQLJ profiles with the db2sqljcustomize tool

 

+

Search Tips   |   Advanced Search

Customize and bind SQLJ profiles with the db2sqljcustomize tool before you install the SQLJ application in the appserver.

If the application is already installed in the appserver, you will need to reinstall the application after customizing 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 appserver
• Deploy SQLJ applications with the ejbdeploy tool

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

New feature: The appserver supports customizing and binding the SQLJ profiles in the admin console or with scripting:

1. Make sure the necessary database tables exist

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. If the application is not running in a clustered environment, we can use a ws_ant script.

   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.

   1. Change the values of the database URL, and the database user and password properties in...

      ejbdeploy.sqlj.properties

      ...which is a common file to all Ant scripts generated by the ejbdeploy command, and defines the global properties for...

      • Database URL - db.url
      • User - db.user
      • - 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 file...

      application_name.ear.properties

      ...which 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 that are 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 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 specify 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.

      A sample Ant command looks like this:

      ws_ant -Dwork.dir=tmp -Dscript.property.file=other.properties -buildfile application_name.ear.xml

      where:

      • -buildfile

        XML file to create.

      • -Dscript.property.file

        Different properties file. Optional.

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

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

   1. 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 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 you issued the command. If we 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 the 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 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.

      Supported configurations:

      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.

      sptcfg

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

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

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

9. Install the application in the appserver.

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

Exceptions pertaining to data access

 

Related tasks

Customize profiles for SQLJ applications
Deploy SQLJ applications that use CMP
Deploy SQLJ applications that use BMP, servlets, or sessions beans
Rational Application Developer: Developing SQLJ applications
Deploy SQLJ applications
Develop data access applications

 

Related

Application management
Data access bean types