17.00 - Teradata SQL Statements - Parallel Data Pump

Teradata® Parallel Data Pump Reference

Parallel Data Pump
June 2020
Programming Reference

Teradata SQL statements define and manipulate the data stored in the 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 perform the following tasks:
  • 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 the following table. 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;).

The following table lists the Teradata SQL statements supported in Teradata TPump.

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



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 a database
EXECUTE Specifies a user-created (predefined) macro for execution. The macro named in this statement resides in a 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


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;
Teradata TPump parses and prevents sending a “SET QUERY_BAND ... FOR TRANSACTION;” statement to the 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 the 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 the database is rejected.

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

Do not issue a DELETE DATABASE statement to delete the database containing the restart log table because this terminates the Teradata TPump job. See Reinitializing a Teradata TPump Job 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 the 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.

See Teradata Vantage™ - SQL Fundamentals, B035-1141 for more information about using Teradata SQL statements.