LoadDataWarehouse job fails

[Rainer Forslund]

Hello!

We upgraded to 15.7 and were hoping this would solve the ldw-job failing issue.
But still no go.

When job is started, after a few minutes it hangs and seems to be doing nothing.

bg-dwh.log shows this as the last entries:
INFO 2020-04-02 14:20:09,536 [dwh_jb_pre_execution UUID: c88661f7-8e23-463b-930c-311ed7052d57] dwh.event dwh_jb_pre_execution - Starting entry [ClarityDB - isOracle?]
INFO 2020-04-02 14:20:09,536 [dwh_jb_pre_execution UUID: c88661f7-8e23-463b-930c-311ed7052d57] dwh.event dwh_jb_pre_execution - Starting entry [dwh_tr_pre_execution_oracle]
INFO 2020-04-02 14:20:09,536 [dwh_jb_pre_execution UUID: c88661f7-8e23-463b-930c-311ed7052d57] dwh.event dwh_tr_pre_execution_oracle - Loading transformation from XML file [file:///opt/clarity/META-INF/datamart/etl_scripts/dwh_cmn_etl_jobs/dwh_tr_pre_execution.ktr]
INFO 2020-04-02 14:20:09,545 [dwh_jb_pre_execution UUID: c88661f7-8e23-463b-930c-311ed7052d57] dwh.event dwh_tr_pre_execution - Dispatching started for transformation [dwh_tr_pre_execution]

After this nothing happens until the 600minutes timeout occurs and job fails.

Any ideas?

[Nika Hadzhikidi]

HI Rainer

Does this happen on all environments of yours? What is the Oracle version you're using? Is DWH and Clarity on the same server and was it always the same issue even before the upgrade?

Please provide the full bg-dwh logs

Please check in the DWH database : select * from DWH_GATHER_METRICS_V 
This will give us some clues on what is slow.

[Nika Hadzhikidi]

Hi Rainer

 

thank you for the files, looks like the execution actually stops at DWH_LKP_DOCUMENT_TYPE.

Could you please try the following:

1. Find the lookup attribute that uses DOCUMENT_TYPE and uncheck it from Data Warehouse. Make sure you uncheck the lookup attribute and also ensure the lookup fields in DWH_META_COLUMNS are is_deleted =1

You can use the query such as: select * from dwh_meta_columns where lookup_type ='DWH_LKP_DOCUMENT_TYPE'

2. Run a Full Load of Data Warehouse

See if this helps. My guess is something is wrong with the lookup or its data.

 

Thank you -Nika

[Rainer Forslund]

Hi!

Here's what we did, our comments in BOLD:

1. Find the lookup attribute that uses DOCUMENT_TYPE and uncheck it from Data Warehouse.

How can I find within the objects the attributes that uses DOCUMENT_TYPE lookup type?

 

Make sure you uncheck the lookup attribute and also ensure the lookup fields in DWH_META_COLUMNS are is_deleted =1

I ran the following update:

update dwh_meta_columns

set is_deleted = '1'

where SRC_COLUMN_NAME = 'document_type';

 

You can use the query such as: select * from dwh_meta_columns where lookup_type ='DWH_LKP_DOCUMENT_TYPE'

This query returns 0 rows.

1. Run a Full Load of Data Warehouse

I got still the same error message as earlier: 'ETL Job did not complete within specified time'.

[Rainer Forslund]

Any help with this?

[Nika Hadzhikidi]

Hi Rainer

Can you please send me the results of select * from DWH_META_COLUMNS from your Clarity database in Excel with headers. Let me see how your attributes look like.

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

[Rainer Forslund]

Hello!

Heres the file, thsnk you :)

Best Regards

Rainer Forslund

Attachment(s)

DWH_META_COLUMNS_11.5.2020.xlsx   134 KB 1 version

[Nika Hadzhikidi]

Hi Rainer

 This results are from DWH not Clarity, can you get me the ones from Clarity please. Sorry for the inconvenience. You do have a couple of lookups I want to see where they are coming from. Thank you!

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

[Rainer Forslund]

Sorry, let's try again with this one :)

-------- Alkuperäinen viesti --------

Attachment(s)

DWH_META_COLUMNS_NIKU_12.5.2020.xlsx   200 KB 1 version

[Nika Hadzhikidi]

Thank you Rainer

I compared your lookups and things look actually alright there. What I would suggest is to drop your DWH schema, restore a blank one from template, recreate the DB link and try again. This will help if you have any weird corruption with the lookup tables.

If this does not work, I recommend opening a case so we can look into this in greater detail in Support.

Thank you -Nika

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

[Rainer Forslund]

Hi Nika,

These latest instructions seemed to do the trick, Thank You for your help :)

Kind Regards, Rainer

[Nika Hadzhikidi]

Glad to hear this Rainer! Thank you for confirming

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