Help on "Before SQL" on DB2 Connector

[JaisonJ]

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.

[chulett]

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

[MT]

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

[JaisonJ]

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

[ray.wurlod]

(there is no 31-JAN-9999 etc.)

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

[chulett]

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

[MT]

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

[ray.wurlod]

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

[MT]

Hi Ray

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

[JaisonJ]

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