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
Using Loops with multiple list values
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 6
- Joined: Wed Aug 03, 2011 12:58 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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
"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
-
- Participant
- Posts: 6
- Joined: Wed Aug 03, 2011 12:58 pm
Re: Using Loops with multiple list values
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
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
-
- Participant
- Posts: 6
- Joined: Wed Aug 03, 2011 12:58 pm
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
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