DSNTIAD

Use the DSNTIAD program to execute SQL statements other than SELECT statements dynamically.

DSNTIAD parameters:

RC0

If you specify this parameter, DSNTIAD ends with return code 0, even if the program encounters SQL errors. If you do not specify RC0, DSNTIAD ends with a return code that reflects the severity of the errors that occur. Without RC0, DSNTIAD terminates if more than 10 SQL errors occur during a single execution.

SQLTERM(termchar)

Specify this parameter to indicate the character that you use to end each SQL statement. You can use any special character except one of those listed in the following table. SQLTERM(;) is the default.

Table 1. Invalid special characters for the SQL terminator
Name	Character	Hexadecimal representation
blank		X'40'
comma	,	X'6B'
double quotation mark	"	X'7F'
left parenthesis	(	X'4D'
right parenthesis	)	X'5D'
single quotation mark	'	X'7D'
underscore	_	X'6D'

Use a character other than a semicolon if you plan to execute a statement that contains embedded semicolons.

example:

Suppose that you specify the parameter SQLTERM(#) to indicate that the character # is the statement terminator. Then a CREATE TRIGGER statement with embedded semicolons looks like this:

CREATE TRIGGER NEW_HIRE
  AFTER INSERT ON EMP
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
  END#

A CREATE PROCEDURE statement with embedded semicolons looks like the following statement:

CREATE PROCEDURE PROC1 (IN PARM1 INT, OUT SCODE INT) 
  LANGUAGE SQL                                       
  BEGIN                                              
    DECLARE SQLCODE INT;                             
    DECLARE EXIT HANDLER FOR SQLEXCEPTION            
      SET SCODE = SQLCODE;                           
    UPDATE TBL1 SET COL1 = PARM1;                    
  END #

Be careful to choose a character for the statement terminator that is not used within the statement.

DSNTIAD data sets:

Data set: Description
SYSIN: Input data set. In this data set, you can enter any number of non-SELECT SQL statements, each terminated with a semicolon. A statement can span multiple lines, but DSNTIAD reads only the first 72 bytes of each line.
You cannot enter comments in DSNTIAD input.
SYSPRINT: Output data set. DSNTIAD writes informational and error messages in this data set. DSNTIAD sets the record length of this data set to 121 bytes and the block size to 1210 bytes.

Define all data sets as sequential data sets.

DSNTIAD return codes:

Table 2. DSNTIAD return codes
Return code	Meaning
0	Successful completion, or the user-specified parameter RC0.
4	An SQL statement received a warning code.
8	An SQL statement received an error code.
12	DSNTIAD could not open a data set, the length of an SQL statement was more than 2 MB, an SQL statement returned a severe error code (-8`nn` or -9`nn`), or an error occurred in the SQL message formatting routine.

Example of DSNTIAD invocation:

Suppose that you want to execute 20 UPDATE statements, and you do not want DSNTIAD to terminate if more than 10 errors occur. Your invocation looks like the one that is shown in the following figure:

//RUNTIAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 DSN SYSTEM(DSN)
 RUN  PROGRAM(DSNTIAD) PLAN(DSNTIAA1) PARMS('RC0') -
       LIB('DSN1010.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
UPDATE DSN8A10.PROJ SET DEPTNO='J01' WHERE DEPTNO='A01';
UPDATE DSN8A10.PROJ SET DEPTNO='J02' WHERE DEPTNO='A02';
⋮
UPDATE DSN8A10.PROJ SET DEPTNO='J20' WHERE DEPTNO='A20';