SQLTP2 Tool - Executing Dynamic SQL Statements

The SQLTP2 tool is equivalent to DB2 DSNTEP2. It is used to execute dynamic SQL statements embedded in JCL, given an ODBC database connection that is established by an XAR definition in Enterprise Server. In the database migration process, we use it to execute DDL against a DBMS to create and modify schema objects.

Important: Because of referential integrity implementation differences between mainframe DB2 and other DBMSs, we recommend that you include an initial DSNTEP2 step to create your tables. After loading the data, you can run additional DSNTEP2 steps to create your indexes and constraints.

Executing SQLTP2

To use SQLTP2, directly submit JCL using the JES CONTROL feature in Enterprise Server. The JCL syntax must be z/OS DB2 compatible.

When you run the MBDT Configuration utility, you have two options for processing SQL statements. These options are specified as the database type, which can be set to the default, A[gnostic], to use the native DBMS SQL dialect, or set to H[COSS] to use the DB2 dialect for SQL Server. When you set the database type to HCOSS (DB2 dialect for SQL Server), the SQLTP2 tool instructs the OpenESQL runtime to translate z/OS DB2 SQL statements to SQL Server statements before dispatching them for execution.

Note: As part of the environment setup, SQLTP2 initializes the ALLOWNULLCHAR SQL compiler directive option to preserve the X"00" null character in CHAR and VARCHAR fields. See ALLOWNULLCHAR in the Enterprise Developer documentation for details.

The following is an example of JCL for a CREATE TABLE statement using the DB2 dialect for SQL Server:

Note: The MBDT Configuration utility HCOSS option does not support SQL statements of mixed dialects. In addition, any SQL Server-specific syntax in SQL statements could cause errors.

//DDLJOB2 JOB 
//RESETDB  EXEC PGM=IKJEFT01
//SYSTSPRT DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//SYSPUNCH DD  SYSOUT=*                                
//SYSUDUMP DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(HCOD)
 RUN  PROGRAM(DSNTEP2)
 END
/*
//SYSIN DD *
CREATE TABLE HCOSQL.EMP
(
EMPNO       CHAR(6) NOT NULL,
FIRSTNME    VARCHAR(12) NOT NULL,
MIDINIT     CHAR(1) NOT NULL,
LASTNAME    VARCHAR(15) NOT NULL,
WORKDEPT    CHAR(3),
PHONENO     CHAR(4),
HIREDATE    DATE,
JOB         CHAR(8),
EDLEVEL     SMALLINT,
SEX         CHAR(1),
BIRTHDATE    DATE,
SALARY      DECIMAL(9,2),
BONUS       DECIMAL(9,2),
COMM        DECIMAL(9,2),
PRIMARY KEY (EMPNO)
) IN XDBIBMDB.XDBIBMTS;

/*
//

Where HCOD is the ODBC XAR ID for your database connection, SQLTP2 has been established as an alias for DSNTEP2, and the semi-colon (;) is the SQL terminator.

Parameters

The SQLTP2 parameters supported are:

ALIGN (MID), ALIGN(LHS)
MIXED, NOMIXED
TOLWARN(YES), TOLWARN(NO)
SQLTERM (termchar)

Control Statements

Control statements for the SYSIN data set use the following syntax:

--#SET control-option-value

Where control-option-value can be any of the following:

TERMINATOR
ROWS_FETCH
ROWS_OUT
MAXERRORS
TOLARTHWRN
TOLWARN

The following control option values and error-raising values are not supported:

MULTI-FETCH
PREPWARN
SQLFORMAT

Note: To ensure proper parsing of SYSIN, do not include the tab character (ASCII decimal character code 9; EBCDIC decimal character code 5) in its contents. When parsed, the tab character could cause spurious, unintended errors.