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 Structured Query Language (SQL) database requests. You
can use the DB2® for z/OS® Legacy driver with your data
access applications.
About this task
Notes: - To use SQLJ with WebSphere® Application Server for z/OS and
the DB2 for z/OS Legacy Driver, install DB2 APAR
PQ76442.
- 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 WebSphere Application Server for z/OS v6.0 using
the DB2 for z/OS Legacy driver.
Procedure
- Design your application in Rational® Application Developer
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).
- From your DB2 for z/OS installation,
copy the db2sqljclasses.zip file to a directory on your workstation,
then modify the Java Build Path of your EJB Java
archive (JAR) project to include the db2sqljclasses.zip file.
- Translate your SQLJ code according to the following
steps:
- Locate your SQLJ file, then use ASCII mode transfer to FTP it
to an HFS in your z/OS environment.
- Use the sqlj command to translate your SQLJ code into Java code. Two files are produced, one with
a .java extension and the other with an .ser extension.
sqlj -compile=false SQLJ_FILE_NAME
- 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 your workstation where the SQLJ file resides.
- Refresh the project.
- Generate deployment code for your application.
- Export your EAR file.
- Install your application
- Create a data source with the DB2 for
zOS Local JDBC Provider (RRS). When you define your JDBC
Provider and data source, the default values are sufficient for providing
SQLJ support.
- Install your application into WebSphere Application Server.
Use the data source you created in Step 1 to resolve your
resource references.
- Customize your serialized profiles When you
generate your deployment code, serialized profiles, or files with an .ser extension,
that are specific to your application, are created. These profiles must
be customized in a z/OS environment before they can
be used.
- 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.
- Use the db2profc command to customize your serialized
profiles. You can get information about the various options
associated with this command from the DB2 documentation;
however, here are the minimum requirements to customize your profile:
db2profc -pgmname=PROGRAM_NAME PROFILE_NAME
- Where:
- PROGRAM_NAME must be a valid MVS™ PDS
member name, and can be up to seven characters.
- PROFILE_NAME is the name of the serialized profile that
you want to customize. You must run db2profc one time for each profile.
- The profile customizer creates four DBRM data sets in the PDS USERNAME.DBRMLIB.DATA.
The member names of the DBRMs begin with what you specified as PROGRAM_NAME.
- Ensure that your CLASSPATH environment variable includes:
- The location of the serialized profile
- The EJB JAR file in your installed EAR directory
- Allocate a PDS to contain the DBRMs that are created. Name this
PDS USERNAME.DBRMLIB.DATA, where USERNAME is the user
who implements 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
- 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 your 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,
you can move the customized profile into the EJB JAR file, replacing
the original. It is recommended that you replace the original file.
IMPORTANT: If
you run the db2profc command from the directory where the serialized
profile exists, the profile customizer overwrites the serialized profile.
Because you need only the customized version after the profile customizer
has run, this is not a problem.
- Bind your DBRMs into a package.
Note: You must create
your database tables before binding your DBRMs. If you do not, the
bind job fails.
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 you log in as
IBMUSER, and you specify -pgmname=TESTBMP, then run the db2profc command,
the four data sets, TESTBMP1, TESTBMP2, TESTBMP3, AND TESTBMP4 are created
and placed in the PDS IBMUSER.DBRMLIB.DATA.
These data sets must
be bound into packages with isolation of UR, CS, RS, and RR. You must
run a bind for each serialized profile that you customize.
- 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 your
new plan. The default names for the JDBC packages to include are
DSNJDBC.DSNJDBC1, ..., DSNJDBC.DSNJDBC4. If your installation did
not use the default names for the JDBC packages, contact your DB2 administrator
to determine the names of the JDBC packages that you 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
/*
- Grant the appropriate authority to your 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 WebSphere Application Server
runs; for example, CBSYMSR1.
- Configure your data source to use your new plan
- From the WebSphere Application Server for z/OS Administrative Console, navigate
to your Data Source and select Custom Properties.
- Select the Custom Property planName.
- Update the value of planName with what you named
your plan when it was bound.
- Set enableSQLJ to true.
- Stop and restart your server.
- Run your application.