[resolved] Using job start timestamp variable to filter rows

One Star

[resolved] Using job start timestamp variable to filter rows

Hi,
I need to filter rows in my extracts based on the start time of the extract process. I have a series of jobs that extract data from 71 live Oracle tables. While this job is taking place, I want to ignore records that are updated in the source tables while the extract process is executing. To do this I would have each extract with selection criteria as follows:
SELECT ....
FROM x
WHERE UPDATE_TS <
I need to get the start time of the job from Oracle sysdate and then store this in a global variable that can be accessed across the whole project (I've seperated my extracts into seperate jobs).
I've tried context variables, tSetGlobalVar, tJavaRow and a combination of all of these and can't seem to get it to work. The closest I got was with tSetGlobalVar and tJavaRow but then it became apparent that the value in tSetGlobalVar is not visible outside the job it is set in.
Any ideas on how to get and set global variables that are accessible across the whole project would be great. I also would like to know the syntax for the WHERE clause in my queries.
Thanks in advance,
Tom

Accepted Solutions
One Star

Re: [resolved] Using job start timestamp variable to filter rows

Each job is a separate java/perl program, so you have to pass information into that program through arguments or some other mechanism. To do this via command line arguments, in the trunjob component use the context Param box to pass information to a context variable in the sub job.
I use TalendDate.formatDate to format the date to a string that the Oracle database recognises.
"select * from mytable where mytable.date <= '" + TalendDate.formatDate("dd-MMM-yyyy",context.jobStartDate) + "'";

All Replies
One Star

Re: [resolved] Using job start timestamp variable to filter rows

Each job is a separate java/perl program, so you have to pass information into that program through arguments or some other mechanism. To do this via command line arguments, in the trunjob component use the context Param box to pass information to a context variable in the sub job.
I use TalendDate.formatDate to format the date to a string that the Oracle database recognises.
"select * from mytable where mytable.date <= '" + TalendDate.formatDate("dd-MMM-yyyy",context.jobStartDate) + "'";
One Star

Re: [resolved] Using job start timestamp variable to filter rows

Thanks mslane, I have it working using command line arguments.