15.00 - Multistatement Requests - Teradata Database

Teradata Database SQL Fundamentals

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

Multistatement Requests

An atomic request containing more than one SQL statement, each terminated by a SEMICOLON character.

Syntax

ANSI Compliance

Multistatement requests are non‑ANSI/ISO SQL:2011 standard.

Rules and Restrictions

Teradata Database imposes restrictions on the use of multistatement requests:

  • Only one USING modifier is permitted per request, so only one USING modifier can be used per multistatement request.
  • This rule applies to interactive SQL only. Embedded SQL and stored procedures do not permit the USING modifier.

  • A multistatement request cannot include a DDL statement.
  • However, a multistatement request can include one SET QUERY_BAND … FOR TRANSACTION statement if it is the first statement in the request.

  • The keywords BEGIN REQUEST and END REQUEST must delimit a multistatement request in a stored procedure.
  • Power of Multistatement Requests

    The multistatement request is application-independent. It improves performance for a variety of applications that can package more than one SQL statement at a time. BTEQ, CLI, and the SQL preprocessor all support multistatement requests.

    Multistatement requests improve system performance by reducing processing overhead. By performing a series of statements as one request, performance for the client, the Parser, and the Database Manager are all enhanced.

    Because of this reduced overhead, using multistatement requests also decreases response time. A multistatement request that contains 10 SQL statements could be as much as 10 times more efficient than the 10 statements entered separately (depending on the types of statements submitted).

    Implicit Multistatement Transaction

    In Teradata session mode, a multistatement request is one form of implicit transaction. As such, the outcome of an implicit multistatement transaction is typically all-or-nothing. If one statement in the request fails, the entire implicit transaction fails and the system rolls it back.

    Statement Independence for Simple INSERTs

    When a multistatement 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.

    Parallel Step Processing

    Teradata Database can perform some requests in parallel (see “Parallel Steps” on page 133). This capability applies both to implicit transactions, such as macros and multistatement requests, and to Teradata-style transactions explicitly defined by BEGIN/END TRANSACTION statements.

    Statements in a multistatement request are broken down by the Parser into one or more steps that direct the execution performed by the AMPs. It is these steps, not the actual statements, that are executed in parallel.

    A handshaking protocol between the PE and the AMP allows the AMP to determine when the PE can dispatch the next parallel step.

    Up to twenty parallel steps can be processed per request if channels are not required, such as a request with an equality constraint based on a primary index value. Up to ten channels can be used for parallel processing when a request is not constrained to a primary index value.

    For example, if an INSERT step and a DELETE step are allowed to run in parallel, the AMP informs the PE that the DELETE step has progressed to the point where the INSERT step will not impact it adversely. This handshaking protocol also reduces the chance of a deadlock.

    “Parallel Steps” on page 133 illustrates the following process:

    1 The statements in a multistatement request are broken down into a series of steps.

    2 The Optimizer determines which steps in the series can be executed in parallel.

    3 The steps are processed.

    Each step undergoes some preliminary processing before it is executed, such as placing locks on the objects involved. These preliminary processes are not performed in parallel with the steps.

    Parallel Steps

    Related Topics

     

    For more information on …

    See …

    multistatement requests
    multistatment request processing

  • SQL Data Manipulation Language.
  • SQL Request and Transaction Processing.