7 Replies Latest reply: Jul 11, 2013 4:16 PM by LuizFilipe RSS

    ODI - IKM SQL to File Append - Header not Generated

    user8948518
      I'm using ODI IKM SQL to File Append to create a text file, but the header is not being generated. And the GENERATE_HEADER is set to Yes. The file is Tab delimited and the Heading (number of lines) is set to 1.

      Seems to only be an issue with HFM files coming from the Unix server.

      Any suggestions?

      Thanks, Mike
        • 1. Re: ODI - IKM SQL to File Append - Header not Generated
          932033
          Execute interface and go to Operator tab.
          Expand task node and view execution steps.
          Find header-generated step and view code.
          If code is empty then step not executed => check IKM code - why.
          If code is not empty => check that code correct (not generated empty header).
          • 2. Re: ODI - IKM SQL to File Append - Header not Generated
            tluefex
            I had similar issue. Setting all field data types to string in the Datastore did the trick to generate the headers, but introduces other issue with double quotes around numbers. Feature request was placed with Oracle
            • 3. Re: ODI - IKM SQL to File Append - Header not Generated
              user8948518
              Ok, getting the following error in step 6 - Integration - HFM_EA_Translate - Insert Column Headers

              java.lang.NumberFormatException
                   at java.math.BigDecimal.<init>(BigDecimal.java:459)
                   at java.math.BigDecimal.<init>(BigDecimal.java:728)
                   at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java)
                   at com.sunopsis.sql.SnpsQuery.addBatch(SnpsQuery.java)
                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execCollOrders(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.i(e.java)
                   at com.sunopsis.dwg.cmd.h.y(h.java)
                   at com.sunopsis.dwg.cmd.e.run(e.java)
                   at java.lang.Thread.run(Thread.java:662)

              The last two columns in the file are numeric which seems to be causing the issue. Will change formatting for the two columns (String, Numeric, etc.) to see if I can resolve the header issue.

              Thanks,
              Mike
              • 4. Re: ODI - IKM SQL to File Append - Header not Generated
                user8948518
                Ok, converting the numeric columns to string resolved the issue with the header not being generated.

                However I now see the real problem that the numeric column has leading spaces in the number to make the the column 30 in length. Seems like a bug with HFM EA extracts and data values.

                Now I need to remove the spaces from the data value. May need to create a table to load the data to to use the string functions.

                Any other suggestions would be helpful.

                Thanks,
                Mike
                • 5. Re: ODI - IKM SQL to File Append - Header not Generated
                  mRainey
                  Have you tried changing the order of the columns? Moving the numeric columns so they are not last, and keeping their datatype as numeric, and instead placing a string column at the end? I remember seeing this as the solution to a similar issue at some point, but cannot recall the exact details. Worth a shot.

                  Regards,
                  Michael Rainey
                  • 6. Re: ODI - IKM SQL to File Append - Header not Generated
                    tluefex
                    if it is only about removing leading or trailing spaces you could try using a Linux/Unix tool like sed or awk on your source system (if your ODI Agent is running there).
                    To be honest, I actually think it is more a bug than a missing feature when an ETL tool is not capable of creating proper csv files as i.e. described here: http://en.wikipedia.org/wiki/Comma-separated_values
                    • 7. Re: ODI - IKM SQL to File Append - Header not Generated
                      LuizFilipe

                      You can implement the generation in Jython, its quite easy.

                       

                      import os

                      vSrc = open('<%=odiRef.getSchemaName( "<YourSchema>" , "D" )%>/<%=snpRef.getTargetTable("RES_NAME")%>', 'w')

                      try:

                          vCol = "<%=snpRef.getColList("", "[COL_NAME]", ";", "", "INS") %>" + "\n"

                          vSrc.write(vCol)

                      finally:

                          vSrc.close()

                       

                      Just add this command to KM.

                       

                      []'s