How to Automatically Delay ReportCaster Jobs By Jodye Yates

Overview

In our shop we have hundreds of reports that are scheduled to run via ReportCaster every morning. These reports are running against various data sources that are updated daily. The updates are supposed to be finished very early in the morning, but sometimes the data loads can be late. When this happens, ReportCaster will distribute reports based on incomplete or even empty data sets. Obviously, we want to avoid sending erroneous data to our users.

This article will describe the method that we use to automatically delay ReportCaster jobs by 10-minute increments.

In summary, this is the logic surrounding all of our daily scheduled jobs:

1. Determine whether the data load is complete. If it is, then simply run the job.
2. If the data load is not complete, then the recipients should receive an e-mail informing them that the report will be late. This e-mail should only be sent once. The report should then be delayed by 10 minutes. ReportCaster should automatically continue delaying the report in 10-minute increments until the data is completely loaded, at which point the report will be distributed.
3. Once the report has been successfully distributed, we must ensure that the schedule is set to run at the correct time the following day. Also, if a report was late and it was distributed to FTP, then we must e-mail the recipients, informing them that the report is now ready. Otherwise, they will not know when it is done. Users who receive their reports by e-mail, of course, do not need to be notified that the job has completed.

The techniques described in this article were developed by Jodye Yates and Jasmin Lépine, Web developers at Air Canada.

Warning

The process described in this article involves the manipulation of the NEXTRUNTIME value in the BOTSCHED table in the ReportCaster Repository. This is not supported by Information Builders. However, I can confirm that we have been using this technique without any problems.

Determine Whether the Data Load Is Complete

ReportCaster permits jobs to include preprocessing and post-processing tasks. These are fexes that will run before and after a job has run. We will need to make use of both of these. Here is the format that we must use. Assume that the preprocessing fex is called pre_caster and the post-processing fex is called post_caster. When you create the jobs in ReportCaster, include the following for pre- and post-processing values.

Pre-processing: pre_caster DS=1, JN=My Job Name

Post-processing: post_caster JN=My Job Name

Please notice that the fex name does not include the .fex extension. We are passing two parameters to the pre-processing fex. The first parameter, DS, is used to identify the data source. As I will explain below, you might not need this. The second parameter, JN, is the name of the job in question. Notice that the parameters are separated by a comma and are case sensitive.

When the job starts, the first thing that will happen is that pre_caster will run. We will be able to access the passed parameters with amper variables: &DS and &JN.

The first step inside pre_caster is to determine whether the data is ready. This of course will be specific to each environment and each job. In our shop, the table that we verify depends on the value of the DS parameter (DS= data source). You might not need that if you are always verifying the same data. In any case, you can use &RECORDS to determine whether your data is ready. Here is a simple example, which assumes that XXX is updated once the daily data load is complete.

-SET & NOWDATE = &YYMD;
TABLE FILE XXX
PRINT SOMEFIELD
IF LOAD_DATE EQ '&NOWDATE';
ON TABLE HOLD
END

-SET &BLNREPORTOK=IF &RECORDS EQ 1 THEN 'Y' ELSE 'N';

At this point we simply check the value of &BLNREPORTOK:

-IF &BLNREPORTOK = 'Y' THEN GOTO DATAGOOD;

-DATAGOOD is a label at the very end of pre_caster. Essentially, we are saying, if the data is loaded then jump to the end of the pre_processing fex. Everything is fine and the job should run.

If &BLNREPORTOK = 'N' then we know the daily data is not ready, so we must move to step 2, delaying the report.

Delaying the Report and E-mailing Recipients to Warn Them That the Report Will Be Delayed

We are still inside pre_caster and we know that the data is not ready. The first thing we need to do is determine the current NextRunTime value. NextRunTime is an A32 value that corresponds to the number of milliseconds since January 1, 1970. This value is known as "Zulu Time". For example, "00000000000000000001123701669562" is the corresponding Zulu Time for Wednesday, August 10, 2005, at 12:23 PM. We need to determine the current NextRunTime because in order to delay the job by 10 minutes, we will need to add 600,000 milliseconds to the current A32 NextRunTime.

In the ReportCaster Repository BOTSCHED table there is a field called NEXTRUNTIME. This is an A32 value corresponding to the date/time when the job will next run. However, for our purposes we need to determine the A32 Zulu Time value for "right now." Note that by "right now" I am referring to the current date and time when pre_caster is running. I am not referring to the value of NEXTRUNTIME that is stored in the BOTSCHED table. The reason for this is that the NEXTRUNTIME in BOTSCHED is already set to the subsequent NEXTRUNTIME.

Confused? This is what I mean: Let's say a report is set to run daily at 9:00 AM. If you look at the NEXTRUNTIME value in BOTSCHED during the day you will see a value that signifies "tomorrow at 9:00 AM." However, once the pre_caster fex starts running, the BOTSCHED NEXTRUNTIME value is already updated to the NEXT day. So if today is Monday, once the pre_caster fex starts, the NEXTRUNTIME in BOTSCHED is already set to Tuesday at 9:00 AM. This is not useful to us. We need the CURRENT A32 Zulu Time for "right now," not tomorrow. We will need this value later, since in order to delay the report by 10 minutes we need to say "add 600,000 ms to value X" where X is the current NextRunTime that signifies "right now."

Susannah Jones has already written an excellent article about reading and interpreting Zulu Time and our methods are based on her article.

So, here is the code for storing the current NextRunTime as well as the job's ScheduleID, which we will also need, in amper variables:

-SET &OFFSETHOURS = IF &NOWDATE IS-FROM 20050403 TO 20051030 THEN 4
-ELSE IF &NOWDATE IS-FROM 20060402 TO 20061029 THEN 4
-ELSE IF &NOWDATE IS-FROM 20070401 TO 20071028 THEN 4
-ELSE IF &NOWDATE IS-FROM 20080406 TO 20081026 THEN 4
-ELSE IF &NOWDATE IS-FROM 20090405 TO 20091025 THEN 4
-ELSE IF &NOWDATE IS-FROM 20100404 TO 20101031 THEN 4
-ELSE 5;

DEFINE FILE BOTSCHED
NOWDATETIME_A/HYYMDS=HGETC(8, 'HYYMDS');
NOWDATETIME/HYYMDS=HADD(NOWDATETIME_A, 'minute', -3, 8, 'HYYMDS');
NOWDATE/YYMD=&YYMD;
STARTDATE/YYMD=19700101;
DAYSDIFF/D8=DATEDIF(STARTDATE, NOWDATE, 'D');
MSDIFF/D32=DAYSDIFF*24*60*60*1000;
ADDOFFSET/D12=&OFFSETHOURS*60*60*1000;
NOWMILLISEC/D12 = HTIME(8, NOWDATETIME, 'D12.2');
NEEDTOADD/D13=NOWMILLISEC+ADDOFFSET+MSDIFF;
NEXTRUNTIME/A17=FTOA(NEEDTOADD, '(D13c)', 'A17');
END
TABLE FILE BOTSCHED
PRINT
SCHEDULEID
NEXTRUNTIME 
WHERE JOBDESC EQ '&JN';
ON TABLE HOLD AS MYHOLD
END

-RUN
-SET &NEXTRUNTIME='';
-SET &SCHEDULEID='';
-READ MYHOLD &SCHEDULEID.A12. &NEXTRUNTIME.A13.
-SET &NEXTRUNTIME='0000000000000000000' | &NEXTRUNTIME;

Essentially, this code is calculating the number of milliseconds since January 1, 1970. This will give us the current A32 Zulu time. For example, if I run it now, I will get a value of 00000000000000000001123701669562 for Wednesday, August 10, 2005, at 12:23 PM.

Some notes: &OFFSETHOURS is used to determine the offset from GMT. Our server is in the EST time zone. The offset varies due to Daylight Savings Time. There is a way to automatically detect this value but we have hard coded the dates for now. The automatic technique is explained by John Gray.

Also note that we are adding 0000000000000000000 to the left of the NextRunTime in order to make it A32. To be more accurate, the code should determine how many zeros are missing in order to make the string 32 characters long. We have hard coded it for now.

OK, so at this point we have two new amper variables that look something like this:

&SCHEDULEID: S107puloh311
&NEXTRUNTIME: 00000000000000000001123703379906

Our next step is to e-mail the recipients of the report to inform them that the report will be late today. If you do not want to e-mail users to warn them that the report is late then skip to the "Delaying the Job" section below.

Keep in mind that if the report is delayed four times, we only want to warn the recipients once. So we will first check to see whether the e-mail was already sent. This is done by storing the fact that an e-mail was sent in TBLDELAYEMAIL.

-*DID THE USER ALREADY GET AN EMAIL?
TABLE FILE TBLDELAYEMAIL
PRINT BLN_GOT_EMAIL_DELAY
WHERE SCHEDULEID EQ '&SCHEDULEID';
ON TABLE HOLD 
END
-SET &BLNGOTEMAIL=IF &RECORDS = 0 THEN 'N' ELSE 'Y';

If the recipients did not already receive an e-mail then we must send one now. We start by looking up the recipient's e-mail address. In our case, we do this based on the job name. For example the job name is always in the format XX1234 My Job and we lookup the e-mail address in another table based on XX1234. You can also use the values in BOTSCHED's CASTER_USER or DISTLIST field if you like. Once you have the e-mail address in question, store it in an amper variable. In our case we are using &MYEMAIL. We use a Stored Procedure in SQL Server which uses CDOSYS to send the e-mail. Here is our code.

-SET &TIMEIS = HHMMSS('A8');
-SET &HH = EDIT(&TIMEIS,'99$$$$$$');
-SET &MM = EDIT(&TIMEIS,'$$$99$$$');
-SET &SS = EDIT(&TIMEIS,'$$$$$$99');
-SET &NOWDATE=&YYMD;
-SET &THEYY=EDIT(&NOWDATE, '9999$$$$');
-SET &THEMMO=EDIT(&NOWDATE, '$$$$99$$');
-SET &THEDD=EDIT(&NOWDATE, '$$$$$$99');
-SET &CURDATE='Current Date = ' | &THEMMO 
	| '/' | &THEDD | '/' | &THEYY;
-SET &CURTIME='Current Time = ' | &HH 
	| ':' | &MM | ':' | &SS;
-SET &THESUB=&JN | ' is delayed';
-SET &THEBODY='Unfortunately we are not 
able to distribute your scheduled 
	report called "' || &JN | '" at this time.';
-SET &THEBODY=&THEBODY | ' We will try 
	again in 10 minutes.';
-SET &THEBODY=&THEBODY | ' ' | &CURDATE | '.';
-SET &THEBODY=&THEBODY | ' ' | &CURTIME | '.';

SQL SQLMSS EX MASTER.dbo.sp_send_cdosysmail  
	'from_address@whatever.com', '&MYEMAIL', 
	'&THESUB ', '&THEBODY'
END

Our next step is to update the TBLDELAYEMAIL to indicate that the user has received an e-mail. We use another stored procedure but of course you could do that from the pre_caster fex.

SQL SQLMSS EX caster.dbo.sp_caster
	_Insert_Delay_Email '&SCHEDULEID', '&JOBNAME'
END

Delaying the Job

Finally, we need to delay the job by 10 minutes. We do this by calling an SQL Server Stored Procedure like this:

SQL SQLMSS EX caster.dbo.sp_caster_RuntimeChange
'&SCHEDULEID', '&NEXTRUNTIME', '600000', 'N'
END

This stored procedure receives four parameters: the ScheduleID of the job in question, the current NextRunTime and the number of milliseconds to add (600,000 = 10 minutes). The final value, N, is used to turn off the ON_ERROR notification e-mail that the caster administrators receive when a job fails. This way if the report is delayed four times, the caster administrators do not get four notifications informing them that the report failed.

The stored procedure simply updates one row in BOTSCHED based on the received ScheduleID. You could of course update the row directly from the pre_caster fex if you like. As long as you have the correct value to store in BOTSCHED's NEXTRUNTIME field.

Once this update is done, the value in BOTSCHED's NEXTRUNTIME field will correspond to 10 minutes from now. So if the job is supposed to run today at 9:00 AM, it will now say today at 9:10 AM (in ZULU A32 millisecond format).

Here are the contents of the Stored Procedure:

CREATE procedure sp_caster_Runtime
@SCHEDULEID	varchar(12),
@nextruntime	varchar(32),
@time_ms	INT,
@notify_flag	varchar(1)
as

UPDATE   caster.dbo.BOTSCHED
SET              NEXTRUNTIME = 
	RIGHT('00000000000000000000000000000000' + 
	CONVERT(varchar, CAST(@nextruntime AS numeric) 
	+@time_ms), 32),
NOTIFY_FLAG = @notify_flag
WHERE     (SCHEDULEID =@SCHEDULEID)
commit
GO

-SET &&KILL_RPC='Y';

So at this point the job is no longer running, but it will run again in 10 minutes. This process will repeat every 10 minutes until the data is ready.

The preceding logic is illustrated in the figure below:

Ensure That the Schedule Is Set to Run at the Correct Time the Following Day and E-mail FTP Recipients

This was a tricky step. Here is the problem: if a report finally runs after it has been delayed, ReportCaster will automatically update BOTSCHED's NEXTRUNTIME to the correct date of the Next Run, (i.e., tomorrow for a daily job), but the delay will still be present. For example, let's say a report is supposed to run daily at 9:00 AM and we delayed it twice by 10 minutes. This means it finally ran at 9:20 AM. Once it has run, ReportCaster will automatically change the value of BOTSCHED's NEXTRUNTIME to tomorrow, but the time portion will still be set for 9:20 AM, as opposed to 9:00 AM. We need to move the time back to what it is supposed to be.

In order to do this we will make use of the post-processing fex. This is a fex that will automatically run after our job has been distributed.

We will start by determining the following values for our job:

1. The corresponding ScheduleID.
2. The value of NEXTRUNTIME that is stored in the BOTSCHED table
3. The value of STARTTIME that is stored in the BOTSCHED table. STARTTIME is the time of day that the job is supposed to run. This value will not be affected by our earlier manipulations. So if a job is originally scheduled to run at 9:00 AM it will always say 9:00 AM. The format of the field is 0900.
4. The difference in milliseconds between the time of day when the job is supposed to run and the time of day it is currently set to run next. For example, if the job is supposed to run at 9:00 AM and is currently set to run at 9:20 AM then this value will equal 1200000 ms.
5. The value of METHOD_CODE that is stored in BOTSCHED. We will use this to determine whether we should e-mail the recipients to inform them that the job is ready. If METHOD_CODE is F then the report was distributed by FTP. These users need to be informed that the report has been distributed. Otherwise, they will have no way of knowing when it has finally finished.

The following code will determine all of the above. Remember that the job name is passed as a parameter called &JN.

-SET &ECHO=ALL;

DEFINE FILE BOTSCHED
TEMP1/A13         =  SUBSTR(32,NEXTRUNTIME,20,32,13,TEMP1);
TEMP2/D13         =  EDIT(TEMP1);
ICASTERBASE/I8YYMD WITH NEXTRUNTIME = 19700101;
CASTERBASE/YYMD       = ICASTERBASE;
CASTERBASEDTM/HYYMDIA = HDTTM(CASTERBASE,8,'HYYMDIA');
FOCUSDATE/HYYMDIA      =
HADD(CASTERBASEDTM,'MILLISECONDS',TEMP2,8,'HYYMDIA');

OFFSET2/I2=IF FOCUSDATE IS-FROM DT(20050403 
	00:00AM) TO DT(20051030 00:00AM) THEN -4
ELSE IF FOCUSDATE IS-FROM DT(20060402 00:00AM) 
	TO DT(20061029 00:00AM) THEN -4
ELSE IF FOCUSDATE IS-FROM DT(20070401 00:00AM) 
	TO DT(20071028 00:00AM) THEN -4
ELSE IF FOCUSDATE IS-FROM DT(20080406 00:00AM) 
	TO DT(20081026 00:00AM) THEN -4
ELSE IF FOCUSDATE IS-FROM DT(20090405 00:00AM) 
	TO DT(20091025 00:00AM) THEN -4
ELSE IF FOCUSDATE IS-FROM DT(20100404 00:00AM) 
	TO DT(20101031 00:00AM) THEN -4
ELSE -5;

FOCUSDATE/HYYMDIA      = HADD(FOCUSDATE,'HOUR',
OFFSET2,8,'HYYMDIA');

MS_PART/D12.2=HTIME(8 , FOCUSDATE ,'D12.2');
-*GET THE TIME PORTION WITH TODAYS DATE
TODAYDATE/YYMD=&YYMD;
TODAYDATE2/A8YYMD=TODAYDATE;
TODAYDATE3/A14=TODAYDATE2 | STARTTIME;
SHOULD_BE/HYYMDS = HINPUT(14, TODAYDATE3, 8, 'HYYMDS');
-*get the ms for when the runtime should be
SHOULD_BE_MS/D12 = HTIME(8, SHOULD_BE, 'D12');
IS_NOW_MS/D12 = HTIME(8, FOCUSDATE, 'D12');
MS_DIFF/D12=IS_NOW_MS-SHOULD_BE_MS;
-*REMOVE LEADING ZEROES
MS_DIFF_ALPHA/A12=FTOA(MS_DIFF, '(D12c)', 'A12');
END
TABLE FILE BOTSCHED
PRINT SCHEDULEID NEXTRUNTIME MS_DIFF_ALPHA METHOD_CODE
WHERE JOBDESC EQ '&JOBNAME';
ON TABLE HOLD AS MYHOLD
END

-*-IF &RECORDS EQ 0 THEN GOTO SKIPFIXTIME; 
-SET &SCHEDULEID='';
-SET &NEXTRUNTIME='';
-SET &MS_DIFF_ALPHA='';
-SET &METHOD_CODE='';
-RUN
-READ MYHOLD &SCHEDULEID.A12. &NEXTRUNTIME.A32. 
	&MS_DIFF_ALPHA.A12. &METHOD_CODE.A1.
-TYPE SCHEDULEID=&SCHEDULEID
-TYPE NEXTRUNTIME=&NEXTRUNTIME
-TYPE MS_DIFF_ALPHA=&MS_DIFF_ALPHA
-TYPE METHOD_CODE=&METHOD_CODE

After this runs we have amper variables that look something like this:

&SCHEDULEID: S107puloh311
&NEXTRUNTIME: 00000000000000000001123703379906
&MS_DIFF_ALPHA: 1200000
&METHOD_CODE: F

Our next step is to e-mail the recipients of the report to inform them that the report is ready. We only want to do this for reports that were distributed by FTP (METHOD_CODE = "F"). The code for sending the e-mail is the same as earlier (SQL Stored Procedure).

The next step is to fix the BOTSCHED NextRunTime value if it needs to be fixed. If our pre_caster fex never delayed the report, then &MS_DIFF_ALPHA will be empty and there will be no need to fix the NEXTRUNTIME.

Here is the code:

-IF &MS_DIFF_ALPHA EQ '' THEN GOTO SKIPFIXTIME; 

-*CONVERT POSITIVE NUMBERS TO A NEGATIVE VALUE. 
-SET &MS_DIFF_ALPHA2 = TRIM('L', &MS_DIFF_ALPHA, 
	12, ' ', 1, 'A12');
-SET &CHAR1=EDIT(&MS_DIFF_ALPHA2,'9');
-SET &MS_DIFF_FIN=IF &CHAR1 NE '-' THEN '-'| 
	&MS_DIFF_ALPHA2 ELSE EDIT(&MS_DIFF_ALPHA2, '$999999999999');

-*OK NOW WE NEED TO SUBTRACT THE MS FROM THE ACTUAL
-*NEXTRUNTIME and turn the notification back on
SQL SQLMSS EX caster.dbo. sp_caster_RuntimeChange  
	'&SCHEDULEID', '&NEXTRUNTIME', '&MS_DIFF_FIN', 'E'
END

-SKIPFIXTIME

As you can see, we are using the same Stored Procedure to fix the NEXTRUNTIME as we did to delay it in the pre_caster fex. The stored procedure will simply subtract &MS_DIFF_FIN milliseconds from &NEXTRUNTIME and store the result in BOTSCHED's NEXTRUNTIME field. Once this is done, the NextRunTime will be correct. So if we delayed a 9:00 AM job by 20 minutes, it will now run at 9:00 AM the next day and not at 9:20 AM. In addition, we will pass E as a parameter. This will tell the Stored Procedure to turn the Error Notification for ReportCaster administrators back on.

The final step is to delete any records from the TBLDELAYEMAIL table, which was used to indicate that the recipient already knows that the report is running late. Of course this could be done directly from the fex as well as via a stored procedure.

-*NOW WE NEED TO DELETE ALL RECORDS 
-*FROM THE TBLDELAYEMAIL TABLE
SQL SQLMSS EX caster.dbo.sp_caster_Delete_Delay_Email 
	'&SCHEDULEID'
END

The logic for the post-processing fex is illustrated in the figure below:

Well, that's it. Please feel free to contact me at jodye.yates@aircanada.ca if you have any questions or comments or suggestions for ways that we can improve this process.