It’s rare that you need to sort a database file (or table), but I have encountered a circumstance in which a sorted file had to be delivered. Being naturally lazy, I started looking around for the least-work method of achieving this and ended up looking at the RGZPFM command. This is a command I have used quite often to compress a physical file (it removes the deleted records), but this time around it was the reorganise part of Reorganise Physical File Member that I was interested in.
From the help text:
If a keyed file is identified in the Key file (KEYFILE) parameter, the system reorganizes the member by changing the physical sequence of the records in storage to either match the keyed sequence of the physical file member’s access path, or to match the access path of a logical file member that is defined over the physical file.
The important thing here is that any keyed file can be specified. So if I have an appropriately keyed logical file, I can very easily sort the physical.
Some names have been changed, but if I have a physical file called ITEMLIST and I want it sorted by field BARCODE, I need to first create an index:
create index ITEMLIST01
on ITEMLIST (BARCODE);
label on index ITEMLIST01
is 'Logical on ITEMLIST (Keyed: BARCODE)';
Then I can easily reorder the physical file ITEMLIST into BARCODE sequence with the command:
RGZPFM FILE(ITEMLIST) KEYFILE(ITEMLIST01 ITEMLIST01)
The only thing to take not of is that RGZPFM will issue an error if the file has no records. The approach I took wast to use RTVMBRD to retrieve the NBRCURRCD for the member, an alternative approach would be to use a MONMSG immediately after the RGZPFM.