This discussion is archived
5 Replies Latest reply: Dec 26, 2012 9:49 AM by 800221 RSS

ODI Error - Using IKM Incremental Update

LeoG Newbie
Currently Being Moderated
New error I can't figure out. I'm having problems in ODI with a simple ETL interface (.csv file to SQL table)

This error occurs In IKM Incremental Update module in step create index on flow table. It seems like it doesn't know for which field it should create the index.

My target table has an "ID" field (that is the primary key and it's an identity field type). I have no link to this field and I've defined it as a primary key. In the KM (IKM Incremental Update ) I also put No option in Static Control and Flow Control.

Has anyone had this problem before? How can you resolve it to load data? Thank you.


ODI SQL:
create index     IX_NPR_Staging_Data
on        NPR_REPOSITORY.dbo.I$_NPR_Staging_Data ()
102 : S0001 : com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.


ERROR:
102 : S0001 : com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.
     at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
     at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)
     at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
     at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
     at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
     at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unknown Source)
     at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)
     at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java)
     at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
     at com.sunopsis.dwg.dbobj.SnpSessTaskSqlI.treatTaskTrt(SnpSessTaskSqlI.java)
     at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
     at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
     at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
     at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
     at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
     at com.sunopsis.dwg.cmd.e.k(e.java)
     at com.sunopsis.dwg.cmd.g.A(g.java)
     at com.sunopsis.dwg.cmd.e.run(e.java)
     at java.lang.Thread.run(Thread.java:619)

Edited by: LeoG on Dec 27, 2011 6:30 PM
  • 1. Re: ODI Error - Using IKM Incremental Update
    GuruSankar Guru
    Currently Being Moderated
    Hi,

    Couple of hints,

    Make sure ur "Update key" should be an "Active mapping". You cant use a key column for Incremental update which is no longer mapped in ur interface. This rule holds for Incremental Update only.

    If your requirement is like, new records to be inserted and old record to be updated then use Incremental update else go for IKM SQL Control Append.

    Thanks,
    Guru
  • 2. Re: ODI Error - Using IKM Incremental Update
    Phanikanth Pro
    Currently Being Moderated
    Hi Leo,

    SQL being generated by ODI is incorrect and throwing an error at ODi load step.
    Which version of SQL Server are you using ?

    Regards,
    Phanikanth
  • 3. Re: ODI Error - Using IKM Incremental Update
    LeoG Newbie
    Currently Being Moderated
    Thanks Guru,

    I've tried to keep my Key column as an Active Mapping but then everytime I save it and close it, my changes are not saved. Everytime I open my integration it's NOT selected as an Active Mapping. And my requirement is to insert new and update old records. So I think I need to use the Incremental update IKM.

    Thank you!

    Leo G.
  • 4. Re: ODI Error - Using IKM Incremental Update
    LeoG Newbie
    Currently Being Moderated
    Hi Phanikanth,

    Thanks for you reponse and help! I'm using SQL Server 2008. Do you think that's my problem? I've been reading that it appears the SQL code is wrong, is that why the error then? Can we work around it?

    Thank you,

    Leo G.
  • 5. Re: ODI Error - Using IKM Incremental Update
    800221 Newbie
    Currently Being Moderated
    There appears to be a bug in the IKM MSSQL Incremental Update where the ID column is being read as a "read only" column and is there fore excluded from the flow table. I was able to get around this by modifying the IKM to remove the exclusion on "read only" columns (REW).

    Step: Insert flow into I$ table
    insert into     <%=odiRef.getTable("L","INT_NAME","A")%>
    (
         <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS or UPD) and !TRG)")%>,
         IND_UPDATE
    )
    <%for (int i=0; i < odiRef.getDataSetCount(); i++){%>
    <%=odiRef.getDataSet(i, "Operator")%>
    select      <%=odiRef.getPop("DISTINCT_ROWS")%>
         <%=odiRef.getColList(i,"", "[EXPRESSION]", ",\n\t", "", "((INS or UPD) and !TRG)")%>,
    <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %>
         JRN_FLAG IND_UPDATE
    <%} else {%>
         'I' IND_UPDATE
    <%}%>
    from     <%=odiRef.getFrom(i)%>
    where     (1=1)
    <%=odiRef.getJoin(i)%>
    <%=odiRef.getFilter(i)%>
    <%=odiRef.getJrnFilter(i)%>
    <%=odiRef.getGrpBy(i)%>
    <%=odiRef.getHaving(i)%>
    <%}%>

Legend

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