Load Data Warehouse job will fail if baseline is updated by OWB on the ahead of time zone from PPM.

Document ID : KB000044430
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:

 

If baseline is updated by Open Workbench(OWB) on the ahead of time zone from PPM, Load Data Warehouse job - Full Load will create orphan record in DWH_INV_PROJECT_LN table. After that, the subsequent run of Load Data Warehouse job will fail for a long hours with below error messages.

 

Couldn't execute SQL: ALTER TABLE DWH_INV_PROJECT_LN ENABLE VALIDATE CONSTRAINT DWH_INV_PROJECT_LN_FK1
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (XXXXXXX.DWH_INV_PROJECT_LN_FK1) - parent keys not found ...
 

 

Steps to Reproduce:

 

1.        Configure CA PPM with Data Warehouse and app/db on same date/time/timezone 
2.        Ensure Load Data Warehouse runs fine with no errors 
3.        Now prepare a separate server with OWB installed 
4.        Check the app/db time and set the server time for OWB server to be AHEAD in time.
           For example, the time difference should be 16 hours ahead (PST to JST).
           In those cases the date will be sometimes on the next day. 
5.        Now restart OWB and open a project from CA PPM. Go to Project Tab. 
6.        Select Scope Project, Baseline Settings - Set, click OK 
7.        When prompted if you would like to continue, confirm with Yes 
8.        Now check in PRJ_BASELINE_DETAILS for the same project (example query below, replace the internal id with the correct one): 

              select LAST_UPDATED_DATE from PRJ_BASELINE_DETAILS
              where object_type ='PROJECT' 
              and     object_id =XXXXXXXX 

 

9.        Note the time saved there is the actual time in future, which is set on the OWB client server 
10.      Now run Load Data Warehouse job - Full Load 

 

 

 

Expected result:

 

The project to be brought in Data Warehouse investments successfully

 

 

 

Actual result:

 

Load Data Warehouse job fails, with error: ORA-02298: cannot validate (XXXXXXX.DWH_INV_PROJECT_LN_FK1) - parent keys not found.
Any subsequent runs will fail too, until the future date from step 9 is reached.

 

 

 

Environment:

 

CA PPM Clarity 14.2, 14.3, 14.4

 

 

 

Cause:

 

CLRT-80691

 

 

 

Workaround:

 

step1. To identify orphan project in DWH schema

 

select investment_key 
from dwh_inv_project_ln
where investment_key not in (select investment_key from dwh_inv_investment) 

 

step2. To identify orphan project in PPM schema.

 

select object_id
from PRJ_BASELINE_DETAILS
where object_type ='PROJECT'
and last_updated_date > sysdate;
? 

 

step3. If investment_key from step1 and object_id from step2 are the same, update last_updated_date of PRJ_BASELINE_DETAILS with sysdate.

 

update PRJ_BASELINE_DETAILS
set last_updated_date = sysdate 
where object_type ='PROJECT'
and object_id = xxxxxxxxxxx
???????<------- object_id from step2
and last_updated_date > sysdate 

 

step4. Run DWH Full load job

Performing above steps, orphan record will be resolved.