Function of REPLACE MACRO Requests
REPLACE MACRO runs as a DROP MACRO request followed by a CREATE MACRO request except for the handling of privileges. Vantage retains all of the privileges that were granted directly on the original macro.
If the specified macro does not exist, a REPLACE MACRO request creates it. The REPLACE request has the same effect as a CREATE MACRO request.
If an error occurs during the replacement of the macro, the existing macro remains in place as it was before the performance of the REPLACE MACRO request (it is not dropped). This is analogous to a ROLLBACK on the operation.
Rules for Using Parameters in Macros
- You cannot pass the names of database objects to a macro as parameters.
This refers to tables and views and their columns, databases, users, and similar database objects.
You can use dynamic SQL within a stored procedure. See Using Dynamic SQL in Stored Procedures.
- Use of parameters is optional in a macro and, when required, must be specified as part of the CREATE MACRO or REPLACE MACRO request.
- In defining parameters for a macro, follow the macro name in your CREATE/REPLACE MACRO request with the names and attributes of the appropriate parameters. Data type definitions are required for each parameter. Other attributes can include format specifications or default values. Define new formats using a FORMAT phrase and defaults by a default control phrase, as necessary.
- If you specify them where either an ordinal positional integer number or an expression is valid, such as an ordinary grouping set in a GROUP BY clause or the ordering specification for an ORDER BY clause, Vantage treats parameters referenced by requests that are contained within a macro as expressions, not as constant literals.
Therefore, Vantage does not apply such parameter expressions as constant literals, if it applies them at all. Instead, to specify grouping and ordering specifications explicitly within the body of the macro. See GROUP BY Clause and ORDER BY Clause.
- If you supply a parameter value in the EXEC request that does not conform to the specified format, data type, or default value, the request aborts and returns a message to the requestor.
- The maximum number of parameters you can specify per macro is 2,048.
- If you specify a query band by means of a macro parameter, it must be a transaction query band.
If you submit a CREATE MACRO or RENAME MACRO request that uses a parameter to specify a session query band, the request aborts and the system returns a message to the requestor.
Using the ASTERISK (*) Character in Macros
A macro defined using the ASTERISK (*) character is bound to the definitions of any tables it references as they were defined at the time the macro was created or replaced.
For example, consider the following macro:
CREATE MACRO get_emp AS ( SELECT * FROM employee;)
If a column is later added to or removed from the employee table, the following statement still returns the number of columns that existed in employee when get_emp was defined:
EXEC get_emp;
If columns have been dropped or data types have been changed, performing the macro can cause an error message or unexpected behavior.
Macros and Tactical Queries
Macros can be an effective method of performing tactical queries. The pros and cons of using macros to perform tactical queries in applications and application workloads are described in detail elsewhere; see Application Opportunities for Tactical Queries for further information about using macros to perform tactical queries.
Limit on Request Size with CREATE MACRO and REPLACE MACRO
The maximum size of the fully expanded text for a macro is 2 MB.
Because expansions of source text in macro definitions are made to fully qualify object names and to normalize expressions, it is possible for a macro to be defined but also to be unusable because of stack overflows in the Syntaxer at performance time. The system returns an error.
Restriction on External Authorization from a Macro
You cannot specify CREATE AUTHORIZATION in a macro because the authorization password is not saved in either encrypted or unencrypted form, compromising the security of the OS logon user ID. See CREATE AUTHORIZATION and REPLACE AUTHORIZATION.
Otherwise, the system returns an error to the requestor.