We're using TPT Stream operator through Informatica 8.6. The operation I am trying to do is quite simple. Here is some outlines
1) I have a table with Identity column defined as UPI. 2) We're planning to do insert and update to the table based on Identity column 3) In Informatica, I used Update Strategy to flag the record is insert or update. 4) 2 streams are used. One for Insert and one for Update.
Run for the first time, reading from source is no issue and the Informatica session is hanging for quite some time without processing any record. I guess there might be some deadlock.
Change the Stream Operator for Update to "Is Staged" and rerun the Informatica session. All records are inserted correctly. Then change one attribute in the source, and rerun the job. I was hoping that the record will be updated correctly in target table. Informatica session said the records are updated successfully. No loader log is created for update operation. But in staged file, I can see all the records and in the database no record has been updated.
Sorry I haven't checked this forum for a week now. Still no luck with the issue. But what I did was instead of sourcing from one instance, I created 2 instances, one for insert and one for update and mark update as priority in target load plan. Then use TPT for Insert and Relational Writer for Update.
We've run into a similar issue and the COE for Informatica/TPT at Teradata should be able to provide guidance. From the last time I checked, the TPT API stream operator builds its own UPDATE strategy which negates some updates.
Did you configure the Informatica session property to insert into target as 'Data Driven'? You need to set this accordingly as the default is set to 'Insert'. The default will not work for an update strategy.
Please post your feedback when you try this option out.
Please bear with me and sorry for such a big explanation...
Here is the issue I am encountering:
I am on Infa8.6 with Teradata 12 and I am using Teradata Stream Operator to insert/update data into my target tables.
My target table - TableA (on teradata) has 10 fields and INSERTS and UPDATES will happen on this table. On the database side, Field2 has been marked as NUPI. Field1 currently has no index and will be populated by Informatica Sequence generator when a record has been inserted.
As of now, I guess I don't have issues with INSERTS.
My problem is with UPDATES...
Field1 and Field2 combined will uniquely identify a record that needs to be updated.
Right now, as per the database definition, I have Field2 marked on NUPI on the database side. Since Update needs to happen on both Field1 and Field2, I have added Field1 as PK and Field2 as PK in informatica target definition. And since I have defined Filed1 and Field as PK on the informatica side, Teradata is trying to SERIALIZE on both the fields when it is establishing connection to the target.
With the kind of data I am having, SERIALIZING on Field1 and Field2 will not work for me as most of the times, I will be updating the target table and possibly the same row many times and I am encountering deadlock issues most of the times which is causing my process to run for a longer time. So, I need to SERIALIZE on Field2 but update the table by Field1 and Field2.
So, what I am trying to do here is:
Mark only Field2 as PK on the informatica side (as it is NUPI on DB side) and Use Update Override on the target and generate UPDATE statement which updates by both Field1 and Field2.
Unfortunately, my problem is, It is not using the UPDTAE OVERRIDE sql that I have in the update override. It is using the same default Update statement which informatica generates by default and it is ignoring the oveeride statement that i have provided...
Can someone Please help me on what to do or how to get over this issue???
I have the following options In my session: I have marked "Treat Source Rows - as Update" I have checked Serialize option in mappings tab for target and I have checked the Update Else Insert optin for the target.