Hi There

In my pre-processing fex in rcaster, I want to update some values stored in the BOTSCHED table in the caster database. There are many reasons that we need to do this. Here are two:

1) Every time the report runs, change the name of the JOBDESC to something like REPORTNAME_YYMMDD.xls

2) Check for data. If no data is found, then update the NEXTRUNTIME by 60000ms and Kill the job. via &&KILL_RPC.

Both of these can easily be done as long as I know which PACKETID or SCHEDULEID is currently running. Any idea how to access these from the pre-processing fex?

Thanks

Jodye

I think there is a variable &DSTSCHEDID

Hi Texas

When I try to output the value of DSTSCHEDID in the preprocessing fex, with "-TYPE &DSTSCHEDID" I get

Task error: A VALUE IS MISSING FOR: DSTSCHEDID

Any other ideas?

Thanks

Jodye

and you don't want to do it the hard way...a separate prefex for each schedule...hard coding the schedule id, etc., ? Too last millenium??

Hi Susannah

Actually that never occurred to me. How can you hard code the SCHEDULEID? And Yes I am using one prefex which will work for many different reports and scheduled jobs.

Actually, I am working on a workaround where I pass the JOBDESC to the prefex and then I can use SQL Passthru to update based on the JOBDESC. This will work since the JOBDEC must be unique. But I still think there must be a way to access the current SCHEDID.

Thanks!

Jodye

Well, it seems to be tricky to try and update the NEXTRUNTIME value in BOTSCHED. Like someone suggested in a previous post, I will have to reverse engineer the Zulu method.

We simply want to use the prefex to check whether the data is ready to be displayed. If not, then delay the schedule by 10 mins and try again then. I am surprised there is no easier way to do this. Have you used the zulu code for something like that Susannah?

Thanks

Jodye

&DSTSCHEDID is only set when running via Report Caster you can set a default to somthing like xxxxxxxx and then check it. If it is xxxxxxxx then you are not running via caster.

Hi Texas

I am always testing through caster. That error message "A VALUE IS MISSING FOR: DSTSCHEDID" comes up in my caster trace files and in the caster log file.

Jodye

Sorry It appears that DSTSCHEDID is only available in the main task and not pre execution jobs. If you are trying to create an Alert (with is kinda sounds like) you can set the main schedule to run every so often then have the pre-execution job determine if the main job should run. if not set the &&KILL_RPC to Y this will prevent the main job from running and caster will automaticly set the next run time.

For exploratory purposes you can insert the dialogue manager command
-? &DSTSCHEDID
to see whether it exists and (if so) what it contains.

Or
-? &DST
to see all starting with the DST prefix

Or
-?
to list all &vars

-? &
is the command to see all & vars

No Jodye, i haven't , but i certainly have aneed for that ...so i'm going to try it.

as for the sked-id, i just meant writing a prefex for each schedule..you know the skedid from the caster api...so in your prefex, you take the hardcoded skedid and modify botsched matching on that skedid.
Not an elegant solution, but i'm going to try it.
hmmmm. i'm just wondering..about the reaon you're having trouble modifying the botsched..
maybe its because the botsched is being modified currently by caster itself, as it executes the sked in quesion...hmm. two modify's at one time...probably not going to work.

Hi Again

It is very tricky to programmatically play with the NEXTRUNTIME, and not supported at all by IBI, but we have managed to make everything work perfectly. Now when a scheduled report starts, the pre processing fex checks for the presence of data. If the data is not there yet, then the prefex delays the report by 10 minutes and kills the job. Also, the user gets an email saying that the report is late. Finally, when the report actually runs, the post processing fex sets the runtime back to what it should be.

If people are interested I can post our method for making this work.

oh yes please!
it sounds like an article for the Developer's Articles section of focal point..
or even a session presentation at Summit.

Hi jodye,

Can you please post your method to the board? So that the information can be shared among the whole forum community.

Thanks a lot!!

Regards,
Kerry
Focal Point Moderator
Email me: kerry_zhan@ibi.com

Hi Everyone

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?

YES
do nothing
NO
send 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
END

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(CASTERBASEDTM,'MILLISECONDS',TEMP2,8,'HYYMDIA');
FOCUSDATE/HYYMDIA = HADD(FOCUSDATE,'HOUR',
&OFFSETHOURS,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 = TRIM('L', EDIT(MS_DIFF), 12, '0', 1, 'A12');
END

-*******************************************************
-*TABLE FILE
-*******************************************************
-*GET THE SCHEDULE INFO
TABLE FILE BOTSCHED
PRINT SCHEDULEID NEXTRUNTIME MS_DIFF_ALPHA
WHERE JOBDESC EQ '&JN';
ON TABLE HOLD AS MYHOLD
END

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.

Jodye

jodye, thanks.
holy cow i just read this thing thru.
APPLAUSE.
who's your boss? so we can all be sure to attend that session?

Hi Susannah

I just found out that I will be at the conference as well. We will be presenting on May 18.

Replacing a Legacy System Using WebFOCUS
Chantal Berthiaume, Air Canada
Wednesday, May 18 1:30PM - 2:30PM

Jodye

There is a much simpler way to control the delay without having to do anything. LOOP your pre processing job putting it to sleep if it does not find any data.
(as far as I know the SLEEP command is not documented)

-SET &COUNTER=0;
-LOOP
-SET &COUNTER=&COUNTER +1;
TABLE FILE datafile
PRINT column
IF READLIMIT EQ 1
ON TABLE SAVE
END
-RUN
-SET &HAVEDATA=&LINES;
-IF &HAVEDATA NE 0 GOTO ENDJOB;
-IF &COUNTER GT 1 GOTO NOMAIL;
-*
-* e-mail Notification job
-*
-NOMAIL
SLEEP 600
-GOTO LOOP
-ENDJOB

What is the 600? Is that seconds?

Also, I searched through all the pdfs, etc and could not find a single reference to SLEEP so you are right about it being undocumented.

Does anyone else eknow anything about SLEEP?

I haven't tried it yet. Have you actually used it? What does the nextruntime show after sleep is issued?

Sorry, yes the 600 is seconds.

As far as I�ve been able to find out it�s an internal eda/iway command used by the system.

NEXTRUNTIME will be that set automatically by caster when the job starts.

I use this technique for batch processing extracts against multiple systems simultaneously.

A specific example is where I create an extract from multiple SAP BW systems. Each individual extract takes about 30 minutes so five times that equals 21/2 hours.
By batching the jobs and having a controlling process that waits for the sub-processes to complete I can run the whole thing in a little over 30 minutes.

I have also used this kind of technique several times and it works ok.
Here is an example.

-* STEP-1
-*************************************************************************
-* Check for the existence of IPMMTH1.TXT File.
-*************************************************************************
-SET &CTR=0;
-LOOP
-SET &CTR=&CTR+1;
DOS STATE D:\ALERTTRIGGERS\COPYTO\IPMMTH1.TXT
-IF &RETCODE EQ 0 GOTO DONE ;
-IF &CTR EQ 8 GOTO DUMMY ;
-SET &ERRMSG = IF &CTR EQ 8 THEN 'TIME OUT' ELSE ' ' ;
SLEEP 3600
-GOTO LOOP

-* STEP-2
-*********************************************************************
-* Following is a dummy report to satisfy broker routing requirements
-*********************************************************************
-DUMMY
DEFINE FILE CAR
DUMMSG/A80='NO IPMMTH1.TXT! - MONTHLY EXTRACT FOR IPM SALE SUPPORT WAS UNSUCCESSFUL!';
END
TABLE FILE CAR
PRINT DUMMSG AS ' '
BY CAR NOPRINT
IF RECORDLIMIT EQ 1
ON TABLE SET EMPTYREPORT ON
END
-RUN
-EXIT

-DONE
-*********************************************************************
-* Execute Monthly Extract program ACIPMOLD.FEX
-*********************************************************************
-INCLUDE ACIPMMLD
-RUN
-*
-*********************************************************************
-* Delete IPMMTH1.TXT file.
-*********************************************************************
DOS DEL D:\ALERTTRIGGERS\COPYTO\IPMMTH1.TXT

One think that I could think of is that while the SLEEP may work it is still tieing up a WebFOCUS and Report Caster process. Not to good if you have lots of jobs to schedule and they SLEEP. Once the MAXIMUM is reached every think will be waiting on a process to finish. could be a big problem.

Hi Texas

I was worried about that. That is why I need some documentation. What exactly is happening when the SLEEP command is issued? If we have 100 reports all starting at the same time and all of them have SLEEP, what will that do to RCaster? Is SLEEP altering the NEXTRUNTIME like we do in the procedure described above? Or is something happening during the sleep period? In our method once the nextruntime is delayed, we kill the job and that's it, it is out of the picture until 10 minutes later. Is that what happens with Sleep?

Thanks

I'm not exactly sure what the SLEEP command is doing but if you look at the code above WebFOCUS keeps repeating the loop. If the process that creates the file for the DOS STATE command fails and the file never gets created you are now in a never ending loop. You must manually kill the process. When ever possible I would create the main procedure that produces the report. Then create a pre-execution procedure that does the DOS STATE checking for the existance of the file. Then create a post procedure that deletes the file.

In the pre-execution procedure if the file does not exist then set the KILL_RPC variable to Y else do not set it. If KILL_RPC equals Y the main procedure will not be execute. You could schedule the caster job to run say every 10 minutes. If the file is not there Caster updates the next runtime by adding 10 minutes and the main procedure is not executed because KILL_RPC is set to Y. This is repeated every 10 minutes. When the file is there the main procedure gets executed. Then 10 minutes later the whole process starts over again.

This way everything is being done as recommended by IBI. Updating the BOT tables directly is NOT supported by IBI and could cause problems you may later regret.

Hi Texas

But we dont want the job to run every 10 mins. The job (actually over 100 jobs) is supposed to run once daily at 6am. If the data is late then and only then do we delay by 10 mins. This 10 min delay is repeated until the data is available (usually within 1 hour). Once the job runs successfully then the job should not run again until tomorrow at 6am. How would you accomplish this?

Our method works perfectly but I am intrigued by this whole SLEEP isssue if I can get some more info about it. As you say, our manipulation of BOTSCHED data is not supported by IBI.