Teradata SQL statements define and manipulate the data stored in the database.
- 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 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 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 |
| 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; 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 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.
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.