This discussion is archived
7 Replies Latest reply: Jul 16, 2012 2:18 AM by PeakIndicators_Alastair RSS

Configure CDC on tables not having Primary Key

760134 Newbie
Currently Being Moderated
Hi,

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.


Regards,
Shashi Ranjan
  • 1. Re: Configure CDC on tables not having Primary Key
    Himanshu Rai Journeyer
    Currently Being Moderated
    Hi,

    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.
  • 2. Re: Configure CDC on tables not having Primary Key
    SutirthaRoy Guru
    Currently Being Moderated
    Himanshu Rai wrote:
    Hi,

    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
    http://docs.oracle.com/cd/B13789_01/server.101/b10736/cdc.htm#i1025409
    http://docs.oracle.com/cd/E25178_01/integrate.1111/e12644/oracle_as_legacy_adapters.htm

    Thanks,
    Sutirtha
  • 3. Re: Configure CDC on tables not having Primary Key
    760134 Newbie
    Currently Being Moderated
    Hi,

    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.


    Regards,
    Shashi Ranjan
  • 4. Re: Configure CDC on tables not having Primary Key
    Himanshu Rai Journeyer
    Currently Being Moderated
    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.
  • 5. Re: Configure CDC on tables not having Primary Key
    SutirthaRoy Guru
    Currently Being Moderated
    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 .

    Thanks,
    Sutirtha
  • 6. Re: Configure CDC on tables not having Primary Key
    760134 Newbie
    Currently Being Moderated
    Hi,

    Thank you for support. I've conveyed the same to client and sure I will raise an SR. Really appreciate your prompt help.

    Regards,
    Shashi Ranjan
  • 7. Re: Configure CDC on tables not having Primary Key
    PeakIndicators_Alastair Guru
    Currently Being Moderated
    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,
    Alastair

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points