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