16.20 - Running a Macro - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

Running a macro constitutes an implicit transaction. Therefore, in Teradata session mode, a macro need not be enclosed between BEGIN TRANSACTION and END TRANSACTION statements.

When the session performing a macro is in ANSI mode, the actions of the macro are uncommitted until a commit or rollback occurs in subsequent statements unless the macro body ends with a COMMIT statement. If you define a macro using a COMMIT statement, then it can be performed only in sessions running in ANSI mode.

The following table explains how to define and use a macro so it can be committed in both ANSI and Teradata session modes:

IF a macro is to be performed in this session mode … THEN …
ANSI exclusively add a COMMIT WORK statement to the end of its definition.
both ANSI and Teradata omit the COMMIT WORK statement from the definition.

Tell users to specify a COMMIT WORK statement explicitly whenever they perform the macro in ANSI session mode.

Users do not receive responses to individual statements contained in the macro body until all its requests have been completed successfully. Any object that is referenced by a request is locked until the macro transaction is completed, or until the macro is terminated because of a statement error.

If a macro contains a data definition statement, it cannot contain other requests. A data definition statement in a macro is not fully resolved until the macro is performed. At that time, unqualified references to database objects are resolved using the default database for the user submitting the EXECUTE statement. It is therefore recommended that object references in a macro data definition statement be fully qualified in the macro body.