Types of Macro Support - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Date Strings

Date strings in a macro are validated when the macro runs. The format of date strings validate against a DATE column format or the default DATE format, both of which can change after creating the macro.

To guarantee that dates are properly validated, create or replace your macros by doing either or both of the following:
  • Specify dates as ANSI date literals instead of strings.

    ANSI date literals are accepted as dates for any date operation.

  • Specify the date format you want in a FORMAT phrase in the macro.

Global Temporary and Volatile Tables

Volatile tables are only supported on the Block File System on the primary cluster. They are not available for the Object File System.

You can reference both global temporary tables and volatile tables from a macro.

When you run a macro that involves a global temporary table, references to the base temporary table are mapped to the corresponding materialized global temporary table within the current session. Macro results depend on the content of the materialized global temporary tables.

When you perform a macro that contains an INSERT request that inserts rows into a global temporary table, and that table is not materialized in the current session, then an instance of the global temporary table is created when the INSERT request performs.

When you perform a macro that contains a CREATE VOLATILE TABLE request, then the referenced table is created in the current session. If the macro includes a DROP on the named volatile table, then that table is dropped from the current session.

Large Object Data Types

You can use BLOB and CLOB columns in macros if they do not violate the restrictions on large object use with SQL.

CLOB LATIN/UTF16 is only supported on the Block File System on the primary cluster. It is not available for the Object File System.
These restrictions are named in the following bulleted list:
  • You cannot create a base table with more than 32 LOB columns, nor can you alter a base table to have more than 32 LOB columns.
  • You can increase the maximum size of a LOB column up to the system limit, but you cannot decrease the maximum size of an existing LOB column.
  • LOB columns can only carry the following attributes:
    • NULL
    • NOT NULL
    • TITLE
    • FORMAT
  • LOB columns cannot be a component of any index.
  • A base table containing LOB columns must have at least one non-LOB column to act as its primary index.
  • The first column specified in a base table definition cannot be a LOB unless a uniqueness constraint is defined on a non-LOB column set in that table.
  • The uniqueness of a SET base table must be defined on the basis its non-LOB column set because LOB columns cannot be used to specify row uniqueness.
  • You cannot define integrity constraints on LOB columns, nor can you define integrity constraints that reference LOB columns.
  • LOB columns can be components of a view subject to the restrictions provided in this list and the semantics of view definitions.
  • LOB columns can be parameters of a user-defined function.

For information about large object data types, see BLOB Data Type and CLOB Data Type.

User Defined Functions

You can invoke both external and SQL UDFs from any SQL request encapsulated within a macro definition if you follow the rules for invoking UDFs from the statement.

Query Bands

Macro support for the SET QUERY_BAND statement is different depending on whether the Query Band is set for a session or a transaction:
  • SET QUERY_BAND … FOR SESSION is supported in the same way that other DDL statements are.

    You cannot set a session query band using a macro parameter. Otherwise, the system returns an error to the requestor.

  • SET QUERY_BAND … FOR TRANSACTION cannot be the only statement in a macro.

    You can set a transaction query band using a macro parameter, including the QUESTION MARK parameter.

Query Bands in Proxy Connections

The following rules apply to macro support for query bands in proxy connections.
  • Once a macro has been created, its immediate owner is its containing database or user, not the user who created it. The immediately owning database or user must have all the appropriate privileges for executing the macro, including WITH GRANT OPTION.
  • The CONNECT THROUGH privilege for a SET_QUERYBAND with a PROXYUSER in a macro is validated against the trusted user when the macro runs.

Workload Analysis Statements

The following workload analysis statements are not supported for macros. If you try to run a macro that contains any of these statements, the system returns an error to the requestor.
  • COLLECT DEMOGRAPHICS
  • INITIATE INDEX ANALYSIS
  • INITIATE PARTITION ANALYSIS
  • RESTART INDEX ANALYSIS