DSNTEP2 and DSNTEP4 – an in-depth analysis!

This description of IBM‘s DB2 sample programs DSNTEP2 and DSNTEP4 bases on DB2 for z/OS Version 8. The programs, DB2 and z/OS are property of the IBM Corporation. Information can be found in appropriate IBM program product documentation.

• INVOCATION
• PARAMETERS
• VALID INPUT
• RESTRICTIONS
• SUPPORTED SQL DATA TYPE
• RETURN CODE AND ERROR HANDLING
• PROGRAM INTERNAL FORMATTING OPTIONS - LIMITS
• OUTPUT
• VARIATIONS OF IKJEFT01

DSNTEP2 is a sample dynamic SQL program that is written in the PL/I language. With this program, you can execute any SQL statement that can be executed dynamically. You can use the source version of DSNTEP2 and modify it to meet your needs, or, if you do not have a PL/I compiler at your  installation, you can use the object code version of DSNTEP2.

DSNTEP4, new with DB2 Version 8, is a sample dynamic SQL program that is written in the PL/I language. This program is identical to DSNTEP2 except DSNTEP4 uses multi-row           fetch for increased performance. If you use DSNTEP2, then changing to DSNTEP4 can save a lot of cpu time if you fetch large numbers of rows. You can use the source version of DSNTEP4 and modify it to meet your needs, or, if you do not have a PL/I compiler at your installation, you can use the object code version of DSNTEP4.

For program preparation see installation jobs DSNTEJ1P (source) and DSNTEJ1L (object). You can find the PL/I source code in the appropriate dsnlvl.SDSNSAMP library members DSNTEP2 and DSNTEP4. The DBRM‘s will be created by the precompiler, but have also been provided as members DSN§EP2L and DSN§EP4L of the SDSNSAMP library.

The DSNTEP2 and DSNTEP4 do not work in compatibility mode (CM) due to new functions (NEWFUN) used inside the code. You cannot precompile and cannot bind the modules in CM mode. If you need to deploy these programms in CM mode, you have to work with Version 7 code instead.

Invocation

//DSN     EXEC PGM=IKJEFT01 )1            
//STEPLIB  DD  DSN=dsnlvl.RUNLIB.LOAD,DISP=SHR
//SYSTSPRT DD  SYSOUT=*                           
//SYSTSIN  DD  *                                  
 DSN SYSTEM(ssid)                                 
  RUN PROGRAM(program) -
 [PLAN(planname)] -
 [PARM('/parms')] -
      [LIB('dsnlvl.RUNLIB.LOAD']
 END
//SYSIN    DD  *
-- your SQL Statements;
/*

• SSID:DB2 subsystem identifier

• Program: DSNTEP2 and DSNTEP4

• Planname:no specification, or DSNTEP2 and DSNTEP4

• Parms:up to 3 optional parameters, see next section

• Lib:Specify LIB parameter or //STEPLIB JCL statement. Only necessary, if not specified within z/OS LINKLST or JOBLIB JCL statement. Programs may reside in dsnlvl.RUNLIB.LOAD, or dsnlvl.SDSNLOAD, or even other libraries. This depends on individual installation.

)1 IKJEFTxx:Possible variations, see section „Variations of IKJEFT01“.

Parameters

Parameters are optional. You may specify up to 3 parameters.  The parameter string cannot have more than 50 bytes.

Valid Input

An input line consists of characters from columns 1-72.  If an input statement spans over multiple lines, the lines are concatenated and blanks are removed such that only one blank occurs between words.

Input SQL statements will be transferred to the statement buffer with one blank between words. Blanks in delimited strings will be transferred into the statement buffer exactly as they appear in the input statement.

Longest SQL statement may have 2 meagabytes. Multiple blanks will be removed before further internal procesing. You may raise this limit changing an internal constant. (See section „Program Internal Formatting options - Limits“ below.)

Following input will be accepted by DSNTEP2:

1. All valid dynamic SQL commands                        
2. These static SQL commands:
   CONNECT  
   SET CONNECTION
   SET QUERYNO
   RELEASE                                                    
3. These commands, which are used to terminate processing:
   END  
   EXIT
   QUIT                                     
4. These commands, which provide brief HELP text:        
   HELP
   H
   ?                                          
5. EXEC SQL, which is permitted as a prefix to test statements that will be put in programs.                   
6. Standard comments, which are of these forms:          
   -All text in any line that starts with an asterisk (*)
   -Not valid within an SQL statement                 
   -All text that follows a double hyphen (--), to the end of the line                                     
   -Can start in any column                           
   -Is valid within an SQL statement
7. Functional comments, which are of these forms:       
   --#SET ROWS_FETCH n                                  
    where 'n' is a non-negative integer that indicates  the maximum number of rows to be FETCHed for each subsequent SELECT statement.  Use -1 to indicate that all rows should be fetched.                   
   --#SET ROWS_OUT n                                    
   where 'n' is a non-negative integer that indicates  the maximum number of rows to be outputted for each subsequent SELECT statement.  Use -1 to indicate that all rows should be outputted.                 
   --#SET TOLWARN    x                                  
   where 'x' is a YES/NO, allow SQL warnings or not
   --#SET TERMINATOR x                                  
   where 'x' is a one-byte character to be used to terminate the next SQL statement.  Any character is valid except a blank, comma, single or double quote, underscore, or parenthesis.                        
   --#SET TOLARTHWRN x              
   where 'x' is a YES/NO, allow Arithmetic warning
   --#SET MAXERRORS n                                    
   where n is the number of errors allowed before the program should continue.  Use -1 to indicate that the program should tolerate an unlimited number of   errors.  Severe SQL errors will cause cause program termination whenever encountered.
   --#SET MULT_FETCH n                
   (DSNTEP4 only!) where 'n' is a non-negative integer between 1 to 32767 that indicates the amount of rows to be fetched for each subsequent FETCH statement. Default is 1.

Restrictions

• ·This module uses the semicolon as the default terminator for SQL statements.  The user may specify an alternate termination character by either of these means:

• Use the SQLTERM parameter when invoking DSNTEP2 SQLTERM parameter.                                
• Use the following "functional comment" in the SQL statement stream.  Note that this allows you to change the SQL terminator "on the fly":           
  --#SET TERMINATOR x                           
  where 'x' is the terminator character.            
  For either SQLTERM or TERMINATOR, any character is valid except a blank, comma, single or double quote, underscore, or parenthesis.

• Comments prefixed by two hyphens (--) are allowed within SQL statements.                              

• The maximum length of SQL statements that this program can handle is STMTMAX, due to its allocation of space (STMTBUF).  The first INPUTL characters of  each input record are inserted into this buffer.

• For statements other than SELECT, only error information or 'SUCCESSFUL" is put out, with the number of rows updated, inserted, or deleted.  For a SELECT statement, the output is more detailed.

Supported SQL Data Type

CHAR      VARCHAR   LONGVARCHAR    
DECIMAL   SMALLINT  INTEGER         
DATE      TIME      TIMESTAMP       
FLOAT     FLOAT(n)  DOUBLE PRECISION
REAL      BLOB      CLOB            
DBCLOB    ROWID

Return Code and Error Handling

In general both of the sample programs will end with following return codes:

Normal Exit (RC=0):

No errors were found in the source and no errors occurred during processing.

If the only non-zero SQL codes generated are +100s, the return code from DSNTEP2 or DSNTEP4 will be 0.

1. The following message will be generated when a HELP request is made:                                    
   ALL DYNAMIC SQL COMMANDS ARE SUPPORTED.          
   THE FOLLOWING STATIC SQL COMMANDS ARE ALSO       
   SUPPORTED:                                       
   CONNECT  SET CONNECTION  SET QUERYNO  RELEASE
2. The following message will be generated for all input statements:
   ***INPUT STATEMENT:   SQL input statement       
3. The following message will be generated when a non select SQL statement is entered:                    
   RESULT OF SQL STATEMENT:                        
   all SQL messages printed ---              
4. The following messages will be generated when a zero SQLCODE is returned:                                   
   SUCCESSFUL command OF nnn OBJECT(S)               
   The command is ALTER, CREATE, or DROP.            
   command SUCCESSFUL                                
   The command is COMMIT,GRANT,LOCK,REVOKE, ROLLBACK, or SET                                            
   SUCCESSFUL command OF nnn ROW(S)                  
   The command is SELECT, DELETE, INSERT, or UPDATE  
5. The following message will be generated when a 'not found' condition  was encountered as a result of an open cursor:
   "NOT FOUND" CONDITION ENCOUNTERED DURING OPEN       
6. The following message will be generated when an unrecognizable SQLTYPE is encountered:                 
   INVALID SQLTYPE mmm ENCOUNTERED FOR FIELD # nnn     
   mmm is the SQLTYPE code                             
   nnn is the field number on the SELECT that was unrecognized.
    

Error Exit (RC=4, RC=8 or RC=12):

Errors were found in the source, or occurred during processing. DSNTEP2 or DSNTEP4 does not produce an ABEND code.

If one of these errors are encountered, DSNTEP2 or DSNTEP4 stops processing immediatelly with error exit (RC=8):

• -804: BAD PARMLIST OR SQLDA      

• -805: NO PLAN FOR APPLICATION PGM

• -902: SYSTEM ERROR               

• -904: UNAVAILABLE RESOURCE       

• -906: PRIOR SQL ERROR DISABLED FURTHER EXECUTION          

• -911: ROLLBACK DUE TO DEADLOCK   

• -913: FAILURE DUE TO DEADLOCK    

• -922: CONNECTION NOT AUTHORIZED  

• -923: CONNECTION NOT ESTABLISHED 

• -924: DB2 CONNECTION INTERNAL ERR

• -927: LANGUAGE INTERFACE WAS CALLED WHEN CONNECTING ENVT WAS NOT ESTABLISHED

Other negative SQL Codes will be ignored up the maximum number of errors (MAXERRORS, see section „Program Internal Formatting options - Limits“ below), which is 10 by default. One more statement with a negative SQL code will be treated as error (RC=8) and will lead to an error exit.

Warnings, possibly returned by FETCH operations (see SQLCA.SQLWARN0) are gathered. The maximum warning code (at least RC=4) will be returned at the end of DSNTEP2, if no error occurs.

If the only non-zero SQL codes generated are +100‘s, the return code from DSNTEP2 or DSNTEP4 will be 0.

·RETURN CODE =  4 - warning-level errors detected.   
      SQLWARNING found during execution.               
      REASON CODE = none
·RETURN CODE =  8 - errors detected.                 
      SQLERROR   found during execution.               
      REASON CODE = none
·RETURN CODE = 12 - severe errors detected.          
      Unable to open files.                            
      Internal error, error message routine return code.
      Statement is too long.                           
      SQL buffer overflow.                             
      REASON CODE = none                               
      Invalid functional comment (--#SET) encountered    

• The following message will be generated when a SQL error is found:                                        
  SQLERROR ON command  COMMAND SQL_function FUNCTION  
• The following message will be generated when a SQL  warning is found:                                      
  SQLWARNING ON command COMMAND SQL_function FUNCTION 
• The following message will be generated when an input SQL statement is greater than MAXARRAY size:           
  **ERROR:  SQL STATEMENT GREATER THAN nnn CHARACTERS.
  STMT:                                         
  SQL_statement.                          
  nnn is MAXARRAY which is the maximum array defined in the program.                                    
  SQL_statement is the current SQL statement being  processed.
• The following message will be generated when the size of the SQL statement (SQLDA + FIELD BUFFERS) is greater  than MAXARRAY:                                         
  **ERROR:  SQL BUFFER OVERFLOW.  MAXIMUM SIZE IS nn  
  nn is MAXARRAY.  A return code of 12 is set.        
• The following message will be generated when a non select SQL statement is entered:
  RESULT OF SQL STATEMENT:                           
  RETURN CODE nnn FROM MESSAGE ROUTINE DSNTIAR.      
  any SQL messages printed ---                 
• The following message will be generated when an invalid functional comment (--#SET) is entered                 
  DSNTEP2 halted due to a functional comment (--#SET) error:       
  Invalid value, <token> specified for <command>

Program Internal Formatting options - Limits

Output

The answer for a SELECT may be visualized by a table and my may split into several pages if necessary. Those will show the answer, giving the pages from left to right, then top to bottom, as shown in an example below. The numbers and internal boxes represent pages inside the answer.

Warning: Do not specify any DCB attribute on SYSPRINT DD card, since DSNTEPx (an JES) allocates attributes by itself! The default value is RECFM=FB and LRECL=133 which is equivalent to PAGEWIDTH.

IIBM literatur says:
A change to SYSPRINT blocking in DSNTEP2 will speed up the rate in which DSNTEP2 outputs its results. The blocking size was very small before, thus impacting the performance when processing large result sets. DSNSTEP2 can now also use the system default or user assigned JCL block size. (DB2 Requirement MR0419006957: Change SYSPRINT blocking in DSNTEP2)

SYSPRINT and SYSIN allocation in PL/I sample program looks like ...
DCL                                                                 
    SYSIN       FILE STREAM INPUT,  /* INPUT FILE                  */
    SYSPRINT    FILE STREAM OUTPUT  /* OUTPUT FILE                 */
                ENV(FB,RECSIZE(PAGEWIDTH));                         

(Nota bene: Pagewidth is an programm-internal fixed value! See table above.)

 

Variations of IKJEFT01

The program needed to execute TSO/E commands from the background is a terminal monitor program (TMP), which may be one of the following:

PGM=IKJEFT01
When a command completes with a non-zero return code, the program goes to the next command. When a command abends, the step ends with a condition code of 12 (X'C').

PGM=IKJEFT1A
If a command or program being processed by IKJEFT1A ends with a system abend, IKJEFT1A causes the job step to terminate with a X'04C' system completion code. IKJEFT1A also returns to the caller the completion code from the command or program in register 15.
If a command or program being processed by IKJEFT1A ends with a user abend, IKJEFT1A saves the completion code in register 15 and then terminates.
If a command, program or REXX exec being processed by IKJEFT1A returns a non-zero return code to IKJEFT1A, IKJEFT1A saves this return code in register 15 and then terminates. Non-zero return codes to IKJEFT1A from CLISTs will not affect the contents of register 15 and the TMP will continue processing.
For a non-zero return code or an abend from a command or program that was not given control directly by IKJEFT1A, no return code is saved in register 15, and IKJEFT1A does not terminate.

PGM=IKJEFT1B
If a command or program being processed by IKJEFT1B ends with a system or user abend, IKJEFT1B causes the job step to terminate with a X'04C' system completion code. IKJEFT1B also returns to the caller the completion code from the command or program in register 15.
If a command, program, CLIST, or REXX exec being processed by IKJEFT1B returns a non-zero return code to IKJEFT1B, IKJEFT1B saves this return code in register 15 and then terminates.
For a non-zero return code or abend completion code from a program or command that was not given control by IKJEFT1B, no return code is saved in register 15, and IKJEFT1B does not terminate

See z/OS TSO/E Customization for information on abend and non-zero return code processing by these alternate programs, as well as conditional disposition processing for data sets.