Page 1 of 1
passing parameter to a select query
Posted: Tue Feb 28, 2006 8:59 am
by babbu9
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
Posted: Tue Feb 28, 2006 9:28 am
by kcbland
Batch job control with a loop, or a Sequencer with a loop. Both feed the appropriate parameters to controlled jobs.
Posted: Tue Feb 28, 2006 12:55 pm
by babbu9
Can you be more specific. I have tried using loop concept in the sequences but the values used are only for determining the no of times a job runs rather than passing a distinct parameter value for each run.
Thanks
Posted: Tue Feb 28, 2006 1:23 pm
by kcbland
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:
Code: Select all
For i=1 to 40
...Call DSAttachJob...
...Call DSSetParam(jobhandle, parameter name, i)
...Call DSRunJob...
...Call DSWaitForJob....
Next i
Posted: Tue Feb 28, 2006 1:49 pm
by kwwilliams
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.