Focal Point
Problem correctly populating a Focus file

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7151047712

March 12, 2007, 10:54 AM
mark66
Problem correctly populating a Focus file
Hi all,

I am wanting to create a simple log file, so I can monitor report usage.

I have created the following file:

FILE=USERLOG, SUFFIX=FOC
SEGNAME=ROOT_SEG, SEGTYPE=S4, $
FIELD=USER, ALIAS=USER, FORMAT=A8, FIELDTYPE=I, $
FIELD=PROC, ALIAS=PROC, FORMAT=A8, $
FIELD=RUNDATE, ALIAS=RUNDATE, FORMAT=YYMD, $
FIELD=STARTED, ALIAS=STARTED, FORMAT=A8, $
FIELD=ENDED, ALIAS=ENDED, FORMAT=A8, $
FIELD=RETURNED, ALIAS=RETURNED, FORMAT=I5, $

And initially wish to populate the file with:

USER person who has submitted the report
PROC the report they have called
RUNDATE the date the report was submitted
STARTED the time the report was submitted

The code that was suggested to me is:

-SET &USER = &G_USER;
-SET &RUNDATE = &YYMD;
-SET &PROC = &REPORT;
-SET &STARTED = HHMMSS('A8');


MODIFY FILE USERLOG
FIXFORM USER/8 X1 PROC/8 X1 RUNDATE/8 X1 STARTED/8
MATCH USER PROC RUNDATE STARTED
ON MATCH REJECT
ON NOMATCH INCLUDE
DATA
&USER &PROC &RUNDATE &STARTED
END

If the variables &USER and &PROC have been fully populated then the record is correctly written

PAGE 1

USER____ PROC____ RUNDATE_ STARTED_ ENDED___ RETURNED
12345678 ABCDEFGH 2007/12/12 15.48.15 0

However if the data in USER or PROC is not the max length, as it can vary, then the whole file gets misaligned:

USER____ PROC____ RUNDATE_ STARTED_ ENDED___ RETURNED
1234ABCD2007/12/12 15.48.15 0

This is the first time I have used Modify file and I am not sure how to pad out my variables so that the length is always that of the field it is being written to?

I am sure there must be a very simple solution to this, but I have yet to stumble across it!!

Many thanks

Mark.

This message has been edited. Last edited by: mark66,


WebFocus 765. iSeries v5r4
March 12, 2007, 11:01 AM
Alan B
Change to use FREEFORM, and sepearte the &fields with a comma, end with a $
  
-SET &USER = &G_USER;
-SET &RUNDATE = &YYMD;
-SET &PROC = &REPORT;
-SET &STARTED = HHMMSS('A8');


MODIFY FILE USERLOG
FREEFORM USER PROC RUNDATE STARTED
MATCH USER PROC RUNDATE STARTED
ON MATCH REJECT
ON NOMATCH INCLUDE
DATA
&USER, &PROC, &RUNDATE, &STARTED,$
END

Which should allow for variable lengths of hte fields.


Alan.
WF 7.705/8.007
March 12, 2007, 11:16 AM
FrankDutch
but be careful...if the user or the proc has a comma in their name you will get an error




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

March 12, 2007, 11:26 AM
mark66
Thanks Alan, that was just the trick I needed!!

I had only been pointed in the direction of FIXFORM, which to me sounded like it should be appropiate, but I guess not!

What does the $ do at the end of the data string?

Thanks again Smiler


WebFocus 765. iSeries v5r4
March 12, 2007, 11:28 AM
Danny-SRL
You can pad your variable length fields:

-SET &USER = &G_USER || ' '; (7 SPACES)
-SET &USER = EDIT(&USER, '99999999');

Same for &PROC


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

March 12, 2007, 11:30 AM
Alan B
Glad it worked mark.

The $ is the delimiter to declare the end of that data transaction.

FIXFORM is really used where you have a flat file, like a HOLD file in FOCUS, where each field is of a fixed length.

FREEFORM allows for variable lengths of the input fields up to the maximum allowed for a field. &variables are normally variable length.


Alan.
WF 7.705/8.007
March 12, 2007, 11:36 AM
mark66
quote:
Originally posted by FrankDutch:
but be careful...if the user or the proc has a comma in their name you will get an error
Hi Frank,

I am certain that should never be the case, but would they be a way around the problem or would you use another solution?


WebFocus 765. iSeries v5r4
March 12, 2007, 11:44 AM
Danny-SRL
Mark,
Should it be the case, revert to your FIXFORM and pad the variable length fields with blanks as I showed previously.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

March 12, 2007, 12:16 PM
Alan B
If you do get a comma in your data, or to be very safe, you should use:
  
DATA
&USER.QUOTEDSTRING, &PROC.QUOTEDSTRING,......
END



Alan.
WF 7.705/8.007
March 12, 2007, 01:04 PM
mark66
Thanks for the further suggestions Alan & Danny.

And finally my last question (I hope) is how can I calculate the difference in two time stamps?

My process is basically -

1, Write initial entry to log (as coded above)
2, Call report
3, Update Initial entry to log with an endtime and number of rows returned.

So my log file contains two fields, STARTED and ENDED, and in the file that was suggested to me the time fields were declared as A8 and are populated in the style:

-SET &STARTED = HHMMSS('A8');
-SET &ENDED = HHMMSS('A8');

I want to calculate the run time (difference between the two fields in seconds), however I cannot run a calculation on Alpha fields. Do I need to change the field format for STARTED and ENDED? If so, what format would be appropriate and what would be the correct method to populate the variables?

Thanks again for everyone’s help!! Smiler


WebFocus 765. iSeries v5r4
March 12, 2007, 01:23 PM
Alan B
To convert a time in Alpha format to seconds, try:
  
SECONDS_1/I8= EDIT(EDIT(STARTED,'99'))*60*60)+(EDIT(EDIT(STARTED,'$$$99'))*60)+(EDIT(EDIT(STARTED,'$$$$$99')));
SECONDS_2/I8= EDIT(EDIT(ENDED,'99'))*60*60)+(EDIT(EDIT(ENDED,'$$$99'))*60)+(EDIT(EDIT(ENDED,'$$$$$99')));

Basically splitting the time in to component parts with EDIT, then using EDIT to convert to integer. then you can simply subtract the 2 fields. This can be done in a define in the TABLE request.


Alan.
WF 7.705/8.007
March 12, 2007, 02:15 PM
FrankDutch
It appears to be an interesting procedure, how do you create the log entries and at what moment are you putting this in the database?
When does the record get the endtime and what is the field returned for?

Is this not a build in function in Webfocus?

BTW you can put the calculated seconds in a computed field in the master.

Frank




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

March 12, 2007, 02:39 PM
mark66
quote:
Originally posted by Alan B:
To convert a time in Alpha format to seconds, try:
  
SECONDS_1/I8= EDIT(EDIT(STARTED,'99'))*60*60)+(EDIT(EDIT(STARTED,'$$$99'))*60)+(EDIT(EDIT(STARTED,'$$$$$99')));
SECONDS_2/I8= EDIT(EDIT(ENDED,'99'))*60*60)+(EDIT(EDIT(ENDED,'$$$99'))*60)+(EDIT(EDIT(ENDED,'$$$$$99')));

Basically splitting the time in to component parts with EDIT, then using EDIT to convert to integer. then you can simply subtract the 2 fields. This can be done in a define in the TABLE request.
Awesome, again that has worked just great!!

However, I don't quite understand why the final EDIT to extract the seconds only uses five dollar symbols and not six?

I would have thought it needed 6 as the format of the time is : 12:34:56, therefore it needs to ignore the first 6 characters? It does work with both 5 and 6 dollars, but why?

Again, thank you for all your help today Good One


WebFocus 765. iSeries v5r4
March 12, 2007, 02:47 PM
Alan B
Oh Error,
It should be 6 $s' Mark, not 5, and because the time delimiter is a '.' (not a ':'), it is legal to put that into a numeric field.
Well spotted Eeker


Alan.
WF 7.705/8.007
March 12, 2007, 03:01 PM
mark66
quote:
Originally posted by FrankDutch:
It appears to be an interesting procedure, how do you create the log entries and at what moment are you putting this in the database?
When does the record get the endtime and what is the field returned for?

Is this not a build in function in Webfocus?

BTW you can put the calculated seconds in a computed field in the master.

Frank
Hi Frank, I am not sure if this is already a built in function of WebFocus as I do not know the product very well yet, but I would imagine there must be some similar log functionality?

Basically I am trying to improve a process that we already have running here:

Every one of our reports starts with a:

-INCLUDE STARTUP

This procedure sets many things like what data libraries on the AS400 our user should be pointed to depending on their location etc. There is also a bit of script that does a WRITE to a log.fex. This includes the users id, the report they have submitted (FOCFOCEXEC), the system date and system time and their location.

And again every one of our reports ends with a:

-INCLUDE FINISH

This procedure does a similar 2nd WRITE to log.fex, again with the same details.

Therefore every report that runs creates 2 lines on what is really just a text file. The problem is that this is not a true database file and so when I want to query the file to see who has run a report today it takes a very long time. We also have a problem with users requesting too much data and extracting thousands of records that takes a long time. Therefore the start and end records can be 50 log records apart and are not the easiest to match up.

So I decided to create a Focus log file, with the aim to write a record on the report being called and then to locate and update the same record when the report finishes, with extra data like the runtime and the number of rows extracted.

I will now replace the old writes to log.fex in the STARTUP and FINISH procedures with the new code I have refined today. And hopefully I should now be able to create some snazzy reports to help us really analyse the use of WebFocus in the company and target the problem reports and users. We can also analyse who is actually using the resource and how frequently they logon, to help us manage who needs a license etc.


WebFocus 765. iSeries v5r4
March 21, 2007, 07:53 AM
mark66
Hi all,

My log file has been running just great for the last week, but has suddenly decided to stop working.

For some reason the programs just hang when trying to write or update any record on the Focus Database file.

I can still read from the file and run any reports against it, but I cannot do any modifications? I have tried to delete a record and again it just hangs when trying to modify the file and does not return any error message.

The file is showing as being 592KB in size and has around 3366 records in it. Is this too big? I did a quick Google search and it says the max size is 2gb!

I have created a new Focus file and the process works just fine, but for some reason my proper log file is broke!!

Anyone have any ideas?

Many thanks

Mark


WebFocus 765. iSeries v5r4
March 21, 2007, 08:13 AM
Alan B
Mark

There should be no problems with the size you have, though an S4 like you have can get a bit disorganised. Again that should not stop a maintenence process, just slow it down.

What platform are you on?

Are you using the FOCUS sink machine, FOCSUnn?


Alan.
WF 7.705/8.007
March 21, 2007, 08:16 AM
Danny-SRL
Hi Mark,

As I see it, for every FOCEXEC which is run you are updating the FOCUS file. It is probable that there were 2 procedures who tried to update your FOCUS file simultaneously and this could have caused some type of corruption of the file, pointers creating a loop.

I would suggest one of 2 possibilities:
1. Using a Simultaneous User Focus file.
2. Using a relational database.

Personally I would go with the 2nd suggestion.

Good luck!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

March 28, 2007, 06:23 AM
mark66
Thanks for your thoughts guys. The file crashed a couple more times in the week, each time leading me to having to re-create it.

So I have changed the log file to write to a file on our AS400 and have had no issues since!

Cheers


WebFocus 765. iSeries v5r4