15.00 - Teradata SQL Statements - Parallel Data Pump

Teradata Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
15.00
category
Programming Reference
featnum
B035-3021-034K

Teradata SQL Statements

Teradata SQL statements define and manipulate the data stored in Teradata Database.

Teradata TPump supports a subset of Teradata SQL statements so other utilities do not have to be invoked to perform routine database maintenance functions before executing Teradata TPump utility tasks. For example, use the supported Teradata SQL statements to:

  • Create the table to load
  • Establish a database as an explicit table name qualifier
  • Add checkpoint specifications to a journal table
  • The Teradata SQL statements supported by Teradata TPump are summarized in Table 9. Teradata TPump supports only the Teradata SQL statements listed in this table. To use any other Teradata SQL statements, they must be entered from another application, such as BTEQ.

    The subset of Teradata SQL supported by the Teradata TPump support environment excludes user‑generated transactions (BEGIN TRANSACTION; END TRANSACTION;).

    Table 9 lists the Teradata SQL statements supported in Teradata TPump.

     

    Table 9: Teradata SQL Statements in Teradata TPump 

    Teradata SQL Statement

    Function

    ALTER TABLE

    Changes the column configuration or options of an existing table

    CHECKPOINT

    Adds checkpoint entry to a journal table

    COLLECT STATISTICS

    Collects statistical data for one or more columns of a table

    COMMENT

    Stores or retrieves a comment string associated with a database object

    CREATE DATABASE
    CREATE MACRO
    CREATE TABLE
    CREATE VIEW

    Creates a new database, macro, table, or view

    DATABASE

    Specifies a new default database for the current session

    DELETE

    Removes rows from a table

    DELETE DATABASE

    Removes all tables, views, and macros from a database

    DROP DATABASE

    Removes an empty table from Teradata Database

    EXECUTE

    Specifies a usercreated (predefined) macro for execution. The macro named in this statement resides in Teradata Database and specifies the type of DML statement (INSERT, UPDATE, DELETE, or UPSERT) being handled by the macro.

    GIVE

    Transfers ownership of a database to another user

    GRANT

    Grants privileges to a database object

    INSERT

    Inserts new rows to a table

    MODIFY DATABASE

    Changes the options of an existing database

    RENAME

    Changes the name of an existing table, view, or macro

    REPLACE MACRO
    REPLACE VIEW

    Redefines an existing macro or view

    REVOKE

    Rescinds privileges to a database object

    SET QUERY_BAND ...FOR SESSION

    Sets the query band for a session

    The statement can be used in two ways:

    SET QUERY_BAND = 'Document=XY1234; Universe=East;' FOR SESSION;
     
    SET QUERY_BAND = NONE FOR SESSION;

    Note: Teradata TPump parses and prevents sending a “SET QUERY_BAND ... FOR TRANSACTION;” statement to the Teradata Database.

    SET SESSION COLLATION

    Overrides the collation specification for the current session

    SET SESSION OVERRIDE REPLICATION ON/OFF

    Turn on/off replication service

    UPDATE Statement and Atomic Upsert

    Changes the column values of an existing row in a table

    Now that Teradata TPump supports temporal semantics, it scans up to the keywords in the above list only in the sense that it submits them to Teradata Database and deals with the success, failure, or error response.

    While restarting, only DATABASE and SET statements are reexecuted. The existence of a log table causes Teradata TPump on the client to execute its restart logic.

    Note that, although SET is in the list, the only SET statements truly supported are the Teradata SQL SET statements: SET SESSION COLLATION and SET SESSION DATABASE. Any other SET statement passed through to Teradata Database is rejected.

    Teradata SQL statements from the input command file are sent to Teradata Database for execution via CLIv2. Pertinent information returned in SUCCESS, FAILURE, or ERROR parcels is listed in the message destination.

    Notice:

    Do not issue a DELETE DATABASE statement to delete the database containing the restart log table because this terminates the Teradata TPump job. See “Reinitialize a Teradata TPump Job” on page 48 for restart instructions if the restart log table is accidentally dropped.

    Support environment statements may be executed between invocations of Teradata TPump tasks. These include DATABASE, CHECKPOINT, and CREATE TABLE statements. The BEGIN LOAD command then starts a Teradata TPump task script.

    The action of Teradata TPump may be directed by commands and DML statements retrieved from an external source. The data source for these commands and statements may be specified in the Teradata TPump RUN FILE command, if one is used.

    The Teradata TPump support environment parses lines that begin with a period as commands. The period distinguishes commands from Teradata SQL statements, which are passed to Teradata Database without parsing. More than one statement per line is not allowed but statements can span multiple lines.

    Teradata TPump follows the same rules as standard Teradata SQL for OPERATIONS on NULL.

    Refer to SQL Fundamentals (B035‑1141) for more information about using Teradata SQL statements.