Generic TPT script with update operator

Tools & Utilities
Highlighted

Generic TPT script with update operator

Hi Team,

 

I have a requirement to copy data from one TD server to another.

Based on volume of data we have categorized tables either to use Fastload (TPT- load operator )  or MultiLoad(TPT - update operator).

We have created generic Fastload (TPT- load operator ), but facing issues for MultiLoad(TPT - update operator).

 

Do we have any template for generic MultiLoad(TPT - update operator)?

3 REPLIES
Teradata Employee

Re: Generic TPT script with update operator

Seems a little odd to choose between LOAD and UPDATE based on volume; normally that choice is based on capabilities / restrictions. Low volume would more often suggest a switch to STREAM or even SQL INSERTER.

 

That said, have you looked at the TPT Users Guide and the corresponding examples in the <install-directory >/sample/userguide directory? Also review the use of operator templates in the  "Advanced Scripting Strategies" chapter.

Teradata Employee

Re: Generic TPT script with update operator

We could probably offer more assistance if we were told (at a minimum):

1. version of TPT

2. platform

3. more details about the "issues" you are having (screenshot might even help)

 

-- SteveF

Re: Generic TPT script with update operator

1. version of TPT  

        Teradata 15

2. platform

       from TD on premise to cloud

3. more details about the "issues" you are having (screenshot might even help)

       I have created one TPT script using update operator to perfom full extract and then do update then insert. The script is working fine. But I have 200 odd tables, so I'm looking for a generic  TPT script for update operator and the input to that script will be parameter file which will have SCHEMA, SELECT statement, Update statement and Insert statement. Below is the script which I have created for a table.

 

 

DEFINE JOB MOVE_TABLE
DESCRIPTION 'MOVE **** table to DR'
(
/******************************************
* Define TPT export operator file output *
* information. *
*****************************************/


DEFINE SCHEMA <tablename>_Schema
DESCRIPTION '<tablename> SCHEMA'
(
<column 1 details>
<column 2 details>
<column 3 details>
);


DEFINE OPERATOR EXPORT_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
TYPE EXPORT
SCHEMA <tablename>_Schema
ATTRIBUTES
(
VARCHAR PrivateLogName = '/exportoper_privatelog',
INTEGER MaxSessions = 8,
INTEGER MinSessions,
VARCHAR TdpId = '<server name1>',
VARCHAR UserName = '<user name1>',
VARCHAR UserPassword = '<password1>',
VARCHAR WorkingDatabase = '<database_name1>',
VARCHAR SelectStmt = 'SELECT Column 1,Column 2,Column 3, FROM <tablename>;'
);

DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
(
VARCHAR PRIVATELOGNAME = '/ddloperator__privatelog',
VARCHAR TdpId = '<server name2>',
VARCHAR UserName = '<user name2>',
VARCHAR UserPassword = '<password2>',
VARCHAR WorkingDatabase = '<database_name2>',
VARCHAR ErrorList = '3807'
);

DEFINE OPERATOR UPDATE_OPERATOR
DESCRIPTION 'Teradata PT UPDATE OPERATOR'
TYPE UPDATE
SCHEMA <tablename>_Schema
ATTRIBUTES
(
VARCHAR PrivateLogName = '/loadoper_privatelog',
INTEGER MaxSessions = 16,
INTEGER MinSessions =4,
VARCHAR TargetTable = '<tablename>',
VARCHAR TdpId = '<server name2>',
VARCHAR UserName = '<user name2>',
VARCHAR UserPassword = '<password2>',
VARCHAR WorkingDatabase = '<database_name2>',
VARCHAR ErrorTable1 = 'ET1_<tablename>',
VARCHAR ErrorTable2 = 'UV1_<tablename>',
VARCHAR LogTable = 'LT1_<tablename>'
);

/*** Apply Statement ***/

STEP stSetup_Tables
(
APPLY
('DROP TABLE ET1_<tablename>;'),
('DROP TABLE UV1_<tablename>;'),
('DROP TABLE LT1_<tablename>;')
TO OPERATOR (DDL_OPERATOR);

);


STEP upd_data
(
APPLY

('UPDATE <tablename>
SET Column1=:Column1,
Column2=:Column2,
WHERE Column3 = :Column3 ;',


'INSERT INTO <tablename>(:Column1,:Column2,:Column3);'
)
INSERT FOR MISSING UPDATE ROWS
IGNORE MISSING UPDATE ROWS
TO OPERATOR (UPDATE_OPERATOR [2])

SELECT * FROM OPERATOR (EXPORT_OPERATOR [2]);
);

 

);