Iterated Requests | SQL Fundamentals | Teradata Vantage - 17.10 - Iterated Requests - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
Language
English (United States)

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 Tools and Utilities 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 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.

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
Microsoft OLE DB Provider for ODBC and Teradata ODBC Driver 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 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.

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 Information

For more information about:
  • The PACK option, see Usage.
  • Iterated request processing, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
  • Which DML statements can be specified in an iterated request, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
  • CLIv2, see Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418.
  • ODBC parameter arrays, see ODBC Driver for Teradata® User Guide, B035-2526.
  • JDBC driver batch operations, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html.
  • Microsoft OLE DB Provider for ODBC and ODBC Driver for Teradata, see ODBC Driver for Teradata® User Guide, B035-2526.
  • BTEQ PACK command, see Basic Teradata® Query Reference, B035-2414.