Hi
I have requirement to run a job 40 times with a parameter value sent to the source query of my job. The parameter is different for each run and should be generated anew for every run. Ex:
Select empno from emp
where deptno=#A#
where A=1 for first run
2 for second and so on.
The entire job must be put in a loop so that it executes 40 times.
We are not using shell scripts and hence the parameter has to be generated by datastage itself.
Is there a way to do this?
Thanks
Bob
passing parameter to a select query
Moderators: chulett, rschirm, roy
Batch job control with a loop, or a Sequencer with a loop. Both feed the appropriate parameters to controlled jobs.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
I never wrote a looping Sequencer that passes the loop argument to jobs as a variable. I'm sure others have written one. In a Batch job, it's a piece of cake.
In pseudo code:
In pseudo code:
Code: Select all
For i=1 to 40
...Call DSAttachJob...
...Call DSSetParam(jobhandle, parameter name, i)
...Call DSRunJob...
...Call DSWaitForJob....
Next i
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Bob,
Are the values that you need passed literally the number of times you have been through the loop?
Set up a sequence that looks like this (assuming 7.5.x):
Start Loop --> Job --> End Loop
In your job you can pass the activity variable StartLoop_Activity.$Counter. Which will be the number of times that you have been through the loop.
You might also want to think about not hardcoding the 40, but instead having a job that loads the number of departments into a flat file and then have a routine read the flat file and use that routine to populate the number of times to pass through the loop. That way if your company grows and creates a new department (corporate growth is a good thing), then your job would not have to be manually updated to reflect this change.
Are the values that you need passed literally the number of times you have been through the loop?
Set up a sequence that looks like this (assuming 7.5.x):
Start Loop --> Job --> End Loop
In your job you can pass the activity variable StartLoop_Activity.$Counter. Which will be the number of times that you have been through the loop.
You might also want to think about not hardcoding the 40, but instead having a job that loads the number of departments into a flat file and then have a routine read the flat file and use that routine to populate the number of times to pass through the loop. That way if your company grows and creates a new department (corporate growth is a good thing), then your job would not have to be manually updated to reflect this change.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com