One Star

How to check if DB connection available later in the JOB flow

Hi Team,
Scenario: I have a long running PARENT job, where PreJob condition establishes Postgres/MS SQL/Kafka connections. Once connections are established, then KafkaInput keeps reading messages continuously and push those messages to different child jobs as per filter conditions to process. This parent job calls multiple child jobs to read kafka messages and load into Postgres.
Problem: I faced an issue during IDLE TIMEOUT, all the DB connections were lost. In my Parent job, after connections are established and ONCE message is read from KAFKA, before sending that message to child job, i want to CHECK IF POSTGRES/MSSQL DB connection is available then only send the message to child job. 
Otherwise, 
either log that message and fail the job
or try to re-establish DB connection

Please help me solve this problem. Thanks in advance.
THanks,
Rera
4 REPLIES
Moderator

Re: How to check if DB connection available later in the JOB flow

Hi,
Would you mind posting your whole job design as screenshot into forum so that we can get more information from your work flow.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to check if DB connection available later in the JOB flow

Hi Sabrina,
Please find a test job screenshot for reference.

As you can see, m first establishing all DB connections and then reading messages from KafkaInput and replicating that to multiple data flows as per the filter.
Problem: How can i check DB connection availability before calling a CHILD job AFTER treplicate component? I want to pass the kafka read message for processing only when there is DB connection available. Since my job is a lONG running job, there could be some unhandled exceptions/scenarios which might drop DB connection say during night when network is IDLE. So, i want before i call the child job and pass the kafka read message, i want to CHECK IF DB connection is UP and available then only call the child job to process the message. Otherwise, try to first re-establish connection by doing 3 attempts. Please guide me how can i achieve this.
Thanks so  much for your time and help.
Rera
One Star

Re: How to check if DB connection available later in the JOB flow

Hi Sabrina & Team,
Just want to check for your input/pointer for my earlier reported problem.
Thanks so much for your time!
Rera
One Star

Re: How to check if DB connection available later in the JOB flow

use the retry logic connection before each of your jobs.
the idea is simple, before each child process , you launch a joblet that does the retry process , and loop on the connection until it gets successful then start the child.retry process