SQLTUL Tool - Unloading SQL Tables

The SQLTUL tool is equivalent to DB2 DSNTIAUL. It is used unload some or all rows from up to 100 tables in one JCL step. With SQLTUL, you can unload data of all built-in data types except LOB and time-interval types. The format of unload data is compatible with SQLUTB LOAD utility and the load control statements are generated during the unload process. The unload data is encoded in ASCII character set as the default and in EBCDIC character set as optional.

You can also use SQLTUL to execute SQL non-SELECT statements dynamically.

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

SQLTUL supports a number of popular RDBMSs via the OpenESQL runtime, including SQL Server, DB2 for z/OS, DB2 LUW, Oracle, and PostgreSQL. When running against SQL Server, the SQL dialect of the statements used in SQLTUL can be native or of DB2 for z/OS. For the latter, set the HCOSS option in the MBDT Configuration Utility. For example, when targeting SQL Server, use the T-SQL dialect.

The following is an example of JCL using SQLTUL:

//UNLOADTL  JOB
//RESETDB  EXEC PGM=IKJEFT01
//SYSTSPRT DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//SYSREC00 DD  DSN=MCS.UNLOAD. UNLOADTL,
//             UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,CATLG),
//             VOL=SER=SCR03,RECFM=FB
//SYSPUNCH DD  DSN=MCS. UNLOADTL.SYSPUNCH,
//             UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,CATLG),
//             VOL=SER=SCR03,RECFM=FB
//SYSUDUMP DD  SYSOUT=*
//SYSOUT      DD  SYSOUT=*
//SYSTSIN      DD  *
 DSN SYSTEM(HCOD)
 RUN  PROGRAM(SQLTUL) PARMS('SQL,250') 
//SYSIN       DD  *
SELECT * FROM MFIXCH.EMP;
/*

Where HCOD is the ODBC XAR ID for your database connection.

Parameters

The following parameters are supported by SQLTUL:
  • SQL
  • TOLWARN
  • EBCDIC (specifies that the dataset is encoded in EBCDIC)

SQLTUL ignores parameters that indicate the number of rows per fetch, and ignores the LOBFile parameter.