TrueSight Server Automation Datawarehouse (TSSA-DW) - ETL failure  in 2_FOUNDATION and FOUNDATION_FACT F_JOB_RUN

Version 2
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    BladeLogic Decision Support for Server Automation (5 Report Viewers, 1 Query Studio)


    APPLIES TO:

    BMC BladeLogic Decision Support for Server Automation (5 Report Viewers, 1 Query Studio)



    PROBLEM:

    The TSSA-DW ETL job is failing in the following step:

    Session: FOUNDATION_FACT
    Step_name: F_JOB_RUN

      

    With the following error message:

    ODI-1228: Task F_JOB_RUN (Integration) fails on the target MICROSOFT_SQL_SERVER connection BSA_phy_dw_svr_site1.
    Caused By: com.microsoft.sqlserver.jdbc.SQLServerException: The INSERT statement conflicted with the FOREIGN KEY constraint "REFAPPLICATION_SERVER16". The conflict occurred in database "TSSADW_DW_DB", table "dbo.APPLICATION_SERVER".

      

    Message: 2291 : 23000 : java.sql.SQLException: ORA-02291: integrity constraint (BSARA_DW.REFAPPLICATION_SERVER16) violated - parent key not found
    java.sql.SQLException: ORA-02291: integrity constraint (BSARA_DW.REFAPPLICATION_SERVER16) violated - parent key not found

      

     

     


    SOLUTION:

     

    The error basically means that entries in the JOB_RUN are referencing an appserver which no longer exists in the application_server table. This should not be a common occurrence but has been encountered a few times in certain environments.

      

    BMC Support assistance will be required to remediate this problem data condition in the TSSA DB. Run the following on the core BladeLogic DB and send the output to BMC Support referencing this KB article.

      

    Select JOB_RUN_ID, JOB_ID, JOB_VERSION_ID, APPLICATION_SERVER_ID from JOB_RUN where APPLICATION_SERVER_ID not in (Select APPLICATION_SERVER_ID from APPLICATION_SERVER);

      

    Select APPLICATION_SERVER_ID, IS_DELETED, APP_SERVER_NAME from APPLICATION_SERVER;

      

     

     


    Article Number:

    000302929


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles