Best approach to develop Frequent DB inserts/updates in the job

[Anonymous]

Hello,

I have a requirement where i need to update/insert Exasol DB tables after almost every step performed in the Talend job. These tables hold Statistics of the job, for instance, Running environment, project name and status, task name and status(Running, pending, failed, completed). After every step, for instance, source data extraction, i need to update the Db table with task_name = extraction, status= running, start time and end time etc. And upon successful extraction completion, again update multiple DB tables with task_name = extraction, status= completed, start time and end time etc. 

I also need to check these stats tables before starting the job.

The Db connection details come from a Configuration file and then loaded as context variables and cannot be hard coded anywhere in the code.

Using a tdbrow to update/insert after every step would add too many components in the job. Is there a better approach?

Would creating a JAVA routine help? And, in the routine, can i use one DB connection for all the different DB actions to be performed throughout the job?

I cannot hard code the DB connection details in the routine. Should i then pass the connection parameters(obtained from context variables) from the job to the routine every time i need to execute a query on the DB?

Thanks in Advance.

DSK

[Anonymous]

Hello, can anyone help me with my question, please?

[JohnRMK]

Hello,

One approach you can use is the use of tLogCatcher and several tWarns.

Let me explain :

Suppose you have a component that reads data from one DB and inserts it into another.

You want to retrieve the start, the end and the number of recordings as well as the status

A basic design model is:

tWarn_1

|

|

tDbInput -> tMap -> tDbOutput (Data)

|

|

tWarn_2

With

|

|

On Subjob Ok

In your job you will also have:

tLogCatcher -> tMap -> tDbOutput_1 (Insert logs)

                   -> tDbOutput_2 (Update logs)

PS: tLogCatcher must capture only tWarn message no tDie or anything else

In your first tWarn, you can put a message and a code (eg 100) in tWarn_2 a code 200

At runtime, you will have two triggering of the sub job with the tLogCatcher.

so in tMap you can modify the default values ​​by your own data and add other columns.

In tMap, you will have two outputs, you will filter according to the code column and you will choose 100 for insertion into the table and 200 for updating.

For example, in output 100, you will have the start date and the pending status,

in output 200 you will have the status ok or ko (you must have a variable in the job).

One last thing after inserting a new record (tDbOutput_1) you must have an onComponentOk trigger to a tDbInput (SELECT MAX (ID) FROM Logs) -> tContextLoad

to save it or use a global variable, you need it for update data at the end