Copy Macros | Teradata Data Mover - About Copying Macros - Teradata Data Mover

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
17.10
Published
June 2021
Language
English (United States)
Last Update
2021-06-16
dita:mapPath
fcu1619423186706.ditamap
dita:ditavalPath
mpm1591127278842.ditaval
dita:id
B035-4101
lifecycle
previous
Product Category
Analytical Ecosystem
A macro consists of one or more SQL statements that can be run by performing a single request. Each time the macro is executed, one or more rows of data are returned. For example, a macro could be created with following SQL statements:
CREATE MACRO Empinfo
AS (
	SELECT EmplId (TITLE 'Id')
	,LastName	 (TITLE 'Name')
	,City	 (TITLE 'City')
	FROM employee
	ORDER BY 2;
	);
After creation, the macro definition can be retrieved by executing a show macro query on the macro.
SHOW MACRO Empinfo;

Copying Macros using Data Mover

Data Mover copies macros as follows:

  • Data Mover retrieves the macro creation DDL by executing a show macro dbname.macroname SQL statement.
  • Data Mover does not parse a create macro DDL; the exact same DDL is run on the target system. Data Mover does not relocate or rename macro objects.
  • The underlying table for the macro must already exist on the target system or be copied with the job.
  • If the macro name in the macro definition is not fully qualified, the macro is created in the database having the same name as the source macro database.
  • If the table names in the macro definition are not fully qualified, those tables must exist in the database having the same name as the source macro database to avoid an error.
  • If the object being copied does not exist on the target database, it is created on the target database. If the object does exist on the target database, the object is overwritten – unless the overwrite_existing_objects property is set to false. In that case, a create time error is generated.
  • If a macro is copied during a full database copy, it is copied with the database using DSA. It is not copied separately.