Usage of Teradata MLOAD stage

[Sivatkv]

Hi,

We are having Oracle Connector as Source and Teradata MLOAD stage as target stage and in between,a transformer has been used in one of parallel jobs.When I select primary Index as key in MLOAD stage,the job works fine(No error but some records get dropped due to duplicates in Index value).However,when I select the primary key(as mentioned in the source and it being on different column from primary index in teradata table),the job fails saying 'UTY0805 RDBMS FAILURE : A MULTILOAD UPDATE STATEMENT IS INVALID. ' which is the error written in MLOAD log file.
We tried checking the script also and that seems to be fine.

Failing Statement:- UPDATE XYZ set A= :A,B=:B where P=P' /*where XYZ is target table name,Primary Index(in target) is on col(say) B and source primary key is on col P*/ ?

At the same time the other Update statement works fine i.e. UPDATE XYZ set P=P' ,A=:A where B=:B /*where XYZ is target table name,Primary Index(in target) is on col(say) B and source primary key is on col P*/

Could any one provide pointers on this?

Thanks in advance.

[chulett]

http://www.tek-tips.com/viewthread.cfm? ... 55&page=21

[Sivatkv]

http://www.tek-tips.com/viewthread.cfm? ... 55&page=21

Thanks for your response Craig.
So please check if got it correctly:-
In the where clause in update script(generated by MLOAD stage),it is a must to include Primary Index(on teradata table) with equity condition and this primary index should not be on Updateable column.

[chulett]

That's the impression I got from googling around but I have no hands-on TD experience. Why not run this by your DBA?

[richdhan]

Hi Siva,

Try to do a metadata import in DataStage for this Teradata table and you will find out that the Primary Index columns are automatically translated to Key columns in DataStage. This is because Teradata uses the Primary Index (not the Primary Key) for its data distribution, data retrieval and joins.

So if you are doing an update (which requires key columns), the Primary Index columns should be marked as Key columns and then the Update will be successful as you have mentioned.

Tip: I always test the load with a Teradata API stage before moving to Teradata Multiload stage.

HTH
--Rich

[hamzaqk]

your PI columns should be the same one used in the where clause.