6.1 Teradata Parallel Transporter - DDL operator

DDL operator capabilities in brief:

• DDL operator is neither a consumer operator or producer operator. It does not read from the Data stream and does not even write to the Data stream.

• Normally DDL operator is used to perform set up operations prior to executing loads or export tasks.

DDL Operator Definition:

Following is the example of DDL operator definition:

DEFINE OPERATOR DDL_OPERATOR

DESCRIPTION 'Teradata PT DDL OPERATOR Sample'

TYPE DDL

ATTRIBUTES

(

VARCHAR TdpId,

VARCHAR UserName,

VARCHAR UserPassword,

VARCHAR ErrorList,

VARCHAR PrivateLogName

);

1st 3 of the attributes are security attributes.

And PrivatelogName specifies the name of the

Errorlist : This attribute specifies the specific error conditions to ignore.

We can use this when we drop in the script UV and Error tables. If the tables exist then the table will be dropped. But if the table does not exist the job might fail with errorcode 3807 . In such cases if we wish to ignore this error code we can write the attribute as

VARACHAR ERRORLIST = '3807'

Supported SQL Statements:

The DDL operator supports almost every SQL statement except statements that Return data to the operator, such as the SELECT, HELP, and SHOW statements or Require the operator to send data back to the Teradata Database.

The DDL operator does not support the USING clause with the INSERT, UPDATE, and DELETE DML

SQL statements.

Specifying DDL Statements in the Teradata PT APPLY Statement:

The following examples show how to specify DDL statements in the APPLY statement:

• One SQL statement per group

APPLY

'ddl stmt1',

'ddl stmt2',

.........

'ddl stmt3'

TO OPERATOR (operator specification)

• Multiple SQL statements in a group, but only one group

APPLY

('ddl stmt1','ddl stmt2', ... ,'ddl stmtn')

TO OPERATOR (operator specification)

• Multiple SQL statements per group, and multiple groups

APPLY

('ddl stmt11','ddl stmt12', ... ,'ddl stmt1n'),

.

.

.

('ddl stmtx1','ddl stmtx2', ... ,'ddl stmtxm')

TO OPERATOR (operator specification)

If more than one statement is specified in one group, then the DDL operator combines all statements into one single multistatement request and sends it to the Teradata Database as one implicit transaction. This means that any statement failure or any error rolls back the entire transaction.

We can group several SQL statements together to perform a desired logical database task and

still take advantages of the automatic rollback feature on Teradata Database in case of any

statement failures or any errors occurring during the transaction.

We can also have one SQL statement per group if you desire to execute each statement in its own transaction.

However note that when multiple SQL statements are specified as the part of one DML group in the APPLY statement, then if the group contains a DDL statement it must be the last statement in the implicit transaction, which means the last statement in a group.

Therefore, given that the information in parentheses (below) represents a group, the validity

of the statements can be determined as follows:

• Group 1: (DDL) is valid.

• Group 2: (DDL, DDL) is invalid because only one DDL statement is allowed.

• Group 3: (DML, DML, DDL) is valid.

• Group 4: (DML, DML, DML) is valid, even though the group contains no DDL statement.

• Group 5: (DML, DDL, DML) is invalid because the DDL statement is not the last statement in the group.

Check pointing with DDL operator:

The SQL statements are executed by groups in the order they are specified in the APPLY statement.
Hence the DDL operator can take a checkpoint after each group is executed.
A checkpoint, with respect to the DDL operator, marks the last group of DDL/DML SQL statements to execute successfully.

The DDL operator restarts at the beginning of the group of SQL statements whose execution

is interrupted by an abnormal termination. If the interrupted group has only one SQL

statement, the DDL operator restarts at that statement.

If the last request was successful prior to a restart, the operator can resume at the next request

in line. If the last request failed prior to a restart, then the operator resumes at the failed

request.

Ex:

APPLY

('DROP TABLE ' || @jobvar_wrk_dbname || '.ET_Trans;'),

('DROP TABLE ' || @jobvar_wrk_dbname || '.UV_Trans;'),

('DROP TABLE ' || @jobvar_tgt_dbname || '.Trans;'),

('CREATE TABLE ' || @jobvar_tgt_dbname

|| '.Trans (Account_Number VARCHAR(50),

Trans_Number VARCHAR(50),

Trans_Date VARCHAR(50),

Trans_ID VARCHAR(50),

Trans_Amount VARCHAR(50));')

TO OPERATOR (DDL_OPERATOR);

Each of the Statements above are in own group.

Say if the statement that drop UV table fails for some reason, when we restart the job it starts after the last successful group and hence drops the UV table.