DSNTIAD sample program

Preparing the DSNTIAD sample program

Before you can use the DSNTIAD sample program, you must precompile, assemble, link, and bind it first. Also, when you are ready for DSNTIAD to start using to use new capabilities in later Db2 13 function levels, you must rebind the packages at the corresponding APPLCOMPAT level.

For more information, see Db2 productivity-aid sample programs

Running the DSNTIAD sample program

To run the DSNTIAD sample program, use the RUN (DSN) command and specify the following load module and plan name.

For more information about the RUN command, see RUN subcommand (DSN).

DSNTIAD parameters

PKGSET(collection)

Specifies that DSNTIAD implicitly executes a SET CURRENT PACKAGESET statement to assign a value to the CURRENT PACKAGESET special register before processing the dynamic SQL statements in SYSIN.

collection

The value to assign to the CURRENT PACKAGESET special register. You can specify up to 40 characters.

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.

For 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 SQL statement must be terminated with the SQL termination character. If you specify the SQLTERM(termchar) parameter, termchar is the SQL termination character. Otherwise, the SQL termination character is a semicolon. A statement can span multiple lines, but DSNTIAD reads only the first 72 bytes of each line.

Comments in DSNTIAD input are not supported.

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