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:
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.
|
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 |
Creates a new database, macro, table, or view |
|
Specifies a new default database for the current session |
|
|
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 |
|
Specifies a user‑created (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 |
|
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 |
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 |
|
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.