CODEWITHSUNDEEP
kettlepdi
Patrick
Patrick

Reputation: 23

Pentaho Kettle- run check db connections without stopping job

I have read blogs and one question close to mine, but have not found a solution to my problem. I have a transformation job setup to extract three tables from 84 DBs to generate one report. My problem is when a DB connection is not available, the whole job stops.

I would like to be able to check DB connections before initializing the job, log errors for inaccessible DBs and create a new dynamic list of successful tests from which I will then run my job. I have used the check DB connections step but it still stalls when a connection is false.How can I process my list of DBs, running through to the end, without aborting the job?

Upvotes: 1

Views: 3446

Answers (1)

Rishu S
Rishu S

Reputation: 3968

First of all you have absolutely used the correct step to check the DB Connections. Now for your question, i would try to explain in parts (hope i am correct):

Case I: "My problem is when a DB connection is not available, the whole job stops"

This scenario is obvious. Whenever a step finds any error, it would throw an exception and would stop the entire execution of the Job.

But does it mean that the step "Check Db connections" would stop checking the db connections if it gets an error connecting. Answer is NO. The Step would complete testing all the connections even if it gets an error in some connection in middle. Try observing the logs carefully, it would give you a final consolidated list of all the checked db connections (check the image below):

enter image description here

I tried testing with 4 db connections out of which i got One error and 3 Success.

Now for the "Whole Job Stops" portion: Since the stopping behavior is obvious (as i have mentioned above), what you can do is to pass the flow using "Error hop" so that if a job finds an error, it will take the error hop. Check the image below:

enter image description here

Here i have used two hops: One Success and One Error. If the Job fails, it would take the error path (red colored hop) else it would take the Success path (green colored hop).

CASE II: "log errors for inaccessible DBs and create a new dynamic list of successful tests"

You can either log the errors into a separate log files or table (depends on your requirement) and then read through the log to generate a list of DB connections. Check the image below:

enter image description here

The output generates a list of Connections along with an Error flag. 

Y : Failure in connecting to Database
N : successful connection

Note: i have used text file input since i have logged the previous step into a text file instead of database. You can customize as per your req.

I have placed sample code in gist. You can check for your ref.

Hope it helps :)

Upvotes: 1

Related Questions