TPT Bulk Delete

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Enthusiast

TPT Bulk Delete

Hello,

I'm attempting to use TPT Update to perform a MLOAD DELETE.  I found the Example 17 in the TPT Users guide and in the /opt/teradata/client/14.10/tbuild/sample/userguide/17 path on the teradata node where the tool is installed.  I was able to get this script to delete the one row by hard coding a single key value as shown.

USING CHARACTER SET ASCII
DEFINE JOB purge_data_idw_sht_tkt
DESCRIPTION 'Deletes data from the IDW_SHT.TKT table, after it has been archived off of the system.'
(
DEFINE OPERATOR UPDATE_OPERATOR
TYPE UPDATE STANDALONE
ATTRIBUTES
(
VARCHAR UserName = 'tptarchive',
VARCHAR UserPassword = 'TPTu123#',
VARCHAR TdpId = '192.168.105.130',
VARCHAR PrivateLogName = 'del_tkt_sht_log',
VARCHAR targettable = 'dba_ap.tkt_sht',
VARCHAR logtable = 'dba_ap.DeleteTask_LG',
VARCHAR errortable1 = 'dba_ap.DeleteTask_ET',
VARCHAR errortable2 = 'dba_ap.DeleteTask_UV',
VARCHAR deletetask = 'Y'
);

APPLY
('DELETE FROM DBA_AP.TKT_SHT WHERE TKT_ID = 1812240121600807776;')
TO OPERATOR (UPDATE_OPERATOR);
);

What I really want to do is something like this, where I delete a number of rows based on the TKT_ID values in a driver table.  Unfortunately, I'm receiving an error that says the MLOAD script is invalid.  I found something about you cannot reference more than one table in an MLOAD DELETE, so I guess my subselect is the problem. 

Does anyone have any suggestions on how to accomplish this is TPT?

I'm really attempting to use the TPT tool as it's the future but it has been quite a challenge for me. :(


DEFINE OPERATOR UPDATE_OPERATOR
TYPE UPDATE
SCHEMA *
ATTRIBUTES
(
UserName = 'tptarc',
UserPassword = 'sdfsdfdf',
TdpId = '192.168.999.130'
,PrivateLogName = 'del_tkt_sht_log'

,targettable = 'dba_ap.tkt_sht'
,logtable = 'DeleteTask_log'
,deletetask = 'Y'
);

APPLY
(
'DELETE FROM  DBA_AP.TKT_SHT  WHERE  TKT_ID IN  (SEL TKT_ID  FROM   DBA_AP.ARC_PRG_DRVR_TKT_SHT)  ;'
)
TO OPERATOR ( UPDATE_OPERATOR[1] );
);

Many Thanks,

              Mike


Accepted Solutions
Teradata Employee

Re: TPT Bulk Delete

Despite the name, the "DDL Operator" can be used to execute any SQL statements that neither consume input nor produce output. This is explained reasonably well in the User Guide but perhaps could be spelled out more clearly in the Reference manual.

 

To do a DELETE with STREAM, the APPLY would simply have a parameterized DML statement such as 'DELETE FROM myTable WHERE key_col=:schema_field;'

By default you'll get a warning logged if no rows or more than one row is deleted per statement execution; you can append the IGNORE MISSING DELETE ROWS and/or IGNORE EXTRA DELETE ROWS option if you want to eliminate those.

 

1 ACCEPTED SOLUTION
8 REPLIES
Teradata Employee

Re: TPT Bulk Delete

You can't have a SELECT (even a sub-select) within an UPDATE operator DML statement.

 

Why not just use the TPT DDL operator or BTEQ to execute this as a SQL DELETE instead?

 

Enthusiast

Re: TPT Bulk Delete

Thanks for your replay Fred.  I was hoping to utilize the bulk delete as this will be a monthly purge process and be removing a decent amount of rows from each table.

-Mike

Teradata Employee

Re: TPT Bulk Delete

A "bulk delete" via MLOAD protocol may perform better than SQL delete if you have highly non-unique NUSIs - but you probably shouldn't have those anyway.

Otherwise, SQL delete is simpler, more flexible, and likely performs better.

 

And if you are deleting a large percentage of the rows (not just a large number), then inserting the rows to keep to a new table may perform better than delete.

 

 

 

Enthusiast

Re: TPT Bulk Delete

Thanks Fred, I'll give that a try.  As I mentioned, I'm new to TPT, based on this table I found in the documentation, the UPDATE or STREAM operators are the only two that are used to Delete rows.  I don't see an example in the TPT manual of a STREAM Delete though.  Any example you could direct me to would be much appreciated.

Thanks,

        Mike

Teradata Employee

Re: TPT Bulk Delete

Despite the name, the "DDL Operator" can be used to execute any SQL statements that neither consume input nor produce output. This is explained reasonably well in the User Guide but perhaps could be spelled out more clearly in the Reference manual.

 

To do a DELETE with STREAM, the APPLY would simply have a parameterized DML statement such as 'DELETE FROM myTable WHERE key_col=:schema_field;'

By default you'll get a warning logged if no rows or more than one row is deleted per statement execution; you can append the IGNORE MISSING DELETE ROWS and/or IGNORE EXTRA DELETE ROWS option if you want to eliminate those.

 

Enthusiast

Re: TPT Bulk Delete

The DDL OPERATOR worked like a charm.  Thanks for your help Fred!!

Enthusiast

Re: TPT Bulk Delete

Fred,

Quick follow-up question.  I know with the FastExport utility we can specify the mlscript option to have it also generate an MLOAD script to reload the same table that we are FastExporting. 

.logtable myuser.fexp_log;
.logon tdp0/myuser;

.begin export;

.export outfile export.txt  mlscript  export.mload;
select * from db1.carrier;

.end export;
.logoff;

Then to execute this FEXP:   fexp < get_tktids > gettkts.flog

Can you tell me if there is a similar option when running a TPT Export job?  My TPT Export is using the EXPORT operator and then the DATACONNECTOR CONSUMER to eventually write a delimited file.

Thanks for your help!

                    Mike

Highlighted
Teradata Employee

Re: TPT Bulk Delete

No, there is no MLSCRIPT equivalent in TPT.

 

But if you are copying from one Teradata system to another, you can have the EXPORT and LOAD or UPDATE operators in the same TPT job.  See the User Guide for some examples.