MLOAD DELETE vs MLOAD Import with Delete statement

[Miteshs]

Hi

When I try to run the MLOAD DELETE, it gives an error. The script uses an input data file. Here's the script.
MLOAD IMPORT works just fine with the import data file. Is import data file supported and what could be a possible workaround ?

**** 12:33:44 UTY1819 Warning: More than one record found in Delete task IMPORT file. Only the
first record will be used.

______________________________________________
.LOGTABLE TPCH.H_REGION_log;
/* .LOGON $SERVER/$USER,$PASSWORD; */
DROP TABLE TPCH.WT_H_REGION;
DROP TABLE TPCH.ET_H_REGION;
DROP TABLE TPCH.UV_H_REGION;
/* .BEGIN IMPORT MLOAD TABLES TPCH.H_REGION SESSIONS 1; */
.BEGIN DELETE MLOAD TABLES TPCH.H_REGION ;
.LAYOUT TRANSACTION;
.FIELD R_REGIONKEY * CHAR(11) NULLIF R_REGIONKEY = '';
.FIELD R_NAME * CHAR(25) NULLIF R_NAME = '';
/* .DML LABEL DELETE; */
Delete from TPCH.H_REGION where R_REGIONKEY = :R_REGIONKEY;
.IMPORT INFILE "d:\data\table1.dat"
FORMAT TEXT
LAYOUT TRANSACTION;
/* APPLY DELETE; */
.END MLOAD;
.LOGOFF;
_______________________________________

Thanks
Mitesh

[Jimm]

A MLoad DELETE task deletes records using a full table scan (normally a fairly high percentage of the table). It is normally used for removing aged records or removing all records for a business unit (eg after a closure).
It only allows one record on the input file - eg a date so you can delete all records with a date less than the specified date.

In your case, you a providing a list of records you want to delete - ie you are applying an input file to the table.

So you need a BEGIN IMPORT - like you commented out. Likewise you need the DML and APPLY statements.
It should then work fine as long as the target table has a PI (UPI or NUPI) of R_REGIONKEY. If not, you need a file with all the PI columns on the target table.