Physical file size unchanged after delete

[PDizzyD]

I am new the the AS/400 but have used several of the commands as I have been slowly moving data to our new 8.12 environment.

I have noticed (and my space is limited, this is why I ask) that after I delete large amounts of records from files the physical size does not change. I remember when I worked with DBaseIV I had to rebuild indexes and the like, is there a similar process I need to follow to reduce the file size after a delete?

Thanks in advance.

 

[Eric Lehti]

Use the RGZPFM command on the file when you have an exclusive lock on it. Try ALCOBJ if running in a CL program.

 

[tpayne]

When you delete records from a file on the AS400 it doesn't reuse the space. If you type WRKF {filename} then take option 8 and scroll to the bottom, it will show you the number of records in the file and the number of deleted records.

To shrink the file and make the space available you need to run the RGZPFM (reorganize physical file member) on the file. This command can only be run when nobody else is using the file, so reorganising is usually run overnight or at weekends.

However, you can run programs to delete records at any time, which does allow you to perform your purge processes separately from file reorganisation.

Actually it is possible to define files so that the deleted records are reused, but I have heard that there are problems with this. Whether this is JDE specific I am not sure. I just know it's not recommended. Maybe someone else can elighten you on this.

 

[scott_parker]

We reuse deleted records on a number of files with no problems.


Scott Parker
Programmer Analyst/Technical Specialist
JDE World 8.1/SalesLogix 6.2
Grote Industries LLC

 

[Colin_Hugill]

G’day Tony,

The AS/400 will reuse deleted record space but you have to set this file attribute – it is AS/400 DB/400 “centric” not JDE/PSoft/Oracle specific. There may have been minor problems with the reuse deleted records attribute in the early days but I would have thought that these would have long been fixed. Obviously the reuse deleted records attribute has some minor playoff in terms of CPU/Op Sys overhead.

I used to set up a weekend SLEEPER job that had a CLP that extracted file details to it’s own file (an option in the DSPFD or DSPFMBR command if I recall) ran a QRY to calculate percentage deleted and output to another file then read the file and executed the RGZPFM command if deleted space exceeded 10 percent AND the total number of records in the file exceeded some nice round value like 10,000. All very simple, easy, and, civilized! Forget the fancy stuff if you adopt this approach – negligible system overhead and if there are “blank” times in processing over a weekend then shut down QINTER, do the SLEEPER thing, restart QINTER etc. You know the drill.

 

[LaLouisiane]

There aren't any current problems with reusing deleted records for DB2 files on AS400 that I'm aware of. There might have been issues at one time.

You might want to make sure you are current on DB2 PTF's.

There are IBM PTF's, DB2 PTF's as well as JDE fixes, etc. that need to be applied from time to time.

Applying current DB2 PTF's ensures that your system run more efficiently using the DB2 files. I always did this as a matter of course when maintaining an AS400.

The RGZPFM command should actually remove the deleted records and reduce the file size for you.

I have never encountered a problem re-using deleted records in a DB2 file on AS400.

Good luck.

/lf

 

[tpayne]

Hi Colin,

Haven't heard from you in a while, good to hear from you.

I had a routine too that would identify any files that had a lot of wasted space and would reorganize them. I had thought though that it was here on JDEList that I had seen postings some time ago about reusing deleted records having problems, but if it's not then I would suggest a combination of reorganising and reusing is the thing to do.

Obviously if you are going to clear out 50% of the records in a file on an infrequent basis you would want to reorganize that file to save space, but for files where you are frequently removing records, like the MRP files for example, reusing space is a great idea.

It does seem a bit crazy in this day and age that an operating system wouldn't allow you to reuse deleted records....

 

[slandess]

REUSEDLT(*YES) can drive programmers crazy - this note from Al Barsa on Midrange.com -
http://archive.midrange.com/midrange-l/200502/msg00485.html

Also see this JDELIST posting that I made in 2002...
http://www.jdelist.com/ubb/showthreaded.php?Cat=3D0&Board=3DW&Number=3D43 226&Forum=3D,,,,,All_Forums,,,,,&Words=3D&Searchpage=3D4&Limit=3D25&Main=3D 43182&Search=3D

 

[tpayne]

Thanks for the update Steve.

I think it was your post previously that I was thinking of with regard to potential problems using REUSEDLT.

I don't think many programmers these days write code that reads a file sequentially, unless it's a purely sequential file, so the problem with Relative Record Number (RRN) would not be an issue. Of course if debugging to have to realise that the record you just added to a file might not appear at the end of the file... I have rarely in the last 10 years had to deal with that.

Going back to the S/34 days (before logicals came on the scene) it was necessary to sometimes use a Direct File with pointers to the previous and next entries in each index as fields in the file, and to have indexed files to point to the first and last record for "say" a customer. The whole update, insert, delete process was horrendous to code, but I am pretty sure nobody does that any more... Of course some companies could still be using really old systems... but I am not aware of any files in JDE that would be a problem there, unless custom programs read JDE files sequentially.

I do know that running a RGZPFM can be an issue, especially with F0911 which can become very large and which has dozens of logicals over it. Our F0911 is currently 85 million records - that could take quite a few hours to reorganize...

 

[jean_driscoll]

I came across an issue with REUSEDLT(*YES) on our F0911. No other company probably would have the issue. But the posting program only reads the F0911 records by partial key, not all of the way to the JE line number. We use a wierd intercompany option that uses the 1st record of the batch to determine the intercompany company.

Our payroll updates take a long time. If an accountant happens to delete a J/E during the time the payroll runs, one of the payroll F0911 records will be put above the JE line 1 for the payroll batch and so the batch ends up posting to the wrong company for intercompany.

this is a fluke that was really difficult to debug, and JDE has no plans of changing their posting program, so we had to change our F0911 not not use the option to reuse deleted records.