BDSSA: 3_PROPERTY step of ETL failing after BSA 8.9.01 or 8.9.02 upgrade - ORA-20001: Error while reading Template Transpose Metadata Table for CVE_IDS*

Version 1
    Share:|

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


    PRODUCT:

    BMC Decision Support - Server Automation (5 Viewer, 1 Query License)


    COMPONENT:

    BMC Decision Support - Server Automation (5 Viewer, 1 Query License)


    APPLIES TO:

    BDSSA 8.9 Hotfix 1 BSA 8.9.01 Oracle environments



    PROBLEM:

     

    The BMC BladeLogic Server Automation (BSA) environment has been upgraded to BSA 8.9.01 or 8.9.02 and the required BDSSA 8.9 hotfix has been applied.

    The ETL job now fails in the following step: 

    TEMPLATE_PROPERTY - LOAD_TEMPLATE_PROP_TRANSPOSE - LOAD_TEMPLATE_PROP_TRANSPOSE

      

    with the following error:

    ODI-1228: Task LOAD_TEMPLATE_PROP_TRANSPOSE (Procedure) fails on the target ORACLE connection BSA_phy_dw_svr_site1. 
    Caused By: java.sql.SQLException: ORA-20001: Procedure LOAD_TEMPLATE_PROP_TRANSPOSE Failed with error message 
    ORA-20001: Error while reading Template Transpose Metadata Table for CVE_IDS* 
    ORA-06512: at "BSARA_DW.LOAD_TEMPLATE_PROP_TRANSPOSE", line 583 ORA-06512:
    at line 2

     

     


    CAUSE:

    The CVE_IDS* property was added in BSA 8.9.01 but is not present in the BDSSA TEMPLATE_PROPERTY Table


    SOLUTION:

    1) Run the following SQL statements as the BSARA_DW Oracle DB user to reset the loading of the TEMPLATE_PROPERTY table in the warehouse schema:

    a)
    Select count(*) from TEMPLATE_PROPERTY; -- Get the original record count.

    b) 
    Create table TEMPLATE_PROPERTY_BCKUP as select * from TEMPLATE_PROPERTY; -- Create backup table

    c) 
    Select count(*) from TEMPLATE_PROPERTY_BCKUP;   -- Check the record count is same as notes in step 1b.

    d)
    TRUNCATE TABLE TEMPLATE_PROPERTY;

    e)
    update table_last_load set LAST_LOAD_DATETIME=to_date('1900-01-01', 'YYYY-MM-DD'),IS_FIRST_LOAD='Y' WHERE     BL_SITE_ID = 1 AND TABLE_NAME='TEMPLATE_PROPERTY';
    commit;

    2) Then rerun the BDSSA ETL job and the issue should be resolved.

    Defect QM002394532  was created to have this SQL automatically be performed by the BDSSA enablement hotfixes and avoid the ETL failure:

    QM002394532 - BDSSA: ETL failure during LOAD_TEMPLATE_PROP_TRANSPOSE after upgading BSA to 8.9.01 and applying BDSSA enablement hotfix


    Article Number:

    000137663


    Article Type:

    Solutions to a Product Problem



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