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.
- 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
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.
- 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.
User Defined Types
Macros support both distinct and structured UDTs and Period data types, which are a special form of UDT.
- UDT parameters
- UDT expressions
Query Bands
- 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
- 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
- COLLECT DEMOGRAPHICS
- INITIATE INDEX ANALYSIS
- INITIATE PARTITION ANALYSIS
- RESTART INDEX ANALYSIS