Record start and finish time of a graph

CloverDX Platform
1

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.

2

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

3

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

4

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.

5

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

6

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

7

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

8

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.