One of the ODI Interface is getting hang every time when am executing and that too for large volume of data, for small data it is working fine. The LKM, IKM and CKM am using is LKM SQL to SQL, IKM SQL Increamental Update with Truncate True, CKM SQL. The Source and Target is on same database Oracle. The step on which interface it is hanging is Insert PK errors in which it is scanning I$ table in the database and taking so much time and not getting completed in even 12 hours.
Tried bouncing database and executed same query directly on database also but same result.
Thanks in Advance...
Yes, I have chosen the correct Pk, in this step it inserting the error records into the E$ table. It is not inserting any row in the E$ table for the same set of few number of records around 10,000. But for higher number it is hanging...
I checked for Index creation step, it is not available in CKM SQL, it is there in CKM Oracle... Does it make any difference in execution?
Step name is insert PK errors of CKM SQL
I modified the query with test table name, target table have two columns TEST_ID and APPLICATION_CODE and one primary constraint created on target table as TEST_TABLE_PK in which TEST_ID defined as constraint.
insert into TEST.E$_TEST_TABLE
'ODI-15064: The primary key TEST_TABLE_PK is not unique.',
from TEST.I$_TEST_TABLE TEST_TABLE
where exists (
from TEST.I$_TEST_TABLE E
where TEST_TABLE.TEST_ID = E.TEST_ID
group by E.TEST_ID
having count(*) > 1