Looping through stages?

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
arsh
Participant
Posts: 9
Joined: Thu Jan 18, 2007 10:44 pm
Location: Kolkata, India

Looping through stages?

Post by arsh »

Hello,

I have a requirement to execute a stored procedure after every 1000 rows inserted into the target table. For example after cdc there are a total of 3224 rows to be inserted, I need to execute the stored procedure a total of 3 times and use a value returned from the procedure and insert into the target.
the database I'm using is SQL Server 2008.
Thank you in advance for any help.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Not really a "looping" scenario to me. In your shoes I would probably try splitting off to another link whatever you need to call the procedure and then use a MOD(counter, 1000) function on a count of the records to call it when the function returns 0, leveraging a filter or transformer.

You sure you don't need to call it 4 times, once after all rows are processed?
-craig

"You can never have too many knives" -- Logan Nine Fingers
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

I would:
-Have a wave generator set to 1,000 after the CDC stage, anywhere before the Target database connector
- Set the commit transaction either to zero, to commit all, or to a 1,000
- Have the call of the store procedure as an after SQL

Good luck
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

After SQL would typically mean it would only be called once unless the Wave Generator effects that? I assume so, hence the suggestion. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
arsh
Participant
Posts: 9
Joined: Thu Jan 18, 2007 10:44 pm
Location: Kolkata, India

Post by arsh »

Thank You Craig. This solved my problem
-AA
Post Reply