13 Replies Latest reply: Sep 18, 2013 4:25 AM by 1007772 RSS

    DAC: failed tasks during ETL for financial apps

    1007772

      I am trying  my first ETL on OBIA 7.9.6.4

      i'm using  Oracle EBS 12.1.1 as source system.

       

       

       

      the ETL completes 200+ tasks successfully ,but it fails the rest of them

       

      first task that fails is :

      SDE_ORA_GL_AR_REV_LinkageInformation_Extract


      Error message:

       

      All Task Batches

        Informatica Session Batch

        ------------------------------

        INFORMATICA TASK:SDE_ORAR1211_Adaptor:SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full:1:(Source : FULL Target : FULL)

        ------------------------------

       

        2013-09-03 14:57:14.627 Acquiring Resources

       

        2013-09-03 14:57:14.643 Acquired Resources

       

       

        2013-09-03 14:57:14.658 INFORMATICA TASK:SDE_ORAR1211_Adaptor:SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full:1:(Source : FULL Target : FULL) has started.

       

       

       

        ANOMALY INFO::: Error while executing : INFORMATICA TASK:SDE_ORAR1211_Adaptor:SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full:1:(Source : FULL Target : FULL)

        MESSAGE:::

        Irrecoverable Error

        pmcmd startworkflow -sv infor_int -d Domain_AMAZON-9C628AAE -u Administrator2 -p ****  -f SDE_ORAR1211_Adaptor  -paramfile C:\Informatica\server\infa_shared\SrcFiles\SDE_ORAR1211_Adaptor.SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full.ORA_R1211.txt  SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full

        Status Desc : Failed

        WorkFlowMessage :

        Error Message : Unknown reason for error code 36331

        ErrorCode : 36331

        EXCEPTION CLASS::: com.siebel.analytics.etl.etltask.IrrecoverableException

       

        com.siebel.analytics.etl.etltask.InformaticaTask.doExecute(InformaticaTask.java:254)

        com.siebel.analytics.etl.etltask.GenericTaskImpl.doExecuteWithRetries(GenericTaskImpl.java:477)

        com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:372)

        com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:253)

        com.siebel.analytics.etl.etltask.GenericTaskImpl.run(GenericTaskImpl.java:655)

        com.siebel.analytics.etl.taskmanager.XCallable.call(XCallable.java:63)

        java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)

        java.util.concurrent.FutureTask.run(Unknown Source)

        java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

        java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)

        java.util.concurrent.FutureTask.run(Unknown Source)

        java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)

        java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

        java.lang.Thread.run(Unknown Source)

       

        (Number of retries : 1)

       

        pmcmd startworkflow -sv infor_int -d Domain_AMAZON-9C628AAE -u Administrator2 -p ****  -f SDE_ORAR1211_Adaptor  -paramfile C:\Informatica\server\infa_shared\SrcFiles\SDE_ORAR1211_Adaptor.SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full.ORA_R1211.txt  SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full

       

        2013-09-03 15:15:01.346 INFORMATICA TASK:SDE_ORAR1211_Adaptor:SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full:1:(Source : FULL Target : FULL) has finished execution with Failed status.

        ------------------------------

      (Failed)

       

       

       

      after that, many dependent tasks fail

       

       

      session logs at "SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full_SESSIONS.log":

       

       

      =====================================

      STD OUTPUT

      =====================================

       

       

      Informatica(r) PMCMD, version [9.1.0 HotFix2], build [357.0903], Windows 32-bit

      Copyright (c) Informatica Corporation 1994 - 2011

      All Rights Reserved.

       

       

      Invoked at Tue Sep 03 15:14:24 2013

       

       

      Connected to Integration Service: [infor_int].

      Folder: [SDE_ORAR1211_Adaptor]

      Workflow: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full]

      Instance: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full]

      Mapping: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract]

      Session log file: [C:\Informatica\server\infa_shared\SessLogs\.SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full.ORA_R1211.log]

      Source success rows: [0]

      Source failed rows: [0]

      Target success rows: [0]

      Target failed rows: [0]

      Number of transformation errors: [0]

      First error code [4035]

      First error message: [RR_4035 SQL Error [

      ORA-00904: "XLA_EVENTS"."UPG_BATCH_ID": invalid identifier

       

       

      Database driver error...

      Function Name : Execute

      SQL Stmt : SELECT DISTINCT

       

       

      DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

       

       

      DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

       

       

      AELINE.ACCOUNTING_CLASS_CODE,

       

       

      GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

       

       

      GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

       

       

      AELINE.AE_HEADER_ID AE_HEADER_ID,

       

       

      AELINE.AE_LINE_NUM AE_LINE_NUM,

       

       

      T.LEDGER_ID LEDGER_ID,

       

       

      T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

       

       

          JBATCH.NAME BATCH_NAME,

       

       

         JHEADER.NAME HEADER_NAME,

       

       

            PER.END_DATE,

       

       

      AELINE.CODE_COMBINATI]

      Task run status: [Failed]

      Integration Service: [infor_int]

      Integration Service Process: [infor_int]

      Integration Service Grid: [infor_int]

      ----------------------------

      Node Name(s) [node01_AMAZON-9C628AAE]

      Preparation fragment

       

       

      Partition: [Partition #1]

      Transformation instance: [SQ_XLA_AE_LINES]

      Transformation: [SQ_XLA_AE_LINES]

      Applied rows: [0]

      Affected rows: [0]

      Rejected rows: [0]

      Throughput(Rows/Sec): [0]

      Throughput(Bytes/Sec): [0]

      Last error code [16004], message [ERROR: Prepare failed. : [

      ORA-00904: "XLA_EVENTS"."UPG_BATCH_ID": invalid identifier

       

       

      Database driver error...

      Function Name : Execute

      SQL Stmt : SELECT DISTINCT

       

       

      DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

       

       

      DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

       

       

      AELINE.ACCOUNTING_CLASS_CODE,

       

       

      GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

       

       

      GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

       

       

      AELINE.AE_HEADER_ID AE_HEADER_ID,

       

       

      AELINE.AE_LINE_NUM AE_LINE_NUM,

       

       

      T.LEDGER_ID LEDGER_ID,

       

       

      T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

       

       

          JBATCH.NAME BATCH_NAME,

       

       

         JHEADER.NAME HEADER_NAME,

       

       

            PER.END_DATE,

       

       

      AELINE.CODE_CO]

      Start time: [Tue Sep 03 15:06:16 2013]

      End time: [Tue Sep 03 15:06:16 2013]

       

       

      Partition: [Partition #1]

      Transformation instance: [W_GL_LINKAGE_INFORMATION_GS]

      Transformation: [W_GL_LINKAGE_INFORMATION_GS]

      Applied rows: [0]

      Affected rows: [0]

      Rejected rows: [0]

      Throughput(Rows/Sec): [0]

      Throughput(Bytes/Sec): [0]

      Last error code [0], message [No errors encountered.]

      Start time: [Tue Sep 03 15:06:20 2013]

      End time: [Tue Sep 03 15:06:20 2013]

       

       

       

       

      Disconnecting from Integration Service

       

       

      Completed at Tue Sep 03 15:14:59 2013

       

       

      =====================================

      ERROR OUTPUT

      =====================================

       

       

       

       

      next failing task is:

      SDE_ORA_APTransactionFact_Payment_Full

       

      error log:

      java.lang.Thread.run(Unknown Source)

      305  SEVERE  Tue Sep 03 11:19:43 GMT 2013  Request to start workflow : 'SDE_ORAR1211_Adaptor:SDE_ORA_APTransactionFact_Payment_Full with the instance name SDE_ORA_APTransactionFact_Payment_Full' has completed with error code 0

      306  SEVERE  Tue Sep 03 11:20:16 GMT 2013  Request to start workflow : 'SDE_ORAR1211_Adaptor:SDE_ORA_APTransactionFact_PaymentSchedule_Full with the instance name SDE_ORA_APTransactionFact_PaymentSchedule_Full' has completed with error code 0

      307  SEVERE  Tue Sep 03 11:20:18 GMT 2013  Request to start workflow : 'SDE_ORAR1211_Adaptor:SDE_ORA_APTransactionFact_Distributions_Full with the instance name SDE_ORA_APTransactionFact_Distributions_Full' has completed with error code 0

      308  SEVERE  Tue Sep 03 11:20:24 GMT 2013  Request to start workflow : 'SDE_ORAR1211_Adaptor:SDE_ORA_Stage_ValueSetHier_Flatten with the instance name SDE_ORA_Stage_ValueSetHier_Flatten' has completed with error code 0

      311  SEVERE  Tue Sep 03 11:22:14 GMT 2013  MESSAGE:::No value for @DAC_SOURCE_PRUNED_REFRESH_TIMESTAMP available!

       

       

       

       

       

      i searched online but can't find useful information about the errors

      could anyone help , please?


      another question : can i continue the ETL when it fails? without starting it from the beginning?

        • 1. Re: DAC: failed tasks during ETL for financial apps
          Srini VEERAVALLI

          It is failing due to ORA-00904: "XLA_EVENTS"."UPG_BATCH_ID"

           

          look for that column from that table.

           

          Once you restart DAC will load from failed tasks, just restart DAC will take care of things for you.

           

          if helps mark

          ~ http://cool-bi.com

          • 2. Re: DAC: failed tasks during ETL for financial apps
            1007772

            thanks for the reply

             

            i found the table in my source DB ,it looks healthy
            but i can't find it in my target DB

             

            what should i do now?

            • 3. Re: DAC: failed tasks during ETL for financial apps
              Srini VEERAVALLI

              Get the SQL from Source Qualifier and run in the database. Looks like some issue with query.

              • 4. Re: DAC: failed tasks during ETL for financial apps
                1007772

                i'm sorry , i don't know anything about source qualifier , could you please provide simple steps or a tutorial?

                • 5. Re: DAC: failed tasks during ETL for financial apps
                  Srini VEERAVALLI

                  Check the session log file and get the SQL query and run in the db.

                  • 6. Re: DAC: failed tasks during ETL for financial apps
                    969151

                    Status Desc : Failed

                      WorkFlowMessage :

                      Error Message : Unknown reason for error code 36331

                      ErrorCode : 36331

                      EXCEPTION CLASS::: com.siebel.analytics.etl.etltask.IrrecoverableException




                    This error happen when informatica unable to execute your query..

                     

                    Go for that failed task in workflow and try to understand the problem

                     

                    Thanks

                    http://mkashu.blogspot.com

                    • 7. Re: DAC: failed tasks during ETL for financial apps
                      1007772

                      i got the query from informatica session logs of the failed task,and executed it in the source DB


                      SELECT

                      DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

                      DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

                      DLINK.ACCOUNTING_LINE_CODE LINE_CODE,

                      AELINE.ACCOUNTING_CLASS_CODE,

                      GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

                      GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

                      AELINE.AE_HEADER_ID AE_HEADER_ID,

                      AELINE.AE_LINE_NUM AE_LINE_NUM,

                      T.LEDGER_ID LEDGER_ID,

                      T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

                      JBATCH.NAME BATCH_NAME,

                      JHEADER.NAME HEADER_NAME,

                      PER.END_DATE,

                      AELINE.CODE_COMBINATION_ID,

                      AEHEADER.EVENT_TYPE_CODE

                      FROM XLA_DISTRIBUTION_LINKS DLINK

                         , GL_IMPORT_REFERENCES GLIMPREF

                         , XLA_AE_LINES AELINE

                         , XLA_AE_HEADERS AEHEADER

                         , GL_JE_HEADERS JHEADER

                         , GL_JE_BATCHES JBATCH

                         , GL_LEDGERS T

                         , GL_PERIODS PER

                      WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN ('AP_INV_DIST','AP_PMT_DIST','AP_PREPAY')

                      AND DLINK.APPLICATION_ID = 200

                      AND AELINE.APPLICATION_ID = 200

                      AND AEHEADER.APPLICATION_ID = 200

                      AND AEHEADER.AE_HEADER_ID = AELINE.AE_HEADER_ID

                      AND AEHEADER.UPG_BATCH_ID IS NULL

                      AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE

                      AND AELINE.GL_SL_LINK_ID = GLIMPREF.GL_SL_LINK_ID

                      AND AELINE.AE_HEADER_ID = DLINK.AE_HEADER_ID      

                      AND AELINE.AE_LINE_NUM = DLINK.AE_LINE_NUM

                      AND GLIMPREF.JE_HEADER_ID = JHEADER.JE_HEADER_ID

                      AND JHEADER.JE_BATCH_ID = JBATCH.JE_BATCH_ID

                      AND JHEADER.LEDGER_ID = T.LEDGER_ID

                      AND JHEADER.STATUS = 'P'

                      AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME

                      AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME

                      AND JHEADER.CREATION_DATE >=

                                TO_DATE('01/01/1970 00:00:00'

                                      , 'MM/DD/YYYY HH24:MI:SS' )

                      AND DECODE('N', 'Y', T.LEDGER_ID, 1) IN (1)

                      AND DECODE('N', 'Y', T.LEDGER_CATEGORY_CODE, 'NONE') IN ('NONE')


                      the result returns 5000 rows ,seems like healthy data


                      what do we get out of that?

                      • 8. Re: DAC: failed tasks during ETL for financial apps
                        1007772

                        i did,i found that there are many failed tasks

                         

                        PositionDimension

                        ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

                         

                         

                         

                        ProductDimension_Full

                        Error Message : Unknown reason for error code 36331

                         

                        MonthDimension_UpdateFlag

                        -Unknown reason for error code 36331

                        -Database driver error...Function Name : Logon - ORA-03113: end-of-file on communication channel

                         

                         

                        QuarterDimension_UpdateFlag

                        -CMN_1022 [Database driver error - Logon - ORA-03113: end-of-file on communication channel

                        -CMN_1076 ERROR creating database connection.

                         

                        YearDimension_UpdateFlag

                        -CMN_1022 [Database driver error - Logon - ORA-03113: end-of-file on communication channel

                        -CMN_1076 ERROR creating database connection.

                         

                        TASK_GROUP_Extract_EmployeeDimension

                        -CMN_1022 [Database driver error - Logon - ORA-03113: end-of-file on communication channel

                        -CMN_1076 ERROR creating database connection.

                        -ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

                         

                         

                         

                        DAC_TASK_GROUP_Extract_GLLinkageInformation

                        -Index W_GL_LINKAGEINFORMATION_GS_M2 does not exist in the named source DataWarehouse. And hence it will not be dropped.

                        --and same error message is stated for other tables

                         

                        these are the most distinct error messages in the logs of failed tasks , does they make any sense?

                        • 9. Re: DAC: failed tasks during ETL for financial apps
                          969151

                          Hi Mate,

                           

                          You have a duplicate values got inserted in your dwh table (w_postion_d)

                           

                          once you removed the duplicate value, change the task status from failed to queue and restart it again.

                           

                          At the same time, change informatica failed task also in queue and restart the dac.

                           

                          Thanks

                          http:mkashu.blogspot.com

                          • 10. Re: DAC: failed tasks during ETL for financial apps
                            1007772

                            thank you for your reply

                             

                            i used a sql script to detect duplicate rows,i got it from an article on metalink

                             

                             

                            the result is:

                             

                             

                            INTEGRATION_ID                 DATASOURCE_NUM_ID      SRC_EFF_FROM_DT          

                            ------------------------------ ---------------------- -------------------------

                            PER~6272                       26                     04-JAN-91                

                            PER~6272                       26                     01-JAN-94                

                             

                             

                            2 rows selected

                             

                             

                            -should i delete one of the rows?

                            wouldn't that affect the data in my data warehouse,and thus affecting the accuracy of the results?

                            • 11. Re: DAC: failed tasks during ETL for financial apps
                              969151

                              Hi Mate,

                               

                              Just delete the duplicate value, It is not a problem

                               

                              finally give commit in your db, make the task in queue and restart your execution plan again..

                               

                              I'm sure this task will complete now.

                               

                              It will be a great help from your side, if you share me were you read those article . and provide me those article number?

                               

                              I'm also looking for some validation script now.

                               

                              Thanks

                              Kashu

                              mkashu.blogspot.com

                              • 12. Re: DAC: failed tasks during ETL for financial apps
                                1007772

                                thanks for the help

                                deleting the duplicated records solved that error ,the task is now successful
                                but i'm still stuck with "SDE_ORA_GL_AR_REV_LinkageInformation_Extract"
                                can you help with it?

                                 

                                P.S:
                                this is the script that i use for detecting duplicates,there are many other scripts online,but this is the simplest

                                 

                                select INTEGRATION_ID,DATASOURCE_NUM_ID,SRC_EFF_FROM_DT from W_POSITION_DS

                                GROUP BY

                                    INTEGRATION_ID,DATASOURCE_NUM_ID,SRC_EFF_FROM_DT

                                HAVING

                                    COUNT(*) > 1

                                • 13. Re: DAC: failed tasks during ETL for financial apps
                                  1007772

                                  Dear SriniVEERAVALLI

                                  thanks for your help

                                  now i know the problem of the query,i added a column name and "deleted" a line
                                  how can i correct the query in the workflow?

                                   

                                  there's another issue : the query takes a lot of time to run in DB ,it took 3 hours without completing,what can i do?