Macros Teradata

Compatibility: Teradata Extension

Macros are SQL statements stored as an object in the Data Dictionary (DD). Unlike a view, a macro can store one or multiple SQL statements. Additionally, the SQL is not restricted to only SELECT operations. INSERT, UPDATE, and DELETE commands are valid within a macro. When using BTEQ, conditional logic and BTEQ commands may also be incorporated into the macro.

The use of macros provide the benefits, listed below:

  • Stored in the DD
    • Can be shared by multiple users
    • SQL is stored in Teradata and not sent across the network or channel
  • Can be secured to keep users from accessing them
  • Provide the access security to tables
  • All updates within a macro are considered a transaction
    • If all steps work, all work is committed
    • If a single step fails, all the updated rows are automatically rolled back (undone) to their original values prior to the macro executing
  • Parameters can be dynamically passed to them for added flexibility

Data Definition Language (DDL) is used to create, delete or modify a macro. The main restriction is that all objects in a database must have unique names. Additionally, since Teradata is case blind, names like Mymacro and mymacro are identical.

Although a macro can have multiple SQL statements within it, if a macro contains DDL, it must be the last statement in the macro. The reason for this is based on the transactional nature of a macro. Since DDL locks one or more rows within the DD and this could prevent user access to the DD, it is desirable to release these locks as soon as possible. Therefore, a macro's DDL transaction needs to finish quickly. Hence, you can only have one DDL statement within a macro.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics