Stream Driver Macro Support - Parallel Transporter

Stream Driver Macro Support

Teradata PT supports executing macros using the Stream driver. Adhering to Teradata Parallel Data Pump protocols that are documented in the Teradata Parallel Data Pump Reference, note the following guidelines:

The macro specified in the EXECUTE statement must reside on the RDBMS prior to the start of the job. For more information on creating a macro, see SQL Data Manipulation Language.

The database user defined for the Stream driver job must have the EXECUTE privilege for the macro specified in the EXECUTE statement.

The EXECUTE statement used in the DML group must follow the format documented in the Teradata Parallel Data Pump Reference. This is the EXECUTE or EXEC command followed by the name of the macros (the database can be specified using the dot notation database.macro) followed by one of the key words, depending on what kind of statement is executed by the macro.

The key words are:

INSERT

UPDATE

DELETE

UPSERT

The EXECUTE statement cannot include a parameter list. The parameter list is automatically generated by the Stream driver based on the provided schema.

The macro must have one or more parameters. The Stream driver does not support executing macros that do not have parameters.

Executing a macro in a Stream driver job

1 Add the EXECUTE statement to a DML group. For guidelines, see “Stream Driver Macro Support” on page 148.

2 Define the schema based on the macro’s parameter list.

3 Add the DML group and Schema to the Connection object.

4 Set the DML group containing the EXECUTE statement using the UseDMLGroups function.

5 Call the PutRow function. The row data provided to PutRow will be used to fill out the macro’s parameter list and the macro will be executed.

6 Call the End Acquisition function.

7 Terminate the Connection object.

Code Example

The following macro was defined prior to execution:

create macro ins_row
(

    p1 integer,

    p2 varchar(32000)

)as(

   insert into table1
   (col1,col2)
   values(:p1,:p2);

);

For the Stream driver, add this EXECUTE statement to the DML group:

EXECUTE ins_row INSERT;

And this schema is built:

Schema * strSchema=new Schema(“input”);

strSchema->AddColumn(“col1”,TD_INTEGER,4);

strSchema->AddColumn(“col2”,TD_VARCHAR, 32000);

strConn->AddSchema(strSchema);