This discussion is archived
6 Replies Latest reply: Mar 4, 2013 4:54 AM by 993605 RSS

Table DWH.W_STATUS_D has index defined upon it.

993605 Newbie
Currently Being Moderated
Hi Friends,

If any one guide me while Execute the Execution Plan in DAC. We are getting below error. Session run perfectly without failure but data not loaded into W_STATUS_D table.
Please check the below error.


LKPDP_4:TRANSF_1_1> DBG_21294 Lookup cache creation completed : (Tue Feb 26 04:08:49 2013)
LKPDP_4:TRANSF_1_1> CMN_1671 Created new cache files PMLKUP19115_262146_0_3595L32.[dat/idx] in directory /d02/informatica/server/infa_shared/Cache for Lookup [mplt_SIL_StatusDimension.Lkp_W_USER_D_Changed_By_Wid].
LKPDP_4:TRANSF_1_1> DBG_21641 mplt_SIL_StatusDimension.Lkp_W_USER_D_Changed_By_Wid: Index cache size = [1740800], Data cache size = [2097152]
LKPDP_4:TRANSF_1_1> DBG_21216 Finished transformations for Source Qualifier [mplt_SIL_StatusDimension.Lkp_W_USER_D_Changed_By_Wid{{DSQ}}]. Total errors [0]
TRANSF_1_1_1> DBG_21298 Existing cache created with override 'SELECT LOOKUP_TABLE.ROW_WID AS ROW_WID, LOOKUP_TABLE.DATASOURCE_NUM_ID AS DATASOURCE_NUM_ID, LOOKUP_TABLE.INTEGRATION_ID AS INTEGRATION_ID, LOOKUP_TABLE.EFFECTIVE_FROM_DT AS EFFECTIVE_FROM_DT, LOOKUP_TABLE.EFFECTIVE_TO_DT AS EFFECTIVE_TO_DT FROM W_USER_D LOOKUP_TABLE ORDER BY DATASOURCE_NUM_ID,INTEGRATION_ID,EFFECTIVE_FROM_DT,EFFECTIVE_TO_DT,ROW_WID' by lookup [mplt_SIL_StatusDimension.Lkp_W_USER_D_Changed_By_Wid] is re-used by lookup [mplt_SIL_StatusDimension.Lkp_W_USER_D_Created_By_Wid]

WRITER_1_*_1> WRT_8167 Start loading table [W_STATUS_D] at: Tue Feb 26 04:08:49 2013
TRANSF_1_1_1> DBG_21216 Finished transformations for Source Qualifier [Sq_W_STATUS_DS]. Total errors [0]
WRITER_1_*_1> CMN_1761 Timestamp Event: [Tue Feb 26 04:08:49 2013]
WRITER_1_*_1> WRT_8229 Database errors occurred:
ORA-26002: Table DWH.W_STATUS_D has index defined upon it.

Database driver error...
Function Name : Prepare DPL
SQL Stmt : INSERT INTO W_STATUS_D(ROW_WID,W_STATUS_CLASS,STATUS_CODE,STATUS_NAME,STATUS_DESC,SUBSTATUS_CODE,SUBSTATUS_NAME,SUBSTATUS_DESC,W_STATUS_CODE,W_STATUS_DESC,W_SUBSTATUS_CODE,W_SUBSTATUS_DESC,ACTIVE_FLG,CREATED_BY_WID,CHANGED_BY_WID,CREATED_ON_DT,CHANGED_ON_DT,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,AUX3_CHANGED_ON_DT,AUX4_CHANGED_ON_DT,DELETE_FLG,W_INSERT_DT,W_UPDATE_DT,DATASOURCE_NUM_ID,ETL_PROC_WID,INTEGRATION_ID,TENANT_ID,X_CUSTOM) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Oracle Fatal Error
Database driver error...
Function Name : Execute Multiple
SQL Stmt : INSERT INTO W_STATUS_D(ROW_WID,W_STATUS_CLASS,STATUS_CODE,STATUS_NAME,STATUS_DESC,SUBSTATUS_CODE,SUBSTATUS_NAME,SUBSTATUS_DESC,W_STATUS_CODE,W_STATUS_DESC,W_SUBSTATUS_CODE,W_SUBSTATUS_DESC,ACTIVE_FLG,CREATED_BY_WID,CHANGED_BY_WID,CREATED_ON_DT,CHANGED_ON_DT,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,AUX3_CHANGED_ON_DT,AUX4_CHANGED_ON_DT,DELETE_FLG,W_INSERT_DT,W_UPDATE_DT,DATASOURCE_NUM_ID,ETL_PROC_WID,INTEGRATION_ID,TENANT_ID,X_CUSTOM) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Oracle Fatal Error
WRITER_1_*_1> CMN_1761 Timestamp Event: [Tue Feb 26 04:08:49 2013]
WRITER_1_*_1> WRT_8425 ERROR: Writer execution failed.
WRITER_1_*_1> CMN_1761 Timestamp Event: [Tue Feb 26 04:08:49 2013]
WRITER_1_*_1> WRT_8076 Writer run terminated. [Commit Error]
WRITER_1_*_1> WRT_8333 Rolling back all the targets due to fatal session error.
WRITER_1_*_1> WRT_8325 Final rollback executed for the target [W_STATUS_D] at end of load
WRITER_1_*_1> CMN_1761 Timestamp Event: [Tue Feb 26 04:08:49 2013]
WRITER_1_*_1> WRT_8081 Writer run terminated. [Error in loading data to target table [W_STATUS_D]]
WRITER_1_*_1> WRT_8168 End loading table [W_STATUS_D] at: Tue Feb 26 04:08:49 2013
WRITER_1_*_1> WRT_8035 Load complete time: Tue Feb 26 04:08:49 2013                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 1. Re: Table DWH.W_STATUS_D has index defined upon it.
    Benoît Journeyer
    Currently Being Moderated
    Hi,

    Do you run a Full Load or an Incremental Load ?
    I think you run a Full Load and you have this issue (ORA-26002: Table DWH.W_STATUS_D has index defined upon it) because Informatica want to load data in a Bulk mode on this table. But you can load data in a Bulk mode only if there is no index on your table.
    DAC deletes index, then loads data in a Bulk mode, then recreates index to finalize task.

    Please see the status of your DAC task to see if index have been deleted. If it is the case maybe another index was created on the W_STATUS_D table but not declared on the DAC for this table.

    Hope it helps,

    Benoit
  • 2. Re: Table DWH.W_STATUS_D has index defined upon it.
    993605 Newbie
    Currently Being Moderated
    Hi Benoit,

    I'm running Full Load process. The major problem is SDE Extraction while running SDE mapping i'm getting below error.


    Cannot find specified parameter file [d02/informatica/server/infa_shared/SrcFiles/SDE_ORAR1213_Adaptor.SDE_ORA_AbsenceEvent_Full.txt] for [session [SDE_ORA_AbsenceEvent_Full.SDE_ORA_AbsenceEvent_Full]].

    Session task instance [SDE_ORA_AbsenceEvent_Full] : [PETL_24049 Failed to get the initialization properties from the master service process for the prepare phase [Session task instance [SDE_ORA_AbsenceEvent_Full]: Unable to read variable definition from parameter file [d02/informatica/server/infa_shared/SrcFiles/SDE_ORAR1213_Adaptor.SDE_ORA_AbsenceEvent_Full.txt].] with error code [174107296].]

    Please quick response for me.

    Thanks,
    Siva
  • 3. Re: Table DWH.W_STATUS_D has index defined upon it.
    Benoît Journeyer
    Currently Being Moderated
    Hi,

    are you able to navigate to the folder /d02/informatica/server/infa_shared/SrcFiles/ and see if the file SDE_ORAR1213_Adaptor.SDE_ORA_AbsenceEvent_Full.txt exist ?
    I expect copying it - there is part of the installation/post installation tasks, did you miss that step maybe?

    Regards,

    Benoit
  • 4. Re: Table DWH.W_STATUS_D has index defined upon it.
    993605 Newbie
    Currently Being Moderated
    Hi,

    Yes,I have navigated that file in that particular location. this file having below parameters. Same parameters i have kept in DAC Parameters.

    Edited by: 990602 on Feb 27, 2013 4:53 AM
  • 5. Re: Table DWH.W_STATUS_D has index defined upon it.
    AhsanShah Expert
    Currently Being Moderated
    did you configure any of the codes manually as part of the csv file configuration? This error can occur if there is duplicate codes either in the source system or in a manually configured csv file. Run the SDE SQL directly and see if you are seeing duplicates. if helpful, pls mark
  • 6. Missing initial value
    993605 Newbie
    Currently Being Moderated
    Hi
    Can you please any one help me .While running information workflow through DAC. I'm getting below error. First session completed without errors ,second session is failure.

    Workflow name ---> Session1 -----> Session2

    Error
    -------


    VAR_27026 Error : Missing initial value for session parameter:[$DBConnection_OLAP)

    Workflow LOg
    --------------------

    ERROR : LM_36488 [Mon Mar 04 02:01:58 2013] : (9768|-1599124592) Session task instance [SDE_ORA_ExchangeRateGeneral_Compress_Full] : [CMN_1761 Timestamp Event: [Mon Mar 04 02:01:58 2013]]
    ERROR : LM_36488 [Mon Mar 04 02:01:58 2013] : (9768|-1599124592) Session task instance [SDE_ORA_ExchangeRateGeneral_Compress_Full] : [VAR_27026 Error: Missing initial value for session parameter:[$DBConnection_OLAP].]
    ERROR : LM_36488 [Mon Mar 04 02:01:58 2013] : (9768|-1599124592) Session task instance [SDE_ORA_ExchangeRateGeneral_Compress_Full] : [CMN_1761 Timestamp Event: [Mon Mar 04 02:01:58 2013]]
    ERROR : LM_36488 [Mon Mar 04 02:01:58 2013] : (9768|-1599124592) Session task instance [SDE_ORA_ExchangeRateGeneral_Compress_Full] : [TM_6270 Error: Variable parameter expansion error.]
    ERROR : LM_36488 [Mon Mar 04 02:01:58 2013] : (9768|-1599124592) Session task instance [SDE_ORA_ExchangeRateGeneral_Compress_Full] : [CMN_1761 Timestamp Event: [Mon Mar 04 02:01:58 2013]]
    ERROR : LM_36488 [Mon Mar 04 02:01:58 2013] : (9768|-1599124592) Session task instance [SDE_ORA_ExchangeRateGeneral_Compress_Full] : [TM_6163 An error occurred initializing variables and parameters for the partition.]
    ERROR : LM_36320 [Mon Mar 04 02:02:00 2013] : (9768|-1609614448) Session task instance [SDE_ORA_ExchangeRateGeneral_Compress_Full]: Execution failed.


    Thanks,
    Siva

    Edited by: 990602 on Mar 4, 2013 4:54 AM

Legend

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