The SQLTP2xx utilities are equivalent to DB2 DSNTEP2. The are used to execute dynamic SQL statements embedded in JCL, given a database connection. In the database migration process, we use it to execute DDL against a DBMS to create and modify schema objects.
To use SQLTP2xx utilities, either create a JCL application project or directly submit JCL using the JES CONTROL feature in Enterprise Server. The JCL syntax must be DB2 compatible.
Use the DB2 dialect for SQL statements, not the SQL Server dialect. The DSNTEP2 program calls the ESQL runtime to process dialect translation before sending the SQL statement to SQL Server for execution. Any SQL Server-specific syntax in the SQL statements could cause errors. For example, the SQL Server dialect allows an explicit NULL in the column definition for nullable columns; but the DB2 dialect does not support NULL.
We provide two options for processing SQL statements. You can use either the native DBMS SQL dialect (Agnostic) or use the DB2 dialect for SQL Server (HCOSS) with your DDL statements. For the second option, the SQLTP2xx utility instructs the ESQL runtime to translate SQL statements to the dialect before sending them to SQL Server for execution. Use the MBDT Configuration Utility to select one of these options.
The following is an example of JCL for a CREATE TABLE statement using the DB2 dialect for SQL Server:
//DDLJOB2 JOB //RESETDB EXEC PGM=IKJEFT01 //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSPUNCH DD SYSOUT=* //SYSREC00 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 DSN name, and SQLTP232 has been established as an alias for DSNTEP2, and the semi-colon (;) is the SQL terminator.
Some DB2 and SQL Server data types are different from each other. The DSNTEP2 utility uses the following data type mappings for HCOSS translation:
| DB2 Data Type | SQL Server Data Type |
|---|---|
| INTEGER | INTEGER |
| BIGINT | BIGINT |
| SMALLINT | SMALLINT |
| DOUBLE | FLOAT(53) |
| FLOAT(n) | FLOAT(n) |
| REAL | REAL |
| DECIMAL(p,s) | DECIMAL(p,s) |
| CHAR | CHAR |
| VARCHAR(n) n <= 8000 | VARCHAR(n) |
| VARCHAR(n) n > 8000 | VARCHAR(max) |
| GRAPHIC | NCHAR |
| VARGRAPHIC | NVARCHAR |
| DATE | DATE |
| TIME | TIME(0) |
| TIMESTAMP | DATETIME2(6) |
| BINARY* | BINARY |
| CHAR FOR BIT DATA* | BINARY |
| BLOB* | VARBINARY(max) |
| CLOB* | NVARCHAR(max) |
| XML* | XML |
| * Not supported by the SQLUTBxx utilities | |