Not having primary keys on the target tables should not affect PWX CDC processing.
PWX constructs row images with all columns, so PC has all columns available to use for the where clause of updates and deletes.
How did you determine that the cause of data not flowing is lack of primary keys on the target?
Thank you Dan. You are right, i do remember that we set up Supplemental logging for all the columns (ALWAYS) and it should not matter whether a table has Primary keys or not.
I started seeing the following error in the session log and i also saw a rejected row. I even checked the Target table and i did not see the update.
ERROR: Target table [Table Name] has no keys specified
Row #  in bad file
This had happened to one other table as well and i defined a Primary Key for that Table at Informatica level and i started seeing changes in the Target table.
You would have to ask a PowerCenter person why PC was giving that error.
There might be some targets where it matters, that I am not aware of.
Thank you Dan.
I will try to do some more research on this and see if i can come up with an explanation.
Thank you for the KB article Ravi.
During the weekend, i tried Update and Delete on bunch of tables without any Primary Keys and the data never gets written to the tables for these actions.
I am not sure if this is how the tool is supposed to function or if i need to open a ticket with Informatica Support.
This error will usually occur when youre trying to do an update or an insert and you dont have a primary key defined.
Have you tried creating a key in designer for this particular column?
Thank you Gabriel for the comments.
Yes, it works fine when i define a key in Target Designer. Even without the keys, it works fine for INSERTS, but for UPDATES and DELETES, it definitely looks for keys.
As Dan stated in his earlier email, it should not matter if a table has keys or not since
"PWX constructs row images with all columns, so PC has all columns available to use for the where clause of updates and deletes"
Have you resolved the issue , we are facing same issue .we are using infa pwx 9.6.1 for oracle source and oracle target databases .
Serious thread necromancy. This was 5 years ago
However, the answer as pointed out above is that if PC (or the target DB) requires PKey on the target, then you have to define the PKey in Designer.
A short general explanation about PowerCenter and relational targets:
If you want to perform UPDATE and/or DELETE statements on a relational target, then PowerCenter requires the target table to have a primary key defined. Whether this PK exists in reality or "only" in the Designer" (by designating one or more target columns as comprising the PK) doesn't matter; it is just important that PowerCenter "sees" a PK defined.
The reason is simple: for UPDATE and DELETE statements on a target table, PowerCenter creates the respective UPDATE or DELETE statement based on the table structure as defined in the target definition. For example, if you have a target table ABC with a PK composed of two attributes ID1 and ID2, then PowerCenter will (almost) always issue UPDATE and DELETE statements like these:
UPDATE ABC SET ... WHERE ID1 = ? AND ID2 = ?
DELETE FROM ABC WHERE ID1 = ? AND ID2 = ?
(the question marks will be resolved by the values provided from the port links at runtime)
I hope that clarifies the matter a little. And also why for INSERT statements the PK doesn't matter.
Thanks a lot Nico & Dan ,
In our PWX real time CDC environment we have target tables with PK defined on it ,
on some occasions in source data values of PK column itself getting changed . i will explain this scenario with above example given by you
UPDATE ABC SET ... WHERE ID1 = ? AND ID2 = ?
ID1 and ID2 values got updated in Source system and obviously we are going miss this update on target due to unavailability these values in target table .
for this issue we have raised case with Informatica support and support team given below suggestion.
Suggested to configure Flexible Key transformation to load data into primary key column.
can you throw some light us on Flexible Key transformation to avoid above issue.
Thanks & Regards,
Have you searched the PowerExchange forum for threads about the Flexible Key transformation? There are a few. Maybe they can illustrate how that works.
You will find more information about it in the PowerExchange User Guide, if memory serves me right.
Currently doc'd in the PowerExchange Interfaces for PowerCenter manual.
Basically, it translates updates that affect a primary key into a delete on the old key and an insert on the new key.
Please refer to the manual for details.