Teradata Macros are used to store sql statements that are frequently used, this eliminates the need for users to retype the SQL when needed.

Teradata Macros

Macros are used to store sql statements that are frequently used, this eliminates the need for users to retype the SQL when needed. Macro may have numerous SELECT statements and DML's but it can have only one DDL statement and that too must be the single statement.
As macro executes exactly same SQL (may be with different parameters) every time, it is probable that the execution plan might be present in 'Request-To-Step' cache(Plan library). Cached plans reduces performance time because parsing and optimizing is not required again when cached requests are repeated.

Macro characteristics: Syntax 1: Macro Block
REPLACE MACRO [database_name.macroname] [(input_paramter datatype[,..])]
AS
(
    [INSERT ...;]
    [UPDATE ...;] 
    [DELETE ...;] 
    [SELECT ... [WHERE column_name=:input_paramter] ;]
    [DROP/CREATE ...;]
);
 
Note: 1.Parameter name must be preceded by colon(:) in order to be recognized by optimizer as argument.
       2.REPLACE command will create MACRO if its already not present.

Syntax 2: Calling Macro
EXECUTE [database_name.macroname] [(parameters)];

EXEC [database_name.macroname] [(parameters)];

Syntax 3: Compiling Macro
.RUN FILE [macro_file];

Syntax 4: Get Macro Code
SHOW MACRO [database_name.macroname];

Syntax 5: Drop Macro
DROP MACRO [database_name.macroname];


Example 1: Sample Non-parameterized Macro
REPLACE MACRO tutorial_user.sample_macro_non_param
AS
(
   UPDATE tutorial_db.employee
   SET commission = COALESCE(commission,0) + 250;
   
   LOCK ROW FOR ACCESS SELECT emp_no,salary,commission from tutorial_db.employee;
);
Syntax: Executing Macro
execute tutorial_user.sample_macro_non_param;
 
exec  tutorial_user.sample_macro_non_param;


Example 2: Sample Parameterized Macro
REPLACE MACRO tutorial_user.sample_macro_param(comm int)
AS
(
   LOCK ROW FOR ACCESS SELECT emp_no,salary,commission from tutorial_db.employee;

   UPDATE tutorial_db.employee
   SET commission = COALESCE(commission,0) + :comm;
   
   LOCK ROW FOR ACCESS SELECT emp_no,salary,commission from tutorial_db.employee;
);
Syntax: Executing Parameterized Macro without argument
execute tutorial_user.sample_macro_param;
Output: *** Failure 3820 The macro 'sample_macro_param' requires parameters.

execute tutorial_user.sample_macro_param(25);
Output:Returned resultset & updated records


Example 3: Checking if DDL can be added in Macro
REPLACE MACRO tutorial_user.sample_macro
AS
(
   COLLECT STATS ON  tutorial_db.employee COLUMN(EMP_NO); 
);

Compiling Output: *** Macro has been replaced.


Example 4: Checking if DDL can be added along with DMLs in Macro
REPLACE MACRO tutorial_user.sample_macro
AS
(
   UPDATE tutorial_db.employee
   SET commission = COALESCE(commission,0) + 250;
   LOCK ROW FOR ACCESS SELECT emp_no,salary,commission from tutorial_db.employee;
   DROP TABLE tutorial_db.employee;
);

Compiling Output: *** Failure 3576 Data definition not valid unless solitary.


Example 5: Checking if multiple DDLs can be added together in Macro
REPLACE MACRO tutorial_user.sample_macro
AS
(
   COLLECT STATS ON  tutorial_db.employee COLUMN(EMP_NO); 
   DROP TABLE tutorial_db.employee;
);

Compiling Output: *** Failure 3576 Data definition not valid unless solitary.


Example 6: Formating a query using ECHO
REPLACE MACRO tutorial_user.sample_macro
AS
(
   ECHO '.set separator "#"';
   ECHO '.set TITLEDASHES OFF';
   LOCK ROW FOR ACCESS SELECT emp_no,salary,commission from tutorial_db.employee;
);


Nested Macros: Even though, Teradata now allows nesting of macros but all the commands in main macro and nested macros will be treated as single transaction and if one fail that means main macro will fail.