DSNTEP2-:It is a PL\1 source program which provides the functionality
of executing the sql statements from jcl in mainframe.
the sql can be executed when run under the plan DSNTEP2
DSNTEP2 DOES NOT ALLOW MUTILPLE FETCHING OF ROWS.
DSNTEP4 -: All the functions are same as of dsntep2.
it also allows the multiple fetching.
MULTIPLE FETCH -: Specify the number of rows that the
SQL is going to fetch at one select.
The default fetch amount for DSNTEP4 is 100 rows, but you can
specify from 1 to 32676 rows.
DSNTEP2 and DSNTEP4
Use the DSNTEP2 or DSNTEP4 programs to execute SQL statements dynamically.
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 of 133 bytes (LRECL=133). Otherwise, 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).
Important: When you allocate a new data set with the SYSPRINT DD statement, either specify a DCB with LRECL=133, or do not specify the DCB parameter.
DSNTEP2 and DSNTEP4 parameters:
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 the PREPARE SQLWARNING message and set the return code to 4 when an SQLWARNING is encountered at PREPARE.
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.
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.
If you want to change the SQL terminator within a series of SQL statements, you can use the --#SET TERMINATOR control statement.
Example: 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 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.
TOLWARN
Specify NO (the default) or YES to indicate whether DSNTEP2 or DSNTEP4 continues to process SQL SELECT statements after receiving an SQL warning:
NO
If a warning occurs when DSNTEP2 or DSNTEP4 executes an OPEN or FETCH for a SELECT statement, DSNTEP2 or DSNTEP4 stops processing the SELECT statement. If SQLCODE +445 or SQLCODE +595 occurs when DSNTEP2 or DSNTEP4 executes a FETCH for a SELECT statement, DSNTEP2 or DSNTEP4 continues to process the SELECT statement. If SQLCODE +802 occurs when DSNTEP2 or DSNTEP4 executes a FETCH for a SELECT statement, DSNTEP2 or DSNTEP4 continues to process the SELECT statement if the TOLARTHWRN control statement is set to YES.
YES
If a warning occurs when DSNTEP2 or DSNTEP4 executes an OPEN or FETCH for a SELECT statement, DSNTEP2 or DSNTEP4 continues to process the SELECT statement.
DSNTEP2 and DSNTEP4 data sets:
Data Set
Description
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 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. Start of changeComments are not considered in dynamic statement caching.End of change 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
The control options are:
TERMINATOR
The SQL statement terminator. The default is the value of the SQLTERM parameter.
ROWS_FETCH
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.
ROWS_OUT
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.
MULT_FETCH
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.
TOLWARN
Indicates whether DSNTEP2 and DSNTEP4 continue to process an SQL SELECT statement after an SQL warning is returned. value is either NO (the default) or YES.
TOLARTHWRN
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.
PREPWARN
Indicates how DSNTEP2 and DSNTEP4 is to handle a PREPARE SQLWARNING message.
NO
Indicates that DSNTEP2 and DSNTEP4 does not display the PREPARE SQLWARNING message and does not set the return code to 4 when an SQLWARNING is encountered at PREPARE. The default is NO.
YES
Indicates that DSNTEP2 and DSNTEP4 displays the PREPARE SQLWARNING message and sets the return code to 4 when an SQLWARNING is encountered at PREPARE.
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.
MAXERRORS
Specifies that number of errors that DSNTEP2 and DSNTEP4 handle before processing stops. The default is 10.
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 32760 bytes, an SQL statement returned a severe error code (-8nn or -9nn), or an error occurred in the SQL message formatting routine.
Example of DSNTEP2 invocation
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 one in the following figure.
//RUNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP91) PARMS('/ALIGN(LHS) MIXED TOLWARN(YES)') -
LIB('DSN910.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SELECT * FROM DSN8910.PROJ;
Example of DSNTEP4 invocation
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(DSNTEP481) PARMS('/ALIGN(MID) MIXED') -
LIB('DSN910.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
--#SET MULT_FETCH 250
SELECT * FROM DSN8910.EMP;
No comments:
Post a Comment