Trigger JOB from 2 SQL Query results - VisualCron

Please note that VisualCron support is not actively monitoring this community forum. Please use our contact page for contacting the VisualCron support directly.

Welcome Guest! To enable all features please Login or Register.

sk_5910
No customer Topic Starter

2019-02-25T03:20:16Z

Hello,

I am very new to VisualCron & need to achieve something moderately complex to complete my trial.

I have several processes (& reports) that are dependent upon certain SQL processes completing. I can tell when they are finished when a simple SELECT from one DB matches the same SELECT from another DB. There is no way to determine the correct time to launch the processes by a clock, only by comparing these two values.

So I want to configure a JOB in VC to use these two SELECTs as Event Triggers to launch the TASKs. Once the JOB is complete, it shouldn't run again until tomorrow & again only when the SQL processing is complete.

Is this reasonable as an ET, or do I need more complex JOB logic to run (& rerun) the JOB until the conditions are met??

Any help/ideas will be greatly appreciated...

Simon

Sponsor

Forum information

Wanna join the discussion?! Login to your Forum forum account or Register a new forum account

jrtwynam
Free support

2019-04-04T14:00:28Z

Hi,

I've never used an SQL as a trigger before, but I have used it quite extensively as a task. If you run the two SQL statements and see the same result, and then launch this other process, is there something that then runs to ensure that these two SQL statements no longer have the same result, so that the process won't run again until the next day?

I suggest creating a job that does this:

SQL Task - run SQL #1 and store the output.
SQL Task - run SQL #2 and store the output, using the same output settings as SQL #1.
Set Job Variable Task - I forget the exact syntax, but I'd use a string compare function something like {LOGIC|String|==|SQLOutput1|SQLOutput2|Y|N}. This says "if the output from SQL1 equals the output from SQL2, set the job variable to 'Y'; otherwise, set it to 'N'".
Task to run your other process (the type of task would depend on the type of process it is) - in the Flow section of the task, at "On Start" to use "If other value", and specify the job variable from above. If the value is "N", stop the job. This would cause it to check the value of that variable as soon as this task starts, and if the value is N (i.e. if the two SQL outputs didn't match), then don't bother executing this task - just stop the job.

You could schedule that job to run every few minutes. Say every 5 minutes. If it runs at 9am and the two SQLs don't match, it wouldn't execute your process. If whatever happens to cause the SQLs to match happens at 9:03am, then when this job runs at 9:05am, it would see that the SQLs match and therefore execute your process. The problem comes in when it runs at 9:10am... would the SQLs still match? If so, it'd execute your process again, so you'd have to figure out a way to cause it to only run once per day. You could do that something like this:

Add a second job variable to the job that stores the "last run date" of the process.
Add a Set Job Variable task with exactly the same flow as described above. If the value is "N", set the variable to the current date.
You'd have to add some logic to the first Set Job Variable task to look at the Last Run Date as well as the SQL outputs. You'd want it to say "if the SQL outputs are the same AND the last run date is NOT equal to the current date".

Gary_W
Free support

2019-04-05T15:31:38Z

I would suggest doing steps 1-3 from jrtwynam 's reply in a stored procedure in the database and "return" the match result for use by the next task in VC. That way the SQL functionality is encapsulated for use by other jobs if needed. Data can be “returned” using a sys_refcursor in one select statement.
Here’s how it would go in Oracle:

Assumptions:
2 databases you need to compare query results from, DB1 and DB2
Return Y if result match, N if they do not.
A database link exists in DB1 that points to DB2 called DB2LINK
The stored procedure will reside in DB1

Create a stored procedure in the database DB1:

CREATE OR REPLACE PROCEDURE ARE_DB_PROCS_DONE(prc OUT SYS_REFCURSOR) IS
BEGIN
  OPEN prc FOR
SELECT 
  CASE 
    WHEN 
      (SELECT COUNT(*)
       FROM TABLENAME) = 
      (SELECT COUNT(*)
       FROM TABLENAME@DB2LINK
       ) THEN 'Y' 
    ELSE
      'N'
  END 
FROM DUAL; 
END ARE_DB_PROCS_DONE;

So the first task in the job would be a SQL task that calls this stored procedure. No parameters. The sys_refcursor result set output goes to STDOUT of the task.
proceed with step 4 from jrtwynam 's post only using the previous task's STDOUT, which will be either Y or N.

CAVEAT: the procedure needs exception error handling but you get the idea. It doesn't have to be one query either. If you don't have permissions to create a dblink (or the equivalent in your environment) you could always have multiple connects to the different databases, store the results in variables, do the compare, etc. Just make the last statement the open prc for the select of the result and you will get the output.

EDIT: P.S. With this stored procedure using a sys_refcursor OUT parameter mechanism, you can get data from the database into the STDOUT of the task for use in processing by VisualCron.

Edited by user 2019-04-05T17:14:01Z | Reason: Not specified