On one of our client majority of tables in their huge database are not having primary key. While starting journaling ODI-15021 error is being thrown. Is there any way to configure CDC on those tables. Even we are not in a situation to create logical primary key on ODI level. Client is asking for an oracle document which talks about such restriction. Kindly help me out. I've gone through documentation as well metalink. I'm not able to find any information regarding it.
You can create a constraint(Primary Key) in ODI on the table(in models --> table --> contraints --> new key) and then start the journal after adding the CDC. No key is mandatory as such on the D. Only the unique column(s) should be known so that the kye can be created in ODI.
Himanshu Rai wrote:
You can create a constraint(Primary Key) in ODI on the table(in models --> table --> contraints --> new key)
But I think Shashi Ranjan mentioned that
>Even we are not in a situation to create logical primary key on ODI level.
Which rules out the option of creating KEY at ODI level.
You can see if LOG based CDC can be achieved without PK
Thanks a lot for your prompt reply. Let me describe my situation bit more clearer. I've configured ODI CDC on one schema which contains 1200 tables. I tried to configure primary key on each data store in source model but being an integrator, I'm not the right person to decide primary key on tables so, I reverted back to client to create primary key on tables or suggest what should be primary key. Now, client is asking for an oracle document stating that primary key is mandatory. They have such 20 schema with similar huge number of tables. I've gone through various documents and meta-links but no luck. Need urgent help on it.
Oh in that case, go for hotlog or Golden Gate.. However, thats a task in itself and has its own pros & cons.
Also, while starting the Journal without the PK, the error message can be referred to prove that CDC requires a key on the table in the ODI model.
I will still suggest if performance(due to triggers) is not an issue then go ahead with the trigger based CDC.
You can get in touch with oracle support people via SR and confirm that CDC with out PK is not possible .
Once you have that communication , produce it befor your client .
All other stuff are nicely explained by Himanshu .
I've been here before, what I did :
Created a flexfield on the datastore to act as a flag,
when flag was set, create an alternate key in the datastore for all columns, modify the JKM (I was using Streams Logminer) to add all columns to the supplemental log group , add all columns to the change table, modify the J$ views (x2) so they dont join back to the source table to retrieve the rest of the row.
This sorted the issue out, you need to work out how to pull the last update (when you have more than one) but you can sort that in the interface.
Hope this helps,