Clover ETL Community Edition - couple of questions

CloverDX Platform
1

1. I made a simple graph. It reads some data from DB and transfers to another one. However I would like this aplication to log the SUMMARY OF EXECUTION on the WARN LEVEL.

Currenly the “tail” of log looks like this:


INFO  [WatchDog] - [Clover] Post-execute phase finalization: 1
INFO  [WatchDog] - [Clover] phase: 1 post-execute finalization successfully.
INFO  [WatchDog] - ----------------------** Final tracking Log for phase [1] **---------------------
INFO  [WatchDog] - Time: 05/08/11 09:20:06
INFO  [WatchDog] - Node                   ID         Port      #Records         #KB aRec/s   aKB/s
INFO  [WatchDog] - ---------------------------------------------------------------------------------
INFO  [WatchDog] - DBInputTable           DB_INPUT_TABLE0                              FINISHED_OK
INFO  [WatchDog] -  %cpu:0.04                        Out:0         2000         704    181      64
INFO  [WatchDog] - Reformat               REFORMAT0                                    FINISHED_OK
INFO  [WatchDog] -  %cpu:..                           In:0         2000         704    181      64
INFO  [WatchDog] -                                   Out:0         2000         704    181      64
INFO  [WatchDog] - DBOutputTable          DB_OUTPUT_TABLE0                             FINISHED_OK
INFO  [WatchDog] -  %cpu:0.02                         In:0         2000         704    181      64
INFO  [WatchDog] - ---------------------------------** End of Log **--------------------------------
INFO  [WatchDog] - Execution of phase [1] successfully finished - elapsed time(sec): 11
INFO  [WatchDog] - Post-execute finalization of connection:
INFO  [WatchDog] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriver@caf0ed]:jndi[null]:url[jdbc:mysql://10.152.0.13:3306/starwood]:user[starwood] ... OK
INFO  [WatchDog] - Post-execute finalization of connection:
INFO  [WatchDog] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriver@18f6559]:jndi[null]:url[jdbc:postgresql://72.36.214.23:5432/saturn]:user[iloopro] ... OK
INFO  [WatchDog] - Post-execute finalization of connection:
INFO  [WatchDog] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriver@caf0ed]:jndi[null]:url[jdbc:mysql://localhost:3306/starwood_test]:user[root] ... OK
INFO  [WatchDog] - -----------------------** Summary of Phases execution **---------------------
INFO  [WatchDog] - Phase#            Finished Status         RunTime(sec)    MemoryAllocation(KB)
INFO  [WatchDog] - 1                 FINISHED_OK                       11             15409
INFO  [WatchDog] - ------------------------------** End of Summary **---------------------------
INFO  [WatchDog] - WatchDog thread finished - total execution time: 11 (sec)
INFO  [main] - Freeing graph resources.
INFO  [main] - Execution of graph successful !

I would like to add an extra line on the end, looking moreless like this:


WARN  [summary] - Exec. time: 11 (sec), errors: 0, Processed records (DB_INPUT_TABLE0): 2000, Processed records (DB_OUTPUT_TABLE0): 2000.

The problem is not logging itself - I figured out that I can put and artificial reformat component, which allows me to wrice custom java code - which can be used for logging. This is a bit “artificial” - If anyone has a better solition for this I will be more than happy to listen to it:)

The main problem is: how to extract the statistical data from within the application, as seen by watchdog ? Like the number of records procceseed, execution time? If possible also #KB aRec/s , aKB/s and such.

I would not like to “grep”, “sed”, or “awk” the clover log - this is just the waste of time and involves writing an extra shell script…

Please help :slight_smile:

2. Does clover have an already ready tool for email notifications?

3. The DB output config looks like this:

http://img824.imageshack.us/img824/2189/screenshotrjc.png

Does this mean that if I want to insert, lets say, 100 records, however an error occurs during the insert of the 40th one (like duplicate primary key) - the graph fails, the next records are not proccessed, however the 39 are already inserted?

4. In general - are there any components apart from reformat that allow me to write and extra Java code?

That’s all that comes to my mind right now - I am looking forward to seein the anwers from You guys:)

2

Hello,
1. You can get all tracking information through org.jetel.graph.runtime.jmx.GraphTracking object. In commercial version of CloverETL Designer you could use JavaExecute component in the next phase to the last phase of transformation itself with the code like:


import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jetel.component.BasicJavaRunnable;
import org.jetel.data.DataRecord;
import org.jetel.exception.TransformException;
import org.jetel.graph.runtime.jmx.GraphTracking;


public class Logging extends BasicJavaRunnable {

    private final static Log logger = LogFactory.getLog(Logging.class);

 	@Override
	public void run() {
		GraphTracking gt = getGraph().getWatchDog().getCloverJmx().getGraphTracking();
		int outRecords = gt.getPhaseTracking()[0].getNodeTracking("DB_INPUT_TABLE0").getOutputPortTracking(0).getTotalRecords();
		logger.warn("Processed records (DB_INPUT_TABLE0):" +  outRecords);
	}

}

2. CloverETL Designer contains EmailSender component but it is not available in Community Edition
3. Yes and this is true even if you have Commit attribute set to a higher value unless you set Error action to ROLLBACK. Reading topic Transcation handling in DBOutputTable could be useful for understanding when commit on database is called.
4. Many components allow to write transformation in java. For full list see Defining Transformations.