Zulu Time – How to Read the ReportCaster Schedule File

Generating Data/Accessing Data

Zulu Time – How to Read the ReportCaster Schedule File

By Susannah Jones
Elie Tahari, Ltd.

Your ReportCaster schedule file, Botsched.foc, keeps the next runtime for every job you have scheduled. With it you can produce a printable, ordered list every morning, post it to an .htm file on your Web site, and your network engineers can know which jobs run when and how long they’re going to take.

There are two ways to look at your schedule.

The Job Info List: From the Job Info screen in ReportCaster console (srv 51), login with your MRE admin ID. An .htm file will appear, showing your jobs, their next run date/time, their schedule ID, and the FOCUS Fex they execute. Select all, copy, and paste into Excel, then spend hours cleaning up your spreadsheet of merged cells before you can sort runtimes into whatever order you want.
Read the ReportCaster Repository: Mine is kept in a FOCUS database, named by default at install time {drive}:\IBI\SRV51\WFS\FDS\BOTSCHED.FOC. (Yours might be in Oracle or another DB form, but the concept here should be the same. In v5.2, it’s named {drive}:\ibi\srv52\wfs\catalog\BOTSCHED.foc.) The Botsched file contains all the same information as in the Job Info list, but when you look at the data in the NEXTRUNTIME file, you expect it to be as nice looking. But yikes! It’s not! It is an A32 field containing something like this: 00000000000000000001063663680000. That wild thing translates to 09/15/2003 6:08 PM EDT, as our Job Info List tells us.

See what we’re up against? The problem is how to read the Botsched.foc and translate that A32 crazy field into human time. My first step was a search of the Information Builders Knowledge Base at support.ibi.com (password required). I came up dry, so I opened a case with Customer Support Services and had the pleasure of working with Steve Hodos, an Information Builders Customer Support Services wizard out on the West Coast. Steve found out what the number meant. "You’re going to love this," he said. "It’s the number of milliseconds since January 1, 1970." Yikes! Milliseconds? I don’t know about you, but that’s far too precise for my Caster needs! January 1, 1970, is referred to as the UNIX Epoch. Amused, but undaunted, we had our first clue, and resolved to answer this question. In true FOCUS fashion, we came up with the same result in totally different ways. Mine was long and pedantic; his was simple and elegant.

There’s a trick. We found out the hard way that the base date for this number is indeed January 1, 1970, but it’s Greenwich Mean Time – also known as Zulu Time. Not only that, but remember that GMT never adjusts for Daylight Savings. For example, in the summer, New York is four hours earlier than Zulu Time, and in the winter, five. (New York EST is known as Romeo Time, just in case you’re wondering.)

The Approach

I defined some virtual variables in the Botsched file to translate the millisecond field into a serial date with a decimal part representing the time of day. Then I added to it the serial value of its base date, 1/1/70 – which happens to be 25568. (You can find that by going to Excel, typing 1/1/70, formatting at a number, and subtracting one. Microsoft and IBM have smartdate basedates that are different by one, but you’ve no doubt tripped over that already.) This results in a D10.4 field I can edit like crazy and produces date, time, and day name in whatever form I might want.

Let’s Code

				-DEFAULT &OFFSETHOURS = -4 ;
-* New York EDT is four hours later than Zulu Time.
-* NY EST is five hours later, but I don't know how 
-* to automate this switch.
USE 
D:\IBI\SRV51\WFS\FDS\BOTSCHED.FOC
D:\IBI\SRV51\WFS\FDS\BOTLOG.FOC
END
DEFINE FILE BOTSCHED
OFFSETHOURS/D5.1 WITH SCHEDULEID = &OFFSETHOURS ;
OFFSETDAYS/D7.5   =  IF OFFSETHOURS LT 0 THEN 1+OFFSETHOURS/24 
                     ELSE OFFSETHOURS/24;
-* Round up the offset; I don't know why, but it works.
GMTCORRECT/D6.4   = OFFSETDAYS + .00005 ;
-* Now read the usable part of the field NEXTRUNTIME.
-* Keep the last three places, even though they're all 
-* zeros and are milliseconds.
TEMP1/A13         =  SUBSTR(32,NEXTRUNTIME,20,32,13,TEMP1);
TEMP2/D13         =  EDIT(TEMP1);
-* Nuke excess decimal places here. Just get minutes.
tMINUTES/I10      =  TEMP2/(60*1000) ;
-* Now from minutes to days and hours in decimal form.
tDAYS/D10.4       =  tMINUTES/(60*24);
-* Now add the base date and the GMT correction.
tDATE/D10.4       =  tDAYS + 25567 + GMTCORRECT ;
-* Remember the base date in Microsoft is one more than 
-* the base date in FOCUS.
EXCELDATE/D10.4   = tDATE + 1 ;
-* Now get just the integer portion, for the serial day.
GETDATE/I5        = tDATE ;
-* Now get just the fractional remainder, for the hours 
-* and minutes.
DAYSHARE/D6.4     = tDATE - GETDATE ;
-* Calculate the number of the hours from this fractional 
-* day share.
HOURSHARE/D6.2    = DAYSHARE * 24 ;
-* Nuke the remainder, pack with leading zeros.
HOURS/I2L         = HOURSHARE ;
-* Calculate the number of minutes from this fractional 
-* hour share.
MINUTESSHARE/D6.2 = HOURSHARE - HOURS;
-* Nuke the remainder and pack with leading zeros for 
-* the hours.
MINUTES/I2L       = MINUTESSHARE * 60;
-* This you get, right?
AMPM/A2           = IF HOURS GE 12 THEN 'pm' ELSE 'am';
-* Reset the hours to a 12-hour clock, not a 24-hour clock.
HOURS/I2         = IF HOURS GE 13 THEN HOURS - 12 ELSE HOURS;
TIME/A8=EDIT(HOURS) | ':' | EDIT(MINUTES) | ' ' | AMPM;
-* Make a smartdate.
iDATE/I8YYMD     = DTYMD(tDATE,iDATE);
SMARTDATE/MtDYY  = iDATE;
-* Get the day of week name.
DAYNAME/A4       = DOWK(iDATE,'A4');
-* When will the job end? You might want to work with this
-* on your own.
tENDDAYSHARE/D10.4 = RUNMINUTS/(60*24);
EXCELEND/D10.4   = EXCELDATE + tENDDAYSHARE ;
tENDDATE/d10.4   = tDATE + tENDDAYSHARE ;

-*-----------------------------------------------
-* Same thing using FOCUS functions, so elegant! 
-* Courtesy of Steve Hodos, Information Builders 
-* Customer Support.
-* Start with that same TEMP2 field we calculate above.
ICASTERBASE/I8YYMD WITH NEXTRUNTIME = 19700101;
CASTERBASE/YYMD       = ICASTERBASE;
CASTERBASEDTM/HYYMDIA = HDTTM(CASTERBASE,8,'HYYMDIA');
FOCUSDATE/HYYMDIA      =
  HADD(CASTERBASEDTM,'MILLISECONDS',TEMP2,8,'HYYMDIA');
-* You have to adjust this result by your GMT offset.
FOCUSDATE/HYYMDIA      = HADD(FOCUSDATE,'HOUR',
                         &OFFSETHOURS,8,'HYYMDIA');
-* Downloadable form:
AFOCUSDATE/A20 = HCNVRT(FOCUSDATE,'(HYYMDIA)',20,'A20');
END

Your Output

You have three types of fields you can choose to report:

	If you want to export to Excel, and use Excel date formatting, you’ll want to bring down EXCELDATE and remember to format the column as date with both date and time.
	If you want to export to HTML in a form that is hours in 12-hour clock and minutes with AM or PM, you’ll enjoy using DAYNAME, SMARTDATE, and TIME.
	If you want just the facts, ma’am, no foolin’ around, go for the alpha conversion AFOCUSDATE calculated with the fancy FOCUS functions.

Let's Trace Through an Example

NEXTRUNTIME		00000000000000000001063663680000
OFFSETHOURS	Your decision, a parameter (see John Gray's article on how to automate this measurement)	-4
OFFSETDAYS	=1 + (4/24)	0.83333
GMTCORRECT	=.83333 + .00005	0.8334
TEMP1	Last 13 digits of NEXTRUNTIME	1063663680000
TEMP2	Double precision form of temp1	1,063,663,680,000
tMINUTES	=temp1 /(60 * 1000)	17727728
tDAYS	=tMINUTES / (60 * 24)	12310.9222222222
tDATE	tDAYS + base + GMT correct	37,879.7556
EXCELDATE	Base date for Excel	37,879.7561, which, when date formatted in Excel, yields 9/15/03 6:08 PM
GETDATE	Integer part of base date, day only	37878
DAYSHARE	Remainder only	0.7556
HOURSHARE	75.56 percent of a 24-hour day	18.13
HOURS	18 o’clock in 12-hour time	06
MINUTESSHARE	Remainder, share of an hour	0.13
MINUTES	13 percent of a 60-minute hour	8
AMPM	Duh	pm
HOURS	Just no leading zero	6
TIME	Nice, contatenated human time	6:08 PM
SMARTDATE		Sept 15, 2003
DAYOFWEEKNAME		MON
AFOCUSDATE	Elegant time	2003/09/15 6:07 PM (see caveat)

How Long Does Each Job Run?

Now that you’re fearless, you can get the latest start and stop times from another file BOTLOG.FOC. Here’s how:

				DEFINE FILE BOTLOG
ASTART/A13   = SUBSTR(32,START_STAMP,20,32,13,ASTART);
DSTART/D13   = EDIT(ASTART);
-*    If you like, you can use the ATODBL fuction
-*    DSTART/D13= ATODBL(ASTART,'13',DSTART);
ASTOP/A13    = SUBSTR(32,END_STAMP,20,32,13,ASTOP);
DSTOP/D13    = EDIT(ASTOP);
DIFF/D13     = DSTOP - DSTART;
RUNMINUTS/D8 = DIFF/(60 * 1000);
END
TABLE FILE BOTLOG
SUM LST.RUNMINUTS  BY SCHED_ID  
ON TABLE HOLD  AS HRUNTIME FORMAT ALPHA
END

And once you’ve made that little HRUNTIME file, you can easily use the RUNMINUTS field by inserting a virtual variable in your BOTSCHED defines:

				DEFINE FILE BOTSCHED
RUNMINUTS/D8        = DECODE SCHEDULEID(HRUNTIME);
... etc.
tENDDAYSHARE/D10.4 = RUNMINUTS/(60*24);
EXCELEND/D10.4   = EXCELDATE + tENDDAYSHARE ;
tENDDATE/D10.4   = tDATE + tENDDAYSHARE ;
... etc.

Now you'll have a length of time field for each job. I used the last time the job ran, you might like the average: AVE.RUNMINUTS.

Here’s My Simple Report Code

				TABLE FILE BOTSCHED
IF ACTIVE IS 'Y'
IF INTERVALTYPE NE 'O'
BY EXCELDATE NOPRINT
PRINT
   AFOCUSDATE AS 'NEXT RUN TIME'
-*If you're exporting to Excel, use these two lines:
   EXCELDATE  AS 'NEXT RUN TIME: format as date/time'
   EXCELEND   AS 'END TIME: format as date/time'
-*
   DAYNAME    AS 'DAY' 
   SMARTDATE  AS 'DATE' 
   TIME 
   RUNMINUTS 
   SCHEDULEID	
   JOBNAME	AS ‘FEXNAME’
-*nb: the fexname in 522 is field TASKOBJ in BOTTASK 
-*    file; also field TASKNAME in same file is quite 
-*    useful, as it differs in 522 from JOBDESC 
   JOBDESC	 
   INTERVALTYPE	
   WEEKDAYS	 
   NEXTRUNTIME
  ON TABLE PCHOLD FORMAT EXCEL
END

Caveat: For some reason, in 522, when I use the fancy FOCUS function, I come up one minute short. So…rather than agonize, I add one minute to the FOCUSDATE.

Resources

	Those fancy FOCUS functions are in a great book called WebFOCUS KeySheet, by John W. Price, and available from the Information Builders bookstore.
	www.greenwichmeantime.com will tell you everything about Zulu Time. To learn how to automate switching from EST to EDT and avoid having a parameter &OFFSETHOURS, read "Using Reg.exe to Offset Time Zones From a Windows-Based System," by Information Builders' own John Gray.
	Information Builders Customer Support is still my favorite resource. They are there for you 24/7 with wisdom, experience, guidance, encouragement, and humor. I couldn’t live without them.
	There’s still work to be done: If a job runs many times in one day, how to get a calendar with all the run times? Any ideas, please e-mail me.

Susannah Jones is a WebFOCUS architect at Elie Tahari, Ltd., in New York. Visit the company and view the Elie Tahari Collection at www.elietahari.com.

Primary links

Search

Zulu Time – How to Read the ReportCaster Schedule File

Zulu Time – How to Read the ReportCaster Schedule File

The Approach

Let’s Code

Your Output

Let's Trace Through an Example

How Long Does Each Job Run?

Here’s My Simple Report Code

Resources

Connect with us