Job/Routine Trigger and Status Update Issue

[rohit.agarwalin]

Hi,

We have a situation where we are using a routine (which takes SQL statement and submits to database - Oracle) to insert some of the data in 5 different tables.

Sample SQL;

INSERT INTO TABLE TARGET
SELECT COL1, COL2 .. from SOURCE
WHERE...

Query 1:
----------
I have 5 different instances (with different SQLs for different tables) of above routine running/triggered at the same time in sequence.
But when we see log then we come to know only 2 instances of that routine triggered and other instances followed them once they finished (though all the 5 instances should be triggered at the same time).

Query 2:
----------
We have another parallel job also running together with these 5 routines. That job started after one routine finished but strange is that job finish status has been updated in Sequence after 30 minute of actual finish. For Example job finished at 4 PM (we can see job finish time as last run) but log in sequence has updated at 4.30 PM which shows job finished.

Could you please help me identifying what could be the issue.

[chulett]

Let's start with specifics of your Sequence job's design and the triggers involved. Also must note that others have posted the same issue in the past and (from what I recall) the solution isn't as straight-forward as it might seem it should be. Let me see if I can turn up any of those posts...

[rohit.agarwalin]

There is one more post with the same issue.
viewtopic.php?t=148786

We have server routine which submits Oracle SQL. These routines need to run in parallel/together and after each server routine we get return value from the corresponding routine.

The reason of collecting return value is to handle the warnings. Without collecting return code, routine usually returns 100 rows inserted and rpoutine thinks that it is invalid return code so we get warning in Director as

@Routine_1 did not run successfully. 100 rows inserted.

so we uncheck the sequence properties 'Log Warnings messages for any activities fail' and collecting return values for each routine. But it is getting submitted in sequential manner.


For query 2 also suggest what to do?