Using User-Created (Predefined) Macros - Parallel Transporter

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.

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]);
   );
 );