Using Loops with multiple list values

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
possibility1234
Participant
Posts: 6
Joined: Wed Aug 03, 2011 12:58 pm

Using Loops with multiple list values

Post by possibility1234 »

Hello All-

I want to ask this question concerning loops in DataStage with either list or numeric loop. I understand that loop work one at a time. My requirement is

I have one single generic job that is used to load about 250 tables from source to target. Using list loop runs the cycle 250 time before all the tables are loaded. I want to ask if we add some twick is such a way that:

--The loop present about 50 list which uses just that only generic job and loads the 50 tables
--Next loop run picks from 51-100 uses the single generic jobs and loads equivalent tables


I am doing this loop now one at a time and will like to see if I can reduce the loop cycle time from 250 to about five time with each run loading 50 tables at once
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Simply design the sequence job with five streams of processing.

You can work out how to provide the five separate sub-lists of things for these streams to do.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

How about having one multi-instance sequence job with a loop? Call it as many times as you want and pass each one a list as a parameter.
Choose a job you love, and you will never have to work a day in your life. - Confucius
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

Sample:

Loop X times to run FTP session, one at a time, and provide for restart from point in list where error happened.

Generic job sequence which reads parameterized loop list name:
1) Loop list is indexed numerically, one (1) to last (X).
2) Execute stage finds maximum index, setting last loop value.
3) Loop checkpoints each run. Temporary file holds value of loop index for checkpoint restart.
4) Loop list contains name of file to get during FTP.

I currently have 12 loop lists with a varying number of files to get in each list. For scheduling requirements, error handling and internal incident paperwork, I have a separate job sequence for each loop list, with loop list file names linked to the job names. Changes to any loop require no changes to DataStage code. Adding, removing or changing a file name in the loop list file is all that is required.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
possibility1234
Participant
Posts: 6
Joined: Wed Aug 03, 2011 12:58 pm

Re: Using Loops with multiple list values

Post by possibility1234 »

Thanks all for your response:

What I want to do is make this jobs simple and scalable by simply adding List if the table grows in one simple location and the process will not be hindered.

Secondly- How do I picking the last List and pass the next fifty list. I have a sample Powell Shell called in the Execute command that build all the list at once

Here is the code and the list:
seq_List..JobControl (@Exec_List): Executed: powershell -command "$list = ls C:\MSSQL\AppETL |% {$_.BaseName};[string]::Join(',', $list);remove-variable -name list;"
Reply=0
Output from command ====>
AB_VIEW,AC_VIEW,PU_VIEW,SH_VIEW,TI_VIEW,P42_VIEW

This list will keep on growing. So If
AB_VIEW,AC_VIEW,PU_VIEW,SH_VIEW,TI_VIEW,P42_VIEW list was passed and executed at once. How to I pick the next set and process at once: AB1_VIEW,AC1_VIEW,PU1_VIEW,SH1_VIEW,TI1_VIEW,P421_VIEW

Thanks
possibility1234
Participant
Posts: 6
Joined: Wed Aug 03, 2011 12:58 pm

Post by possibility1234 »

Work around for this issue is:

Build a command delimited for all the loop delimited variable

AB_1
AB_2
AB_3
....

AB_N

Becomes:
AB_1,AB_2,AB_3.......AB_N

Pass subset of list to each loop. So we If I have to process simultaneously using four loops to run one instance of one generic job, I have this done
Loop1 Processes: AB_1, AB_1+4, AB_1+9...
Loop2 Processes: AB_2, AB_2+4, AB_2+9...
Loop3 Processes: AB_3, AB_3+4, AB_3+9...
Loop4 Processes: AB_4, AB_4+4, AB_4+9...

With this approach, All I just need is keep on adding to the list parameters if another set of of tables needs to be loaded
Post Reply