TAA Tools
SORTDB       SORT DATA BASE (FRONT END TO FMTDTA)         TAADBFH

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
					

Added to TAA Productivity tools April 1, 1995


Home Page Up to Top