15.10 - Array Support - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

prodname
Preprocessor2 for Embedded SQL
vrm_release
15.10
category
Programming Reference
featnum
B035-2446-035K

The DML Array feature improves application performance by allowing iterative requests to be grouped together into a single step, resulting in a decrease of the step-processing overhead per request. Only single INSERT statements and references to arrays of variables are supported. References to arrays of structures are not supported.

To accommodate this feature, the EXEC SQL statement is modified to:

EXEC SQL FOR (<countval) <sql statement string>;

where:

  • FOR indicates that this is an array statement
  • <countval> is a user-defined integer variable or literal integer constant that must be less than or equal to the smallest defined dimension in the list of arrays
  • Note: Ensure that <countval> does not exceed the smallest defined dimension in the list of arrays. An error is generated if this condition is violated and countval is specified as a literal. At precompile time, no check is made when countval is specified as a variable.

  • <sql statement string> is a character string containing a legal SQL statement with references to host variables
  • Literal integer constants that are embedded in the SQL string are not iterated; they are propagated in every inserted row.

    The same host variable can be specified for two or more fields. Any given iteration will obtain the same value.

    Example  

    01  EMPTBL.                                           
          05 EMPNUM      PIC S9(8) DISPLAY OCCURS 6 TIMES. 
                                                           
     01  MGRTBL.                                           
          05 MGRNUM      PIC S9(8) COMP OCCURS 6 TIMES.    
                                                           
      EXEC SQL                              
          PREPARE INSSTMT FROM :STMT-STRING 
      END-EXEC.                             
     
    EXEC SQL FOR 6                             
         EXECUTE INSSTMT USING :EMPNUM, :MGRNUM  
     END-EXEC.