Teradata Macros

Pradeep

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:

Can be shared across multiple users and can be secured by providing execute access to required users.
Can be parameterized (arguments can be passed) or non-parameterized.
All statements within macro are processed as one transaction and if one fails that means others will be ROLLBACKED. This is the reason DDL statement must be the single statement inside macro(because DDL cannot be rollbacked).
May improve performance time.
Conditions(IF/ELSE) & iterations(FOR/WHILE) are not supported.
Allows nesting of macros (worked on TD15 & TD16)

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.

Macro that will be nested


REPLACE MACRO tutorial_user.sample_macro_Test
AS
(
   LOCK ROW FOR ACCESS SELECT emp_no,salary,commission from tutorial_db.employee;
);

Main Macro where other macro is nested



REPLACE MACRO tutorial_user.macro_nesting_Test(comm int)
AS
(
  exec tutorial_user.sample_macro_Test;
  
   UPDATE tutorial_db.employee
   SET commission = COALESCE(commission,0) + :comm;
   
  exec tutorial_user.sample_macro_Test;
);

Macro Output: All 3 commands within main macro have been executed


exec tutorial_user.macro_nesting_Test(10);

 *** Query completed. 14 rows found. 3 columns returned. 

     emp_no                salary            commission
-----------  --------------------  --------------------
    1000346               1300.00                536.00
    1000262               2450.00                536.00
    1000258               2850.00                536.00
    1000336                950.00                536.00
    1000294                800.00                536.00
    1000315               1500.00                536.00
    1000292               3000.00                536.00
    1000299               1600.00                836.00
    1000288               3000.00                536.00
    1000245               5000.00                536.00
    1000326               1100.00                536.00
    1000276               2975.00                536.00
    1000312               1250.00               1936.00
    1000310               1250.00               1036.00

 *** Update completed. 14 rows changed. 

 *** Query completed. 14 rows found. 3 columns returned. 
     emp_no                salary            commission
-----------  --------------------  --------------------
    1000346               1300.00                546.00
    1000262               2450.00                546.00
    1000258               2850.00                546.00
    1000336                950.00                546.00
    1000294                800.00                546.00
    1000315               1500.00                546.00
    1000292               3000.00                546.00
    1000299               1600.00                846.00
    1000288               3000.00                546.00
    1000245               5000.00                546.00
    1000326               1100.00                546.00
    1000276               2975.00                546.00
    1000312               1250.00               1946.00
    1000310               1250.00               1046.00

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

dbmstutorials.com

Teradata Macros