Usage Notes - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2436
lifecycle
previous
Product Category
Teradata Tools and Utilities

SelectStmt

SelectStmt (SELECT statement) is the required attribute that the Export operator uses to perform data selection from Teradata Database tables. Multiple parallel instances of the Export operator and multiple sessions within each instance can improve the performance of an export.

A Select request within an Export script can have multiple SELECT statements. A SELECT statement can be optionally preceded by a LOCKING modifier.

However, Export SELECT requests cannot:

  • Specify a USING modifier.
  • Access non-data tables, such as SELECT DATE or SELECT USER.
  • Be satisfied by one or two AMPs, such as SELECT statement which accesses rows based on the primary index or unique secondary index of a table.
  • Contain character large object (CLOB) or binary large object (BLOB) data types.
  • Contain JSON (JavaScript Object Notation) data type.
  • Contain XML data type.
  • The following table describes types of Select requests.

     

    Table 10: SELECT Requests 

    Type of Select Request

    Result

    Contains multiple SELECT statements

    The Teradata Database might execute the requests in parallel, but responses are still returned in the order of the requests, for the first statement first, then for the second, and so on.

    If the structure of response rows differs, an error results and the job terminates.

    Uses a LOCKING modifier

    The specified lock remains in effect during the execution of all statements within the request that contains the modifier. The Teradata Database does the following:

  • Implements all resource locks for the entire request before executing any of the statements in the request.
  • Maintains the locks until all of the response data for the request is moved to spool tables.
  • Following is a valid SELECT request using the LOCKING modifier:

    LOCKING TABLE MYTABLE FOR ACCESS SELECT COL1, COL2 FROM MYTABLE;

    Note that the LOCKING modifier can precede the SELECT statement.

    Uses an ORDER BY clause

    Specify one Export instance.

    Following is a valid ORDER BY clause:

    SELECT COL1, COL2 FROM MYTABLE ORDER BY COL1;

    Multiple SELECT Statements

    A single Export operator can read data from multiple tables as long as their schemas are the same.

    For example, if Table1 and Table2 have the same schema, use the following SELECT statement in the attribute section of the Export operator definition:

    VARCHAR SelectStmt='SELECT * FROM Table1;SELECT * FROM Table2'

    If the job contains multiple SELECT statements and the job uses multiple instances of the Export operator, only the first instance of the Export operator logs on the special session(s). This is necessary to preserve the exported records in statement order.