DSXchange

I have a target table with 5 columns (Target Table data shown after first run)

Target Table
SOURCE_DATE---ENDDATE----RUN_ID---FROM_DATE----TO_DATE
01-JAN-2013----31-DEC-9999---1-------01-JAN-2013---01-JAN-2013
01-JAN-2013----31-DEC-9999---1-------01-JAN-2013---07-JAN-2013
01-JAN-2013----31-DEC-9999---1-------08-JAN-2013---31-JAN-2013

During second run, source table will have data as below

Source Table
SOURCE_DATE---ENDDATE----RUN_ID---FROM_DATE----TO_DATE
01-FEB-2013----31-DEC-9999---2-------01-FEB-2013---01-FEB-2013
01-FEB-2013----31-DEC-9999---2-------01-FEB-2013---07-FEB-2013
01-FEB-2013----31-DEC-9999---2-------08-FEB-2013---31-FEB-2013

Source table data need to be inserted to target table. Before inserting the data in END_DATE column of the target table need to be updated with 01-FEB-2013 instead 31-JAN-9999. .

I am using DB2 connector, and I achieved above said using SQL "Before SQL" section in DB2 connector.

Data in the target table is as follows

Target Table
SOURCE_DATE---ENDDATE----RUN_ID---FROM_DATE----TO_DATE
01-JAN-2013----31-FEB-2013---1-------01-JAN-2013---01-JAN-2013
01-JAN-2013----31-FEB-2013---1-------01-JAN-2013---07-JAN-2013
01-JAN-2013----31-FEB-2013---1-------08-JAN-2013---31-JAN-2013
01-FEB-2013----31-DEC-9999---2-------01-FEB-2013---01-FEB-2013
01-FEB-2013----31-DEC-9999---2-------01-FEB-2013---07-FEB-2013
01-FEB-2013----31-DEC-9999---2-------08-FEB-2013---31-FEB-2013

My issue is "Before SQL" is executed even if source table has no rows when job runs. How can I restrict "Before SQL" not to be executed if there are no input rows to be inserted to the target table?

i.e END_DATE of rows inserted during 2nd run is getting updated with 3rd RUN DATE without any rows being inserted from 3rd run.

That fails my requirement.

JaisonJ wrote:How can I restrict "Before SQL" not to be executed if there is no input rows to be inserted to the target table?

Sorry, only have time for the short answer - you can't. You'll need another approach if it needs to be conditional. One answer might be to only run the job when you know you have records to process...

Hi JJ,

first of all I think you mixed up some dates in your post (there is no 31-JAN-9999 etc.) but I think I got the idea.
I completely agree with chulet that it is maybe not a good idea to use the before SQL for your purpose.
Second point is that your table - unless you oversimplyfied it for the post - has no real key - this will make things very complicated and maybe less useful.
Besides this I suggest looking at the great functionalities of DB2 itself - check out the temporal tables in DB2 10 - they could do it for you. So maybe you have even less work
Alternatively you could think of a SQL logic for an AFTER SQL statement where you update the run before the current one depending on the current one....

As Chulett replied I will change the design to process this job only if there are rows to insert.

MT wrote:(there is no 31-JAN-9999 etc.)

Yes there is. There's a 31-JAN in every year.

FWIW, we use '12/31/9999' as one of our magic dates.

Hi Ray
of course in every year - but not in the example of the first post

31-FEB-2013 is a bit more of a worry.

Hi Ray

yes you are so right - but I know customers who think this date is ok - and have designed applictions that way

Thank you all for the support.I have changed my design and it is working fine now.