Load DataWarehouse Job Fails

[Anon Anon]

hi,

 

i am using everything in upper case but still getting the same issue.

Could you please let me know why i am getting error here.

---------------------------------------

SELECT @SELECT:INV.MANAGER_ID:MANAGERID@,
@SELECT:SRM.FULL_NAME:MANAGER_NAME@,
@SELECT:INV.LAST_UPDATED_BY:LAST_UPDATED_BY@,
@SELECT:INV.LAST_UPDATED_DATE:LAST_UPDATED_DATE@

FROM
INV_INVESTMENTS INV, INV_PROJECTS PRJ,SRM_RESOURCES SRM
WHERE
@WHERE:SECURITY:PROJECT:INV.ID@
AND INV.ID=PRJ.PRID
AND SRM.USER_ID=INV.MANAGER_ID
AND PRJ.IS_TEMPLATE=0
AND (INV.PURGE_FLAG=0 OR INV.PURGE_FLAG IS NULL)
AND INV.ID= @WHERE:PARAM:USER_DEF:INTEGER:ID@
AND
@FILTER@
@BROWSE-ONLY:AND INV.is_active=1:BROWSE-ONLY@

--------------------------------------------------------

 

Error message:- 

 

---------------------------------------------------------

 

ClarityDB - isOracle? - An error occurred executing this job entry :  Couldn't execute SQL: MERGE INTO DWH_LKP_JS_GIV_MANAGER1 TGT USING       (            select LAST_UPDATED_DATE as CLARITY_UPDATED_DATE, MANAGERID as JS_GIV_MANAGER1_KEY, MANAGER_NAME as JS_GIV_MANAGER1, to_date('2017/01/19 16:22:49', 'yyyy/mm/dd HH24:MI:SS') as dw_updated_date  from DWH_LKP_JS_GIV_MANAGER1_V@MUN580TDWH_MUN580T where 1=1 AND LAST_UPDATED_DATE >= to_date('2017/01/15 12:25:47', 'yyyy/MM/dd HH24:mi:ss')       ) SRC ON       (           SRC.JS_GIV_MANAGER1_KEY = TGT.JS_GIV_MANAGER1_KEY       ) WHEN MATCHED THEN      UPDATE SET TGT.CLARITY_UPDATED_DATE = SRC.CLARITY_UPDATED_DATE, TGT.JS_GIV_MANAGER1 = SRC.JS_GIV_MANAGER1, TGT.dw_updated_date = SRC.dw_updated_date  WHEN NOT MATCHED THEN      INSERT (CLARITY_UPDATED_DATE, JS_GIV_MANAGER1_KEY, JS_GIV_MANAGER1, dw_updated_date)      VALUES (SRC.CLARITY_UPDATED_DATE, SRC.JS_GIV_MANAGER1_KEY, SRC.JS_GIV_MANAGER1, SRC.dw_updated_date)  [CA Clarity][Oracle JDBC Driver][Oracle]ORA-30926: unable to get a stable set of

[Nika Hadzhikidi]

Hi Prakash

 

 

I branched your question in a separate topic as the issue is with a different error message and is not related to the same problem, we can deal with it here. You have the error:

 

ORA-30926: unable to get a stable set of rows

 

 

when running Data Warehouse, this usually happens when the lookup is poorly configured and returns duplicated values. Data Warehouse does not tolerate duplicate values.

 

I looked in your query and ran it as SQL (without the INV ID clause as DWH will not have this):

SELECT

INV.MANAGER_ID,

SRM.FULL_NAME,

INV.LAST_UPDATED_BY,

INV.LAST_UPDATED_DATE

 FROM

 INV_INVESTMENTS INV, INV_PROJECTS PRJ,SRM_RESOURCES SRM

 WHERE

 INV.ID=PRJ.PRID

 AND SRM.USER_ID=INV.MANAGER_ID

 AND PRJ.IS_TEMPLATE=0

 AND (INV.PURGE_FLAG=0 OR INV.PURGE_FLAG IS NULL)

AND INV.is_active=1

Your lookup has the potential to return duplicate records. In CA PPM they won't be visible as they're taken care of with the parameters but when you run this:

SELECT LAST_UPDATED_DATE AS CLARITY_UPDATED_DATE,

                MANAGERID AS JS_GIV_MANAGER1_KEY,

                MANAGER_NAME AS JS_GIV_MANAGER1,

                to_date('2017/01/19 16:22:49', 'yyyy/mm/dd HH24:MI:SS') AS dw_updated_date

FROM DWH_LKP_JS_GIV_MANAGER1_V@DBLINK

You only select the last_updated_date, manager_id, and manager_name. This is the query DWH runs.

 

You have few options here:

 

1. To fix the issue add another field in the select query to make each result unique. In your case I believe it will be enough to add INV_ID to the select statement, it depends on how you use the field.

If your lookup is in use it would be better to correct it via XOG.

Then once you're ready, add the field again to Data Warehouse and run a Full Load.

 

2. If you're not sure how to do it, or just want to have your job fixed for now, just uncheck the field and run a Full Load of the Data Warehouse. Please note that before enabling any new custom fields on Production we recommend to test this on Test or Dev environment first. Once you're ready, enable and test.

 

3. This error should no longer be reproducible on 15.1 and higher. If you upgrade you should not face the issue anymore. You may uncheck the field until upgrade if that's something that can happen soon.

 

 

Hope this helps -Nika

[Anon Anon]

Hi Nika,

 

Thanks for your reply.

 

As suggested, i have added inv.id into my lookup and run load dataware house job for full load and it's ran successfully as earlier but after running incremental, getting an error.

 

Could you please suggest me any thing here.

 

Lookup Code:-

----------------------

SELECT @SELECT:INV.ID:ID@,
@SELECT:INV.MANAGER_ID:MANAGERID@,
@SELECT:SRM.FULL_NAME:MANAGER_NAME@,
@SELECT:INV.LAST_UPDATED_BY:LAST_UPDATED_BY@,
@SELECT:INV.LAST_UPDATED_DATE:LAST_UPDATED_DATE@

FROM
INV_INVESTMENTS INV, INV_PROJECTS PRJ,SRM_RESOURCES SRM
WHERE
@WHERE:SECURITY:PROJECT:INV.ID@
AND INV.ID=PRJ.PRID
AND SRM.USER_ID=INV.MANAGER_ID
AND PRJ.IS_TEMPLATE=0
AND (INV.PURGE_FLAG=0 OR INV.PURGE_FLAG IS NULL)
AND INV.ID= @WHERE:PARAM:USER_DEF:INTEGER:ID@
AND
@FILTER@
@BROWSE-ONLY:AND INV.is_active=1:BROWSE-ONLY@

---------------------------------

 

Error message 1:- 

--------------------------------

User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING LOOKUP JOB EXECUTION FOR THE TABLE - DWH_LKP_JS_GIV_MANAGER1      at Processor.processRow(Processor.java:65)      at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)      at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)      at java.lang.Thread.run(Thread.java:745)
--------------------------

Error message 2:-

--------------------------
dwh_db_check_ - An error occurred executing this job entry :  Couldn't execute SQL: MERGE INTO DWH_LKP_JS_GIV_MANAGER1 TGT USING       (            select LAST_UPDATED_DATE as CLARITY_UPDATED_DATE, MANAGERID as JS_GIV_MANAGER1_KEY, MANAGER_NAME as JS_GIV_MANAGER1, to_date('2017/01/20 13:48:12', 'yyyy/mm/dd HH24:MI:SS') as dw_updated_date  from DWH_LKP_JS_GIV_MANAGER1_V@MUN580TDWH_MUN580T where 1=1 AND LAST_UPDATED_DATE >= to_date('2017/01/20 13:35:39', 'yyyy/MM/dd HH24:mi:ss')       ) SRC ON       (           SRC.JS_GIV_MANAGER1_KEY = TGT.JS_GIV_MANAGER1_KEY       ) WHEN MATCHED THEN      UPDATE SET TGT.CLARITY_UPDATED_DATE = SRC.CLARITY_UPDATED_DATE, TGT.JS_GIV_MANAGER1 = SRC.JS_GIV_MANAGER1, TGT.dw_updated_date = SRC.dw_updated_date  WHEN NOT MATCHED THEN      INSERT (CLARITY_UPDATED_DATE, JS_GIV_MANAGER1_KEY, JS_GIV_MANAGER1, dw_updated_date)      VALUES (SRC.CLARITY_UPDATED_DATE, SRC.JS_GIV_MANAGER1_KEY, SRC.JS_GIV_MANAGER1, SRC.dw_updated_date)  [CA Clarity][Oracle JDBC Driver][Oracle]ORA-30926: unable to get a stable set of rows in
------

[Nika Hadzhikidi]

HI Prakash,

 

I see you went with the option to correct the lookup. It still shows the same columns in the error message though. How did you update the lookup code, please check if it's saved, as at times it's not possible to save from UI if the lookup is in use.

 

The best way would be to go via XOG. After you ensure the lookup is corrected, you must restart the services and then try running the Load DWH job. See if that works.

 

If it doesn't, I'd recommend if you really need this lookup included, you experiment with bringing the lookup to return no duplicate values or upgrade to 15.1. Otherwise just remove the lookup from DWH and this will fix the issue.

 

Thanks -Nika

[Anon Anon]

Hi Nika,

 

Thanks for your support on this.

 

In the first approach, I have changed the Lookup NSQL using XOG and restarted the services and then ran data warehouse job, full load ran successfully but received error as mentioned earlier while running incremental.

 

In the second approach, I have created new lookup by using new N-SQL that having inv.id in it, then created new attribute and ran the load data warehouse and again received the same error.

 

Hence for temporary fix, I have created 2 string attribute and made it read only and updating it through a process.

 

Thanks,

Prakash