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

 

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