topic Re: Catch statistics from a job and store it in DB (using context parameters) in Talend Studio

Catch statistics from a job and store it in DB (using context parameters)

peterko — Tue, 26 Nov 2019 15:23:00 GMT

HI,

I would like to use the following design steps:

Load context from the XML file and set context values in the job
Catch statistics/logs from the job and insert the info into PostgreSQL database. DB connection properties are defined by context values loaded in the previous step.
Start data flow - any data processing

All steps are connected via trigger OnSubjobOk with the step one as is described on the image. All steps are connected via trigger OnSubjobOk with the step one

The problem is, tStatCatcher data flow did not respect OnSubjobOk trigger and start the data output before the context is loaded from XML, thus I got an error:

Nov 25, 2019 2:46:38 PM org.postgresql.Driver parseURL
WARNING: JDBC URL invalid port number: null
Exception in component tDBOutput_2 (TestStatsCatcher)
java.sql.SQLException: No suitable driver found for jdbc:postgresql://null:null/null
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at groupe_renault.teststatscatcher_3_0.TestStatsCatcher.tStatCatcher_1Process(TestStatsCatcher.java:2565)
	at groupe_renault.teststatscatcher_3_0.TestStatsCatcher.runJobInTOS(TestStatsCatcher.java:5120)
	at groupe_renault.teststatscatcher_3_0.TestStatsCatcher.main(TestStatsCatcher.java:4920)
Nov 25, 2019 2:46:38 PM org.postgresql.Driver parseURL
WARNING: JDBC URL invalid port number: null

Based on the error, the job has FAILURE status and tLogCatcher log the error.

Any idea how to fix this?

FYI: I cannot use project settings to set global DB connection for statistics, also I cannot use hard-coded DB connection values because I need to start job dynamically depending on the running environment

Re: Catch statistics from a job and store it in DB (using context parameters)

peterko — Tue, 26 Nov 2019 15:23:34 GMT

I can also show you job if needed

Re: Catch statistics from a job and store it in DB (using context parameters)

peterko — Tue, 26 Nov 2019 17:06:57 GMT

Re: Catch statistics from a job and store it in DB (using context parameters)

Anonymous — Wed, 27 Nov 2019 00:39:51 GMT

Hi,

You will have to mention the DB connection parameters in the stats and logs area of Talend job. Could you please try the method mentioned in the below link?

https://help.talend.com/reader/SY4K8FlNZd1_LG0DB1gAcw/uOutrHx062OrG1LaerZETA

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: Catch statistics from a job and store it in DB (using context parameters)

peterko — Wed, 27 Nov 2019 18:24:55 GMT

Yes,

I did mention the context parameters in the DB connection parameters as follows:

I also did try the solution you've provided via job general tStatCatcher as follows:

But this solution cannot work, because the context is not stored in the project repository, the context is loaded from the file system, thus project setting does not know anything about the DB connection.

The problem, in general, is that tStatCatcher starts before the context is loaded from the file. Even if the trigger is onSubjobok, tStatCatcher does not respect this trigger.

Re: Catch statistics from a job and store it in DB (using context parameters)

peterko — Wed, 27 Nov 2019 18:29:12 GMT

Following image is a visual description of the bug:

Re: Catch statistics from a job and store it in DB (using context parameters)

Anonymous — Thu, 28 Nov 2019 10:35:06 GMT

@peterko, use a tprejob to link to the subjob that load the context variable. eg:
tPrejob--oncomponentok-->tFileinputXML--main--tContextLoad

other subjobs.

tStatCatcher--main--tDBOutput

It should work now.

Regard
Shong

Re: Catch statistics from a job and store it in DB (using context parameters)

peterko — Thu, 28 Nov 2019 13:44:23 GMT

Thank you @shong

This works!

One more question. If I use a prejob, do I need to connect tContextLoad with the main flow via trigger onSubjobOk to make sure the main job won't start if the context has NOT been loaded? Or this behavior is done automatically?

Re: Catch statistics from a job and store it in DB (using context parameters)

peterko — Thu, 28 Nov 2019 13:48:41 GMT

FYI: This works with tStatCatcher component, but does not work with statistics in the job settings/project settings