We’re experiencing an issue at a client that is quite odd. It’s using a partitioned large (5M) Oracle 10g table with Data Services 3.1. I have been able to determine that the issue is with the Table Comparison and the partitioned table using the following three scenarios:
Table A (has partitioning and PKs turned on) [This table we cannot load because DI returns PK constraint error]
Table B (has no partitioning and no PKs. [This table we can load and it correctly updates/inserts the records we need]
Table C (has no partitioning and PKs turned on) [This table we can load and it correctly updates/inserts the records we need]
From this, we need to be able to use the table comparison (row-by-row select) to determine inserts/updates but when we run this, and a row is being updated, the job fails and complains with an Oracle Error that the PK key constraint is being violated.
So since the PK error is being thrown, it’s leading me to believe that the insert statement is being submitted to the database. Since the insert is being submitted, the Table Comparison is not seeing that the row already exists in the database.
Nota Bene: The imported target table has been identified in Data Services as being a range partition (which is correct).
The partitioned case and the not-partitioned case (Table C), both have the same “input primary keys” in TC and the target table is imported into DI with the same PK columns?
And hte source has the same primary key as well?
Best would be to isolate an example row and then run it with debugger to see what’s going on.
In theory, TC does not mind if the table is partitioned. It select * from comparison_table where input_primary_key = xyz; if no record is found it is flagged as INSERT and the table loader will insert it.
Turning off Partitioning within Data Services fixes the problem! Right clicking the table within the Data Store and choosing Properties, then the Partitioning tab and set it from Range to None magically worked!
(BOB member since 2004-12-17)