SQL Batch utility

The SQL Batch utility is included with the IMS Universal JDBC driver and uses type-4 connections to submit the SQL statements. The IBM® Java™ for z/OS® (JZOS) Batch Launcher must be used to run the SQL Batch utility.

Figure 1. SQL Batch utility input and output data sets

Restrictions

Currently, no restrictions are documented for the SQL Batch utility.

Prerequisites

Requirements

You can enable the SQL Batch utility to generate RACF® PassTickets to authenticate users of JDBC applications to access IMS DB. PassTickets are an alternative to RACF passwords and password phrases and provide better security because PassTickets remove the need to send passwords and password phrases across the network in clear text. If you require the SQL Batch utility to generate RACF PassTickets, ensure that all of the following conditions are met:

• Both the IRRRacf.jar and ibmjzos.jar files are in the job's class path.
• In the applName URL property of the DriverManager.getConnection method, the 1- to 8-character application name that is defined to RACF in the PTKTDATA class for DRDA client access to IMS DB is specified.
• The following values are the same as each other:
  • The value of the applName URL property of the DriverManager.getConnection method.
  • The value of the APPL= parameter of the ODACCESS statement, which is in the HWSCFGxx member of the IMS PROCLIB data set.
• On the JOB statement of the JCL for the SQL Batch utility, the z/OS user ID that is associated with the job is specified.

Recommendations

Currently, no recommendations are documented for the SQL Batch utility.

Input and output

Return codes

For more information about return codes 11 - 16, see the output messages in STDOUT and STDERR.

JCL specifications

• A JOB statement that you define to meet the specifications of your installation.
• A SET P1 statement
• An EXEC statement
• DD statements that define inputs and outputs

The SET P1 statement must be in the form:

// SET P1='com.ibm.ims.jdbc.batch.BatchUtil'

In the SET P1 statement, com.ibm.ims.jdbc.batch.BatchUtil is the main SQL Batch utility class. This utility class is in the IMS JDBC driver (imsudb.jar).

EXEC statement

//JAVAJVM EXEC PGM=JVMLDMxx,REGION=0M,                         
//   PARM='/ &P1'                       

The variable JVMLDMxx must be replaced with the version of the IBM SDK for z/OS for the JZOS Batch Launcher. For example, for IBM 64-bit SDK, Version 7, specify JVMLDM76.

DD statements

STEPLIB DD

Points to the JZOS.LOADLIB, which is the STEPLIB for the JVMLDM module.

The STEPLIB DD statement is required.

SYSPRINT DD

Defines the message data set for the system job log. The data set can be on a printer, a tape, or a direct-access device, or be routed through the output stream.

This DD statement is required.

SYSOUT DD

Defines the message output data set for error information for the system job log. The data set can be on a printer, a tape, or a direct-access device, or be routed through the output stream.

This DD statement is required.

STDOUT DD

Defines the message output data set for the Java jobs. The data set contains the output from Java System.out. The data set can be on a printer, a tape, or a direct-access device, or be routed through the output stream. Optionally, you can specify a file on UNIX System Services in this DD statement.

This DD statement is required.

STDERR DD

Defines the message output data set for error information for the Java jobs. The data set contains the output from Java System.err. The data set can be on a printer, a tape, or a direct-access device, or be routed through the output stream.

This DD statement is required.

CEEDUMP DD

Defines the Language Environment® runtime options.

MAINARGS DD

Defines the input arguments to the main Java class.

IMSSQL DD

Defines the SQL statements to be run. You can specify the SQL statements inline, in a data set, or in a file on UNIX System Services.

Example 1: Specifying the statements inline

//IMSSQL DD *
CONNECT jdbc:ims://myConnectServer:myPort/DFSCP001;
CREATE DATABASE myDB...;
CREATE TABLE myTable ...;
COMMIT;
DISCONNECT;

Example 2: Specifying a user name and password

The following example is the same as the preceding example, except that a user name and password are included on the CONNECT statement for security checking. In the example, both semicolons are required after the password.

The syntax of the CONNECT statement conforms to the syntax that is defined by the IMS Universal JDBC driver for a type-4 connection. For more information, see Connecting to an IMS database by using the JDBC DriverManager interface.

//IMSSQL DD *
CONNECT jdbc:ims://myConnectServer:myPort/DFSCP001:user=myUserName;password=myPassword;;
CREATE DATABASE myDB...;
CREATE TABLE myTable ...;
COMMIT;
DISCONNECT;

Example 3: Specifying the statements in a data set

//IMSSQL DD DISP=SHR,
//       DSN=myPDS(myScript)

Example 4. Specifying the statements in a file on UNIX System Services

//IMSSQL DD PATH='/stdin-file-pathname',PATHOPTS=(ORDONLY)                

This DD statement is required.

STDENV DD

Defines the Java environment variables. In this section, ensure that the following statement points to your JDK path:

export JAVA_HOME=myJavaHomePath

Also, ensure that the following statement points to the path for the IMS Universal JDBC driver, which includes the SQL Batch utility:

CLASSPATH="$CLASSPATH":myLibPath/imsudb.jar

This DD statement required.