Six Stars

How to log a talend job result?

We have many talend jobs to transfer data from oracle(tOracleInput) to redshift(tRedshiftOutputBulkExec). I would like to store the result information into a DB table. For example:
Job name, start time, running time, rows loaded, successful or failed
 
I know if I turn on log4j, most those information can be derived from the log. However, saving it into DB table will make it easy to check and report the result. 
I'm most interested in records loaded. I checked this link http://www.talendbyexample.com/talend-logs-and-errors-component-reference.html and manual of tRedshiftOutputBulkExec. None of them gives me such information.
Will Talend Administration Center provide such function?
Thanks,
9 REPLIES
Moderator

Re: How to log a talend job result?

Hi,
If you want to store the result information into a DB table, it is very convenient to set the log in job view to output the record into file or table.
Statistics table records details like begin Time, End Time which is generated by any job.
Logs table records the messages from Java Exception, tDie and/or tWarn (prerequisite: there must be an error in your job).
Flow Meter table records the number of rows processed in the defined flow(prerequisite: you have to use tFlowMeter in your job).
Is it what you are looking for?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: How to log a talend job result?

Hi Sabrina,
I create tStatCater/tLogCatcher/(TFlowmeterCatcher,tFlowMeter) to generate stats/log/flow meter into Oracle database(11r2) tables via tOracleOutput, and it works. But 6 new components make code more complicated. 
Initially I tried your suggestion, adding those configs into "Stats Logs" of Project Settings to save them into the same Oracle database tables. This makes code much tidy. However, I got the following errors:
"Exception in component talendStats_DB
java.lang.ClassCastException: java.lang.String cannot be cast to java.sql.Connection
at lab_one.test_logging_5_1.test_logging.talendStats_STATSProcess(test_logging.java:5218)
at lab_one.test_logging_5_1.test_logging.runJobInTOS(test_logging.java:7044)
at lab_one.test_logging_5_1.test_logging.main(test_logging.java:6789)
Exception in component talendStats_DB
java.lang.ClassCastException: java.lang.String cannot be cast to java.sql.Connection
at lab_one.test_logging_5_1.test_logging.talendStats_STATSProcess(test_logging.java:5218)
"
Thanks,
 
Moderator

Re: How to log a talend job result?

Hi,
"Exception in component talendStats_DB

Could you please show us your component setting screenshot?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: How to log a talend job result?

Hi Sabrina
I think "component talendStats_DB" is a system component.
"Exception in component talendStats_DB

If I uncheck "On Databases", " talendStats_DB" won't be in JAVA code as well as the log4j log.
If I check "On Databases", this component starts appear in the JAVA code. 
  https://www.talendforge.org/forum/img/members/343840/mini_project_settings.png
JAVA code:
"                /**
                 * start
                 */
                ok_Hash.put("talendStats_DB", false);
                start_Hash.put("talendStats_DB", System.currentTimeMillis());
                currentVirtualComponent = "talendStats_DB";
                currentComponent = "talendStats_DB";"
                int tos_count_talendStats_DB = 0;
                if (log.isInfoEnabled())
                    log.info("talendStats_DB - " + "Start to work.");
                StringBuilder log4jParamters_talendStats_DB = new StringBuilder();
"
log4j:
"
2016-04-12 15:43:09,719 -  -4479 FATAL  - talendStats_DB java.lang.String cannot be cast to java.sql.Connection
"
Thanks,
Bin
Moderator

Re: How to log a talend job result?

Hi,
Can you successfully connect to your DB? Is there any issue when you output your log into a file?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: How to log a talend job result?

Hi Sabrina
1. No issue when I output into a file. I prefer to log into DB for better reporting.
2. No issue to connect to DB. I'm able to use tStatCater/tLogCatcher/(TFlowmeterCatcher,tFlowMeter) components to generate stats/log/flow meter into the same database with the same tables via tOracleOutput. However, I don't like this option, because:
* I need to add 6 extra components: tStatCater/tLogCatcher/tFlowmeterCatcher/tOracleOutput x3 into each job;
* I cannot choose "Use an existing connection" in each tOracleOutput(3 of them), since I don't have a way to close the existing connection. I try close the existing connection in Postjob, however, the Postjob run before tStatCater, which mean tStatCater will tiger an error.
Thanks,
Bin
Moderator

Re: How to log a talend job result?

Hi,
Could you please show us your current job design?
Have you tried to log result into DB in job review?


Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: How to log a talend job result?

Hi
Job Setting Stats & Logs of job review gets the same setting from project setting. I tried different options:
* use JDBC driver insteads of Oracle driver;
* use mysql DB;
However, I got the exactly same error. 
- talendStats_DB - Start to work.
- talendStats_DB java.lang.String cannot be cast to java.sql.Connection
This is my Job design:
 
Thanks,
Bin
Moderator

Re: How to log a talend job result?

Hi,
We don't see any error from your job design. Could you please indicate the build version you are using? Open source or subscription version?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.