Tpump Del Issue

Database
Enthusiast

Tpump Del Issue

Hi All ,

I am trying a very simple tpump del script which just deletes data for previous day .However it is giving me an error saying APPLY command is not recognied .I am not sure how to call a DML label once it is created.I tried following commented syntaxes but it did not work.

Can someone help me with correct syntax.

.DML LABEL DEL_TGT;

DEL FROM TPUMP_TEST_1

WHERE LOAD_DT=DATE-1;

 /*APPLY DEL_TGT; */

/* DEL_TGT; */

/*.APPLY DEL_TGT */

/* .DEL_TGT */

4 REPLIES
Enthusiast

Re: Tpump Del Issue

Is it neccessary to use file in tpump operations ?

Enthusiast

Re: Tpump Del Issue

APPLY is not a TPUMP command; it is an argument of the .IMPORT commmand. 

Since your DELETE statement does not reference any variables from an import file, it is not an appropriate application of TPUMP. You can submit it as regular SQL via BTEQ.

Enthusiast

Re: Tpump Del Issue

Actually its Real time data warehouse and we wanted table to be up 100% so we were looking at tpump to use row level lock and delete data.

I later tried passing an argument via file but for some reason it is not able to read the file and gies an error saying

'Access module error '35' received during 'read' operation

     on record number '0': 'EOF encountered before end of record'

'

File just has 1 valuews  which is 1

Enthusiast

Re: Tpump Del Issue

If you want to use row level locking, you will have to create a file of the primary keys of the records to be deleted and import that file into TPUMP. The delete will then be

.DML DelRecs;

Delete from tbl Where primarykey = :primarykey;

.IMPORT INFILE infile.dat

LAYOUT LAY1A

APPLY DMLRECS;