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

Tools
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)