Load Data Warehouse fails after 15.8 upgrade

[Daniel Galvez]

Hi Community,

We upgrade our environment to the last version of Clarity (15.8) without any issue. But when I execute the job "Load Data Warehouse" after some minutes of the execution of a full load I got the following error:

ERROR 2020-06-24 11:03:29,112 [dwh_enable_constraints_oracle - Execute SQL script] dwh.event Execute SQL script - Unexpected error
ERROR 2020-06-24 11:03:29,127 [dwh_enable_constraints_oracle - Execute SQL script] dwh.event Execute SQL script - org.pentaho.di.core.exception.KettleStepException:
Error while running this step!

Couldn't execute SQL: ALTER TABLE DWH_INV_IDEA_LN ENABLE VALIDATE CONSTRAINT DWH_INV_IDEA_LN_FK1

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_INV_IDEA_LN_FK1) - parent keys not found



at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251)
at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
Couldn't execute SQL: ALTER TABLE DWH_INV_IDEA_LN ENABLE VALIDATE CONSTRAINT DWH_INV_IDEA_LN_FK1

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_INV_IDEA_LN_FK1) - parent keys not found


at org.pentaho.di.core.database.Database.execStatement(Database.java:1432)
at org.pentaho.di.core.database.Database.execStatements(Database.java:1538)
at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:225)
... 2 more
Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_INV_IDEA_LN_FK1) - parent keys not found

at com.ca.clarity.jdbc.oraclebase.ddcr.b(Unknown Source)
at com.ca.clarity.jdbc.oraclebase.ddcr.a(Unknown Source)
at com.ca.clarity.jdbc.oraclebase.ddcq.b(Unknown Source)
at com.ca.clarity.jdbc.oraclebase.ddcq.a(Unknown Source)
at com.ca.clarity.jdbc.oracle.ddam.t(Unknown Source)
at com.ca.clarity.jdbc.oraclebase.dde3.y(Unknown Source)
at com.ca.clarity.jdbc.oraclebase.dde3.u(Unknown Source)
at com.ca.clarity.jdbc.oraclebase.dde3.execute(Unknown Source)
at org.pentaho.di.core.database.Database.execStatement(Database.java:1406)
... 4 more

So, digging into the error I thought some "corruption" for the schema DWH that was migrated, so I imported the default schema PPM_DWH from the installer and execute all the deployment of the (admin content-jaspersoft csk upgrade; admin jaspersoft syncPPMContext; admin content-jaspersoft csk restoreDomains; admin update jasperParameters) without any issue; also reconfigure at NSA console to the new schema, dblink, etc, etc but I'm getting the same error.
Does somebody has any idea What parent keys from DWH_INV_IDEA_LN_FK1 are missing? Don't forget that this is the schema imported from the installer "dwh_oracle_base.db" and it suppose that doesn't has any issue.
The database is Oracle 12.2.0.1; Jasper is 7.1; Java is openjdk version "11.0.5"

Hope somebody can help me!!!
Kind regards,
Daniel

[Nika Hadzhikidi]

Hi Daniel

 

Your issue is similar to something I've worked before and documented here:

https://knowledge.broadcom.com/external/article?articleId=142462

So please try the following:

delete from DWH_INV_IDEA_LN where INVESTMENT_KEY not in
(select INVESTMENT_KEY from DWH_INV_INVESTMENT)

then run a Full Load

If it fails again, check what investment comes up with this query:

select * from DWH_INV_IDEA_LN  where INVESTMENT_KEY not in
(select INVESTMENT_KEY from DWH_INV_INVESTMENT)

Hope this helps -Nika

------------------------------
Nika Hadzhikidi
Sr Principal Support Engineer
Broadcom
------------------------------

[Daniel Galvez]

Hi Nika!!!


How are you? Hope everything great! Sorry for the delay in my response but I was like a detective in this case... :s I started again the upgrade process from 15.5 to 15.8 and take a deep look into Clarity if I missed something but last night I finished again the upgrade but I still have the same behavior. I followed those articles

https://knowledge.broadcom.com/external/article?articleId=35711
https://knowledge.broadcom.com/external/article?articleId=142462

Without "luck", I also tried the deletion of the records from the sql you shared, but the the strange thing is:
- After the upgrade (from 15.1 to 15.5): I configured Clarity to "enable" Roadmaps and Investments, this works great (in Clarity 15.5).
- In 15.5 I corrected the "issues" in the health report for Data warehouse (Administration/Security and Diagnostics/Health Report)
- In 15.8 As far I tested everything is working, also the new functionalities like Hierarchies.

I don't know what else to do... we also opened I case but we don't move on...
Regards,
Daniel

[Nika Hadzhikidi]

Hi Daniel
Once it fails again, please 

check what investment comes up with this query:

select * from DWH_INV_IDEA_LN  where INVESTMENT_KEY not in
(select INVESTMENT_KEY from DWH_INV_INVESTMENT)

Then use the investment key to open in UI and take a look. Let me know what you see!

------------------------------
Nika Hadzhikidi
Sr Principal Support Engineer
Broadcom
------------------------------