Use of TPT Script having (IGNORE DUPLICATE UPDATE ROWS,INSERT FOR MISSING UPDATE ROWS,IGNORE MISSING UPDATE ROWS)

Tools & Utilities
Enthusiast

Use of TPT Script having (IGNORE DUPLICATE UPDATE ROWS,INSERT FOR MISSING UPDATE ROWS,IGNORE MISSING UPDATE ROWS)

Hi All,

I have below mentioned Script . I am not completely clear with the use of (IGNORE DUPLICATE UPDATE ROWS,INSERT FOR MISSING UPDATE ROWS,IGNORE MISSING UPDATE ROWS) in the Script could any 1 help me with the same.

/**************************************************************/

/* */

/* Teradata Parallel Transporter */

/* User Guide - Job Example 02A */

/* */

/**************************************************************/

/* */

/* Description: */

/* */

/* This job example uses multiple instances of the */

/* DataConnector Operator to extract rows from a file and */

/* uses multiple instances of the Update Operator to perform */

/* upserts against two tables. */

/* */

/**************************************************************/

DEFINE JOB FILE_UPDATE

DESCRIPTION 'Load 2 TD tables from a file'

(

DEFINE SCHEMA Trans_n_Accts_Schema

(

Account_Number VARCHAR(50),

Number VARCHAR(50),

Street VARCHAR(50),

City VARCHAR(50),

State VARCHAR(50),

Zip_Code VARCHAR(50),

Balance_Forward VARCHAR(50),

Balance_Current VARCHAR(50),

Trans_Number VARCHAR(50),

Trans_Date VARCHAR(50),

Trans_ID VARCHAR(50),

Trans_Amount VARCHAR(50)

);

DEFINE OPERATOR FILE_READER

TYPE DATACONNECTOR PRODUCER

SCHEMA Trans_n_Accts_Schema

ATTRIBUTES

(

VARCHAR PrivateLogName = 'dataconnector_log',

VARCHAR DirectoryPath = @jobvar_datafiles_path,

VARCHAR FileName = 'accounts.txt',

VARCHAR Format = 'Delimited',

VARCHAR OpenMode = 'Read',

VARCHAR TextDelimiter = '|'

);

DEFINE OPERATOR UPDATE_OPERATOR

TYPE UPDATE

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'update_log',

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

VARCHAR LogTable = @jobvar_wrk_dbname ||

'.LG_Trans_n_Accts',

VARCHAR ARRAY TargetTable = [@jobvar_wrk_dbname || '.Trans',

@jobvar_wrk_dbname || '.Accounts'],

VARCHAR ARRAY ErrorTable1 = [@jobvar_wrk_dbname || '.ET_Trans',

@jobvar_wrk_dbname || '.ET_Accts'],

VARCHAR ARRAY ErrorTable2 = [@jobvar_wrk_dbname || '.UV_Trans',

@jobvar_wrk_dbname || '.UV_Accts']

);

APPLY

('INSERT INTO ' || @jobvar_tgt_dbname

|| '.Trans(Trans_Number,

Trans_Date,

Account_Number,

Trans_ID,

Trans_Amount)

VALUES(:Trans_Number,

:Trans_Date,

:Account_Number,

:Trans_ID,

:Trans_Amount);'),

('UPDATE ' || @jobvar_tgt_dbname

|| '.Accounts SET Number = :Number,

Street = :Street,

City = :City,

State = :State,

Zip_Code = :Zip_Code,

Balance_Forward = :Balance_Forward,

Balance_Current = :Balance_Current

WHERE Account_Number = :Account_Number;',

'INSERT INTO ' || @jobvar_tgt_dbname

|| '.Accounts (Account_Number,

Number,

Street,

City,

State,

Zip_Code,

Balance_Forward,

Balance_Current)

VALUES (:Account_Number,

:Number,

:Street,

:City,

:State,

:Zip_Code,

:Balance_Forward,

:Balance_Current);')

IGNORE DUPLICATE UPDATE ROWS

INSERT FOR MISSING UPDATE ROWS

IGNORE MISSING UPDATE ROWS

TO OPERATOR (UPDATE_OPERATOR[2])

SELECT * FROM OPERATOR (FILE_READER[2]);

);

Tags (1)