We've shown how to create DataStage jobs that can process tables and
files generically. What we now need is a way to control those jobs in
some sort of loop such that a new file or table is passed on every loop.
There are many ways to do this - shell script with dsjob, a sequence
with the names of the files and/or tables hard-coded into the StartLoop
activity, a DataStage Server routine that uses the DataStage API to
start and run a list of jobs read from a text file or even another job
that calls the UtilityRunJob routine that comes with DataStage.
The approach taken here will be a combination of a Routine, a Sequence
and a text file. This design will make maintenance very simple. If a
file needs to be added or deleted to or from the list, only the text
file and the job parameter changes.
Here's the plan:
- Create a file containing one record for each file to be processed.
Each record is a comma-delimited record containing the file path, the
table name and the schema file path.
- Write a routine that reads a comma delimited file with three fields - file name, table name and schema file name. Each
record in the file will be loaded into an array and the routine returns the array.
- Create a sequence that calls the routine once and then loops through
each element in the array passing the required fields to a job
Create List of Files
Here is our list of files containing only one entry for this example.
Create Routine
The routine is a fairly simple one. It takes one argument which is
the file name (including the path) of the file containing a
comma-separated list of parameters for each file to be processed. It
opens the file, breaks each line into fields and pushes them on to a
dynamic array which it then returns to the caller.
Function ReadFile
Routine = 'ReadFile'
vFileName = Arg1
vArray = ''
vCounter = 0
OPENSEQ vFileName to vFileHandle
Else Call DSLogFatal("Error opening file list: ":vFileName,Routine)
Loop
While ReadSeq vLine FROM vFileHandle
vCounter = vCounter + 1
vArray = Fields(vLine,',',1)
vArray = Fields(vLine,',',2)
vArray = Fields(vLine,',',3)
Repeat
CLOSESEQ vFileHandle
Ans = vArray
Return Ans
Create Sequence
In the first activity of the sequence (uvReadFile), the routine is called and stored in a variable named fileList.
The next step is the loop initialization. We set up a numeric loop with a
stop value defined by a job parameter whose value is the size of the
list (in this case 1).
Now we set up a variable containing the ith record of the array. This
record will contain the set of parameters required to run the job.
The set of parameters is then further split up into individual variables.
Finally we are ready to call the job using the required parameters.
