Optimizing File Selection

When associating your program with a file, the SELECT statement contains certain clauses, depending on the file organization, that can improve the performance when accessing the specified file.

If you are specifying indexed files, only specify alternate keys that you truly need, as each insert and delete operation on the file requires that the key is updated; specifying keys that go unused can cause unnecessary processing. If you do require complex key structures, with multiple alternate keys, processing the file under Fileshare can reduce some of the I/O processing usually required under normal file handling

Use the WITH DUPLICATES phrase in the ALTERNATE KEY clause for keys that have duplicate values. This technique saves disk space.

Use the SUPPRESS WHEN ALL clause for keys that should not be indexed, for example, if they contain all spaces. This can also save disk space.

Sharing and locking techniques are available through both the SELECT statement and the configuration file. Being able to access a file exclusively is the most efficient method performance-wise.

The SELECT statement can also specify the physical file implicitly or explicitly. If the file is specified implicitly, one or more search methods must be performed to locate the file; therefore, naming the file explicitly, if possible, is the most efficient method. If you cannot name the explicitly, there are other techniques, sometimes depending on how the file is opened, that can speed up the process (for example, the SEARCHONCREATE option). See Filename Mapping for more information.

Disk space can also be conserved in the File Control Description block by setting the record length to variable (as opposed to fixed). See Fixed-length and Variable-length Records for more information.