Running a Macro - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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.