Multiple SQL statements within a macro are performed as a single request. The macro code can also specify parameters that are replaced by data each time the macro is performed. The most common way of substituting for the parameters is to specify a USING request modifier.
For repetitive queries, macros are typically more efficient than single DML statements. Unlike procedures, which return parameters to the requestor, macros return only the answer set for their SQL statements.
Macros have the following advantages:
- Network and channel traffic are reduced.
- Run plans can be cached, reducing parsing engine overhead.
- Macros ensure the efficient performance of their component SQL statements.
- Reusable database components save client resources.
- Macros can be used to force data integrity and locking modifiers.
- Macros can be used to enforce security.