TPT Operator - Partitioned Table

Database
Enthusiast

TPT Operator - Partitioned Table

Hi Folks,

 

we have a scenario where in we are doing data replication from one server to another using TPT fastload, multiload and tstream utilities.

We are using TSTREAM to load for partitioned table. We are fetching incremental extract from source...then delete from target based on select and do plain insert.

While inserting we are getting deadlock issue.

 

Below is the generic TPT script.

DEFINE JOB MOVE_PARTITIONED_TBL_TABLE
DESCRIPTION 'MOVE PARTITIONED_TBL table to DR'
(

SET DB_NM=@DB_NM;

 

DEFINE SCHEMA TBL_SCHM FROM TABLE @DB_NM ||'.' || @TgtTbl;


DEFINE OPERATOR EXPORT_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
TYPE EXPORT
SCHEMA TBL_SCHM
ATTRIBUTES
(
VARCHAR PrivateLogName = '',
INTEGER MaxSessions = 8,
INTEGER MinSessions = 4,
VARCHAR TdpId = @SourceTdpId,
VARCHAR UserName = @SourceUserName,
VARCHAR UserPassword = @SourceUserPassword,
VARCHAR WorkingDatabase = @SourceWorkingDatabase,
VARCHAR SpoolMode = 'NoSpool',
VARCHAR SelectStmt = @SQL
);

DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
(
VARCHAR PRIVATELOGNAME = '',
VARCHAR TdpId = @TargetTdpId,
VARCHAR UserName = @TargetUserName,
VARCHAR UserPassword = @TargetUserPassword,
VARCHAR WorkingDatabase = @TargetWorkingDatabase,
VARCHAR ErrorList = '3807',
VARCHAR DeleteTask = 'Y'
);

DEFINE OPERATOR STREAM_OPERATOR
DESCRIPTION 'Teradata PT STREAM OPERATOR'
TYPE STREAM
SCHEMA TBL_SCHM
ALLOW LATENCY CHECK
ATTRIBUTES
(
VARCHAR PrivateLogName = '',
INTEGER ErrorLimit = 100,
INTEGER BufferSize = 64,
INTEGER TenacityHours = 2,
INTEGER TenacitySleep = 1,
INTEGER MaxSessions = 20,
INTEGER MinSessions = 1,
VARCHAR TraceLevel= 'none',
INTEGER Pack,
VARCHAR PackMaximum='Y',
VARCHAR TdpId = @TargetTdpId,
VARCHAR UserName = @TargetUserName,
VARCHAR UserPassword = @TargetUserPassword,
VARCHAR WorkingDatabase = @TargetWorkingDatabase,
VARCHAR ErrorDatabase =@ErrorDatabase,
VARCHAR WorkTable = @ErrorDatabase||'.' || @TgtTbl || '_WT',
VARCHAR TargetTable = @TgtTbl,
VARCHAR ErrorTable1 = @ErrorDatabase||'.' || @TgtTbl || '_ERR1',
VARCHAR ErrorTable2 = @ErrorDatabase||'.' || @TgtTbl || '_ERR2' ,
VARCHAR LogTable = @ErrorDatabase||'.' || @TgtTbl || '_LOG'
);

DEFINE OPERATOR UPDATE_OPERATOR
DESCRIPTION 'Teradata PT UPDATE OPERATOR'
TYPE UPDATE
SCHEMA TBL_SCHM
ATTRIBUTES
(
VARCHAR PrivateLogName = '',
INTEGER MaxSessions = 16,
INTEGER MinSessions =4,
VARCHAR TdpId = @TargetTdpId,
VARCHAR UserName = @TargetUserName,
VARCHAR UserPassword = @TargetUserPassword,
VARCHAR WorkingDatabase = @TargetWorkingDatabase,
VARCHAR ErrorDatabase =@ErrorDatabase,
VARCHAR WorkTable = @ErrorDatabase||'.' || @TgtTbl || '_WT',
VARCHAR TargetTable = @TgtTbl,
VARCHAR ErrorTable1 = @ErrorDatabase||'.' || @TgtTbl || '_ERR1',
VARCHAR ErrorTable2 = @ErrorDatabase||'.' || @TgtTbl || '_ERR2' ,
VARCHAR LogTable = @ErrorDatabase||'.' || @TgtTbl || '_LOG'

);

/*** Apply Statement ***/

STEP stSetup_Tables
(
APPLY
('DROP TABLE '||@ErrorDatabase ||'.'||@TgtTbl||'_ERR1;'),
('DROP TABLE '||@ErrorDatabase ||'.'||@TgtTbl||'_ERR2;'),
('DROP TABLE '||@ErrorDatabase ||'.'||@TgtTbl||'_LOG;')

TO OPERATOR (DDL_OPERATOR);
);

 

STEP Delete_Target
(
APPLY
(@del)
IGNORE MISSING DELETE ROWS
IGNORE EXTRA DELETE ROWS
TO OPERATOR (UPDATE_OPERATOR [1])
select * from OPERATOR (EXPORT_OPERATOR [1]);
);


STEP upd_data
(
APPLY

(
@ins
)


TO OPERATOR (STREAM_OPERATOR [1])

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

 

);

 

 

So my question is:

1. Which operator should we use for partitioned table with huge volume greater than 20 GB.

2. Any comments on the deadlock issue.


Accepted Solutions
Teradata Employee

Re: TPT Operator - Partitioned Table

To avoid deadlock with the STREAM operator, use the SERIALIZE ON option in the APPLY statement. (Specify any subset or all of the PI columns.)

Or for high volume, consider using UPDATE operator to do the inserts.

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: TPT Operator - Partitioned Table

To avoid deadlock with the STREAM operator, use the SERIALIZE ON option in the APPLY statement. (Specify any subset or all of the PI columns.)

Or for high volume, consider using UPDATE operator to do the inserts.

Enthusiast

Re: TPT Operator - Partitioned Table

Thank you Fred!!

Delete-Insert using Update operator is working for partitioned table with huge volume.

Will the Update operator work for Update-Insert on Partitioned tables with huge volume? Please suggest.

Highlighted
Teradata Employee

Re: TPT Operator - Partitioned Table

Yes. If you are deleting rows only to allow insert of an updated version and the PI isn't changing, then a single "upsert" with UPDATE operator would be better than separate DELETE and INSERT steps.