This topic applies only on the z/OS operating system.

Using embedded SQLJ with the DB2 JDBC Universal Driver

You can embed Structured Query Language in Java (SQLJ) statements in your applications to maximize the efficiency of transactions with your backend data. This article lays out the process for configuring such application code before and after deployment on a WebSphere Application Server for z/OS installation that uses the DB2 JDBC Universal Driver for data access.

Before you begin

Create your application in Rational Application Developer.

Procedure

  1. Set up non-CMP components to use SQLJ.
    1. From your DB2 for z/OS installation, copy the sqlj.zip file to a directory on your workstation. Modify the Java build path of your enterprise bean Java archive (JAR) file project to include the sqlj.zip file.
    2. Set up your Rational Application Developer project to automatically translate SQLJ as follows:
      1. In the Project Navigator, right-click your JAR project, and select Add SQLJ Support....
      2. Select the boxes for the applications for which you want SQLJ support.
      3. In the SQLJ JAR file field, type the fully qualified path to the sqlj.zip file that you previously copied to your workstation.
      4. Click Finish.
    3. Generate the deployment code for your application.
    4. Export your enterprise archive (EAR) file.
    5. Proceed to the following step, Install your application onto WebSphere Application Server.
  2. Set up CMP components to use SQLJ (using CMP beans).
    1. Create a top-down mapping to a DB2 Universal Database for z/OS (Version 7 and later).
    2. From your DB2 for z/OS installation, copy the sqlj.zip file to a directory on your workstation. Modify the Java build path of your enterprise bean JAR project to include this file.
    3. Edit your Map.mapxmi file to add SQLJ support, using the following procedure:
      1. From the Project Navigator, click EJB_JAR_PROJECT_NAME > SOURCE_FOLDER > META-INF > backends > DB2UDBOS390_V7_1. Double-click Map.mapxmi to open this file in the Mapping editor.
      2. On the Overview page, highlight the name of your JAR project in the Enterprise Beans column. You must highlight the name of the JAR project, not the name of one of the enterprise beans listed.
      3. On the Properties page, expand SQLJ.
      4. Set Is using SQLJ? to True.
      5. Set Translator Module to the fully qualified path of the sqlj.zip file on your workstation.
      6. Save the Map.mapxmi file.
    4. Generate the deployment code for your application.
    5. Export your EAR file.
  3. Install your application onto WebSphere Application Server. Resolve the appropriate resource references with a data source created under DB2 Universal JDBC Driver provider.
  4. Customize your serialized profiles
    When you generate your deployment code, serialized profiles (files with a .ser extension) that are specific to your application are created. These profiles exist in the same directory as your SQLJ files, and must be customized in a z/OS environment before they can be used.
    Important: You must create your tables in DB2 before you customize the serialized profiles.
    1. Use binary transfer to transfer the serialized profiles to the z/OS environment on which you installed your application. Alternatively, use the Java jar command to extract the serialized profiles from the JAR file in your installed EAR directory.
    2. Add the following locations to your CLASSPATH environment variable:
      • The directory containing your serialized profiles
      • The JAR file in your installed EAR directory
    3. Use the db2sqljcustomize command to customize your serialized profiles. Here is the recommended syntax for running the db2sqljcustomize command:
      db2sqljcustomize -url JDBC_URL -user USER_NAME
       -password PASSWORD [-rootpkgname PACKAGE_NAME]
       SERIALIZED_PROFILE1 SERIALIZED_PROFILE2 ...
      where:
      • JDBC_URL is the JDBC URL that is 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. If you do customize more than one serialized profile with the same command, you must supply a value for the -rootpkgname parameter.
      Optionally, you can use the SQLJ customize command to enable context caching for the data source connections of your application. This option can improve overall application performance. (See the DB2 documentation for a complete list of options that can be used with the SQLJ customize command.)
      • DB2 V8.1 Fix Pack 6 provides the new caching option, called db2optimize. You can run this option with the SQLJ customize command if your application uses the explicit connection context instead of the default context.
        Requirement: SQLJ context caching support requires Version 2.2 of the DB2 Universal JDBC Driver, with APAR PQ87786 applied.
      • If you want to enable context caching for an application or a BMP bean that caches connections across transaction boundaries, you cannot use shareable connections. You must use the get/use/close pattern of connection usage when you invoke the db2optimize option. Otherwise, an object closed exception occurs.
    4. 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 uncustomized serialized profiles, the customized versions overwrite the uncustomized versions because they both have the same names.

      The customized files must be placed in a location that is part of the application class path, and they must exist ahead of the uncustomized serialized profiles in your JAR file. Use the Java jar command to replace the serialized profiles in your JAR file with the customized profiles.
      Important: If you decide to replace the serialized profiles in your JAR file, make sure that you maintain the directory structure in which the profiles exist.
  5. Stop and restart your server.

Results

You are now ready to run your application.



In this information ...


IBM Redbooks, demos, education, and more

(Index)

Use IBM Suggests to retrieve related content from ibm.com and beyond, identified for your convenience.

This feature requires Internet access.

Task topic    

Terms of Use | Feedback

Last updated: Aug 29, 2010 9:31:45 PM CDT
http://www14.software.ibm.com/webapp/wsbroker/redirect?version=vela&product=was-nd-mp&topic=sqljdb2zosuniv
File name: tdat_sqljdb2zosuniv.html