This discussion is archived
3 Replies Latest reply: Nov 20, 2012 12:38 AM by 34MCA2K2 RSS

Jobs scheduler: Process architecutre

34MCA2K2 Journeyer
Currently Being Moderated
Hi Experts,

I have a requirement implemented in a certain way, If you can suggest something on how to improve it will be great

Current Scenario

1. There are 2,3 tables which are to be updated before starting a process
2. Once those tables are updated & ready. There is another table PROCESS_MON which has rows for each procedure to be called in the process like below
PROCESS_NAME      RUN_FLAG
--------------------------------------------
P1                N
P2                N
P3                N
RUN_FLAG in this table is updated to Y.
3. There is a DBMS_JOB which is monitoring this table PROCESS_MON in every 5 mins. When it finds the RUN_FLAG = Y, it triggers the process.

In the current scenario, I have to update two tables which are to be updated to trigger a process, My question is - Can I get it done in Step 1 itself?

-Thanks in Advance
Regards
  • 1. Re: Jobs scheduler: Process architecutre
    ranit B Expert
    Currently Being Moderated
    - Can I get it done in Step 1 itself?
    If you do the Steps - 2 & 3 in Step - 1... only change will be - You'll call them Step - 1, 1.1 & 1.2 ;-)
  • 2. Re: Jobs scheduler: Process architecutre
    rp0428 Guru
    Currently Being Moderated
    >
    In the current scenario, I have to update two tables which are to be updated to trigger a process, My question is - Can I get it done in Step 1 itself?
    >
    It's not clear what 'step 1' you are talking about.

    You have TWO separate processes.

    Process #1 - update two tables and then set the PROCESS_MON flag to Y as appropriate.

    Process #2 - the process executed by the DBMS_JOB that uses a PROCESS_MON record whose flag is set to Y. This process would normally also set the PROCESS_MON record status to indicate IN_PROGRESS and the start time.

    This would prevent another job or process from trying to start processing the same PROCESS_MON record just because the flag was Y. You would only process records with a Y flag whose status was ACTIVE or READY_FOR_PROCESSING or something similar.

    Then when the processing is complete the PROCESS_MON record would be updated with an end time, the status reset to ACTIVE (or something) and the flag set to N.
  • 3. Re: Jobs scheduler: Process architecutre
    34MCA2K2 Journeyer
    Currently Being Moderated
    Thanks a lot for your reply on this! I apologize if my post is a bit haywire :-(
    >
    Process #1 - update two tables and then set the PROCESS_MON flag to Y as appropriate.

    Process #2 - the process executed by the DBMS_JOB that uses a PROCESS_MON record whose flag is set to Y. This process would normally also set the PROCESS_MON record status to indicate IN_PROGRESS and the start time.

    This would prevent another job or process from trying to start processing the same PROCESS_MON record just because the flag was Y. You would only process records with a Y flag whose status was ACTIVE or READY_FOR_PROCESSING or something similar.

    Then when the processing is complete the PROCESS_MON record would be updated with an end time, the status reset to ACTIVE (or something) and the flag set to N.
    >

    You are bang on for this! Yes this is the current way. I wanted to eliminate manual step of setting the process monitor to Y. I could use triggers to do this, however the problem I have is this - "It is not necessary that whenever those two tables are updated we have to run the process".

    I was thinking of these steps -

    1. Whenever those two base tables are updated
    2. Write a Trigger which
    a. validates the data in both the tables
    b. sends an email, alerting that process will be triggered in next one hour
    c. Triggers a job which will run after one and a half hour

    Does anyone in the forum think this is a bad architecture? please let me know...

    Thanks in advance!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points