Posted: Mon Jun 17, 2013 5:44 am
This is a great thread because it illustrates the flexibility in DataStage to create a solution in lots of ways, and also shows that there are important functional AND non-functional decisions that have to be made.
Obviously, there aren't any realistic limits here...you can write to a whole lot of tables, and as Arnd noted, 10 is not very many.
...but 10 jobs vs one Job? Clearly there are maintenance, support, debugging and management issues to consider, and the threads above highlight the fact that "one huge job" increases complexity. It might cost you far more in time and energy and future maintenance (you may not be the one who has to update or repair the job in the future) if you go with one Job. One large Job also takes away simple flexibility the first time someone says "I need you to re-run the load for ONLY table 7".
You didn't say how long it takes to run. There are great performance points up above regarding the source, re-reading it, etc. Depending on the source, performance could dictate the choice for you. If it were xml, for example, there are major benefits to reading an xml document only once and then parsing it many ways into different output links. And regardless of the source type, if the Job takes many hours to run, performance is a premium. ....but if the Job runs in 5 minutes or collectively as individual Jobs in 10 minutes, and you have a wide open batch window, opt for better long term maintenance and simpler debugging.
Ernie
Obviously, there aren't any realistic limits here...you can write to a whole lot of tables, and as Arnd noted, 10 is not very many.
...but 10 jobs vs one Job? Clearly there are maintenance, support, debugging and management issues to consider, and the threads above highlight the fact that "one huge job" increases complexity. It might cost you far more in time and energy and future maintenance (you may not be the one who has to update or repair the job in the future) if you go with one Job. One large Job also takes away simple flexibility the first time someone says "I need you to re-run the load for ONLY table 7".
You didn't say how long it takes to run. There are great performance points up above regarding the source, re-reading it, etc. Depending on the source, performance could dictate the choice for you. If it were xml, for example, there are major benefits to reading an xml document only once and then parsing it many ways into different output links. And regardless of the source type, if the Job takes many hours to run, performance is a premium. ....but if the Job runs in 5 minutes or collectively as individual Jobs in 10 minutes, and you have a wide open batch window, opt for better long term maintenance and simpler debugging.
Ernie