Hi,
We do have 30 sequencers for different countries.
We do have a 1 Multi instance job which is called by 30 sequencers in intervals of 1 minute.
It is business requirement of refreshing data for all every country every hour.
We use in Target TPT stage with Immediate mode option.
We do have a below update query in MultiInstance job, we do not have any PI or USI in WHERE clause.
UPDATE target_table SET col_b = :a WHERE col_c = :ix;
Most of the time ETL jobs use the above query at a time which results in hanging (i.,e jobs are locked until previous job update finish). Due to which each ETL job will take long time to complete.
Please let me know any resolution/suggestion for this.
Is there any method i can do i.,e if 1 country finishes for that job, next sequence for Multiinstance job can start.
Thank you,
ETL jobs taking long time
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Re: ETL jobs taking long time
Create a sequence with a loop in it. The loop list would contain a delimited list of the 30 countries.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
-
- Premium Member
- Posts: 278
- Joined: Wed Oct 03, 2007 8:45 am
From what I get from your message,
Ex: maybe something like partitioning your DB by country or use row level locking, etc..,
It appears the bottle neck is at the DB update process. Talk to your DBA about the issue and get recommendations on how to solve it.(i.,e jobs are locked until previous job update finish
Ex: maybe something like partitioning your DB by country or use row level locking, etc..,