Note: The SORTDB command is a front end to the system FMTDTA command.
OPNQRYF now allows a physical record sort using the
ALWCPYDTA(*OPTIMIZE) option. For most application use, you will be
better off from a functional and performance view if you use OPNQRYF
instead of FMTDTA.
The FMTDTA command is supported by the Reformat Utility and provides
a basic sort function. This can be very beneficial in terms of
performance for batch applications. Batch performance can be greatly
enhanced by processing the data in arrival sequence. The FMTDTA
command requires a source member which contains sort specifications.
SORTDB requires an externally described file.
FMTDTA has some disadvantages:
** It does not support externally described data. This means
that specific begin and end positions must be used and the
source is subject to changes if the file definition changes.
** SEU does not make it convenient to key in the source member
specifications (No format is supported).
** There is no easy method of dynamically changing the source
statements to perform minor modifications. For example, if
the selection criteria requires only the records for today's
date, the source must be modified for each day.
The SORTDB command allows a command interface for the complete FMTDTA
specifications for simple sort functions. This eliminates the major
disadvantages of FMTDTA and makes it simple to use for many batch
applications.
An option is available to allow a 'number of records' to be input to
the sort. This is helpful when testing a batch report so that the
entire file is not input.
An option is also available to bypass the execution of the FMTDTA
command and only produce the sort specifications. This approach can
be used when the SORTDB command does not provide an interface for the
desired FMTDTA function. The option allows the basic statements to
be generated so you can modify them using SEU. In conjunction with
this option is a parameter which specifies where the sort specs will
be generated. Normally, they are created in QTEMP/FMTTMP
Member-FMTSPC. Either or both of these values may be overridden.
The command operates on either a physical or logical file. If a
logical file is used, it can only have a single format. The command
defaults to access the first member of a file. A specific member can
also be named.
The SORTDB command assumes that a FILE sort will occur meaning that
the records themselves will be sorted (not just the control fields),
all fields will be included in the output and the control fields will
not appear separately at the beginning of the record. The effect is
as if the existing file was read, records meeting the selection
criteria selected and placed in a different physical file (using the
same format) and that file is rearranged in the designated sequence.
Command parameters *CMD
------------------
INFILE The input file name to be sorted. The library
defaults to *LIBL.
SORTFLD A list of up to 10 field names in the sequence of
major to minor to describe the sort. The field
names must exist in the INFILE named.
SEQ A list of A or D entries to denote the sort sequence
(ascending or descending) of the fields in SORTFLD.
If ascending sequence is needed for all fields, this
entry can be left blank. If one descending entry is
needed, then all entries must be specified (e.g. A
A D A). If an entry is made, then the number in the
list must match the number of fields in SORTFLD.
SEL1 The 1st selection statement. It is a list of:
Field name The field to be selected
Relational operator *EQ *NE *LT *LE *GT *GE
Constants Up to 20 characters
This is specified as an 'include' only. There is no
omit capability other than through 'include'. Note
that a field name cannot be specified as a constant.
If a constant is entered, to be compared against a
numeric value, the entry must follow the rules for
the Reformat Utility such as the following for a 5
digit field:
Compare for Entry
----------- -----
+15 00015
+3 bbbb3
-12 0001K
ANDOR1 An entry of *AND or OR. It generates Column 7 of
the Record Specifications. The default is *AND.
This allows you to determine the relationship
between the 1st and 2nd selection statements.
SEL2 2nd selection statement.
ANDOR2 Relationship between the 2nd and 3rd selections.
SEL3 3rd selection statement.
ANDOR3 Relationship between the 3rd and 4th selections.
SEL4 4th selection statement.
EXECSORT A *YES/*NO option which allows you to control
whether the sort is executed or whether only the
sort specifications are output. The default is
*YES. If *NO is specified, the sort specifications
appear in the file QTEMP/FMTTMP member-FMTSPC unless
OUTSRCF or OUTSRCMBR are specified. This option is
of value in a testing environment so you can see
what will be generated. It is also of value if a
function is needed which is supported by FMTDTA
specifications, but not by the SORTDB command.
PRTSPECS Option to control whether the FMTDTA command should
print the sort specifications. The default is
*ERROR meaning the specifications should be printed
only if the FMTDTA command fails. This occurs by
printing the file (the spool file name will be the
name of the OUTSRCMBR) and then deleting the file if
the command completes successfully. An entry of
*YES or *NO may also be made.
OUTFILE The qualified name of the output file to be used.
The default is QTEMP/SORTOUT. This file will be
created using the same format as the INFILE. A
specific file can be named. The FMTDTA command
clears the file before adding records. If a file is
created, the SIZE parameter is set to *NOMAX.
NBRRCDS This allows you to extract a number of records from
a file to be tried with the sort. It is designed to
be used when testing with a batch function so all of
the records in a file will not be sorted until the
entire batch procedure is correct. The default is
*ALL meaning all records should be input to the
sort. If a number is specified, the records are
copied from the INFILE in arrival sequence.
INMBR Member of the INFILE to be used. Default is *FIRST.
OUTMBR Member of the OUTFILE to be used. Default is
*FIRST.
OUTSRCF The name of the source file which will be used to
hold the FMTDTA specifications. This parameter will
be used regardless of the status of the EXECSORT
parameter. The default is QTEMP/FMTTMP. If the
default is not used, the source file must exist.
OUTSRCMBR The name of the source member which will be used to
hold the FMTDTA specifications. This parameter will
be used regardless of the status of the EXECSORT
parameter. The default is FMTSPC. The member will
be added if it does not exist and cleared if it
does.
Examples
--------
1. Assume file FILEA should be sorted in ascending sequence of
FLD1 followed by FLD5 and processed by a batch program (PGMA).
The batch program would be created using FILEA as the input
file. The SORTDB command should use the default output file
of QTEMP/SORTOUT. The SORTDB command and an OVRDBF command
would be used as follows:
SORTDB INFILE(FILEA) SORTFLD(FLD1 FLD5)
OVRDBF FILEA TOFILE(QTEMP/SORTOUT) SEQONLY(*YES 200)
CALL PGMA
To assist in testing this procedure, the NBRRCDS parameter on
the SORTDB command could be used to limit the number of
records which will be sorted. After the program is tested,
the parameter could be eliminated.
2. Assume FILEB must be sorted on FLD1 in ascending sequence
followed by FLD6 in descending sequence and FLD4 in ascending
sequence. Only the records with FLD4 = ABC and FLD5 = 99
should be selected. The output file to be used is FILEB2.
The SORTDB command would appear as:
SORTDB INFILE(FILEB) SORTFLD(FLD1 FLD6 FLD4) +
SEQ(A D A) SEL1(FLD4 *EQ ABC) +
SEL2(FLD5 *EQ 99) OUTFILE(xxx/FILEB2)
Note that since there is an 'and' relationship between the
selection criteria, the default for ANDOR1 is used.
3. Assume that FILEC must be sorted in ascending sequence with
FLD3 followed by FLD1 and FLD2. The selection criteria is:
-- Any record with FLD4 = 123 and FLD5 = ABC
-- Any record with FLD4 = 456
The default output file may be used.
SORTDB INFILE(FILEC) SORTFLD(FLD3 FLD1 FLD2) +
SEL1(FLD4 *EQ 123) SEL2(FLD5 *EQ ABC) +
ANDOR2(*OR) SEL3(FLD4 *EQ 456)
4. Assume that FILED must be sorted in ascending sequence on FLD1
and FLD9 with selection criteria of FLD2 = AA, BB, CC, DD, EE
or FF.
Since the SORTDB command provides only for 4 selection
criteria, the EXECSORT(*NO), OUTSRCF and OUTSRCMBR options
will be used as follows:
SORTDB INFILE(FILED) SORTFLD(FLD1 FLD9) +
SEL1(FLD2 *EQ AA) ANDOR1(*OR) +
SEL2(FLD2 *EQ BB) ANDOR2(*OR) +
SEL3(FLD2 *EQ CC) ANDOR3(*OR) +
SEL4(FLD2 *EQ DD) EXECSORT(*NO) +
OUTSRCF(xxx/SORTSPCS) OUTSRCMBR(SORT29)
The output of the command is the file SORTSPCS in library xxx
with member SORT29 holding the specifications. Using SEU, the
generated statements may be modified and additional selection
statements added. Thus the SORTDB command has saved much of
the work in creating the specifications and the normal FMTDTA
command would be used to execute the sort.
5. Assume that FILEE must be sorted on FLDA with a selection of
the current date. The command must be executed through a CALL
to QCMDEXC.
DCL &DATE *CHAR LEN(6)
DCL &CMD *CHAR LEN(300)
RTVSYSVAL QDATE &DATE
CHGVAR &CMD ('SORTDB INFILE(FILEE) SORTFLD(FLDA) +
SEL1(DATE *EQ ' *CAT &DATE *CAT ')')
CALL QCMDEXC PARM(&CMD 300)
Prerequisites
-------------
The following TAA Tools must be on your system:
EDTVAR Edit variable
SNDCOMPMSG Send completion message
SNDESCMSG Send escape message
SNDSTSMSG Send status message
Implementation
--------------
None, the tool is ready to use.
Objects used by the tool
------------------------
Object Type Attribute Src member Src file
------ ----- --------- ---------- -----------
SORTDB *CMD TAADBFH QATTCMD
TAADBFHC *PGM CLP TAADBFHC QATTCL
TAADBFHR *PGM RPG TAADBFHR QATTRPG
|
