Use SQLJ for Enterprise Java Bean (EJB) Container Managed Persistence beans

SQLJ for EJB Persistence requires DB2 Version 8 FixPak1. You need DB2 UDB Application Development Client Version 8 FixPak1 on the machine where you generate the deployed code, and DB2 UDB Server (Version 8 FixPak1 for UNIX and Windows, and Version 6 or Version 7 for z/OS and OS/390) on the machine containing the database for running the scenario.

SQLJ support requires Version 2.0 CMP Enterprise Java Beans. If you mix EJB 1.1 and 2.0 beans in an EJB 2.0 module, then only the EJB 2.0 beans are deployed to use SQLJ. The EJB 1.1 beans continue using JDBC for data access.

The deployment command for Enterprise Java Beans (EJBDeploy) only provides SQLJ support for the Version 2.0 CMP beans. It is your responsibility to manually translate and customize the profile on the database for the Bean Managed Persistence (BMP) entity beans, session beans, and servlet SQLJ applications. See the Using SQLJ for Enterprise Java Bean (EJB) Bean Managed Persistence (BMP) entity beans, session beans, and Servlets article for more information.

WebSphere Application Server V5.1 provides tools for using SQLJ as the persistence mechanism for CMP Enterprise Java Beans. You can deploy the CMP beans to use SQLJ either by using the EJB deploy tool in WebSphere Studio Application Developer (WSAD), or by using the command utility ejbdeploy with the -sqlj option.

You cannot deploy a CMP bean with the sqlj option from the administrative console or the Application Assembly Tool (AAT) in V5.1.

When you install an application comprised of CMP beans, you have the following choices...

  1. Deploy the EAR file.

    1. Verify that the was_home/bin directory is in your PATH statement.

    2. Verify that the ws_ant.bat file is available on your system.

      You need the ws_ant.bat file to run the rest of the commands.

      Note that On Windows platforms, the Apache Ant tool is ws_ant.bat On UNIX systems, the Ant tool is ws_ant.

    3. Run the ejbdeploy command utility with the -sqlj option.

  2. Perform the SQLJ profile customization steps if your application is running in a clustered environment.

    You must supply the location of the SQLJ translator file with the -cp (classpath) option

    ejbdeploy d:\MyApplication.ear 
              working d:\deployedMyApplication.ear 
              -dbvendor DB2UDB_V81 
              -cp "C:\PROGRA~1\IBM\SQLLIB\java\;C:\PROGRA~1\IBM\SQLLIB\java\ "

    The ejbdeploy command will not access from your system classpath. The ejbdeploy command will generate an EAR file with the name you specify as, for example, deployedMyApplication.ear and an Ant script with the name InputEarNameear.xml, or in this example, deployedMyApplication.ear.xml.

    If you create the EAR file using WebSphere Studio Application Developer (WSAD), you can associate SQLJ with a particular database backend in the mapping editor. If you use the mapping editor, you do not have to specify the -sqlj option in the ejbdeploy command.

    If your application is not running in a clustered environment, go to the Ant tool properties description.

    Perform customization once on a single host.

    1. Run the DB2 SQLJ customizer, db2sqljcustomize, against the serialized profiles.

      One serialized profile exists for each EJB .jar file. You can find the serialized profile in the EJB .jar file. One example of a serialized profile file name is FS_TopDown1_SJProfile0.ser.

      When you run the DB2 SQLJ customizer with the "-automaticbind yes" default option against the serialized profiles, you create static SQL in the database, which is used at runtime. The customization phase creates four database packages that contain static SQL, one for each isolation level.

    2. Include the customized profiles in the EJB .jar files installed on WAS.

      The customization step also updates the generated customized profiles. To make these updated customized profiles available to the generated code at runtime, include the profiles in the EJB .jar file, which is installed in WebSphere Application Server.

      If you omit the customization step, the EJB applications run, but do not use the static SQL stored in the database, and you lose all the benefits of SQLJ.

    3. Use the Ant script to make customization easier.

      When you run batch SQLJ ejbDeploy against an EAR file, it produces an Ant script. Use this script file to run the DB2 customizer program against every serialized profile in every EJB .jar file in the associated EAR file. The script updates each EJB .jar file with a serialized profile, and replaces the .jar files in the EAR file with the modified versions. The Ant script is specific to the corresponding EAR file.

      This script modifies the existing EAR file.

      The script also uses a set of default names for the packages created in the database. Change the names used by the script file to ensure 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 overwrite existing packages unless you change the names. Overwritten packages cause run-time errors.

    4. Change the values of the database URL, and the database user and password properties in the generated Ant script.

      The package names, database URLs, users, and passwords are created in the script using Ant properties.

      The Ant script defines the following global properties for the...

      • Database URL - db.url

      • User - db.user

      • Password - db.password

    5. Change the values used by the Ant script and the serialized profile by specifying new values in a property file.

      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.

      The script for a particular EAR file reads properties from two files...


        The file is common to all Ant scripts generated by the EJBDeploy command. Use the file to specify global properties, such as the database user and password.


        The ear-name value is the name of the EAR file. The file is specific to the Ant script for the EAR file. If you want your Ant script to use a another file instead of, specify the property when you run the script.

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

      It also requires a user and password. The value of the port is 50000, unless you change it when you install DB2.

  3. Run the Ant script, specifying the properties target...

    ws_ant -buildfile deployedMyApplication.ear.xml properties

    This script creates the properties file, The file contains properties specifying default names for the packages corresponding to each serialized profile in the EAR file, as for example

    In this example, the EAR file contains two EJB .jar files... MyEJB1.jar and MyEJB2.jar.

  4. Edit the generated properties file to change the package names.


    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 customizer will create packages called


    You can also specify other properties in this file, such as the database URL, user, or password.

  5. Use the following db2sqljcustomize options to temporarily circumvent profile customization problems.

    These options bypass errors during a profile customization and ensure a successful customization

    -onlinecheck NO and -bindoptions "VALIDATE RUN"
    However, understand what the problems are and fix them at the appropriate time.

  6. Run the Ant script.

    The DB2 db2jcc.jar file must be on the classpath. This file should have been added to the classpath environment variable when DB2 V8 FixPak1 was installed.

  7. Specify a working directory for the Ant script.

    The script will create and delete files and sub-directories 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 the files and directories.

    Use the following command to specify a working directory

    ws_ant -Dwork.dir=tmp 
           -buildfile MyApplication.ear.xml
    The script creates and uses a directory called tmp as its working directory. If you want the script to use a different property file, set the property when you run the script

    ws_ant -Dwork.dir=tmp 
           -buildfile MyApplication.ear.xml
    The Ant script updates the original EAR file with the modified serialized profiles.

  8. If you rerun the EJBDeploy command, rerun the Ant script.

    Generate a new properties file if any of the following conditions are true...

    • You change the number of .jar files in the EAR file.

    • You change the names of the .jar files in the EAR file.

    • You change the database backend ids in any of the .jar files

  9. Install the updated EAR file in WAS.

  10. Create a JDBC provider and data source in WebSphere Application Server.

    Generate a new properties file if any of the following conditions are true...

    • You change the number of .jar files in the EAR file.

    • You change the names of the .jar files in the EAR file.

    • You change the database backend ids in any of the .jar files

    Generate a new properties file if any of the following conditions are true...

    • You change the number of .jar files in the EAR file.

    • You change the names of the .jar files in the EAR file.

    • You change the database backend IDs in any of the .jar files

  11. Install your application through the administrative console but do not redeploy the EJB. If you check the EJBDeploy box, your application is redeployed to JDBC access.

  12. Stop the server.

  13. Start the server.

    You are now ready to run your application.


See Also

Embedded SQLJ support
Data access from an enterprise entity bean
Exceptions pertaining to data access
Developing data access applications
Data access bean types