9 Replies Latest reply: Jun 5, 2013 2:21 AM by Amanullah Khan RSS

    Data not getting inserted into Target Warehouse Table,

    Amanullah Khan
      Regarding an issue we are facing from last sunday(26thmay2013) the following workflow is facing a scenario,
      where the workflow continues to run but no data is getting inserted into the Shown Target Table.

      we could see that the Process has run for nearly One whole day, but not even a single row has been inserted.
      We tried creating a similar mapping with the same source and target tables, still facing the same issue.
      Can you kindly provide your inputs in this regard?
        • 1. Re: Data not getting inserted into Target Warehouse Table,
          Veeresh Rayan
          Please paste entire session log file..people will be able to help u only then..

          Regards,
          Veeresh Rayan
          • 2. Re: Data not getting inserted into Target Warehouse Table,
            Amanullah Khan
            So nice of you to have replied Veeresh.

            I have attached the Session Log of that particular session herewith :




            8.1.0 š ?http://www.informatica.com/pcsf/logservice:LogCallContext:1.0.1 BIA_IS IS node01_ukddxbvla07.unikai.com #| Domain_ukddxbvla07.unikai.com BIA_RS
            Custom_SDE WF_SDE_BATCH_DETAILS_Fact s_SDE_BATCH_DETAILS_Fact 29 30833 89842 0 0 19655 5ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï 2 TM_6014 NInitializing session [s_SDE_BATCH_DETAILS_Fact] at [Wed May 29 13:31:21 2013]. DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï 2 TM_6683 Repository Name: [BIA_RS] DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï 2 TM_6684 Server Name: [BIA_IS] DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï 2 TM_6686 Folder: [Custom_SDE] DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï 2 TM_6685 KWorkflow: [WF_SDE_BATCH_DETAILS_Fact] Run Instance Name: [] Run Id: [89842] DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï 2 TM_6101 :Mapping name: SDE_BATCH_DETAILS_Fact [version CheckedOut]. DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï 8 TM_6963 )Pre 85 Timestamp Compatibility is Enabled DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï 8 TM_6964 6Date format for the Session is [MM/DD/YYYY HH24:MI:SS] DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ] TM_6708 5Using configuration property [DisableDB2BulkMode,Yes] DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ] TM_6708 <Using configuration property [overrideMpltVarWithMapVar,Yes] DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ^ TM_6703 Session [s_SDE_BATCH_DETAILS_Fact] is run by 64-bit Integration Service [node01_ukddxbvla07.unikai.com], version [9.0.1 HotFix2], build [1111]. DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ^
            PETL_24058 Running Partition Group [1]. MANAGER ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï _
            PETL_24000 &Parallel Pipeline Engine initializing. MANAGER ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï `
            PETL_24001 !Parallel Pipeline Engine running. MANAGER ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï a
            PETL_24003 Initializing session run. MANAGER ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï a CMN_1569 Server Mode: [UNICODE] MAPPING ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï b CMN_1570 -Server Code page: [UTF-8 encoding of Unicode] MAPPING ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï b TM_6151 #The session sort order is [Binary]. MAPPING ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï b TM_6185 :Warning. Code page validation is disabled in this session. MAPPING ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï b TM_6156 ¬Using low precision processing. MAPPING ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï b TM_6180 -Deadlock retry logic will not be implemented. MAPPING ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï b TM_6187 0Session target-based commit interval is [10000]. MAPPING ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ‹ TM_6307 DTM error log disabled. MAPPING ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï Œ TE_7022 TShmWriter: Initialized MAPPING ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ‘ TM_6007 CDTM initialized successfully for session [s_SDE_BATCH_DETAILS_Fact] MAPPING ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï “
            PETL_24033 "All DTM Connection Info: [<NONE>]. DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ”
            PETL_24004 CPETL_24004 Starting pre-session tasks. : (Wed May 29 13:31:21 2013) MANAGER ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï —      DBG_21349 “No load constraints for Target [WC_BATCH_DETAILS_FS]. It does not reference any primary table from the same active source or transaction generator. PRE-SESS ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï —      DBG_21104 DDetermining truncate order for [WC_BATCH_DETAILS_FS] [instance id:3] PRE-SESS ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï —      DBG_21531 T Truncate order for [WC_BATCH_DETAILS_FS] [instance id:3] determined successfully PRE-SESS ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡ WRT_8365 _WRT_8365 Starting truncate for target table [WC_BATCH_DETAILS_FS]. : (Wed May 29 13:31:21 2013) PRE-SESS ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡ WRT_8022 _WRT_8022 Truncate table for table [WC_BATCH_DETAILS_FS] succeeded. : (Wed May 29 13:31:21 2013) PRE-SESS ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡!
            PETL_24027 PPETL_24027 Pre-session task completed successfully. : (Wed May 29 13:31:21 2013) MANAGER ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡!
            PETL_24006 Starting data movement. DIRECTOR ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡# TM_6660 HTotal Buffer Pool size is 24000000 bytes and Block size is 128000 bytes. MAPPING ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡'      DBG_21438 ,Reader: Source is [UNIKAI_PROD], user [apps]
            READER_1_1_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡)
            BLKR_16051 LSource database connection [EBS_PROD] code page: [UTF-8 encoding of Unicode]
            READER_1_1_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡€
            BLKR_16003 &Initialization completed successfully.
            READER_1_1_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡ƒ WRT_8146 HWriter: Target is database [PRODOBI], user [DATA_BIAPPS], bulk mode [ON]
            WRITER_1_*_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡ƒ WRT_8106 8Warning! Bulk Mode session - recovery is not guaranteed.
            WRITER_1_*_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡º WRT_8221 QTarget database connection [DataWarehouse] code page: [UTF-8 encoding of Unicode]
            WRITER_1_*_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡¿ WRT_8397 Appending output data to file [u01/OBIEE/Informatica/9.0.1/server/infa_shared/BadFiles/DataWarehouse] (initial size [318915]).
            WRITER_1_*_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡¿ WRT_8124 HTarget Table WC_BATCH_DETAILS_FS :SQL INSERT statement:
            INSERT INTO WC_BATCH_DETAILS_FS(REQUEST_ID,REQUEST_DATE,BATCH_ID,BATCH_NUMBER,BATCH_STATUS,ORGANIZATION_ID,ORGANIZATION_CODE,ORGANIZATION_NAME,INVENTORY_ITEM_ID,ITEM_CODE,ITEM_DESCRIPTION,CHARGE_TYPE,CHARGE_TYPE_SUBCATEGORY,CHARGE_ITEM_ID,CHARGE_ITEM_CODE,CHARGE_ITEM_DESCRIPTION,TRANSACTION_DATE,INGREDIENT_QUANTITY,UOM,INGREDIENT_COST_RATE,INGREDIENT_VALUE,BATCH_QUANTITY,BATCH_RATE,DAILY_BATCH_QUANTITY,MONTHLY_BATCH_QUANTITY,ITEM_COST) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            WRITER_1_*_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡¿ WRT_8020 WNo column marked as primary key for table [WC_BATCH_DETAILS_FS]. UPDATEs Not Supported.
            WRITER_1_*_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡¿ WRT_8270 FTarget connection group #1 consists of target(s) [WC_BATCH_DETAILS_FS]
            WRITER_1_*_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡À WRT_8003 ¬Writer initialization complete.
            WRITER_1_*_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡À
            BLKR_16007 Reader run started.
            READER_1_1_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡À WRT_8005 Writer run started.
            WRITER_1_*_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡À WRT_8158 u

            *****START LOAD SESSION*****

            Load Start Time: Wed May 29 13:31:21 2013

            Target tables:

            WC_BATCH_DETAILS_FS


            WRITER_1_*_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡Ã RR_4029 ÓSQ Instance [SQ_XX_BATCH_DETAILS] User specified SQL Query [SELECT XX_BATCH_DETAILS.REQUEST_ID, XX_BATCH_DETAILS.REQUEST_DATE, XX_BATCH_DETAILS.BATCH_ID, XX_BATCH_DETAILS.BATCH_NUMBER, XX_BATCH_DETAILS.BATCH_STATUS, XX_BATCH_DETAILS.ORGANIZATION_ID, XX_BATCH_DETAILS.ORGANIZATION_CODE, XX_BATCH_DETAILS.ORGANIZATION_NAME, XX_BATCH_DETAILS.INVENTORY_ITEM_ID, XX_BATCH_DETAILS.ITEM_CODE, XX_BATCH_DETAILS.ITEM_DESCRIPTION, XX_BATCH_DETAILS.CHARGE_TYPE, XX_BATCH_DETAILS.CHARGE_TYPE_SUBCATEGORY, XX_BATCH_DETAILS.CHARGE_ITEM_ID, XX_BATCH_DETAILS.CHARGE_ITEM_CODE, XX_BATCH_DETAILS.CHARGE_ITEM_DESCRIPTION, XX_BATCH_DETAILS.TRANSACTION_DATE, XX_BATCH_DETAILS.INGREDIENT_QUANTITY, XX_BATCH_DETAILS.UOM, XX_BATCH_DETAILS.INGREDIENT_COST_RATE, XX_BATCH_DETAILS.INGREDIENT_VALUE, XX_BATCH_DETAILS.BATCH_QUANTITY, XX_BATCH_DETAILS.BATCH_RATE, XX_BATCH_DETAILS.DAILY_BATCH_QUANTITY, XX_BATCH_DETAILS.MONTHLY_BATCH_QUANTITY,XX_DAILY_ITEM_COST_V.ITEM_COST
            FROM
            XX_BATCH_DETAILS, XX_DAILY_ITEM_COST_V where
            XX_DAILY_ITEM_COST_V.inventory_item_id(+)= XX_BATCH_DETAILS.charge_item_id
            and XX_DAILY_ITEM_COST_V.organization_id(+)= XX_BATCH_DETAILS.organization_id
            and XX_DAILY_ITEM_COST_V.transaction_date(+)= XX_BATCH_DETAILS.transaction_date]
            READER_1_1_1 ÿÿ š 9http://www.informatica.com/pcsf/logservice:LogEvent:1.0.1 >ï ¡Ã RR_4049 ARR_4049 SQL Query issued to database : (Wed May 29 13:31:21 2013)
            READER_1_1_1 ÿÿ
            • 3. Re: Data not getting inserted into Target Warehouse Table,
              Veeresh Rayan
              Your table will not have any entry since session has not completed yet.

              Also I can see from log that informatica has not even returned first row.It has issued SQL to DB and waiting for it to complete.

              You ran this SQL in toad/SQL developer and you will never get a row.

              SELECT XX_BATCH_DETAILS.REQUEST_ID, XX_BATCH_DETAILS.REQUEST_DATE, XX_BATCH_DETAILS.BATCH_ID, XX_BATCH_DETAILS.BATCH_NUMBER, XX_BATCH_DETAILS.BATCH_STATUS, XX_BATCH_DETAILS.ORGANIZATION_ID, XX_BATCH_DETAILS.ORGANIZATION_CODE, XX_BATCH_DETAILS.ORGANIZATION_NAME, XX_BATCH_DETAILS.INVENTORY_ITEM_ID, XX_BATCH_DETAILS.ITEM_CODE, XX_BATCH_DETAILS.ITEM_DESCRIPTION, XX_BATCH_DETAILS.CHARGE_TYPE, XX_BATCH_DETAILS.CHARGE_TYPE_SUBCATEGORY, XX_BATCH_DETAILS.CHARGE_ITEM_ID, XX_BATCH_DETAILS.CHARGE_ITEM_CODE, XX_BATCH_DETAILS.CHARGE_ITEM_DESCRIPTION, XX_BATCH_DETAILS.TRANSACTION_DATE, XX_BATCH_DETAILS.INGREDIENT_QUANTITY, XX_BATCH_DETAILS.UOM, XX_BATCH_DETAILS.INGREDIENT_COST_RATE, XX_BATCH_DETAILS.INGREDIENT_VALUE, XX_BATCH_DETAILS.BATCH_QUANTITY, XX_BATCH_DETAILS.BATCH_RATE, XX_BATCH_DETAILS.DAILY_BATCH_QUANTITY, XX_BATCH_DETAILS.MONTHLY_BATCH_QUANTITY,XX_DAILY_ITEM_COST_V.ITEM_COST
              FROM
              XX_BATCH_DETAILS, XX_DAILY_ITEM_COST_V where
              XX_DAILY_ITEM_COST_V.inventory_item_id(+)= XX_BATCH_DETAILS.charge_item_id
              and XX_DAILY_ITEM_COST_V.organization_id(+)= XX_BATCH_DETAILS.organization_id
              and XX_DAILY_ITEM_COST_V.transaction_date(+)= XX_BATCH_DETAILS.transaction_date

              You might need to recheck your source system and show your DBA's to tune the DB.

              Mark helpful or correct if it helps,

              Regards,
              Veeresh Rayan
              • 4. Re: Data not getting inserted into Target Warehouse Table,
                Amanullah Khan
                Thanks for the update Veersh.
                But can you kindly,be a little more clear and specific?
                We cant figure out anywhere for a solution for this issue.
                We have been stuck up with thiis for the past four days.
                What exactly do we have to do, to sort this out.
                • 5. Re: Data not getting inserted into Target Warehouse Table,
                  Amanullah Khan
                  As an update, I would like to mention that when trying the above query in the source system we do get the exact output .
                  • 6. Re: Data not getting inserted into Target Warehouse Table,
                    Amanullah Khan
                    ACtually we are getting the required output, when running the above query you had mentioned inyour last post Veeresh.
                    So theres no issue in the Source side I guess.
                    • 7. Re: Data not getting inserted into Target Warehouse Table,
                      Veeresh Rayan
                      But tell me how long that SQL took the time to get you the first row in toad?..Is there anything u missed out in Session log after this statement query issued to Database ?.If yes please post the remainging session log too.

                      If nothing remained in the session log then do these action itmes.
                      1)Check if there is any table lock happended on those source tables.
                      2)Abort the ongoing ETL process and mark as completed and then start the process freshly.Monitor operating system RAM utilization closely and hows RAM is getting utilized.

                      Mark as correct or helpful if it helps,

                      Regards,
                      Veeresh Rayan
                      • 8. Re: Data not getting inserted into Target Warehouse Table,
                        Amanullah Khan
                        Tried both the steps as mentioned by you Veeresh.
                        Still unable to overcome the issue.
                        • 9. Re: Data not getting inserted into Target Warehouse Table,
                          Amanullah Khan
                          Now its working fine.
                          Tried randomly after few days.
                          Dont knw what actually caused that error.