[Version 5.0.1 and later]Using embedded Structured Query Language in Java (SQLJ) to develop applications for WebSphere Application Server for z/OS v5.0 using the DB2 Universal driver

Why and when to perform this task

Following are the steps required to develop applications with SQLJ that run on WebSphere Application Server for z/OS v5.0 using the DB2 Universal JDBC Driver.

Steps for this task

  1. Create your application in WebSphere Studio Application Developer.
    1. Set up non-CMP components to use SQLJ
      1. Design your application in WebSphere Studio according to your requirements, using SQLJ when necessary.

        For example, if you develop a bean called Test that uses BMP, code TestBean.sqlj (instead of TestBean.java).

      2. From your DB2 for z/OS installation, copy the sqlj.zip file to a directory on your workstation, then modify the Java build path of your EJB jar project to include sqlj.zip.
      3. Choose one of the two following ways to translate your SQLJ code, depending on your version of WebSphere Studio.
        1. For WebSphere Studio Application Developer 5.1 or later, set up your WebSphere Studio project to automatically translate SQLJ as follows:
          1. In the Project Navigator, right-click your EJB jar project, then select Add SQLJ Support....
          2. Check 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.
        2. For versions earlier than WebSphere Studio Application Developer 5.1, translate your SQLJ code manually as follows:
          1. Locate your SQLJ file, then use ASCII mode transfer to FTP it to an HFS in your z/OS environment.
          2. Use the sqlj command to translate your SQLJ code into Java code. This produces two files, one with a .java extension and the other with a .ser extension.
            sqlj -compile=false SQLJ_FILE_NAME

            See the DB2 documentation for a full list of available syntax options.

          3. Use ASCII mode transfer for the .java file and BINARY mode transfer for the .ser file to FTP these files back to the directory on your workstation where the SQLJ file resides.
          4. Refresh the project.
      4. Generate deployment code for your application.
      5. Export your EAR file.
    2. Set up CMP components to use SQLJ (using CMP beans)
      1. Design your application however you like, then create a top-down mapping to a DB2 Universal Database for z/OS (Version 7 and up).
      2. From your DB2 for z/OS installation, copy the sqlj.zip file to a directory on your workstation, then modify the Java build path of your EJB jar project to include this file.
      3. Edit your Map.mapxmi file to add SQLJ support.
        1. From the Project Navigator, select EJB_JAR_PROJECT_NAME > SOURCE_FOLDER > META-INF > backends > DB2UDBOS390_V7_1. Double-click Map.mapxmi to open it in the Mapping editor.
        2. On the Overview page, highlight the name of your EJB jar project in the Enterprise Beans column.

          Note: You must highlight the name of the EJB jar project, not the name of one of the Enterprise Beans listed beneath it.

        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 Map.mapxmi.
      4. Generate deployment code for your application.
      5. Export your EAR file.
  2. Install your application into WebSphere. Resolve the appropriate resource references with a Data Source created under the DB2 Universal JDBC Driver Provider.
  3. 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.

    Note: 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 EJB jar file in your installed EAR directory.
    2. Add the following locations to your CLASSPATH environment variable:

      • The directory containing your serialized profiles
      • The EJB jar file in your installed EAR directory

    3. Use the db2sqljcustomize command to customize your serialized profiles. Here is the recommended syntax for running db2sqljcustomize. See the DB2 documentation for the full list of available options.
      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 PDS member name, up to 7 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 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 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.
    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 classpath, and they must exist ahead of the uncustomized serialized profiles in your EJB jar file. We recommend using the Java jar command to replace the serialized profiles in your EJB jar file with the customized profiles.

      Note: If you decide to replace the serialized profiles in your EJB jar file, make sure that you maintain the directory structure in which the profiles exist.

  4. Stop and restart your server.
  5. Run your application.

Related concepts
Embedded Structured Query language in Java (SQLJ) support



Searchable topic ID:   sqljdb2zosuniv
Last updated: Jun 21, 2007 9:56:50 PM CDT    WebSphere Application Server for z/OS, Version 5.0.2
http://publib.boulder.ibm.com/infocenter/wasinfo/index.jsp?topic=/com.ibm.websphere.zseries.doc/info/zseries/ae/tdat_sqljdb2zosuniv.html

Library | Support | Terms of Use | Feedback