My boss will in fact be presenting our method for delaying reports at this year's Summit. I will write a very detailed description regarding our method later on, but for now here is a summary. Two caveats are that as you will see, this method is very specific to our environment. Also, IBI does not support the manual manipulation of NEXTRUNTIME.
Let's assume report X is supposed to run daily at 8:30am. We load our data daily but sometimes the load is late. So we want to check to see if the data is loaded before distributing the report.
The reports are called with a pre processing line that looks like this...
"pre_fex RN=1, JN=name of job" and a post processing line like this..
"post_fex JN=name of job"
(notice that params are separated by commas and are case-sensitive).
This is what happens....
1) As soon as the job starts, caster automatically updates the NEXTRUNTIME in BOTSCHED to the next runtime. So as soon as the report starts, the nextruntime is already set to tomorrow, even though "real_report" has not yet started. Caster is still in "pre_fex". We need the ORIGINAL NEXTRUNTIME (ie today at 830). We wrote an SQL trigger that saves the ORIGINAL NEXTRUNTIME into a table called "tblDelayCaster". So a record is created with the job scheduleid, the jobname and the original nextruntime, which would be today at 830, of course in the ZULU A32 format. If you cannot save the original NEXTRUNTIME then this procedure won't work. We use SQL Server so there is no problem with the trigger. If you can't use a trigger then you will need to figure out when the original NEXTRUNTIME was supposed to be, based on the schedule. Weekly, daily, days of the month etc. This would be very tricky to do but not impossible.
2) In the prefex, start by checking for the presence of your data. This is an obvious step that simply uses a hold file and then checks &RECORDS for EQ 0. Of course the actual check that we do depends on the value of RN passed to pre_fex.
If there is data then jump to the end of the prefex. Everything is fine.
3) If the report should be delayed then do the following steps.
4) Lookup the scheduleid of the job in the table tblDelayCaster via the job name (JN param in pre fex string).
5) Send the user an email informing him that the report is late. But we only want to send one email. If the report is delayed 3 times for 10 minutes each the user should get one email and not 3. In order to do this, we use a simple table called tblDelayEmail that records the scheduleid of the job. So the prefex checks that table to see if the record already exists. If not then we send an email using an SQL Server Stored Proc using CDOSYS. We then update the table. So pseudo code=
Did User already get an email?
lookup the user's email address
send email via stored proc
SQL SQLMSS EX DB.dbo.sp_NAME '&from', '&MYEMAIL', '&subject', '&body'
update tblDelayEmail table indicating that user has received the email
5) Now we need to update the BOTSCHED in order to delay the report by 10 minutes. Here is the procedure.
- READ tblDelayCaster to get the ORIGINAL NEXTRUNTIME (which was updated by the trigger).
As described by susannah here http://www.informationbuilders.com/support/developers/zulu.html,
the NEXTRUNTIME looks like this: 00000000000000000001106955060000 and is in milliseconds. So we want to add 600000 to it (10 mins). We call an SQL Server Stored Proc to update the NEXTRUNTIME. The call is like this...
SQL SQLMSS EX dbname.dbo.sp_NAME '&SCHEDULEID', '&NEXTRUNTIME', '600000', 'N'
The stored proc simply adds X millisecs. (3rd param) to the original NEXTRUNTIME (2nd param). and updates the BOTSCHED table with the result. Actually at this point we don't care what the nextruntime translates to in real date time. We just want to add 60000 to it. Note that you need to convert the string to integer, strip the zeroes, add 60000 then put the leading zeroes back before you update NEXTRUNTIME. At this point the report will now run today 10 minutes later.
The last param 'N' is used to update the Notify_flag in Botsched. When a report is late, we do not want the recipient of "notify on report error" to get spammed. So we change it from "on error" to "Never".
6) Kill the job like this... -SET &&KILL_RPC='Y'; this means that the report never runs.
OK thats it for the pre fex.
The post fex also has a few steps.
1) If the report was distributed by FTP, then email the user telling him that the report is now ready. You need to look in BOTSCHED to check for that.
2) This was a tricky step. I was saved by Susannah's Zulu method. We have one big problem. In the example above, once the report has run, caster will automatically update the nextruntime to tomorrow. That's fine. The problem is that believe it or not, the delay will still be present. So, if the original nextruntime was today at 8:30 and the report was delayed by our process by 10 minutes and then runs, the final nextruntime will be tomorrow at 8:40. So we need to set the nextruntime back to what it should be.
Here is the code for this...this is based on the zulu method described by Susannah.
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(FOCUSDATE,'HOUR',
MS_PART/D12.2=HTIME(8 , FOCUSDATE ,'D12.2');
-*GET THE TIME PORTION WITH TODAYS DATE
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');
-*REMOVE LEADING ZEROES
MS_DIFF_ALPHA/A12 = TRIM('L', EDIT(MS_DIFF), 12, '0', 1, 'A12');
-*GET THE SCHEDULE INFO
TABLE FILE BOTSCHED
PRINT SCHEDULEID NEXTRUNTIME MS_DIFF_ALPHA
WHERE JOBDESC EQ '&JN';
ON TABLE HOLD AS MYHOLD
In Botsched STARTTIME never changed. This is set to 8:30AM and is not effected by manipulating nextruntime.
Basically what we are doing here is looking at the difference in ms between the current NEXTRUNTIME (ie 840) and what the NEXTRUNTIME should be (ie 830). We are using HTIME to get the ms and then subtracting 840-830.
Finally we take the result (MS_DIFF_ALPHA) and if there is a difference, we convert it to a negative value
-SET &MS_DIFF_ALPHA='-'| &MS_DIFF_ALPHA;
and then we call our stored proc like this...
SQL SQLMSS EX dbname.dbo.sp_NAME '&SCHEDULEID', '&NEXTRUNTIME', '&MS_DIFF_ALPHA', 'E'
Notice that we are also passing "E" to turn on the report notification on error.
Phew. Does that make sense to anyone? This pre fex and post fex method should work for any scheduled report.