COMMIT PROBLEM IN JOB

[prabu]

What is the db you use[try merge statement if you do], hanging issues cannot be answered without tracing your design

[ram1899]

What is the db you use[try merge statement if you do], hanging issues cannot be answered without tracing your design

ORACLE 9I

Design is


I have source and we do lookups on couple of table and look up the same Target table If the record is a alredy exists then We update the table and if new record we send this next Trnsform and Get the Seq# from ORACLE sequncer and insert the record into target table and HASHED file Commit on Both Update and Insert is 5000 Hope this will help you

[chulett]

If you are using two database stages for your Insert and Update links, switch the job to use a single stage for both links. This will keep all work in a single transaction and generally avoids all that pesky 'hanging'.

[ram1899]

If you are using two database stages for your Insert and Update links, switch the job to use a single stage for both links. This will keep all work in a single transaction and generally avoids all that pesky 'hanging'.

Thank you

In this case what TRANSACTION SIZE is best option

[chulett]

That's your call. We generally stick with 0 to get an all-or-nothing load. You need to decide what's appropriate for what you are doing and how you are handling restarts after failure. If you do use something other than 0, make sure it's a multiple of the Array Size.

[ram1899]

That's your call. We generally stick with 0 to get an all-or-nothing load. You need to decide what's appropriate for what you are doing and how you are handling restarts after failure. If you do use something other than 0, make sure it's a multiple of the Array Size.

Thanks A TON Craig Appreciate I will do this change on the same Job and update you how it goes

Thanks again

[ram1899]

That's your call. We generally stick with 0 to get an all-or-nothing load. You need to decide what's appropriate for what you are doing and how you are handling restarts after failure. If you do use something other than 0, make sure it's a multiple of the Array Size.

One more Quick Question what if the Links to Target table are coming from two diffrent Transform stages ???

[chulett]

Doesn't matter. All you lose in that case is the ability to do what's called Transaction Grouping, nothing you need to worry about for this it seems.

[ram1899]

Doesn't matter. All you lose in that case is the ability to do what's called Transaction Grouping, nothing you need to worry about for this it seems.

Thank you

This Jobs are designed by Some one and all left to diffrent project in same company and I have to Rock and roll on this Jobs from the day one day and night. No Error Handling No Restart capability. There is no time to think of how best solution I can give.

[ray.wurlod]

Make time.

[DSguru2B]

Ray is right. Its better to plan and rethink the design before implementing it. "Failing to plan is planning to fail". Especially if you are tweaking someone else's jobs. Make sure you have good amount of comments to avoid any confusion by future developers.

[guoyf]

Failing to plan is planning to fail

Great!!

I think if we can have some patterns or templetes or guides for ETL job with DS just like Java programe patterns

[DSguru2B]

This site is full of patterns and best practices. You can even check out Vincent's blogs. Kims has some tips on his home page. Search is the key. And if you have time, you can even prepare a white paper or build sample templates and share it with us