Executing a child job based on DB table completion status

[vvazza10]

Hi ! I am working on a job design that will trigger a child job based on completion status that the job reads from the database.

If the return value from database is Y, then the child job should execute. If the return value from database in N, the job should sleep and look for the return value from database after 15 minutes. If the return value is Y, then the child job should execute, else it should sleep and this goes on loop.

What is the best design approach for this scenario?

[manodwhb]

@Vivek Venkatesan​ , you need to design a job in the below job and you need to use instead of tfixedflow input tDBInput and insted of tJava2 use child job.

[vvazza10]

Thanks for the reply, Mano ! This worked. I have a question. I am establishing connection to Snowflake. If the completion status is false and if it goes for Sleep, let's say for 30 minutes, would the Snowflake connection remain open or would it get closed automatically? Auto suspend in Snowflake has been set to 1 minute.

[manodwhb]

@Vivek Venkatesan​ ,when the connection is false ,the connection session will be closed ,will create new session for after sleep.

Please like/kudos and accept the answer so that it will help to others.

Thanks,

Manohar

[vvazza10]

Thank you so much ! I created the looping logic based on something similar to what you had recommended. I am facing a situation here. When a sequence value fetched from Snowflake is being passed to child jobs, and when the job goes to sleep, and after few loops, if the If condition satisfies and when the job skips the sleep condition, will the context variable set for sequence gets passed as is or would it be NULL in this case?

load SK gets passed from Parent job to below child job.

Parent Job:

Child job:

What I have noticed is, when the status is 0, it goes to sleep. The loop works fine. After several sleeps, when fetch status is 1 (based on a base table completion) and when it skips Sleep, while calling the stored procedure, the job fails. Not sure if load_sk value is being passed as NULL here.

Am getting below unexpected exception: