ETL jobs taking long time

[srini.dw]

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,

[kwwilliams]

Create a sequence with a loop in it. The loop list would contain a delimited list of the 30 countries.

[sjfearnside]

From what I get from your message,

(i.,e jobs are locked until previous job update finish

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.

Ex: maybe something like partitioning your DB by country or use row level locking, etc..,

[srini.dw]

Create a sequence with a loop in it. The loop list would contain a delimited list of the 30 countries.

Thank you for the reply,

All 30 countries are running from different sequencers.

If you could explain the above it would be great i.,e delimited limit.

Thank you