3 Replies Latest reply: Nov 20, 2012 2:38 AM by 34MCA2K2 RSS

    Jobs scheduler: Process architecutre

    34MCA2K2
      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
          - 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
            >
            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
              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!