DSNTEP2 and DSNTEP4 sample programs

You can use the DSNTEP2 or DSNTEP4 programs to execute SQL statements dynamically.

DSNTEP2 is a sample dynamic SQL program that can issue any SQL statement that can be executed dynamically. DSNTEP2 is written in PL/I language and available in two versions: a source version that you can modify to meet your needs or an object code version that you can use without the need for a PL/I compiler.

The DSNTEP4 sample program is identical to DSNTEP2, except that it uses multi-row fetch for increased performance. DSNTEP4 is written in PL/I language and available in two versions: a source version that you can modify to meet your needs or an object code version that you can use without the need for a PL/I compiler.

When multi-row fetch is used, parallelism might be disabled for the last parallel group in the top-level query block, or entirely disabled for very simple queries. To obtain full parallelism, use DSNTEP2 or specify the control option SET MULT_FETCH 1 for DSNTEP4.

DSNTEP2 and DSNTEP4 write their results to the data set that is defined by the SYSPRINT DD statement. SYSPRINT data must have a logical record length that matches the PAGEWIDTH value in the DSNTEP2 or DSNTEP4 source program. If you use the original version of the program that is shipped with Db2, the logical record length is 133 bytes. If the SYSPRINT data do not have the same logical record length as the PAGEWIDTH value, the program issues return code 12 with abend U4038 and reason code 1. This abend occurs due to the PL/I file exception error IBM0201S ONCODE=81. The following error message is issued:

The UNDEFINEDFILE condition was raised because of conflicting DECLARE 
and OPEN attributes (FILE= SYSPRINT).

If you use applications or other automation to process output from DSNTEP2 or DSNTEP4, be aware that minor changes in the format can occur as a result of service or enhancements. Such changes might require you to adjust your processes that use the output of these programs.

Important: When you allocate a new data set with the SYSPRINT DD statement, either specify a DCB with RECFM=FBA and LRECL=nnn, where nnn matches the PAGEWIDTH value in the source program, or do not specify the DCB parameter.

Preparing the DSNTEP2 and DSNTEP4 sample programs

Before you can use the DSNTEP2 and DSNTEP4 sample programs, you must prepare them. If you use the source-code versions of DSNTEP2 or DSNTEP4, you must precompile, compile, link, and bind them first. If you use the object-code versions of DSNTEP2 or DSNTEP4 you must bind them first.

Also, when you are ready for DSNTEP2 or DSNTEP4 to start using to use new capabilities in later Db2 12 function levels, you must rebind the packages at the corresponding APPLCOMPAT level. End of change

For more information, see "Preparing the productivity-aid sample programs" in Db2 productivity-aid sample programs.

Running the DSNTEP2 and DSNTEP4 sample programs

To run the DSNTEP2 and DSNTEP4 sample programs, use the RUN (DSN) command and specify the following load module and plan name.

DSNTEP2 load module and plan

Load module	DSNTEP2
Plan	DSNTEPC1

DSNTEP4 load module and plan

Load module	DSNTEP4
Plan	DSNTP412

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

DSNTEP2 and DSNTEP4 parameters

PKGSET(collection)

Specifies that DSNTEP2 or DSNTEP4 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.

For an example, see Example: Running dynamic SQL statements at different application compatibility levels in the same SYSIN.

ALIGN(MID) or ALIGN(LHS)

Specifies the alignment.

ALIGN(MID): Specifies that DSNTEP2 or DSNTEP4 output should be centered. ALIGN(MID) is the default.
ALIGN(LHS): Specifies that the DSNTEP2 or DSNTEP4 output should be left-justified.

NOMIXED or MIXED

Specifies whether DSNTEP2 or DSNTEP4 contains any DBCS characters.

NOMIXED: Specifies that the DSNTEP2 or DSNTEP4 input contains no DBCS characters. NOMIXED is the default.
MIXED: Specifies that the DSNTEP2 or DSNTEP4 input contains some DBCS characters.

PREPWARN

Specifies that DSNTEP2 or DSNTEP4 is to display details about any SQL warnings that are encountered at PREPARE time.

Regardless of whether you specify PREPWARN, when an SQL warning is encountered at PREPARE time, the program displays the message SQLWARNING ON PREPARE and sets the return code to 4. When you specify PREPWARN, the program also displays the details about any SQL warnings.

SQLFORMAT

Specifies how DSNTEP2 or DSNTEP4 pre-processes SQL statements before passing them to Db2. Select one of the following options:

SQL: This is the preferred mode for SQL statements other than SQL procedural language. When you use this option, which is the default, DSNTEP2 or DSNTEP4 collapses each line of an SQL statement into a single line before passing the statement to Db2. DSNTEP2 or DSNTEP4 also discards all SQL comments.
SQLCOMNT: This mode is suitable for all SQL, but it is intended primarily for SQL procedural language processing. When this option is in effect, behavior is similar to SQL mode, except that DSNTEP2 or DSNTEP4 does not discard SQL comments. Instead, it automatically terminates each SQL comment with a line feed character (hex 25), unless the comment is already terminated by one or more line formatting characters. Use this option to process SQL procedural language with minimal modification by DSNTEP2 or DSNTEP4.
SQLPL: This mode is suitable for all SQL, but it is intended primarily for SQL procedural language processing. When this option is in effect, DSNTEP2 or DSNTEP4 retains SQL comments and terminates each line of an SQL statement with a line feed character (hex 25) before passing the statement to Db2. Lines that end with a split token are not terminated with a line feed character. Use this mode to obtain improved diagnostics and debugging of SQL procedural language.

SQLTERM(termchar)

Specifies the character that you use to end each SQL statement. You can use any character except one of those that are listed in Table 1. SQLTERM(;) is the default.

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

See Example: changing the SQL terminator.

TOLWARN

Indicates whether DSNTEP2 or DSNTEP4 continues to process SQL SELECT statements after receiving an SQL warning. You can specify one of the following values:

NO

Indicates that the program stops processing the SELECT statement if a warning occurs when the program executes an OPEN or FETCH for a SELECT statement. NO is the default value for TOLWARN.

The following exceptions exist:

If SQLCODE +445 or SQLCODE +595 occurs when DSNTEP2 or DSNTEP4 executes a FETCH for a SELECT statement, the program continues to process the SELECT statement.
If SQLCODE +354 occurs when DSNTEP4 executes a FETCH for a SELECT statement, the program continues to process the SELECT statement.
If SQLCODE +802 occurs when DSNTEP2 or DSNTEP4 executes a FETCH for a SELECT statement, the program continues to process the SELECT statement if the TOLARTHWRN control statement is set to YES.

YES

Indicates that the program continues to process the SELECT statement if a warning occurs when the program executes an OPEN or FETCH for a SELECT statement.

QUIET

The same as YES except that the program suppresses all SQL warning messages from OPEN or FETCH statements if the SQLCODE is 0 or greater. End of change

Settings that you can change in the DSNTEP2 or DSNTEP4 source programs to modify output formatting

If the DSNTEP2 or DSNTEP4 output formatting does not meet your requirements, you can change some variables in the source code to modify the formatting.

For example, if you want to increase the maximum width of a page, the maximum size of a print line, and the maximum number of characters in the output of a character column, you can increase the values of the PAGEWIDTH, MAXPAGWD, and MAXCOLWD variable values.

For a complete description of the settings that you can change, see the information under PROGRAM SIZES in the DSNTEP2 and DSNTEP4 prologs.

DSNTEP2 and DSNTEP4 data sets

The following data sets are used by DSNTEP2 and DSNTEP4:

SYSIN

Input data set. In this data set, you can enter any number of SQL statements, each terminated with a semicolon. A statement can span multiple lines, but DSNTEP2 or DSNTEP4 reads only the first 72 bytes of each line. You must explicitly commit any SQL statements except the last one.

You can enter comments in DSNTEP2 or DSNTEP4 input with an asterisk (*) in column 1 or two hyphens (--) anywhere on a line. Text that follows the asterisk is considered to be comment text. Text that follows two hyphens can be comment text or a control statement. Comments are not considered in dynamic statement caching. Comments and control statements cannot span lines.

You can enter control statements of the following form in the DSNTEP2 and DSNTEP4 input data set:

--#SET control-option value

You can specify the following control option statements. If you specify a value of NO for any of the options in this list, the program behaves as if you did not specify the parameter.

--#SET PKGSET value

Specifies that DSNTEP2 or DSNTEP4 implicitly executes a SET CURRENT PACKAGESET statement to assign a value to the CURRENT PACKAGESET special register before processing dynamic SQL statements after this control statement in SYSIN. value is the value to assign to CURRENT PACKAGESET special register. You can specify up to 40 characters.

For an example, see Example: Running dynamic SQL statements at different application compatibility levels in the same SYSIN.

--#SET TERMINATOR value

The SQL statement terminator. value is any single-byte character other than one of those that are listed in DSNTIAD sample program. The default is the value of the SQLTERM parameter.

See Example: changing the SQL terminator within a series of SQL statements.

--#SET ROWS_FETCH value

The number of rows that are to be fetched from the result table. value is a numeric literal between -1 and the number of rows in the result table. -1 means that all rows are to be fetched. The default is -1.

--#SET ROWS_OUT value

The number of fetched rows that are to be sent to the output data set. value is a numeric literal between -1 and the number of fetched rows. -1 means that all fetched rows are to be sent to the output data set. The default is -1.

--#SET MULT_FETCH value

This option is valid only for DSNTEP4. Use MULT_FETCH to specify the number of rows that are to be fetched at one time from the result table. The default fetch amount for DSNTEP4 is 100 rows, but you can specify from 1 to 32676 rows.

--#SET TOLWARN value

Indicates whether DSNTEP2 or DSNTEP4 continues to process SQL SELECT statements after receiving an SQL warning. You can specify one of the following values:

NO

Indicates that the program stops processing the SELECT statement if a warning occurs when the program executes an OPEN or FETCH for a SELECT statement. NO is the default value for TOLWARN.

The following exceptions exist:

If SQLCODE +445 or SQLCODE +595 occurs when DSNTEP2 or DSNTEP4 executes a FETCH for a SELECT statement, the program continues to process the SELECT statement.
If SQLCODE +354 occurs when DSNTEP4 executes a FETCH for a SELECT statement, the program continues to process the SELECT statement.
If SQLCODE +802 occurs when DSNTEP2 or DSNTEP4 executes a FETCH for a SELECT statement, the program continues to process the SELECT statement if the TOLARTHWRN control statement is set to YES.

YES

Indicates that the program continues to process the SELECT statement if a warning occurs when the program executes an OPEN or FETCH for a SELECT statement.

QUIET

The same as YES except that the program suppresses all SQL warning messages from OPEN or FETCH statements if the SQLCODE is 0 or greater. End of change

--#SET TOLARTHWRN value

Indicates whether DSNTEP2 and DSNTEP4 continue to process an SQL SELECT statement after an arithmetic SQL warning (SQLCODE +802) is returned. value is either NO (the default) or YES.

--#SET PREPWARN value

Specifies that DSNTEP2 or DSNTEP4 is to display details about any SQL warnings that are encountered at PREPARE time.

--#SET SQLFORMAT value

Specifies how DSNTEP2 or DSNTEP4 pre-processes SQL statements before passing them to Db2. Select one of the following options:

SQL: This is the preferred mode for SQL statements other than SQL procedural language. When you use this option, which is the default, DSNTEP2 or DSNTEP4 collapses each line of an SQL statement into a single line before passing the statement to Db2. DSNTEP2 or DSNTEP4 also discards all SQL comments.
SQLCOMNT: This mode is suitable for all SQL, but it is intended primarily for SQL procedural language processing. When this option is in effect, behavior is similar to SQL mode, except that DSNTEP2 or DSNTEP4 does not discard SQL comments. Instead, it automatically terminates each SQL comment with a line feed character (hex 25), unless the comment is already terminated by one or more line formatting characters. Use this option to process SQL procedural language with minimal modification by DSNTEP2 or DSNTEP4.
SQLPL: This mode is suitable for all SQL, but it is intended primarily for SQL procedural language processing. When this option is in effect, DSNTEP2 or DSNTEP4 retains SQL comments and terminates each line of an SQL statement with a line feed character (hex 25) before passing the statement to Db2. Lines that end with a split token are not terminated with a line feed character. Use this mode to obtain improved diagnostics and debugging of SQL procedural language.

--#SET MAXERRORS value

value specifies that number of errors that DSNTEP2 and DSNTEP4 handle before processing stops. The default is 10. Use a value of -1 to indicate that a program is to tolerate an unlimited number of errors.

SYSPRINT

Output data set. DSNTEP2 and DSNTEP4 write informational and error messages in this data set. DSNTEP2 and DSNTEP4 write output records of no more than 133 bytes.

Define all data sets as sequential data sets.

DSNTEP2 and DSNTEP4 return codes

Table 1. DSNTEP2 and DSNTEP4 return codes
Return code	Meaning
0	Successful completion.
4	An SQL statement received a warning code.
8	An SQL statement received an error code.
12	The length of an SQL statement was more than 2097152 bytes, an SQL statement returned a severe error code (-8nn or -9nn), or an error occurred in the SQL message formatting routine.

Examples

Example: invoking DSNTEP2

Suppose that you want to use DSNTEP2 to execute SQL SELECT statements that might contain DBCS characters. You also want left-aligned output. Your invocation looks like the following example.

//RUNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 DSN SYSTEM(DSN)
 RUN  PROGRAM(DSNTEP2) PLAN(DSNTEPC1) PARMS('/ALIGN(LHS) MIXED TOLWARN(YES)') -
       LIB('DSN1210.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
SELECT * FROM DSN8C10.PROJ;

Example: invoking DSNTEP4

Suppose that you want to use DSNTEP4 to execute SQL SELECT statements that might contain DBCS characters, and you want center-aligned output. You also want DSNTEP4 to fetch 250 rows at a time. Your invocation looks like the one in the following figure:

//RUNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 DSN SYSTEM(DSN)
 RUN  PROGRAM(DSNTEP4) PLAN(DSNTEPC1) PARMS('/ALIGN(MID) MIXED') -
       LIB('DSN1210.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
--#SET MULT_FETCH 250
SELECT * FROM DSN8C10.EMP;

Example: Changing the application compatibility level of dynamic SQL statements

Suppose that Db2 is at function level V12R1M508 or higher, but you want to use DSTNEP2 to run dynamic SQL statements at a lower application compatibility level. You can use the following example commands bind packages for DSNTEP2 with two different APPLCOMPAT options:

BIND PACKAGE (M503TEP2) MEMBER(DSN@EP2L) APPLCOMPAT(V12R1M503) + 
   CURRENTDATA(NO) ACT(REP) ISO(CS) ENCODING(EBCDIC) 
BIND PACKAGE (M508TEP2) MEMBER(DSN@EP2L) APPLCOMPAT(V12R1M508) + 
   CURRENTDATA(NO) ACT(REP) ISO(CS) ENCODING(EBCDIC) 
BIND PLAN(DSNTEP2) PKLIST(M508TEP2.,M503TEP2.) + 
   ACTION(REPLACE) RETAIN + 
   CURRENTDATA(NO) ISO(CS) ENCODING(EBCDIC) SQLRULES(DB2)

In this example, V12R1M508 is the default application compatibility level for DSNTEP2 because the package named M508TEP2 is bound with APPLCOMPAT(V12R1M508) and is listed first in the BIND PLAN step. However, you can also use DSNTEP2 to issue dynamic SQL statements at application compatibility level V12R1M503. To do so in this example, specify the following parameters when you run DSNTEP2.

RUN PROGRAM(DSNTEP2) PARMS('/PKGSET(M503TEP2)')

DSNTEP2 implicitly issues the following statement before it runs the dynamic SQL statements, and it runs dynamic SQL statements at application compatibility level V12R1M503:

SET CURRENT PACKAGESET = 'M503TEP2'

Example: Running dynamic SQL statements at different application compatibility levels in the same SYSIN

Assume that you issued the same BIND commands from the previous example, and suppose that you want to create a new multi-table segmented table space, which can only be created at application compatibility V12R1M503 or lower. As in the previous example, the default application compatibility level is V12R1M508, but you can use the following example --#SET PKGSET control statement to run some statements at a lower application compatibility in the same SYSIN.


--#SET PKGSET M503TEP2
CREATE TABLESPACE SEGTS1...
CREATE TABLE TB1 IN SEGTS1...
CREATE TABLE TB2 IN SEGTS1...
--#SET PKGSET M508TEP2

ALTER TABLESPACE SEGTS1 MOVE TABLE TB1 TO TABLESPACE PBGTS1..
ALTER TABLESPACE SEGTS1 MOVE TABLE TB2 TO TABLESPACE PBGTS2...

In this example, the first three statements succeed because the -# SET PKGSET control statement tells DSNTEP2 to run them at application compatibility level V12R1M503. Another -# SET PKGSET control statement changes the application compatibility level to V12R1M508 because ALTER TABLESPACE statements must run at this level or higher to specify the MOVE TABLE clause.

Example: changing the SQL terminator

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.

Example: changing the SQL terminator within a series of SQL statements

Suppose that you have an existing set of SQL statements to which you want to add a CREATE TRIGGER statement that has embedded semicolons. You can use the --#SET TERMINATOR control statement. You can use the default SQLTERM value, which is a semicolon, for all of the existing SQL statements.

Before you execute the CREATE TRIGGER statement, include the --#SET TERMINATOR # control statement to change the SQL terminator to the character #:

SELECT * FROM DEPT;
SELECT * FROM ACT;
SELECT * FROM EMPPROJACT;
SELECT * FROM PROJ;
SELECT * FROM PROJACT;
--#SET TERMINATOR #
CREATE TRIGGER NEW_HIRE
  AFTER INSERT ON EMP
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
  END#

See the following discussion of the SYSIN data set for more information about the --#SET control statement.