5 Replies Latest reply on Sep 22, 2010 1:42 PM by SandeepSaini

    Error with Informatica when running the data load for BI Apps

    799509
      Hi All,

      We are facing a different issue on prod which we haven't got on QA. Below given is session log :




      Target tables:

      W_ORDER_PARTY


      READER_1_1_1> RR_4050 First row returned from database to reader : (Wed Sep 22 10:36:16 2010)
      READER_1_1_1> BLKR_16019 Read [885] rows, read [0] error rows for source table [S_ORDER_BU] instance name [S_ORDER_BU]
      READER_1_1_1> BLKR_16008 Reader run completed.
      LKPDP_1> DBG_21097 Lookup Transformation [LKP_ETL_PROC_WID]: Default sql to create lookup cache: SELECT ETL_PROC_WID,ROW_WID FROM W_PARAM_G ORDER BY ROW_WID,ETL_PROC_WID

      LKPDP_1> TE_7212 Increasing [Index Cache] size for transformation [LKP_ETL_PROC_WID] from [1048576] to [1050000].
      LKPDP_1> TM_6660 Total Buffer Pool size is 609824 bytes and Block size is 65536 bytes.
      LKPDP_1:READER_1_1> DBG_21438 Reader: Source is [SFAPROD1], user [siebel]
      LKPDP_1:READER_1_1> CMN_1021 Database driver event...
      CMN_1021 [DB2 Event Using Array Inserts. connect string = [SFAPROD1]. userid = [siebel]]

      LKPDP_1:READER_1_1> BLKR_16003 Initialization completed successfully.
      LKPDP_1:READER_1_1> BLKR_16007 Reader run started.
      LKPDP_1:READER_1_1> RR_4049 SQL Query issued to database : (Wed Sep 22 10:36:17 2010)
      LKPDP_1:READER_1_1> CMN_1761 Timestamp Event: [Wed Sep 22 10:36:17 2010]
      LKPDP_1:READER_1_1> RR_4035 SQL Error [
      *[IBM][CLI Driver][DB2/AIX64] SQL0204N "SIEBEL.W_PARAM_G" is an undefined name. SQLSTATE=42704*

      sqlstate = 42S02

      Database driver error...
      Function Name : Execute
      SQL Stmt : SELECT ETL_PROC_WID,ROW_WID FROM W_PARAM_G ORDER BY ROW_WID,ETL_PROC_WID
      DB2 Fatal Error].
      LKPDP_1:READER_1_1> CMN_1761 Timestamp Event: [Wed Sep 22 10:36:17 2010]
      LKPDP_1:READER_1_1> BLKR_16004 ERROR: Prepare failed.
      WRITER_1_*_1> WRT_8333 Rolling back all the targets due to fatal session error.
      TRANSF_1_1_1> CMN_1761 Timestamp Event: [Wed Sep 22 10:36:17 2010]
      TRANSF_1_1_1> TM_6085 A fatal error occurred at transformation [LKP_ETL_PROC_WID], and the session is terminating.
      TRANSF_1_1_1> CMN_1761 Timestamp Event: [Wed Sep 22 10:36:17 2010]
      TRANSF_1_1_1> TM_6085 A fatal error occurred at transformation [EXPTRANS1], and the session is terminating.
      TRANSF_1_1_1> CMN_1761 Timestamp Event: [Wed Sep 22 10:36:17 2010]
      TRANSF_1_1_1> TM_6085 A fatal error occurred at transformation [EXPTRANS1], and the session is terminating.
      TRANSF_1_1_1> CMN_1761 Timestamp Event: [Wed Sep 22 10:36:17 2010]
      TRANSF_1_1_1> TM_6085 A fatal error occurred at transformation [SQTRANS], and the session is terminating.
      TRANSF_1_1_1> CMN_1761 Timestamp Event: [Wed Sep 22 10:36:17 2010]
      TRANSF_1_1_1> TM_6085 A fatal error occurred at transformation [SQTRANS], and the session is terminating.
      TRANSF_1_1_1> CMN_1761 Timestamp Event: [Wed Sep 22 10:36:17 2010]
      TRANSF_1_1_1> TM_6085 A fatal error occurred at transformation [SQTRANS], and the session is terminating.
      TRANSF_1_1_1> DBG_21511 TE: Fatal Transformation Error.
      WRITER_1_*_1> WRT_8325 Final rollback executed for the target [W_ORDER_PARTY] at end of load
      WRITER_1_*_1> WRT_8035 Load complete time: Wed Sep 22 10:36:16 2010




      In the above log W_PARAM_G table is a warehouse table. We have checked Informatica to find out the issue but there is no error in informatica.

      Thanks in advance.
      Deepak.
        • 1. Re: Error with Informatica when running the data load for BI Apps
          SandeepSaini
          Hi,

          Please post your question here as this is an OBIEE forum

          Business Intelligence Applications

          you will get more response on this forum.

          Coming to your problem.Have you checked whether W_PARAM_G is there in your warehouse or not.if its there then It should contain one row .Also check whether it is under schema name 'SIEBEL'. ETL will use columns like ETL_PROC_WID from w_param_g etc in lookups and based on ETL_PROC_WID data loading will happen.

          Hope that helps.

          Regards,
          Sandeep

          Edited by: Sandeep Saini on 22-Sep-2010 05:53
          • 2. Re: Error with Informatica when running the data load for BI Apps
            799509
            Hi Sandeep ,
            Thanks for the immediate responce.

            Yes the table is existing in DWH,my concern is if you look at the issue ,it is looking into SIEBEL.W_PARAM_G.
            But W_PARAM_G is a DWH Table.Previously it was running fine,suddenly we got the issue.

            Thanks for the Help.

            Deepak.
            • 3. Re: Error with Informatica when running the data load for BI Apps
              SandeepSaini
              So W_PARAM_G is not under Siebel schema.If you are on BI apps then there will be one mapplet which do the comparison of ETL_PROC_WID of w_param_g.Just open that mapplet and in that mapplet open LKP_w_param_g.Just check what is the name of table specified there.

              If its siebel.w_param_g then change it to w_param_g.

              Also check in the other environemnt if there is any diffrence between the names of W_PARAM_G.

              Regards,
              Sandeep
              • 4. Re: Error with Informatica when running the data load for BI Apps
                799509
                Hi Sandeep,

                Yes but if you see the log ,its connecting to OLTP DB(DB2 for Siebel source DB),which it shouldnt

                KPDP_1:READER_1_1> DBG_21438 Reader: Source is [SFAPROD1], user [siebel]
                LKPDP_1:READER_1_1> CMN_1021 Database driver event...
                *CMN_1021 [DB2 Event Using Array Inserts. connect string = [SFAPROD1]. userid = [siebel]]*

                LKPDP_1:READER_1_1> BLKR_16003 Initialization completed successfully.
                LKPDP_1:READER_1_1> BLKR_16007 Reader run started.
                LKPDP_1:READER_1_1> RR_4049 SQL Query issued to database : (Wed Sep 22 10:36:17 2010)
                LKPDP_1:READER_1_1> CMN_1761 Timestamp Event: [Wed Sep 22 10:36:17 2010]
                LKPDP_1:READER_1_1> RR_4035 SQL Error [
                *[IBM][CLI Driver][DB2/AIX64] SQL0204N "SIEBEL.W_PARAM_G" is an undefined name. SQLSTATE=42704*


                I Have check the mapplet ,its same no issues in that as well.

                Regards,
                Deepak.
                • 5. Re: Error with Informatica when running the data load for BI Apps
                  SandeepSaini
                  Hi,

                  Can you check your connections in Workflow manager>Connections>Relational.You can configure your own connection also.

                  Then you need to give the same connection in specific_workflow>Right click>edit>mapping>Source qualifier.

                  You need to give correct connection information fo both source and target.

                  Can you check these things and confirm.

                  Regards,
                  Sandeep