16.10 - Using User-Created (Predefined) Macros - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
16.10
Published
July 2017
Content Type
Programming Reference
Publication ID
B035-2436-077K
Language
English (United States)

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 Teradata 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 Chapter 2 in this manual.

For more information about creating a macro, see SQL Data Definition Language (B035-1184).

For more information about executing a macro, see 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 rules for user-created macros are:

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