0

I need to generate a report on 28th of every month . So for that I need to run an autosys job. In that I have a query with the condition validation_date >= (number of days since last run) Could you please help me on this .How can I achieve this condition in DB2 ? This is a monthly job.So I don't want to hard code my previous run date in the query .At the same time I need to get a condition which satisfies for all the months .

Note : If the query is running on feb 28th ,then feb 28th is not included. I need to get data from january 28th(included) till feb 27th(included) similarly for march 28th run ,I need to get data from feb 28th(included) till march 27th(included)...Thanks in advance.Please help

Rima Joy
  • 3
  • 3
  • Which query did you try? – data_henrik May 15 '17 at 07:52
  • Your date/time/timestamp queries should always be [upper-bound exclusive](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) (use `<`). In this case, say "before today", `< CURRENT_DATE` (or some other thing indicating actual end date). Note this will automatically make things safe for higher resolution timestamps. – Clockwork-Muse May 17 '17 at 20:14

2 Answers2

1

Consider putting your report generation in a procedure, and parameterizing the start and end dates. In other words, have something like this:

create procedure monthly_report(
    start_date date,
    end_date   date
)
language sql
begin

   ... report queries here ...

end

Now you potentially have something much more flexible (depending on the report requirements). If, in the future, you want to run a report on a different day, or for a different length of time, you will be able to do that.

Once you design it this way, it also may be easier to set the dates in your job scheduling script, rather than in SQL. If you did it in SQL, you could do something like this:

call monthly_report(
    (select 
        year(current timestamp - 2 months) ||'-'|| 
        month(current timestamp - 2 months) ||'-'|| 
        '28' from sysibm.sysdummy1
    ),
    (select 
        year(current timestamp - 1 month) ||'-'|| 
        month(current timestamp - 1 month) ||'-'|| 
        '27' from sysibm.sysdummy1
    )
)

You may need to tweak it to handle some edge cases (I'm not exactly sure if you care what happens if it runs on the 29th of the month, and if so, how to handle it). But you get the basic approach.

0

You can use DAY() function that extracts day of month from date and you can use it for triggering job. for example where day(param)=28. other two parameters can be calculated with date calculation , here is example for trigger , date_to value and date_from value 

select day(timestamp_format(20170228,'yyyyMMdd') ),timestamp_format(20170228,'yyyyMMdd')- 1 DAY,timestamp_format(20170228,'yyyyMMdd')  -1 month from sysibm.sysdummy1;

if your parameter/column is date/timestamp you can remove timestamp_format(20170228,'yyyyMMdd') function and just put your column/parameter

zlidime
  • 1,124
  • 11
  • 6