This discussion is archived
9 Replies Latest reply: Jun 5, 2013 12:21 AM by Amanullah Khan RSS

Data not getting inserted into Target Warehouse Table,

Amanullah Khan Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Now its working fine.
    Tried randomly after few days.
    Dont knw what actually caused that error.

Legend

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