For greater efficiency, the Stream operator also supports the use of predefined macros, rather than creating macros from the actual DML statements. A predefined macro is created by the user and resides on the database before a TPT Stream job begins. The macro specifies the type of DML statement (INSERT, UPDATE, DELETE, or UPSERT) being handled by the macro.
When a predefined macro is used, the Stream operator uses this macro directly instead of creating another macro. The use of predefined macros allows the Stream operator to avoid the overhead of creating/dropping macros internally, and also to avoid modifying the data dictionary on the database during the job run. The user needs decide to remove or keep the macros after all rows are loaded. The EXECUTE MACRO privilege is required on the database where the macros are placed.
The Stream operator uses the EXECUTE command to support predefined macros. For more information on using predefined macros, refer to the EXECUTE statement in Object Definitions and the APPLY Statement.
For more information about creating a macro, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.
For more information about executing a macro, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
Using predefined macros saves time because the Stream operator does not need to create and drop new macros each time a Stream operator job is run.
- The Stream operator expects the parameter list for any macro to match the list specified by the schema.
- The macro should specify a single prime index operation: INSERT, UPDATE, DELETE, or UPSERT. The Stream operator reports an error if the macro contains more than one supported statement, as in the following example.
Here is an example of the use of predefined macro.
/*********************************************************************/ /* Description: Use the TPT Stream operator to load data into a */ /* table via a predefined macro. */ /*********************************************************************/ USING CHARACTER SET ASCII DEFINE JOB LOAD_TABLE_USING_STREAM_OPER DESCRIPTION 'LOAD TABLE USING STREAM OPERATOR' ( DEFINE SCHEMA EMPLOYEE_SCHEMA DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA' ( COL1 VARCHAR(5), COL2 VARCHAR(5) ); DEFINE OPERATOR DDL_OPERATOR DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR' TYPE DDL ATTRIBUTES ( VARCHAR TraceLevel = 'none', VARCHAR PrivateLogName = 'ddloper_log', VARCHAR TdpId = @MyTdpId, VARCHAR UserName = @MyUserName, VARCHAR UserPassword = @MyPassword, VARCHAR ErrorList = '3807' ); DEFINE OPERATOR STREAM_OPERATOR DESCRIPTION 'TERADATA PARALLEL TRANSPORTER STREAM OPERATOR' TYPE STREAM SCHEMA EMPLOYEE_SCHEMA ATTRIBUTES ( VARCHAR TraceLevel = 'none', VARCHAR PrivateLogName = 'streamoper_privatelog', VARCHAR TdpId = @MyTdpId, VARCHAR UserName = @MyUserName, VARCHAR UserPassword = @MyPassword, VARCHAR ErrorTable = 'STREAMOPER_ERRTABLE', VARCHAR LogTable = 'STREAMOPER_LOGTABLE', VARCHAR PackMaximum = 'Yes', INTEGER MaxSessions = 4, INTEGER MinSessions = 4 ); DEFINE OPERATOR FILE_READER DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR' TYPE DATACONNECTOR PRODUCER SCHEMA EMPLOYEE_SCHEMA ATTRIBUTES ( VARCHAR TraceLevel = 'none', VARCHAR PrivateLogName = 'dataconnoper_privatelog', VARCHAR FileName = 'VARDATAa', VARCHAR OpenMode = 'Read', VARCHAR Format = 'DELIMITED', VARCHAR Delimiters = '|' ); STEP step1_setup ( APPLY ('DROP TABLE STREAMOPER_ERRTABLE;'), ('DROP TABLE STREAMOPER_LOGTABLE;'), ('DROP TABLE STREAMTARGETTABLE'), ('CREATE TABLE STREAMTARGETTABLE, FALLBACK (COL1 VARCHAR(5), COL2 VARCHAR(5));'), ('REPLACE MACRO T1INSERT ( COL1 VARCHAR(5), COL2 VARCHAR(5) ) AS ( INSERT INTO STREAMTARGETTABLE VALUES (:COL1,:COL2); );') TO OPERATOR (DDL_OPERATOR); ); STEP step2_load_data ( APPLY ('EXEC T1INSERT INSERT;') TO OPERATOR (STREAM_OPERATOR [2]) SELECT * FROM OPERATOR (FILE_READER [1]); ); );