Job Scheduling

[Team_2007]

Hi All,

I am looking out for some design suggestions for the following requirement mentioned below.

We need to replicate data from the holding database after the replication from source to holding is completed by other systems. we need to trigger our sequence once the status is completed in one of the holding tables.Also we are triggering the sequence from UNIX OS. Whenever the status in the holding table is completed , automatically the sequence should be triggered to continue the further process. please do let us know what are different possible ways we can design such requirement.

Source--->holding--->staging---->DW

Thanks in advance for your valuable suggestions.

[ray.wurlod]

First step is you telling us how you propose to detect - from UNIX - the change of state of the table in the holding area. Once you have advised that, then maybe we can proceed with further analysis.

[Team_2007]

First step is you telling us how you propose to detect - from UNIX - the change of state of the table in the holding area. Once you have advised that, then maybe we can proceed with further analysis.

Thanks for your response.


That is we have a table in the holding area to record the status of the replication, once the load is completed from source-->holding the table is inserted with a new record with status=1, on seeing this we need to immediately start our load which is a job sequence.

Our master job sequence will be called by a scheduling script(UNIX shell script) which will be scheduled in cron on particular time basis or it will continuously polls after every 10 or some fixed minutes. So we have a doubt like how does an insert in one oracle table can automatically trigger the job sequence.

For this ,I had one design such that creating a trigger for that table so that whenever a new record is inserted with status=1 a file will be created in a Unix box and we need to continuously poll that directory for the file. if the file exists immediately load will be triggered. i dont how much feasible this design is.Also we don't want to poll the oracle table continuously as it will cause performance overhead on the oracle server.

please do let me know if you require more explanation on this.

[chulett]

While obviously you could poll an Oracle table every X for this record, or do the same for a file, why not simplify things? Why not arrange for whatever process knows when this 'load is completed from source' to then start your Sequence? Should be trivial with an Enterprise Scheduler and a 'dsjob' script, which it sounds like you already have. Link the successful completion of one to the starting of the other.

Or just add it as one last step in the load process.

[Team_2007]

While obviously you could poll an Oracle table every X for this record, or do the same for a file, why not simplify things? Why not arrange for whatever process knows when this 'load is completed from source' to then start your Sequence? Should be trivial with an Enterprise Scheduler and a 'dsjob' script, which it sounds like you already have. Link the successful completion of one to the starting of the other.

Or just add it as one last step in the load process.

Thanks Chulett.

We can link the systems if they are in the same server but the other system which populates the holding tables are in different environment and in different server where the configurations are totally different.

[chulett]

Do you not have an 'enterprise scheduler'? Something like Control-M would not care that the two parts of this process are on different servers.

Even so, you don't think you could use some form of 'remote shell' from one to the other to launch the dsjob script on completion of the load?