15.00 - Iterated Requests - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Iterated Requests

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
   

Note: The PACK option is ignored if the database being used does not support iterated requests or if the request that follows the REPEAT command is not a DML statement supported by iterated requests. For details, see “Rules” on page 134.

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

 

Tool/Interface

Facility

CLIv2 for workstation-attached systems

using_data_count field in the DBCAREA data area

CLIv2 for mainframe systems

using-data-count field in the DBCAREA data area

ODBC

Parameter arrays

JDBC type 4 driver

Batch operations

OLE DB Provider for Teradata

Parameter sets

BTEQ

  • .REPEAT command
  • .SET PACK command
  • Rules

  • The iterated request must consist of a single DML statement from the following list:
  • ABORT
  • DELETE (excluding the positioned form of DELETE)
  • EXECUTE macro_name
  • The fully-expanded macro must be equivalent to a single DML statement that is qualified to be in an iterated request.

  • INSERT
  • MERGE
  • ROLLBACK
  • SELECT
  • UPDATE (including atomic UPSERT, but excluding the positioned form of UPDATE)
  • 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.
  • Note: Iterated requests do not support the USING modifier with the TOP n operator.

  • 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

     

    For more information on …

    See …

    iterated request processing

    SQL Request and Transaction Processing.

    which DML statements can be specified in an iterated request

    SQL Data Manipulation Language.

    CLIv2

    Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems

    ODBC parameter arrays

    ODBC Driver for Teradata User Guide.

    JDBC driver batch operations

    Teradata JDBC Driver User Guide.

    OLE DB Provider for Teradata parameter sets

    OLE DB Provider for Teradata User Guide.

    BTEQ PACK command

    Basic Teradata Query Reference.