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

[peterko]

HI,

I would like to use the following design steps:

1. Load context from the XML file and set context values in the job
2. 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.
3. 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

[peterko]

I can also show you job if needed

[peterko]

[Anonymous]

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

[peterko]

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.

[peterko]

Following image is a visual description of the bug:

[Anonymous · Accepted solution]

@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

[peterko]

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?

[peterko]

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