Genesys CTI User Forum
Genesys CTI User Forum => Genesys CTI Technical Discussion => Topic started by: victor on October 25, 2008, 06:39:54 AM
-
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
-
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.
-
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!!!
-
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
-
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...
-
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
-
Vic,
It's Sunday here - lemme check tomorrow... :)
T
-
Dear Tony,
what day do you think is here? :)
-
Workday..?
-
If my memory serve me correct, I ran tenant-alias-update to fix this issue.
-
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
-
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
-
??? ??? How can this guy ask that to Vic? Have two posts and 2 in Karma? A super ego arrived? lol
-
Maybe it's Pavel using another account ;D
-
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
-
Ok, guys,
the problem was really really ugly.
But, first, thank you for your prompt replies and support.
As you can imagine, my first try was to see if the problem was with ODS so I just ran the update for OL_CHUNK_LOG table as many of your pointed out. Unfortunately, this did not work.
My next step, meant increasing the logging level of ETL runtime, so I could actually see what is happening. Well, guess what - levelOfLog option showed that UPDATE command was pretty much ok, except for missing one field (the one that had filter added to it).
Strange, I thought... Since there is no way to really see all SQL commands that etl is spewing out (do not believe the manual that says you can set it to _ALL_:9 and get it all), I use SQL Enterprise Trace to see what exactly our beloved ETL was doing. And Lo' and Behold! It was ALTERING my VIEWS!!!!
Yes, it was ALTERING views and ... get this.... tried to add a new field to it. The problem was that the new field already existed in the view, and thus, SQL server would return the error and ETL would not transfer the data.
This made me think... Why was ETL adding a new field if all I did was add a filter to it? So, I ran some experiments (yes, my girlfriend is far away and thus I have all this time on my hands) to see what is happening.
Here is what I found:
whenever you add a filter to a layout, ETL treats it as a new field and goes out of its way to propagate it to all the views. But, should something go wrong during the propagation, it DOES NOT ROLLBACK all the previous changes, so you end up with views that have the new field and some that don't!
Looking back through ETL and DMA and DataSourcer release notes, I ran across the latest release 7.2.100.x that said that now ETL tries to use existing fields instead of making the new ones...
Genesys Support also suggested that we just upgrade and hope that the problem will go away. Of course, this still leaves one question out there: how can something like this (adding filters to fields) cause so much damage so late into release. We are talking over 4 years here since ETL was introduced and still have this bug that late into the version for something this widely used... I don't get it.
I will keep you update of the progress, but I hope that it will work.
Here is what I learned:
1. DO NOT TURN OFF DATASOURCER when modifying layouts
2. stop ETL runtime when modifying layouts
3. have a backup of everything before you do something
(well, I knew (3), but still #1 and #2... especially #2 is a bit strange...)
Vic
P.S. BTW, I have smited Cavagnaro for being jealous that Koldun got +1. :P
-
hahahahaha not jealous, just curious... :-[
Hey Vic, this is really strange and ugly. I guess we will have to see in more detail how ETL and ODS works...pretty scary...
-
Hi Vic,
Yeah, CCA behaves really strange and it will stay so because Genesys does not care about CCA anymore. They wait until it dies naturally with first successful GIM release or the other stuff called Informiam.
e.