Record start and finish time of a graph

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

kasturi
Posts: 26
Joined: Wed May 25, 2011 5:06 pm

Record start and finish time of a graph

Postby kasturi » Tue Jul 05, 2011 4:01 pm

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.

jurban
Posts: 163
Joined: Fri Jul 20, 2007 9:25 am

Re: Record start and finish time of a graph

Postby jurban » Thu Jul 07, 2011 9:48 am

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
Jaroslav Urban
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com

kasturi
Posts: 26
Joined: Wed May 25, 2011 5:06 pm

Re: Record start and finish time of a graph

Postby kasturi » Thu Jul 07, 2011 2:47 pm

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

avackova
Posts: 841
Joined: Fri Jul 20, 2007 9:28 am

Re: Record start and finish time of a graph

Postby avackova » Tue Jul 12, 2011 12:14 pm

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.
Agata Vackova
Javlin a.s.
[email protected]

kasturi
Posts: 26
Joined: Wed May 25, 2011 5:06 pm

Re: Record start and finish time of a graph

Postby kasturi » Thu Jul 21, 2011 6:15 pm

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

avackova
Posts: 841
Joined: Fri Jul 20, 2007 9:28 am

Re: Record start and finish time of a graph

Postby avackova » Fri Jul 22, 2011 11:08 am

Hello Kasturi,
can you show your graph?
The exception should be thrown only if incrementalKey is set but where is missing in sqlQuery.
Agata Vackova

Javlin a.s.

[email protected]

kasturi
Posts: 26
Joined: Wed May 25, 2011 5:06 pm

Re: Record start and finish time of a graph

Postby kasturi » Fri Jul 22, 2011 3:54 pm

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
Attachments
LoadUserTelephoneincr.grf
(14.15 KiB) Downloaded 234 times

avackova
Posts: 841
Joined: Fri Jul 20, 2007 9:28 am

Re: Record start and finish time of a graph

Postby avackova » Mon Jul 25, 2011 11:48 am

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.
Agata Vackova

Javlin a.s.

[email protected]