ETL is not transferring data from ODS

[victor]

Hi,

we are facing an issue where ETL stopped transferring data from ODS to ETL because  template was modified while ETL was running. Now, we have ETL spewing an UNIQUE CONSTRAINT VIOLATION error during an UPDATE.

Anyone know what to do?

Vic

[ecki]

Hi Vic,

That looks like your ETL is trying to transfer already transferred chunks in to DM. Compare ODS database with DM and mark all already transferred chunks as transferred with setting the transferred_time in OL_CHUNK_LOG table. This should help.

Cheers,

Ecki.

[victor]

Hi, Ecki,

I was thinking that too, but it unfortunately it is not that.
We have the following problem:

We are using 7.2.xxxx ETL and Datasourcer.
We have layout A that is using Template 1
Layout A was active and collecting data for about 2 years
Someone added a new filter into Layout A while Layout A was active
We started to receive error inside ETL that I mentioned. Actually error says something a field should only be inserted into a table once. I have never seen this error before.

(This is when I posted this error)

We deactivated the layout and deleted the field.
We activated layout.
Guess what!!! Data Sourcer does not notice that field was deleted from template!!!

[cavagnaro]

I remember someone asked same thing long time ago...can't find however the post. But someone gave a script I believe to fix it

[tony]

I remember this from my DBA's - it cleaned up the ODS tables.  It was due to the fact that there are some chunks somewhere in the ODS that has not been marked as transferred, even though they had transferred. The script did a "search and destroy" on the ODS and cleaned it up...

I'll take a look and see what I can find, locally...

[victor]

Hi, Tony, Cav,

thank you for your prompt reply.

The problem seems to be something else, because I updated OL_CHUNKS_LOG or whatever that table was and set transferred time for all chunks that were still pending.

The problem seems from the fact that ETL messed up and marked some statistics as DELETED and forgot to remove it from underlying views and tables. At least this is how it looks to me here. Has anyone seen anything like this?

I would love it to be chunk problem. I really would!!! And I really still wish it to be like this. If there a quick way to check it?

Best regards,
Vic

[tony]

Vic,

It's Sunday here - lemme check tomorrow...

T

[victor]

Dear Tony,

what day do you think is here?

[tony]

Workday..?

[CTIgem]

If my memory serve me correct, I ran tenant-alias-update to fix this issue.

[tony]

Vic,

You could try a "search and destroy" mission, through SQL.  Log into ODS (with TOAD?) and run;

select log_id
from ol_chunk_log
where TRANSFERRED_TIME is null
AND receive_time < sysdate;

If that finds entries, you need to clear them down;

update ol_chunk_log set TRANSFERRED_TIME = BEGIN_TIME
where TRANSFERRED_TIME is null
AND TRUNC(UPDATE_TIME) <> TRUNC(SYSDATE);

Tony

[Koldun]

Hi, Vic

Can you write down here the exact error messages, which you have in your ETL logs? This could be useful...

Do you have access to solution search : http://solutionsearch.genesyslab.com ?

Regards

[cavagnaro]

How can this guy ask that to Vic? Have two posts and 2 in Karma? A super ego arrived? lol

[Fra]

Maybe it's Pavel using another account 

[tony]

Oh now gentlemen ...play nice! 

Koldrun - FYI: Vic runs/hosts this site and I am sure he has already been to the Genesys Knowledge Base...

Tony