SQL Batch utility
Use the SQL Batch utility to run multiple SQL statements by using JCL to invoke a batch program.
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.
Restrictions
Currently, no restrictions are documented for the SQL Batch utility.
Prerequisites
- The IBM Java for z/OS (JZOS) Batch Launcher is installed.
- The IMS open data access solution is installed.
- The IMS catalog is installed and configured.
Requirements
- 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
- Inline with the JCL
- In a data set that the JCL references
- In a file on UNIX System Services that the JCL references
Return codes
- Code
- Meaning
- 0
- No errors were detected.
- 11
- The connection to IMS Connect failed. Verify that the connection parameters are correct.
- 12
- The SQL statement could not be run, and all work was rolled back to the previous commit point. Verify that the SQL statement is valid.
- 13
- The commit did not complete successfully.
- 14
- The rollback did not complete successfully.
- 15
- The connection was not cleaned up properly.
- 16
- An invalid SQL statement was specified. Verify that the input file contains only valid SQL statements.
- 100
- The Java main class was not found or the main method threw an exception.
- 101
- A configuration or setup error occurred. For more information, see the SYSOUT messages.
- 102
- A system or internal error occurred. For more information, see the SYSOUT messages.
For more information about return codes 11 - 16, see the output messages in STDOUT and STDERR.
The JZOS Batch Launcher issues return codes 0, 100, 101, and 102.JCL specifications
The SQL Batch utility is run through a standard z/OS job that uses JZOS Batch Launcher to start the utility. The following JCL statements are required:- 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.