Page 1 of 1

Record start and finish time of a graph

Posted: Tue Jul 05, 2011 4:01 pm
by kasturi
I want to record the date and time of start and finish of a graph in a database table. I can then use that date/time to extract data from source for incremental load. I am not sure how to do that.

Re: Record start and finish time of a graph

Posted: Thu Jul 07, 2011 9:48 am
by jurban
Hi,

you can use phases and DBExecute components for this:
1) first phase - DBExecute with a statement that writes current timestamp (e.g. something like UPDATE GraphTimestamps SET Start=CURRENT_TIMESTAMP , but the statement is database specific)
2) ordinary phases of your graph - let the graph do its business logic
3) separate final phase - similar DBExecute to write the current timestamp

However, are you sure this is the best way for you of doing incremental load? CloverETL has built-in support for incremental loading, see documentation:
http://www.cloveretl.com/documentation/ ... ading.html
http://www.cloveretl.com/documentation/ ... table.html

Best regards,
Jaro

Re: Record start and finish time of a graph

Posted: Thu Jul 07, 2011 2:47 pm
by kasturi
Jaro,
Thank you very much for your reply. I read the documentation on the links you have sent me. I will definitely use clover's built in function.
I tried to use it. It is giving me an warning on not having incremental file defined. I also am not clear on 'initial value'. I am loading based on the last updated date of the record. I am defining the key as Max(updated_date). Can I leave the initial value blank? In the select clause, I am using the 'where' clause with #Key.
Kasturi

Re: Record start and finish time of a graph

Posted: Tue Jul 12, 2011 12:14 pm
by avackova
Hello Kasturi,
current value of the key must be defined. It can be done in two ways:
  • current value is read from the incremental file
  • if incremental file doesn't exist or the key value is not defined in it, initial value is used - in your case use any date earlier from the all dates, that can be in updated_date field
See ExtExamples/graph/graphDBUnload.grf from our examples.

Re: Record start and finish time of a graph

Posted: Thu Jul 21, 2011 6:15 pm
by kasturi
Hello Agata ,
Thank you for your reply. I could use incremental file to run some graphs. However, sometimes I am getting this following error "Node DB_INPUT_TABLE0 finished with status: ERROR caused by: "where" clause not found in sql query!!!".
I have defined the incremental file, incremental key and also the initial value. In the where clause, I am also using where clause to take the records > #key01 ( which is what should have the max updated date as defined in the incremental key). I cannot find the cause of this error. What am I missing here?
Regards,
Kasturi

Re: Record start and finish time of a graph

Posted: Fri Jul 22, 2011 11:08 am
by avackova
Hello Kasturi,
can you show your graph?
The exception should be thrown only if incrementalKey is set but where is missing in sqlQuery.

Re: Record start and finish time of a graph

Posted: Fri Jul 22, 2011 3:54 pm
by kasturi
Hello Agata,
Thank you for your quick reply. I am attaching the graph that is giving me this 'where' clause not found message.
We are using mysql 5.0.44 as our source . Our target database id postgres(
PostgreSQL 9.0.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit).
Regards,
Kasturi

Re: Record start and finish time of a graph

Posted: Mon Jul 25, 2011 11:48 am
by avackova
Hello Kasturi,
the problem is, that DBInputTable recognizes only lower case "where" clause (https://bug.javlin.eu/browse/CL-1860). Please change it to lower case.