(By way of contributing to the group...)
In the world of unix/win based systems and ETL (and to some extent DataStage) I've been frustrated by the lack of control schedulers give me. (Just call me an ex-JCL person where you have 110% control over what is happening and restarts.)
As ever, if you want something done right you must do it yourself..
So, I have written two new utilitles that might be of interest to DS users. (I have published quite a few other utilities and even some ETL software but they are 'free/low cost alternatives to ETL tools' so they have not been applicable to posting here. These two new utilties were actually requested by some of my customers.)
1. A utility to submit a DS job from a win2000 machine to any DS server running anywhere that the win2000 machine can see. (which is not so special until you read about utility 2 which is....)
2. A utility that manages schedules of batches for any commands that run on a win2000 (free) or unix (not free because I must give you the source code). Of course, one of the commands that it can run is the command to run a DS job on any DS server....
The scheduler allows you to define processes, then process groups, then batches. The multiple process groups in a batch can run in parallel, and the processes within process group run sequentially. The scheduler has all the normal things you need in a scheduler as well as managing dependencies intelligently.
Why is this of interest to a person with DS?
Say you have a 4 processor windows machine. (Just to keep it all free.)
Lets say you have 80 staging area tables, 40 dimensions and 20 fact tables, or numbers near those. It could be lots more.
What you can now do is submit a batch that will initially start 4 process groups to perform data transfer for source to staging. All 4 run at once and are then brought to completion. You might run 20 jobs per process group or something like that to balance the load as effectively as possible.
You might then start another batch (or set of process groups) that process the dimension tables, again, parallelising as much as possible for maximum throughput. You would bring them all to completion as well.
Then you might start another batch (or set of process groups) to process all the fact tables.
By creating the dependencies between the process groups based on known computing requirements of the processes in the groups you can balance out the load so that there is less time spent waiting for those straggling processes to complete.
So what? You can do all this in DS control language if you feel like writing it all.....or mostly anyway because it's harder to establish cross job dependencies.....
So, now for the 'BIG DEAL'.
When a process crashes, as they do, all you do is fix whatever the problem was and go into the table that defines the batches and type 'restart'.
Yes, just type 'restart'.
The scheduler will see the restart command and figure out where the whole set of batches was up to and restart the failed job at the beginning. It will then progress through all the batches/process groups according to dependencies.
No more editting DS control jobs to restart the batch processing after a failure. (Which has been something of the source of errors for projects I have worked on. A machine might crash but it takes a human to really stuff it up!!!
)(And the scheduler will have only halted those processes, process groups and batches that were dependent on the failed process. Anything not dependent on the failed process would have continued to run.)
So, for DS users who would like a better scheduler than the (current) DS scheduler you might like to check out the following link:
www.peternolan.com/pr26.htm
And because I know people here will want to read the code to see if it actually works all the source code for win2000 is published and you can get to it via this link. In fact the link is for the source code for my 'free' utilities.
www.peternolan.com/pr27.htm
Any requests/idea for improvements most welcome, the 'freeware' gets improved on a 'time available' basis.
Hope you like these two utilities.
