Preparing Programs for Static Execution

In order to execute Natural programs that contain Db2 statements statically with NDZ, you must perform the static generation steps for each program you want to execute. All necessary information about static program preparation and execution with NDZ is described in this section.

• About Preparation for Static Execution with NDZ

• Static Preparation Steps

• Unix Shell Scripts for Static Preparation

• Comparison Between NDZ and NDB Static Preparation

About Preparation for Static Execution with NDZ

Compatibility with Natural for Db2 (NDB)

The program static preparation for Natural for Db2 for zIIP (NDZ) is independent from the program static preparation for Natural for Db2 (NDB). Natural programs already prepared for static execution with NDB are not impacted in any way when NDZ is installed. Those programs will continue to execute statically with NDB. However, when a Natural session is successfully initialized with NDZ, they will be executed dynamically until they are also prepared for static execution with NDZ. The Natural programs subject to NDZ static generation and NDB static generation don't have to be identical. However, one Natural program can be in only one SQLJ profile and/or in one NDB DBRM/PACKAGE. The following graphic shows an NDZ static generation done for Natural programs PROG1 and PROG2 contained in the SQLJ profile and package PROF1 and an NDB static generation done for Natural programs PROG2 and PROG4 contained in package PACK1.

About NDZ and the SQLJ Standard

NDZ uses Java to connect to Db2 and execute SQL statements. The standard for embedding SQL statements in Java programs is called SQLJ and the Java programs containing embedded SQL are called SQLJ programs. NDZ provides a SQLJ Generator, which translates temporary assembler programs generated by the NDB command CMD CREATE into SQLJ. The NDZ SQLJ Generator is described in Step 2 - NDZ SLQJ Generator.

About the SQLJ Translator and Serialized Profiles

NDZ requires SQLJ Serialized Profiles to execute Db2 SQL statements from Natural programs statically. SQLJ serialized profiles are files containing the description of the SQL statements and cursors from a particular program. NDZ uses SQLJ serialized profiles at runtime, therefore they are mandatory for Db2 static execution with Natural and NDZ. They are generated by the SQLJ translator. The SQLJ translator is provided by IBM.

The SQLJ translator receives an SQLJ program as input. The usage of the IBM SQLJ Translator in the context of NDZ is described in Step 3 - IBM SLQJ Translator. For more information about the SQLJ translator, please refer to the IBM Db2 Java documentation for SQLJ.

Serialized Profile Customization and Binding

An SQLJ program can run statically only when it has SQLJ serialized profiles that are customized and bound to Db2. NDZ provides its own binder and customizer program which calls the IBM SQLJ customizer.

The NDZ binder uses the connection parameters specified in the NDZ configuration file db2.properties to execute the customization and optionally the bind process in Db2.

If you want, you can set the NDZ Binder and customizer to skip the bind process and instead generate a DBRM file to be bound to Db2 in subsequent step. The NDZ Binder and Customizer is described in Step 5 - NDZ SQLJ Binder / Customizer.

Static Preparation Steps

To execute Natural programs containing Db2 statements statically with NDZ, you must always have a SQLJ serialized profile. The SQLJ profile is used at runtime and, optionally, in the bind process. If you want, you can generate a DBRM file from the SQLJ serialized profile and bind that file to Db2 using the Db2 BIND PACKAGE command. This is especially useful when the user specified in NDZ does not have the required permissions to execute the bind in Db2. In this case, the bind can be done with the permissions of the executor of the BIND PACKAGE command, for example, the user that submits the batch job containing the command. See the JCL in step 6 for an example of that.

• Step 1 – CMD CREATE

• Step 2 – NDZ SQLJ Generator

• Step 3 – IBM SQLJ Translator

• Step 4 – CMD MODIFYZ

• Step 5 – NDZ SQLJ Binder / Customizer

• Step 6 – Db2 Binder (Optional)

Step 1 – CMD CREATE

Run the CMD CREATE command, selecting the Natural programs you want to prepare for static execution with NDZ.

The CMD CREATE statement generates a temporary assembler program with the SQL statements contained in the selected Natural programs. This step is identical for NDZ and NDB. For detailed information about the CMD CREATE command, please refer to Natural for Mainframes > Database Management System Interfaces > Natural for Db2 > Dynamic and Static SQL Support > Generation Procedure: CMD CREATE Command.

JCL Example:

//GENERATE EXEC PGM=natural batch nucleus
//CMPRMIN  DD
    Natural parameters
//*
//STEPLIB  DD DSN=nucleus load library,DISP=SHR
//SYSUDUMP DD SYSOUT=X
//*********  OUTPUT DECKS
//CMWKF01  DD DSN=&&TMP1,UNIT=SYSDA,DCB=(DSORG=PS,RECFM=FB,LRECL=80,
//         BLKSIZE=3120),DISP=(,PASS),SPACE=(TRK,(5,5))
//CMWKF02  DD DSN=&&TMP2,UNIT=SYSDA,DCB=(DSORG=PS,RECFM=FB,LRECL=80,
//         BLKSIZE=3120),DISP=(,PASS),SPACE=(TRK,(5,5))
//CMWKF03  DD DSN=&&TMP3,UNIT=SYSDA,DCB=(DSORG=PS,RECFM=FB,LRECL=80,
//         BLKSIZE=3120),DISP=(,PASS),SPACE=(TRK,(5,5))
//CMWKF04  DD DSN=&&TMP4,UNIT=SYSDA,DCB=(DSORG=PS,RECFM=FB,LRECL=80,
//         BLKSIZE=3120),DISP=(,PASS),SPACE=(TRK,(5,5))
//CMWKF05  DD DSN=&&TMP5,UNIT=SYSDA,DCB=(DSORG=PS,RECFM=FB,LRECL=80,
//         BLKSIZE=3120),DISP=(,PASS),SPACE=(TRK,(5,5))
//CMWKF06  DD DSN=&&TMP6,UNIT=SYSDA,DCB=(DSORG=PS,RECFM=FB,LRECL=80,
//         BLKSIZE=3120),DISP=(,PASS),SPACE=(TRK,(5,5))
//CMPRINT  DD SYSOUT=X
//CMSYNIN  DD *,SYMBOLS=JCLONLY
LOGON SYSDB2
CMD CRE DBRM PROFILE NAME USING INPUT DATA WITH XREF YES|NO FS ON/OFF
LIBRARY,PROGRAM
LIBRARY,PROGRAM
.
FIN

Step 2 – NDZ SQLJ Generator

Execute the com.softwareag.ndz.sqljgen.SQLJGenerator Java class through the IBM JZOS Batch Launcher using the assembler program that you generated in step 1 as input.

NDZ provides the Java class com.softwareag.ndz.sqljgen.SQLJGenerator to translate temporary assembler programs generated in step 1 into SQLJ programs. The Java class must be executed through the IBM JZOS Batch Launcher. The IBM JZOS Batch Launcher is available with any Java version for z/OS supported by NDZ.

DD Names

JCL Example:

//GENSQLJ  EXEC PROC=JVMPRC86|11|17|xx,REGSIZE=1024M,
//         JAVACLS='com.softwareag.ndz.sqljgen.SQLJGenerator'
//INPUT    DD DSN=&&TMP6,DISP=(OLD,PASS)
//OUTPUT   DD PATH='programs directory/program name.sqlj',
//         PATHDISP=(KEEP,DELETE),
//         PATHOPTS=(OCREAT,ORDWR),
//         PATHMODE=(SIRUSR,SIWUSR,
//             SIRGRP,SIWGRP,
//             SIROTH,SIWOTH),
//         FILEDATA=TEXT
//MAINARGS DD *,SYMBOLS=JCLONLY
&PROFILE
//STDENV   DD *,SYMBOLS=JCLONLY
. /etc/profile
. ndz installation path/bin/setenv.sh

/*

Step 3 – IBM SQLJ Translator

Execute the sqlj.tools.Sqlj Java class through the IBM JZOS Batch Launcher using the SQL program translated in step 2 as input.

The Java class sqlj.tools.Sqlj is provided with the IBM JDBC/SQLJ driver for Db2 for z/OS. The IBM SQLJ Translator has a similar function as a Db2 pre-compiler for Assembler or COBOL. The translator allows the embedded SQL statements in a program to be executed statically with Db2.

The main difference is that the SQLJ Translator generates both a serialized profile and a modified version of the input program as output. Another important difference is that the IBM SQLJ Translator does not generate a DBRM file. For details about the IBM SQLJ Translator, please refer to the IBM Db2 Application Programming and Reference for Java documentation.

JCL Example:

//GENPROF  EXEC PROC=JVMPRC86|11|17|xx,REGSIZE=1024M,
//         JAVACLS='sqlj.tools.Sqlj'
//MAINARGS DD *,SYMBOLS=JCLONLY
-compile=true -d=profiles directory
programs directory/program name.sqlj
//STDENV   DD *,SYMBOLS=JCLONLY
. /etc/profile
. ndz installation path/bin/setenv.sh
/*

Step 4 – CMD MODIFYZ

Run the CMD MODIFYZ command to update the Natural objects from step 1 with the information generated in step 3.

The modification procedure writes the SQLJ serialized profile name and the sequence numbers of the SQL statements to the Natural objects. For more details about the CMD MODIFYZ command, please refer to Natural for Mainframes > Database Management System Interfaces > Natural for Db2 > Dynamic and Static SQL Support > Modification Procedure: CMD MODIFYZ Command.

JCL Example:

//MODIFYZ  EXEC PGM=natural batch nucleus,
//         REGION=3000K
//CMPRMIN  DD  *
    Natural parameters
/*
//*
//STEPLIB  DD DSN=nucleus load library,DISP=SHR
//*********  OUTPUT DECKS
//CMWKF01  DD  DSN=&&TMP6,DISP=(OLD,DELETE)
//CMWKF02  DD DSN=&&TMP22,UNIT=SYSDA,DCB=(DSORG=PS,RECFM=FB,LRECL=80,
//         BLKSIZE=3120),DISP=(,PASS),SPACE=(TRK,(5,5))
//CMPRINT  DD SYSOUT=X
//CMSYNIN  DD *,SYMBOLS=JCLONLY
LOGON SYSDB2
CMD MODIFYZ XREF YES|NO
FIN

Step 5 – NDZ SQLJ Binder / Customizer

Run the Java class com.softwareag.ndz.sqljbinder.Main through the IBM JZOS Batch Launcher to customize the serialized profile generated in step 3 and, optionally, execute the Db2 bind.

NDZ provides the Java class com.softwareag.ndz.sqljbinder.Main with the following options:

NDZ SQLJ BInder/ Customizer Options

If the option -automaticbind is set to YES, the class binds the serialized profile to Db2 using the connection parameters specified in the NDZ configuration file db2.properties, unless the properties -url, -user and -password are specified. In any case, the user must have the SYSADM or DBADM privileges. In addition, if the package doesn't exist, the user must have the BINDADD privilege as well as either CREATEIN or PACKADM authority on the collection, or all collections. If the package exists, the user must have the BIND privilege on the package.

If the option -automaticbind is set to NO and the options -genDBRM and -DBRMDir are specified, the class does not execute the bind of the serialized profile to Db2. Instead, the class generates a DBRM file in the directory specified in the option -DBRMDir. You can copy the DBRM file to a dataset and bind it to Db2 by executing the Db2 BIND PACKAGE command, the same way as NDB. For more information about the Db2 BIND PACKAGE command, please refer to the IBM Db2 for z/OS SQL Reference.

When the option -onlinecheck is set to YES, the user specified in the properties file db2.properties, or the user specified in the parameter -user, must have access to execute the statements contained in the SQLJ program that is being customized.

JCL Example:

JCL Example
//CUSTOM   EXEC PROC= JVMPRC86|11|17|xx,REGSIZE=1024M,
//         JAVACLS='com.softwareag.ndz.sqljbinder.Main'
//MAINARGS DD *
-profile profile name
-onlinecheck YES|NO 
-staticpositioned YES|NO
-collection collection
-bindoptions ISOLATION CS|RS|RR|UR|NC
-automaticbind YES|NO
-genDBRM -DBRMDir DBRM directory
//STDENV   DD *,SYMBOLS=JCLONLY
. /etc/profile
. ndz installation path/bin/setenv.sh
/*

Step 6 – Db2 Binder (Optional)

Bind the DBRM file generated in step 5 to Db2 by using the Db2 BIND PACKAGE command.

This step must only be completed when both -automaticbind is set to NO and -genDBRM is specified in step 5. For more information about the Db2 BIND PACKAGE command, please refer to the IBM Db2 documentation.

JCL Example:

//BIND     EXEC PGM=IKJEFT01,DYNAMNBR=20,REGION=4096K
//STEPLIB  DD DSN=Db2 high level qualifier.SDSNLOAD,DISP=SHR
//DBRMLIB  DD DSN=DBRM Dataset,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN  DD *,SYMBOLS=JCLONLY
  DSN SYSTEM(DB2 SSID)
  BIND PACKAGE(COLLECTION)  -
  MEM(DBRM NAME)            -
  Other bind options -
  END
/*

Unix Shell Scripts for Static Preparation

Some of the static preparation steps can be done using z/OS Unix System Services shell scripts. NDZ provides the following scripts in the bin subdirectory:

• ndz-generate-profile.sh – Execute Step 3 - IBM SQLJ Translator

• ndz-binder.sh – Execute Step 5 - NDZ Binder / Customizer

• ndz-print-profile.sh – SQLJ Profile Printer

ndz-generate-profile.sh – Execute Step 3 - IBM SQLJ Translator

Syntax:

ndz-generate-profile.sh  program name

Where program name is the SQLJ program name.

ndz-binder.sh – Execute Step 5 - NDZ Binder / Customizer

Syntax:

ndz-binder.sh  parameters

Where parameters are the NDZ Binder / Customizer parameters.

ndz-print-profile.sh – SQLJ Profile Printer

This script executes a IBM Db2 utility to print a SQLJ profile. For more details, please refer to IBM Db2 documentation for Java / SQLJ.

Syntax:

ndz-print-profile.sh  profile name

where profile name is an SQLJ profile previously created and stored in the NDZ static profiles directory defined in the configuration file ndz.properties.

Comparison Between NDZ and NDB Static Preparation

The following table compares the steps you must perform to prepare a program for static execution with NDZ and NDB.