FMTDTA Command (a.k.a. Sort)

FMTDTA Command (a.k.a. Sort)
Many application people I meet aren’t familiar with the Format Data command, but they’ve usually heard of the function it performs. It’s called Sort. In some application scenarios, you can achieve dramatic processing-time improvements by changing the physical sequence of the data to put it in the order of the most commonly used key(s). You can do this either by sorting the file or reorganizing it into the sequence in which it is most often processed and then processing the file sequentially. Most application writers don’t do this and consequently there is a lot of room for improvement, especially when you have large files in which every record has to be processed. sorting data ahead of time and processing it sequentially
Some users say that they’re already using sequential processing, but upon investigating the files used in a sample job, Further investigation shows that only their logical files are being processed sequentially. The logical files read a record with key value n, then the record with key value (n + 1), and so on. Meanwhile, the data in the physical file records is scattered all over the ASP. Performance analysis of these jobs usually reveals a ratio of one physical read for every logical read (i.e, practically no blocking).
To see if this situation applies to your system, look at the application file processing. If the physical file can be sorted into the processing key sequence and read sequentially (using blocking), and you can use a separate file description for output, you may be able to further improve performance.
Applications generated by most of the 4GLs don’t process physical files; for the most part they work exclusively with logical files and don’t bother to sort the real data into the best sequence for processing performance. If the code works, that’s the extent of it. Truly optimizing database performance isn’t a consideration with 4GL-built code. By applying some of the techniques in this article, however, you may be able to improve their batch throughput. Beware, though, that if you change your 4GL generated code, there may be no guarantee of processing integrity.
That’s a lot of information for you to absorb and understand! But if you’re goal is to drive your processor to the maximum with your nightly, monthly, or quarterly long-running batch work, applying these methods to your system will help.