passing parameter to a select query

[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

[kcbland]

Batch job control with a loop, or a Sequencer with a loop. Both feed the appropriate parameters to controlled jobs.

[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

[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

[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.