16.10 - Iterated Requests - Teradata Database

Usage

Iterated requests do not directly impact the syntax of SQL statements. They provide an efficient way to execute the same single-statement DML operation on multiple data records, like the way that ODBC applications execute parameterized statements for arrays of parameter values, for example.

Several Teradata Database client tools and interfaces provide facilities to pack multiple data records in a single buffer with a single DML statement.

For example, suppose you use BTEQ to import rows of data into table ptable using the following INSERT statement and USING modifier:

   USING (pid INTEGER, pname CHAR(12))
   INSERT INTO ptable VALUES(:pid, :pname);

To repeat the request as many times as necessary to read up to 200 data records and pack a maximum of 100 data records with each request, precede the INSERT statement with the following BTEQ command:

   .REPEAT RECS 200 PACK 100
   

The following tools and interfaces provide facilities that you can use to execute iterated requests.

Rules

The DML statement may not be a CALL statement.

The DML statement must reference user-supplied input data, either as named fields in a USING modifier or as '?' parameter markers in a parameterized request.

All the data records in a given request must use the same record layout. This restriction applies by necessity to requests where the record layout is given by a single USING modifier in the request text itself; but the restriction also applies to parameterized requests, where the request text has no USING modifier and does not fully specify the input record.

The server processes the iterated request as if it were a single multistatement request, with each iteration and its response associated with a corresponding statement number.

Statement Independence for Simple INSERTs

When an iterated request includes only simple INSERT statements, a failure of one or more INSERTs does not cause the entire request to be rolled back. In these cases, errors are reported for the INSERT statements that failed, so those statements can be resubmitted. INSERT statements that completed successfully are not rolled back.

This behavior is limited to requests submitted directly to the database using an SQL INSERT multistatement request or submitted using a JDBC application request.

Related Topics