Schedules triggering day scheduled and day after

[MichaelB]

Our Cognos environment has been experiencing a weird issue for a couple of years now that no one has seemed to be able to get to the bottom of. We have a trigger sent to us M-F when the overnight data warehouse batch is complete. This trigger fires off several events that check to see what day of the week it is to be able to run our daily, weekly, or once a month reports. Below is a the expression that we have in our reports that run on the 1st of the month:

Code Select Expand

([Member Count] > 0 AND day(getdate()) = 1 AND _day_of_week(getdate(), 1) not in (6, 7))

OR ([Member Count] > 0 AND day(getdate()) = 2 AND _day_of_week(getdate(), 1) = 1)

OR ([Member Count] > 0 AND day(getdate()) = 3 AND _day_of_week(getdate(), 1) = 1)

The problem we are experiencing with this and every other event that fires jobs is that it always happens correctly on the day that it is scheduled, in this instance on the 1st, but will also run the next day. We see this with our daily reports that run on Saturday. Tuesday jobs run on Tuesday and Wednesday.

As far as I understand, this issue started when we upgraded from 10 to 11.0.3 a few years ago. We recently upgraded to 11.1.2 and this did not resolve the issue. The events and jobs have been rebuilt at various times and this did not resolve the issue. At this point, I have exhausted all options that I know and turn to the ever wise Cognoise world. Thanks in advance for the help!

[Francis aka khayman]

have you eliminated the possibility that one of your or conditions results to true during the day when its not supposed to run?

[MichaelB]

The way that I have it coded (and the way that I have understood in the context of an implied IF) the Member Count is always over 0. The point of that is to have a fact in the expression so that the query gets sent to the server for processing.

I have repeatedly questioned myself in the the second half of the expression, but I have taken each of those parts and threw them into data items in a report many, many times and that part of the equation seems solid as well.

[MichaelB]

As a follow up for some simpler code, here is the expression for our daily reports:

Code Select Expand

[Member Count] > 0 AND _day_of_week(getdate(), 1)  in (1,2,3,4,5)

This ends up running M-F and Saturday. I thought that it might have been a difference in the understanding of day of week between Cognos and SQL, but then it would miss triggering on Monday, not just add an extra day.

[MFGF]

Hi,

I notice you're mixing database functions and Cognos functions in the expression. Just as a sanity check, what results do you get if you use just Cognos functions

eg instead of using day(getdate()) try extract(day, current_date)

and instead of using _day_of_week(getdate(), 1) try _day_of_week(current_date, 1)

Cheers!

MF.

[MichaelB]

Hi MFGF - thanks for the suggestion. This was actually something that I was thinking of testing as well since I brought the topic up. Probably just the whole typing it out triggering my brain to think about it deeper.

Anyways, I made the tweak to our Friday job, so I will see Saturday if it triggered or not and report back with what I find on Monday. Have a great weekend!

[MichaelB]

Well, unfortunately this did not fix the issue. The event triggered the job on Friday and Saturday.

[Francis aka khayman]

1.

We have a trigger sent to us M-F when the overnight data warehouse batch is complete.

have you also eliminated the possibility that the trigger is being sent M-S by mistake?


2. where exactly are those code placed in your report?

[MichaelB]

Thanks Francis. In this circumstance, the trigger probably is being sent M-Sat, but the event itself should prevent it from triggering on Saturday. There is one trigger sent each night/morning, and we use the events to parse whether it is Mon-Fri, Mon, Tue, Fri, 1st, 5th, 26th of month. Every Mon-Fri triggers M-Sat. Every Mon event fires Mon and Tues. Every Tues event fires Tues and Wed, etc.

So the issue isn't the trigger that we receive from the data warehouse team, the issue is that Cognos somehow thinks that Monday and Tuesday are both Monday's, that the 26th and the 27th are both the 26th., etc.

Hope that clarifies a little.

EDIT: To address your second question, these pieces of code are in events that fire jobs, not in the reports themselves.

[Francis aka khayman]

so those codes are in event studio?

can you check what timezone the cognos server is using as well as the timezone when the trigger is sent. maybe when the trigger is sent on tuesday morning, server time is still monday evening?

[MichaelB]

I can and will check if I have the correct access, but this doesn't account for the fact that it fires on the day that it is scheduled for in addition to the day after. With what you are suggesting, I would expect that Monday would fire only on Tuesday, not Monday *and* Tuesday.

[Francis aka khayman]

In this circumstance, the trigger probably is being sent M-Sat

so trigger fires friday PM,
server time = -12 hours = friday AM
_day_of_week(getdate(), 1) = 5 True
report runs

trigger fires Sat AM,
server time = -12 hours = friday PM
_day_of_week(getdate(), 1) = 5 True
report runs

[MichaelB]

so trigger fires friday PM,
server time = -12 hours = friday AM
_day_of_week(getdate(), 1) = 5 True
report runs

trigger fires Sat AM,
server time = -12 hours = friday PM
_day_of_week(getdate(), 1) = 5 True
report runs

But...

so trigger fires Monday AM (around 2:00 am),
server time = -12 hours = Sunday PM
_day_of_week(getdate(), 1) = 1 False
reports shouldn't run...but they do

That is why a simple time/zone desync couldn't be the problem (I do have a ticket in to verify) because it would be equally applied throughout the week, and we would never see reports on Monday. But we have two jobs that do fire from these events on Monday: Our M-F, and our Monday jobs.

EDIT: Both Application and gateway servers are correctly synced for time and time zone. Awaiting info on data server.

[Francis aka khayman]

But we have two jobs that do fire from these events on Monday

two jobs seem to be explaining it more. ok so -12 hours seems to be a stretch. let us say trigger fires at eastern time while server is in central.


so trigger fires Monday AM (around 2:00 am ET),
server time = -3 hours = Sunday PM
_day_of_week(getdate(), 1) = 1 False
reports shouldn't run... on first trigger


so trigger fires Monday AM (around 4:00 am ET),
server time = -3 hours = Monday AM
_day_of_week(getdate(), 1) = 1 True
reports run...

=============

so trigger fires Tuesday AM (around 2:00 am ET),
server time = -3 hours = Monday PM
_day_of_week(getdate(), 1) = 1 True
reports run... when they shouldn't


so trigger fires Tuesday AM (around 4:00 am ET),
server time = -3 hours = Tuesday AM
_day_of_week(getdate(), 1) = 1 False
reports did not run... they shouldn't

[MichaelB]

I'm a bit confused by what you are writing. And considering the other information that I provided I am not sure that is even the correct path to be going down.

This has been an ongoing problem for over two years since our upgrade to 11.0.3. Every single week, without a break in the routine.

Only one trigger is ever sent from the data warehouse - upon completion. Of course, data warehouse completion can vary at times and can happen anywhere from 1:30am to 8:00am. But most of the time it's at 2am. When it does vary, there is no change to how the jobs run - it's always what is scheduled that day and what was scheduled the previous day.

[Francis aka khayman]

makes sense to me if the server used to upgrade to v11 is set in different timezone.

now there is only 1 trigger?

to me the most plausible explanation, however improbable, unless ruled out with solid evidence, is that the job finish late on mondays, server time is already monday so it runs.

on tuesdays or days during which it should not run the job finishes early, the server time is still previous day, so the job still runs.

[MichaelB]

I appreciate your help, but I think you are misunderstanding the issue. I am not sure how to re-frame it help you understand, but essentially no, the timezone issue is not plausible and I am fairly confident that the time's are not out of sync anyways. If they were, we would probably see a lot more issues with encryption.

One trigger. One Top level job houses multiple Events. Each event has different criteria to gauge whether it should run that job: M-F, M, Tu, Fri, 26th, 1st, 5th, 5th of Quarter, etc. Every single job that is run by the events happens to run one extra time, the day after it already ran. With there being only one trigger, your timezone suggestion is not plausible. If it were plausible, there would potentially be days where this pattern did not occur; however, it has occurred for over two years without fail.