+

Search Tips   |   Advanced Search

Use embedded Structured Query Language in Java (SQLJ) with the DB2 for z/OS Legacy driver


Structured Query Language in Java(SQLJ) is a set of programming extensions that enable a programmer, using the Java programming language, to embed statements that provide SQL (Structured Query Language) database requests. Use the DB2 for z/OS Legacy driver with the data access applications. Notes:

  1. To use SQLJ with WAS for z/OS and the DB2 for z/OS Legacy Driver, install DB2 APAR PQ76442.

  2. Container Managed Persistence (CMP) beans generated using SQLJ are not supported by the DB2 for z/OS Legacy Driver. Use the DB2 Universal Driver for CMPs that are generated using SQLJ.

Following are the steps required to develop applications with SQLJ that run on WAS for z/OS v6.0 using the DB2 for z/OS Legacy driver.

 

  1. Design the application in Rational Application Developer (RAD) according to the requirements, using SQLJ when necessary. For example, if we develop a bean called Test that uses BMP, code TestBean.sqlj (instead of TestBean.java).

    1. From the DB2 for z/OS installation, copy the db2sqljclasses.zip file to a directory on the workstation, then modify the Java Build Path of the EJB jar project to include db2sqljclasses.zip.

    2. Translate the SQLJ code according to the following steps:

      1. Locate the SQLJ file, then use ASCII mode transfer to FTP it to an HFS in the z/OS environment.

      2. Use the sqlj command to translate the 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

      3. Use ASCII mode transfer for the .java file and BINARY mode transfer for the .ser file to move these files back to the directory on the workstation where the SQLJ file resides.

      4. Refresh the project.

    3. Generate deployment code for the application.

    4. Export the EAR file.

  2. Install the application

    1. Create a data source using the DB2 for zOS Local JDBC Provider (RRS).

      When you define the JDBC Provider and DataSource, the default values are sufficient for providing SQLJ support .

    2. Install the application into WAS.

      Use the data source you created in Step 1 to resolve the resource references.

  3. Customize the serialized profiles When you generate your deployment code, serialized profiles (files with a .ser extension) that are specific to the application are created. These profiles must be customized in a z/OS environment before they can be used.

    1. Use binary transfer to transfer the serialized profiles to the z/OS environment on which you installed the application. Alternatively, use the Java jar command to extract the serialized profiles from the EJB jar file in the installed EAR directory.

    2. Use the db2profc command to customize the serialized profiles.

      We can get information on the various options associated with this command from the DB2 documentation; however, here are the minimum requirements to customize the profile: 

      db2profc -pgmname=PROGRAM_NAME PROFILE_NAME

      • Where:

        • PROGRAM_NAME must be a valid MVSPDS member name, and can be up to seven characters.

        • PROFILE_NAME is the name of the serialized profile that you want to customize. Run db2profc once for each profile.

      • The profile customizer creates four DBRM datasets in the PDS USERNAME.DBRMLIB.DATA. The member names of the DBRMs begin with what you specified as PROGRAM_NAME.

      • Verify the CLASSPATH environment variable includes:

      • Allocate a PDS to contain the DBRMs that are created. Name this PDS USERNAME.DBRMLIB.DATA, where USERNAME is the user who will execute the db2profc command.

        The following fields are an example: 

        Space units=TRACK Primary quantity=15 Secondary quantity=5 Directory blocks=10 Record format=FB Record length=80 Block size=27920 Data set name type=PDS

    3. Place the existing serialized profiles, which are now customized, into a location that is part of the application classpath and that is ahead of the serialized profiles that exist in the EJB jar file.

      The output of the DB2 profile customizer and the input file have the same name. Move the output file ahead of the original serialized profile in the classpath. Alternatively, we can move the customized profile into the EJB jar file, replacing the original. We recommend that you replace the original file.

      IMPORTANT: If we run the db2profc command from the directory where the serialized profile exists, the profile customizer overwrites the serialized profile. Because we need only the customized version after the profile customizer has run, this is not a problem.

    4. Bind the DBRMs into a package.

      You must create your database tables before binding the DBRMs. If we do not, the bind job will fail.

      The db2profc customization command creates a series of DBRMs that must be bound into packages. For each customized profile, four DBRMs are created. These DBRMs:

      • Are located in USERNAME.DBRMLIB.DATA

      • All have names that begin with what you specified as PROGRAM_NAME

      • Are numbered from 1-through-4

        For example, if we log in as IBMUSER, and you specify -pgmname=TESTBMP, then run the db2profc command, the four datasets, TESTBMP1, TESTBMP2, TESTBMP3, AND TESTBMP4 are created and placed in the PDS IBMUSER.DBRMLIB.DATA.

        These datasets must be bound into packages with isolation of UR, CS, RS, and RR, respectively. Run a bind for each serialized profile that you customize.

    5. After you bind all of the DBRMs into packages, bind the packages into a plan. Name the plan whatever you like.

      IMPORTANT: You must also include the JDBC packages in the package list (PKLIST) of the new plan. The default names for the JDBC packages to include are DSNJDBC.DSNJDBC1, ..., DSNJDBC.DSNJDBC4. If the installation did not use the default names for the JDBC packages, contact the DB2 administrator to determine the names of the JDBC packages that we need to include. Following is a sample job used to bind a new plan.

      • One serialized profile was created while logged on as IBMUSER.

      • -pgmname=TESTBMP was specified to run db2profc.

      • The new plan is named SQLJPLAN. 

      //BBOOLS  JOB (516B,1025),'IBMUSER',MSGCLASS=H,CLASS=A,PRTY=14, 
//         NOTIFY=&SYSUID,TIME=1440,USER=IBMUSER,PASSWORD=IBMUSER, 
//         MSGLEVEL=(1,1)

//********************************************************************

//BINDOLS  EXEC PGM=IKJEFT01,DYNAMNBR=20

//DBRMLIB  DD  DSN=IBMUSER.DBRMLIB.DATA,DISP=SHR

//*        DD  DSN=MVSDSOM.DB2710.SDSNDBRM,DISP=SHR

//SYSTSPRT DD  SYSOUT=*

//SYSPRINT DD  SYSOUT=*

//SYSUDUMP DD  SYSOUT=*

//SYSTSIN  DD  *


       DSN SYSTEM(DB2)


       BIND -
     PACKAGE(TESTBMP) -
     QUALIFIER(IBMUSER) -
     MEMBER(TESTBMP1) -
     VALIDATE(BIND) -
     ISOLATION(UR) -
     SQLERROR(NOPACKAGE) -


       BIND -
     PACKAGE(TESTBMP) -
     QUALIFIER(IBMUSER) -
     MEMBER(TESTBMP2) -
     VALIDATE(BIND) -
     ISOLATION(CS) -
     SQLERROR(NOPACKAGE) -


       BIND -
     PACKAGE(TESTBMP) -
     QUALIFIER(IBMUSER) -
     MEMBER(TESTBMP3) -
     VALIDATE(BIND) -
     ISOLATION(RS) -
     SQLERROR(NOPACKAGE) -


       BIND -
     PACKAGE(TESTBMP) -
     QUALIFIER(IBMUSER) -
     MEMBER(TESTBMP4) -
     VALIDATE(BIND) -
     ISOLATION(RR) -
     SQLERROR(NOPACKAGE) -



       BIND PLAN(SQLJPLAN)                 -
  QUALIFIER(IBMUSER)                 -
  PKLIST(TESTBMP.*                   -
         DSNJDBC.*                 ) -
  ACTION(REPLACE) RETAIN             -
  VALIDATE(BIND)

       END
/*

    6. Grant the proper authority to the new plan. Use an interface to DB2, such as SPUFI, to grant the authority. Issue this command: 

      GRANT EXECUTE ON PLAN PLANNAME TO APPSERVERID
      Where:

      • PLANNAME is the name of the plan that you bound.

      • APPSERVERID is the ID under which WAS runs; for example, CBSYMSR1.

  4. Set the data source to use the new plan

    1. From the WAS for z/OS Administrative Console, navigate to the Data Source and select Custom Properties.

    2. Select the Custom Property planName.

    3. Update the value of planName with what you named the plan when it was bound.

    4. Set enableSQLJ to true.

  5. Stop and restart the server.

  6. Run the application.

 

Related tasks


Deploy Structured Query Language in Java (SQLJ) applications