Job retry mechanism in case of DB connection failure in Talend

[Anonymous]

Hi Guys,

I have a requirement in which I extract data from files and databases as source into target databases (mostly oracle).

But sometimes job fails because of some db connection errors like "Connection timed out" and "network adapter could not establish connection" like these. when such issues arise I would like the job to retry to connect again and again(for fixed number of times) until there is successful connection.

So can anyone help me on how can I achieve this instead of running the job manually when these errors occur?

Quick reply would be appreciated.

Thanks in advance.

[Anonymous]

Hi Guys,

Finally I was able to prepare the proper job for DB connection retry mechanism.

PFA screenshots of parent job and child job.

Please let me know if anyone have any doubts on this

Parent job flow :  tLoop -----> tRunJob -----> tJavaRow

tLoop code (use while loop) : context.status (status is a boolean context variable with default value as true

tJavaRow : 

                      if(row1.errorCode==0){
                      context.status=false;
                     }else if(row1.errorCode==1&&
                    ((Integer)globalMap.get("tLoop_1_CURRENT_ITERATION"))<15){
                    context.status=true;
                    Thread.sleep(5*1000);
                    }else{
                    context.status=false;

PFA screenshots for both the jobs and let me know for any queries

4.PNG
5.PNG

[Anonymous]

@kiran1250 

Hi, I tried using this solution but didn't work. It is not reconnecting back after the failure.also the iteration is not working. Can you please provide more screenshots and details?

[ruslan_ua]

I think your iteration is not working because you have checked the checkbox in your tRunJob component called --"Die on child error".

If you disable it, then iteration will work and after tRunJob you should use "Run-if" connection where you will check if 

((Integer)globalMap.get("tRunJob_1_CHILD_RETURN_CODE")) !=0 and "Some_iteration_Param" < N.

So the job should look like this:

tLoop ---- iterate ----> tRunJob_1(without "Die on child error") ------ run-if(required condition) ------> tDie(in case connection not restored and all retries passed or error not related to connection)

*Additionally, in case you need retry only if the connection failed, you can set desired ExitCode in tDie component connected to your tConnection component(on component Error) inside that subjob. Then simply check for that code as described above in parent Job inside that "run-if" line condition.

[Jazz_shomp]

I am using this in my job but for errorCode I am getting error like:

errorCode can not be resolved or is not a field

Please help.

---

@kiran1250 wrote:

Hi Guys,

Finally I was able to prepare the proper job for DB connection retry mechanism.

PFA screenshots of parent job and child job.

Please let me know if anyone have any doubts on this

Parent job flow :  tLoop -----> tRunJob -----> tJavaRow

tLoop code (use while loop) : context.status (status is a boolean context variable with default value as true

tJavaRow : 

                      if(row1.errorCode==0){
                      context.status=false;
                     }else if(row1.errorCode==1&&
                    ((Integer)globalMap.get("tLoop_1_CURRENT_ITERATION"))<15){
                    context.status=true;
                    Thread.sleep(5*1000);
                    }else{
                    context.status=false;

PFA screenshots for both the jobs and let me know for any queries

---

[Jazz_shomp]

for errorCode i am getting an error saying:

errorCode cannot be resolved or is not a field.

[Anonymous]

Hey,

Please make sure you have a child job which you are calling and having the actual DB connection which we need to check and on failure of that connection we will return an errorCode to 0/1 for SUCCESS/FAILURE and store it in buffer output component and call that variable in the tJavaRow in main job.

Thanks,

Kiran Kumar