Using FMTDTA

Instead of logical files or OPNQRYF, you can use the Format Data (FMTDTA) command to sort a database file. FMTDTA used to offer better performance than OPNQRYF when the file to be sorted is very large (more than 100,000 records), but the advantage has been virtually eliminated with the addition of the *OPTIMIZE value to OPNQRYF's ALWCPYDTA parameter.

FMTDTA uses sort specifications to define the order of sorting and what records are included or omitted in the process. The sort specifications are identical to the #GSORT specifications of the S/36.

Drawbacks of FMTDTA

FMTDTA is an old-fashioned technique and rarely used anymore. But you might still run across it in certain situations, which is why it will be covered in this chapter.

• FMTDTA doesn't use the external file definition of the file being sorted. You have to code beginning and ending positions for the fields you need to reference. For example, if you want to sort a customer master file by customer number (which happens to occupy positions 1 to 7), you instruct FMTDTA to sort by whatever is stored in bytes 1 to 7. Neither you nor FMTDTA knows what is there.

• Because the i5 system encourages file design using external definitions (DDS or SQL), you may not even know the absolute positions of the fields you need to reference. You can obtain this information using the Display File Field Description (DSPFFD) command.

• FMTDTA produces an output file (containing the sorted records), but this file must exist before you run FMTDTA. It is easy to forget that requirement. You must code DDS for the output file and create it, or make a duplicate of an existing file that may serve as a model.

• You cannot give the sort specifications "on the fly." FMTDTA requires that you enter the sort specifications in a source member, which is referenced in parameters SRCFILE and SRCMBR of the FMTDTA command. These sort specifications don't allow any form of parameter passing or substitution or variations of any form.

An Example of Use

Because FMTDTA presents so many obstacles, programmers usually shy away from it. Either OPNQRYF or logical files provide much easier coding and more flexibility. One example of its use will explain the workings of FMTDTA.

Suppose that you have a huge Inventory Transaction History File that contains approximately 500,000 records. You need to prepare a summary report, sorted by item number, of all sales transactions that have taken place between January 1, 1999 and December 31, 1999. Your Inventory Transaction History File (ITH) has the DDS (simplified for this example) shown in Figure 28.2.

Figure 28.2: Example of DDS for a physical file.

From the DDS in Figure 28.2, you can conclude that the item number begins on byte 1 and ends on byte 15 of the record. The transaction type ("SAL" for sales) begins on 18 and ends on 20. And the transaction date begins on 25 and ends on 28. Therefore, the sort specifications you need are shown in Figure 28.3.

Figure 28.3: Example of sort specifications.

The example shown in Figure 28.3 uses the tag-along sort, which is not particularly efficient, but it is somewhat easier to follow.

The H-spec says that the sort control field has a total length of 15 bytes, which agrees with the length of the item number field, the field you will use to sort the records. The A means ascending sequence. The X prevents FMTDTA from writing the sort field to the output record automatically.

The I-specs describe what records to include. Note that no mention is made of the names of the fields. The comments beginning at column 40 are your way to remember what you are doing. The first I-spec says that a record should be included if "SAL" is present in bytes 18 through 20 of the record. The transaction type happens to be at this location, and you are interested in sales transactions only.

The second and third I-specs have an A (and) that ties them to the first one with an AND operator. The packed decimal value in bytes 25 to 28 (transaction date) must be greater than or equal to 990101 and less than or equal to 991231.

Finally, the first F-spec indicates in what order to sort the records. The N means normal sort (which agrees with the A in the H-spec, therefore sorting in ascending sequence). The numbers next to that reference the sort field (item number). The next F-spec directs the output file to receive the data (that is the D in column 7) contained between bytes 1 and 45, which happens to be the entire input record.

Suppose that now you enter these sort specifications into member XYZ of source file MYLIB/QFMTSRC. By the way, IBM recommends using source file QFMTSRC, but you don't have to. The fragment of a CL program like that shown in Figure 28.4 would carry out the sort.

Figure 28.4: Fragments of a CL program performing a sort.

The Create Duplicate Object (CRTDUPOBJ) command creates an empty file [DATA(*NO)] that has the same record layout as the original file. The empty file is also named ITH, but it goes into QTEMP. The FMTDTA command sorts MYLIB/ITH (the original file) and writes to QTEMP/ITH, using the sort specifications you have just written.

The OVRDBF command overrides file ITH to make sure that the program that follows processes the file in QTEMP and not the original (unsorted) file. The SEQONLY(*YES 1000) parameter ensures that the program will read the records (in blocks of 1,000 records for efficiency).