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.
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.
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.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.
- 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.
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.
- --#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.
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. - --#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
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.