Teradata Learning - Parameterized MACRO's (02/23/2013)

• Macros are nothing but pre-written SQL (select, insert, update) which is stored in DBC.


• Macros are Teradata Extension.


• To execute macro we need EXEC privilege over the macro. Its very imp to note that if a user has access on macro, then it does not matter whether or not he has access over the underlying tables or views that macro uses. .i.e. if you don’t have access over a table, but if there is macro that reads from this table and you have access over this macro then you can still read from that table.


• Macro's can be executed from any of viable SQL front end:


• SQL assistant(QueryMan)
• BTEQ
• Preprocessor
• CLI
• Another MACRO( note that macro can be called from another macro)


• MACRO's can be parameterized which allow values to be passed at run time.
Following the MACRO name we write the parameter list. In the parameter list we write each parameter followed by its data type. When we use this parameter in the macro body we precede it with a colon.


Example 1: The Below macro will calculate the current perm space used by the table name we pass as parameter. It takes two parameters, database name and table name as inputs.


create macro MY_SUPPORT_MACROS.CAL_SPACE
( DATABASENAME VARCHAR(30),TABLENAME VARCHAR(30)) --> Parameter List
AS
(select sum(currentperm) as currentperm_in_kb from DBC.tablesize where
DATABASENAME =:DATABASENAME --> using parameter with :(colon)
AND
TABLENAME = :TABLENAME; --> Note this semicolon. Inner query should have its own semicolon.
);


Executing a macro:


EXEC MY_SUPPORT_MACROS.CAL_SPACE('REFERENCE','MY_TEST_TABLE'); --> Passing Parameters to macro
Note that when we execute a macro we don’t use the  word 'MACRO'.


Example 2:


CREATE MACRO MY_SUPPORT_MACROS.SEARCH_TABLE
(tablename VARCHAR(40)) AS(
select * from DBC.tables where tablename like :tablename || '%' ;)
;


EXEC  SUPPORT_MACROS.SEARCH_TABLE('TCCR');


• The order of parameters is important .The first value in the EXEC of the macro will be associated with the first value in the parameter list. The second value in the EXEC is associated with the second value in the parameter list, and so on.


• Passing parameters by explicitly naming them.


We can explicitly name the parameters while passing them to MACRO. When this option is used we can pass parameters in any sequence.


In case we have given a default value for any of the parameter while defining the macro, we can simply omit that parameter.


Example:


EXEC MY_SUPPORT_MACROS.CAL_SPACE(TABLENAME='MY_TEST_TABLE',DATABASENAME='REFERENCE');


Note that the sequence of the parameters passed is different in the above example.




• Defaulting a parameter value :


Example:


create macro MY_SUPPORT_MACROS.CAL_SPACE
( DATABASENAME VARCHAR(30) DEFAULT 'MY_TEST_DB' --> if we don’t provide any value for database name it will pick up 'MY_TEST_DB' as the default database name
,TABLENAME VARCHAR(30))
AS
(select sum(currentperm) as currentperm_in_kb from DBC.tablesize where
DATABASENAME =:DATABASENAME
AND
TABLENAME = :TABLENAME;
);




• Passing NULL values to a MACRO:


To pass NULL value we can use the keyword 'NULL'
OR
Use positional comma's to implicitly pass null.


EXEC MY_TEST_MACRO('value1',NULL);


OR


EXEC MY_TEST_MARCO2('value1',,'value3');




• Performance Effect of using MACRO:


When using parameterized macro's, we can execute the same macro by changing the parameter multiple times. Each time the body of the query does not change. Only the value being passed changes.


This allows to reuse the same cached steps. Thus it helps improve performance.