Teradata Multi Load uodate fail

bikan · Post by **bikan** » Mon Sep 29, 2008 3:38 am

Hi I am getting following error in multiload report file.

USING A(DECIMAL(18,0)), B(DECIMAL(18,0)) UPDATE Table_X
SET A = :B
WHERE A = :A;
**** 10:03:57 UTY0805 RDBMS failure, 3538: A MultiLoad UPDATE Statement is Invalid.

The job has sequential file ( Col A, B). transformer & Teradata multi load stage.
I am using load typr custom & In Script- Custom tab I have following script.
.DML label updates;
UPDATE Table_X
SET A= :B
WHERE A = :A;
.DML label deletes;
DELETE FROM Table_X
WHERE A = :A;
.DML label inserts;
INSERT Table_X (A,
B)
VALUES (:A,
:B);
.import INFILE #$PATH#/data/input.dat
format fastload layout internal
apply updates where 1=1;

hamzaqk · Post by **hamzaqk** » Mon Sep 29, 2008 4:50 am

Paste the entire code generated by the stage. What areyou trying to achieve here as you are deleting, updating and inserting at the same time ?

bikan · Post by **bikan** » Mon Sep 29, 2008 4:53 am

I want to do update only

hamzaqk · Post by **hamzaqk** » Mon Sep 29, 2008 4:57 am

well if its just a simple update select the method" update" from the drop down list and it should work ok.

toshea · Post by **toshea** » Mon Sep 29, 2008 6:40 am

It appears that your UPDATE statement is trying to change the value of the primary key. Perhaps MultiLoad does not support such an operation?

hamzaqk · Post by **hamzaqk** » Tue Sep 30, 2008 12:16 am

Well there is no issue in TD when it comes to updating the UPI or PI. it wil do it and just redistribute the table. I think its the problem with the what is being specified in the update script by him which is giving an error. Are you generating the script or trying to use a custom one?

shershahkhan · Post by **shershahkhan** » Tue Sep 30, 2008 12:27 am

Check the table defination, MLOAD update will only work if you specify the whole primary key in the Where clause else it will not work. I think A is not the only key in the Primary Index.

toshea · Post by **toshea** » Tue Sep 30, 2008 6:05 am

Here's the description of error 3538 in the Teradata Messages manual. The problem is that you are trying to update the primary index which is not supported by MultiLoad. For future reference, you can find the Teradata Messages manual for each release here:

http://www.info.teradata.com/DataWareho ... m=Messages

3538 A MultiLoad UPDATE Statement is Invalid.
Explanation: The user has provided an UPDATE
statement in a MultiLoad Complex Import Task that
does not fully specify a primary index value, does not
fully specify the values of all the partitioning columns,
or modifies the primary index or partitioning columns.
Generated By: OPT modules.
For Whom: End User.
Remedy: Correct the error and resubmit the request.
The WHERE condition, including any taken from a
view definition, must allow for primary index access
(and, if partitioned, partition access) with possible
ANDed residual conditions. The list of updated fields
must not include any column in the primary index or
partitioning expression, and only one table may be referenced.