Hello,
I have an old FOCUS database that needs to be updated. I'm not familar with updating and deleting records out of a Focus Database. Is this possible?

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

I'm sorry, I forgot to add that this database needs all of 2009 data to be deleted.
Thanks,

Joe

You will have to look up MODIFY FILE in the documentation - documentation on the Mainframe version of WebFOCUS would be your best bet.

You can also use an option of REBUILD to do a bulk delete.

You could also use Maintain:

This is psuedo-code

MAINTAIN FILE
FOR ALL NEXT keyfields INTO STK
WHERE DATE = '2009'
FOR ALL DELETE keyfields FROM STK
END

Remember to make a back up of the data first.

Mark Derwin

Thanks for your help.

I used this, however it will delete all records both 2008 and 2009 related to ppnumber in the entire file within the date criteria. I want to delete only PPNUM at the date level criteria. I still want the PPNUM records with the other dates.


MAINTAIN FILE Wklygfoc
-* Read records to delete into stack.
FOR ALL NEXT PPNUMBER INTO STK WHERE WEEK_OF EQ '03/01/2009';
TYPE "
-* Delete all records from stack.
FOR ALL DELETE PPNUMBER FROM STK ;
TYPE "ERROR: END

WebFocus 7.7.02 WinXP

Joe,

I would add that any suggestions you find here are used at your own risk. As Mark suggested, take a back-up of the file before you start.

To reiterate the point, you use what is suggested here at your own risk.

With that out of the way ....

To use MAINTAIN you should ensure that you are licensed correctly - it's an additional cost product.

Using the REBUILD function will reorganise your data, thus keeping it in it's most efficient state. Don't forget that you can also use REBULD, REORG, DUMP and then LOAD to achieve your goal.

REBUILD, REBUILD with selection criteria actually performs a pseudo data extract, dumping the data into a temporary file. The FOCUS file is then deleted and created afresh before the data is reloaded. You could perform a similar function yourself to help you understand it better, however, with a multi segment file you need to ensure your extract uses key fields to ensure correct summations and ordering etc.

One final thing, if you don't know the product then try and learn more than the fundementals about it first. If you end up deleting more than you intended you can only blame yourself!!

T

Another possibility is to first create a table requests that lists all the records you want to delete. You can then first visually check the records to ensure that those indeed are the records to delete. It also gives you the possibility to narrow or widen your asearch criteria, before anything gets altered in your database.
When the request is correct, add ON TABLE HOLD FORMAT ALPHA to it.
Then use this hold file as input to either maintain or modify to delete the records.
For modify it would be something like:

MODIFY FILE FOCUSFILE
FIXFORM FROM HOLD
MATCH keyfields
ON MATCH DELETYE
DATA ON HOLD
END  

And for maintain, see previous posts.

Hope this helps ...

A further point to take into consideration would be if your FOCUS file is a multi-path hierarchy. If this is the case, I would go with Gerard's suggestion (GamP). You will need one table request for each path, if not for each segment.

Thanks for all suggestions. I do have the data backup no problem. I'm cleaning up some of our reporting and this particular report stored data in a FOC database. I did not create it and I'm not too familar with master files and segments - but I'm using this particular issue as a learning exersice. Here is the Master file layout:

FILENAME=WKLYGFOC,SUFFIX=FOC,$
SEGNAME=SEG01,SEGTYPE=S1,$
FIELDNAME=PPNUMBER, 0XX, A8,FIELDTYPE=I,$
FIELDNAME=FULLNAME, 001, A35,$
SEGNAME=SEG02,SEGTYPE=S2, PARENT=SEG01,$
FIELDNAME=WEEK_OF, 002, MDYY, FIELDTYPE=I,$
FIELDNAME=MARKETTEAM, 029, A40, $
FIELDNAME=ANSWERED, 003, I8C, $
FIELDNAME=AVGTALK, 004, I4, $
FIELDNAME=DNIN, 007, I5, $
FIELDNAME=DNOUT, 008, I6, $
FIELDNAME=MARKET, 009, A20, $
FIELDNAME=TEAM, 010, A20, $
FIELDNAME=TTLSTAFFED, 012, D9.2, $
FIELDNAME=WAITTIME, 013, D9.2, $
FIELDNAME=AUXPERCENT, 014, D8.2, $
FIELDNAME=UTILIZATION,015, D8.2, $
FIELDNAME=PTFT, 019, A1, $
FIELDNAME=ACWTIME, 025, I6, $
FIELDNAME=TOPSKILL, 026, A3, $
FIELDNAME=AVGHOLDTIME,027, I8, $
FIELDNAME=TRANSFERRED,028, I8, $

Thanks!

With Maintain you delete from the lowest level. Also, do you want ALL the dates for 2008? Then you would do this:

MAINTAIN FILE Wklygfoc
FOR ALL NEXT PPNUMBER WEEK_OF INTO STK
WHERE WEEK_OF GE '01/01/2008'
AND WEEK_OF LE '12/31/2008'
FOR ALL DELETE PPNUMBER WEEK_OF FROM STK
END

That should do it.

Mark

If you did not purchase MAINTAIN, revert to TABLE and MODIFY:

  
TABLEF FILE WKLYGFOC
IF WEEK_OF FROM '01/01/2008' TO '31/12/2008'
PRINT PPNUMBER WEEK_OF MARKETTEAM
ON TABLE HOLD
END
MODIFY FILE HOLD
FIXFORM FROM HOLD
MATCH PPNUMBER
ON MATCH CONTINUE
ON NOMATCH REJECT
MATCH WEEK_OF MARKETTEAM
ON MATCH DELETE
ON NOMATCH REJECT
DATA ON HOLD
END

I notice the ALIAS values are all numeric; that's odd, and could be dangerous -- will WF interpret 001 as an integer, or as a reference to FULLNAME?

In any event, here's a Modify equivalent to Mark's Maintain code:

-* hold the keys for segments to be deleted
TABLEF FILE WKLYGFOC
PRINT
  PPNUMBER
  WEEK_OF MARKETTEAM
WHERE WEEK_OF GE '01/01/2008';
WHERE WEEK_OF LE '12/31/2008';
ON TABLE HOLD AS DEL
END

-* summarize 
TABLE FILE DEL
WRITE
  CNT.MARKETTEAM
  FST.PPNUMBER
  FST.MARKETTEAM
BY WEEK_OF
ON TABLE SUMMARIZE
END

-* delete the lower-segment instances
MODIFY FILE WKLYGFOC
FIXFORM FROM DEL
MATCH PPNUMBER
  ON NOMATCH REJECT
  ON MATCH CONTINUE
MATCH WEEK_OF MARKETTEAM
  ON NOMATCH REJECT
  ON MATCH DELETE
DATA ON DEL
END

Thanks for all the suggestions. I will go ahead and constuct something and try to get this to work. I'll report back with the results. Thanks again!