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.