clear physical file member question
is it possible for me to only clear certain records of a physical while leaving certain records available?
I have a file with thousands of records, but I only need the records starting with a certain date. How can I delete everything prior to the date that i need with out totally wiping out the entire file with the CLRPFM command?
Software/Hardware used:
Software/Hardware used:
ASKED:
September 13, 2006 9:25 AM
UPDATED:
December 17, 2009 2:50 PM
Answer Wiki:
CLRPFM doesn't delete individual records, so it is all or nothing. Try:
DELETE FROM foobar
WHERE date_column < :certain_date;
To see all answers submitted to the Answer Wiki: View Answer History.
If you’re not into SQL you can do this using the CPYF command.
First create a duplicate copy of the file to work from:
CRTDUPOBJ OBJ(myfile) FROMLIB(mylib) OBJTYPE(*FILE) TOLIB(QTEMP)
Then, use the CPYF command to copy the file over the original whilst removing the records you don’t want:
CPYF FROMFILE(QTEMP/myfile) TOFILE(mylib/myfile) MBROPT(*REPLACE) INCREL((*IF datefield *GE datevalue))
The INCREL parameter tells the system to only include records where a condition is met. In this case we’re checking the value of a datefield against it’s contents.
For this to work your date would have to be in YYYYMMDD order. If it’s a date data type then you would probably need to test against something like d’YYYY-MM-DD’.
Hope it helps
Jonathan
http://www.astradyne-uk.com
If a CPYF is used, a second copy of the file is created. Before copying this result back, review it carefully to be sure that it contains all of the desired records.
If SQL is used to DELETE large sets of records, run the statement first as a SELECT * statement to see a list of selected records. If only the records you want to delete are in the list, run the same statement after typing DELETE over SELECT *.
Tom
I can’t tell you how many times SELECTing before DELETEing has prevented major headaches!