TAA Tools

 SORTDBF         SORT DATA BASE FILE                    TAAQRYB

 The  Sort Data  Base  File  command command  provides  a front  end  to
 OPNQRYF  and a sort.   The  sort is  done using an  API instead  of the
 OPNQRYF sort.  Three significant advantages exist:

   **   Selection  can specified  using a  'list parameter'  rather that
        the QRYSLT expression

   **   The API  sort  is faster  than  the OPNQRYF  sort when  a  large
        number of records must be sequenced and large memory exists

   **   Since an actual  file is created, any utility  like function can
        be used on the sorted data.

 The  entire process  of selection  and sequencing is  made considerably
 easier than the direct use  of OPNQRYF.  Fewer associated commands  are
 required.  Simpler options and better defaults exist with SORTDBF.

 A typical series of commands would be:

              SORTDBF       FROMFILE(LIB1/FILEA) TOFILE(LIB2/FILEA) +
                              SELFLD((FLDA *EQ ABC)) +
                              KEYFLD(FLDB)
              OVRDBF      FILE(FILEA) TOFILE(LIB2/FILEA)
              CALL        PGM(zzz)
              DLTOVR      FILE(FILEA)

 The 'From file' FILEA  would be read by OPNQRYF  and only records where
 FLDA  is equal  to 'ABC'  would be  selected.   FLDB  would be  used to
 sequence the records in the To file (LIB2/FILEA).

 Note that the selection  is done using a  'list parameter' rather  than
 the OPNQRYF QRYSLT parameter.   This makes it significantly  easier for
 the typical  programmer to specify what he  wants and particularly when
 the values are variables.

 For example, in a CL program you could specify:

              DCL         &VARA *CHAR LEN(3)
               .
              SORTDBF     FROMFILE(LIB1/FILEA) TOFILE(LIB2/FILEA) +
                            SELFLD((FLDA *EQ &VARA)) +
                            KEYFLD(FLDB)
              OVRDBF      FILE(FILEA) TOFILE(LIB2/FILEA)
              CALL        PGM(zzz)
              DLTOVR      FILE(FILEA)

 No  quotes,  double  quotes, or  *CAT  operators are  needed.   SORTDBF
 generates the  proper  expression and  passes it  to  OPNQRYF which  is
 executed internally.

 Instead of using LIB2, the QTEMP library could be used.

 Note  that you  do not  need to  specify the  SHARE(*YES)  parameter on
 OVRDBF as  is normally done with OPNQRYF.  You  do not need to use CLOF
 to close  the  OPNQRYF ODP  (Open Data  Path).   Both  these  functions
 occur automatically within SORTDBF.

 Because a  'To file' is named,  the selected/sorted data is  written to
 a physical  file instead of the  OPNQRYF ODP.  This  allows any utility
 like function (such as  CPYF, DFU, Query, or  TAA Tools such as  PRTDB)
 to be used.   OPNQRYF requires the  use of a shared ODP  which prevents
 the use of many utilities.

 For example,  to use SORTDBF  to perform a simple  query operation, you
 could specify:

              SORTDBF     FROMFILE(LIB1/FILEA) TOFILE(LIB2/FILEA) +
                            SELFLD((FLDA *EQ ABC)) +
                            KEYFLD(FLDB)
              PRTDB       FILE(LIB2/FILEA)

 PRTDB  will prompt you for  the fields that exist  in the file, let you
 specify  the left  to  right  sequence  of fields  to  be  printed  and
 automatically adds up any decimal fields.

 See the section on 'Samples' for additional examples.

 An  option exists  to  use the  OPNQRYF  sequencing function  if a  'To
 file'  is not desirable.   See the later section  on 'Using the OPNQRYF
 ODP'.

 Processing a sorted 'To file' (TOFILE is named)
 -----------------------------------------------

 When you name a 'To  file', the file does  not have to exist.   SORTDBF
 will create the 'To file' automatically.

 If  you create  the 'To  file' ahead  of time,  it must  have the  same
 definition  as the 'From file'.   Creating the 'To  file' ahead of time
 will allow you copy test  data into the file  and test a program  using
 a smaller version.

 The simplest solution  for creating the 'To  file' ahead of time  is to
 use DUPFILFMT  (this command is  used internally within  SORTDBF if the
 outfile must be created).

              DUPFILFMT FILE(xxx) TOLIB(yyy)

 This  will create a file  with the same format  and an arrival sequence
 access path (no keys).

 Basic rules for SORTDBF
 -----------------------

   **   Either a physical  or logical file  (single format only) may  be
        specified.   This includes pre-defined Join files.   The OPNQRYF
        'dynamic join' function is not supported.

   **   The  'To file'  cannot  be the  same file/library/member  as the
        'From file'.

   **   If the  'To  file/member' has  existing  records,  REPLACE(*YES)
        must be specified.

   **   If the 'To file' does not exist, it will be created.

   **   If the  'To file' does  exist, it must  have the same  format as
        the 'From file'.

   **   You  must use either  'select' fields  or 'key' fields  or both.
        An error will occur if  neither 'select' or 'key' fields  exist.

   **   By default,  any  character fields  in the  data  base used  for
        selection  are translated  using a  translate table  (default is
        QSYSTRNTBL).   This  allows you to  make an upper  case entry on
        the command and not  have to consider the  type of case used  in
        the value in the data base.  See the section on Translation.

   **   The *WC  (Wildcard) Operator makes it simple  to perform generic
        selections  by defaulting  to provide a  wildcard at  the end of
        the value  entered  if  none  exists.   See  the  discussion  of
        'Wildcard processing'.

   **   The Sort  capability allows  positions within a  character field
        to  be  sorted   (such  as  positions  4-5)  as  unique  fields.
        OPNQRYF does not allow this  function directly, but it could  be
        achieved  with MAP  fields  and  unused  fields in  the  format.
        Decimal fields can only be sequenced on their full value.

 Major functions in OPNQRYF that are not supported
 -------------------------------------------------

   **   Expression capability  for selection.  While  this provides some
        powerful  capability,  typical  functions  are  more  easily met
        with the SORTDBF SELFLD parameter.

   **   Dynamic join capability.   While OPNQRYF does support  a dynamic
        join  capability,  it  is  of limited  practical  value  because
        either  a specific  format must  be created  (which means  it is
        not truly dynamic) or  a complex set of  MAP statements must  be
        used.

        Most  users  of OPNQRYF  use  a  pre-defined  Join file.    This
        allows  greater  control  and  simplifies  the  use of  OPNQRYF.
        This approach is supported by SORTDBF.

   **   Grouping.

   **   Map fields.

   **   Unique key support  (allows the first  record of a  group to  be
        processed).

   **   Not every field type  is supported for sorting.   Only character
        fields,  the decimal  types B, F,  S, and  P, and  the date/time
        types L, T, and Z are supported.

 Translation
 -----------

 Translation  is  the  default  for  character  fields  to  be  selected
 (Decimal fields  are  never translated).    Translation occurs  on  the
 data values in the data base (not on the SORTDBF compare value).

 Translation of the key fields is done using the SORTTBL parameter.

 See  the  examples in  the  discussion of  the  Translate  part of  the
 SELFLD parameter.

 The  default  for the  translate table  to be  used  is defined  in the
 TAATRNTBL data area  in TAASECURE.  The  shipped default is  QSYSTRNTBL
 (the  system  supplied  translate  table  for  translating  lower  case
 English letters to upper case).

 The   data   area   may  be   changed   with   the  command   EDTCONARR
 DTAARA(TAASECURE/TAATRNTBL).     For   more   information   about   the
 translate table, see the TAA Tool RTVTRNTBL.

 And/Or relationships
 --------------------

 One or  more fields may  be defined  to be a  group to  select on.   If
 FLDA  must equal  ABC and  FLDB  must be  greater than  500,  you would
 specify:

          SORTDBF       ... SELFLD((FLDA *EQ ABC)(FLDB *GT 500)

 The   last  part   of  the   SELFLD  'list   parameter'  describes  the
 relationship of the  current select field  with the next select  field.
 The  default is *AND  to form  an 'and'  relationship meaning  that the
 next  select field is  part of the  same group.  In  this example, both
 comparisons must be satisfied to select a record.

 The default  And/Or  value  for  the last  select  field  specified  is
 ignored (FLDB in the previous example).

 An 'or' relationship may also be specified such as:

         SORTDBF       ... SELFLD((FLDA *EQ ABC *OR)(FLDB *GT 500))

 Each  time you  enter  *OR, you  are describing  that  the next  select
 field starts a new group.

 You can combine And/Or relationships.  Assume you wanted:

         FLDA = ABC and FLDB *GT 500

                  or

         FLDA = DEF and FLDC *GT 1000

 You  would specify an  *OR at  the end of  the definition of  FLDB such
 as:

         SORTDBF       ... SELFLD((FLDA *EQ ABC )(FLDB *GT 500 *OR)
                              (FLDB *EQ DEF )(FLDC *GT 1000))

 If you want:

                FLDA  = ABC  and FLDB *GT 500

                         or

                FLDA  = ABC  and FLDC *GT 1000

 you must specify 2 groups:

         SORTDBF       ... SELFLD((FLDA *EQ ABC )(FLDB *GT 500 *OR)
                              (FLDA *EQ ABC )(FLDC *GT 1000))

 Wildcard processing (*WC Operator)
 ----------------------------------

 Both fixed and floating wildcards are supported.

   **   Floating form - Default is '*'

        A floating wildcard  allows a  series of values  to be  ignored.
        When the  floating form is  used at  the end of  a value, it  is
        like  'generic' support.   SORTDBF adds  a floating  wildcard at
        the  end of the  value (if you  do not specify  it) whenever the
        *WC operator is  used and there  is room.   For example, if  the
        field is  3 bytes long  and you request  a value of AB,  you are
        requesting  AB  followed by  any  value (as  if you  had  made a
        generic request of AB*).

        If you  only want  the ABC  values of  a 3  position field,  you
        must use the *EQ operator.

        If you  use the *WC  operator with a compare  value of ABC  on a
        field  with a length of  3, there is  no room at the  end of the
        value to add  the floating  wildcard.   If the  *WC operator  is
        used, you  must have  a wildcard  in the  compare value  (either
        fixed  or floating) or  have room  at the  end of the  value for
        SORTDBF to add the floating wildcard.

        Assume FLD4 is four bytes long.

            FLD4 *WC A                Selects  'A   ', 'AB  ', 'ABCD'
                                      Bypasses 'BA  ', 'CABC'

            FLD4 *WC A*               Selects  'A   ', 'AB  ', 'ABCD'
                                      Bypasses 'BA  ', 'CABC'

        Note  that the prior  two examples give  the same result because
        SORTDBF  adds  the  wildcard   character  at  the  end   if  not
        specified.

        When  a floating wildcard  is used  in the  middle of  a compare
        value,  it means that  any number of characters  may be ignored.

            FLD4 *WC A*A              Selects  'ABA ', 'ABCA', 'AB A'
                                      Bypasses 'ABBB', 'ABCC'

   **   Fixed form - Default is '_'.

        The fixed  form  of wildcard  support  allows  any value  for  a
        single  character to  be ignored.    SORTDBF will  also add  the
        floating  wildcard at the end  of the value (if  there is room).

        Assume FLD2 is two bytes long.

            FLD2 *WC A_               Selects  'AA', 'AB'
                                      Bypasses 'BA', 'CA'

            FLD2 *WC _A               Selects  'AA', 'BA'
                                      Bypasses 'AB', 'AC'

        Note that  specifying 'A_',  or  'A*', or  'A' all  produce  the
        same result on a field of length 2.

        Assume FLD3 is 3 bytes long

            FLD3 *WC A_X              Selects  'AAX', 'ABX'
                                      Bypasses 'ABC', 'ABD'

            FLD3 *WC _A               Selects  'AA ', 'AAB'
                                      Bypasses 'ABC', 'ABD'

            FLD3 *WC A__              Selects  'AA ', 'AB ', 'AAA' 'ABC'
                                      Bypasses 'BAA', 'CAA'

 You can combine both forms of wildcard such as 'A_*C'.

 You can  change the defaults  of the wildcard  characters if your  data
 contains  the  default characters.    See the  FIXWLDCRD  and FLTWLDCRD
 parameters.  For example, you could specify:

            FLD4 *EQ A%A&      FIXWLDCRD(%) FLTWLDCRD(&)

 Using an OPNQRYF ODP (Open Data Path)
 -------------------------------------

 The  normal use  of SORTDBF  is to  use OPNQRYF  for selection  and the
 Sort API  to  sequence the  records.   You  can  use OPNQRYF  for  both
 selection and  sequencing by specifying  TOFILE(*NONE).  The  result is
 the normal ODP (Open Data Path) created by OPNQRYF.

 You  would write  your  program using  the 'To  file' as  an Externally
 Described input file and specify arrival sequence processing.

 Your processing statements would be:

              OVRDBF      FILE(FILEA) SHARE(*YES)
              SORTDBF     FROMFILE(FILEA) TOFILE(*NONE) ...
              CALL        PGM(xxx)
              CLOF        OPNID(FILEA)
              DLTOVR      FILE(FILEA)

 Performance
 -----------

 Many  applications  need  a  traditional  batch  approach   of  'select
 sequence, and  process.' There are several  solutions that can  be used
 on  the system  for  this type  of  application.   One of  the  best is
 OPNQRYF.

 The major performance advantage of OPNQRYF  is that it uses the  system
 'Optimizer'.   The  Optimizer  can utilize  existing  access paths  for
 selection to  greatly reduce the  number of records that  must be read.
 The  'Optimizer' is  also used  by SQL  and several other  functions on
 the system.

 OPNQRYF can  provide either a  keyed access path  to sequence the  data
 or a  physical sort of  the data.   A physical sort builds  a temporary
 file  (the  sort  algorithm  use  differs  from  the  sort provided  by
 SORTDBF  in  that  it  does  not  take  advantage  of   large  memory).
 Performing a physical  sort function allows a program to  read the file
 in  arrival   sequence.    Arrival  sequence  processing  significantly
 reduces the amount of disk arm  movement required if a large number  of
 records must be read.

 Measuring  performance  of  a  keyed  access  path  approach  versus  a
 physical  sort is  more complex that  it appears.   Building  an access
 path  takes less time than a physical  sort, but you must also consider
 the time it takes to process the file sequentially.

 If only  a single  job is  running, you  could measure  the results  of
 both  approaches.   You  would normally  find  that a  small number  of
 records  is  better  handled  by  creating  an  access  path,  but  the
 performance difference  is not great.   If  a large  number of  records
 must  be processed,  you  should  see a  significant  gain  by using  a
 physical sort.

 While  measuring a single job  may provide some answers,  the impact on
 a system of  disk arm  movement when several  jobs are  running at  the
 same time is very  difficult to measure.  Because disk  arm movement is
 not  prioritized, jobs that  require a great  deal of arm  movement can
 have a definite negative impact on system performance.

 OPNQRYF  allows a  user to  request that a  sort be  used by specifying
 ALWCPYDTA(*OPTIMIZE).    However,  the  OPNQRYF  sort   does  not  take
 advantage  of large  main memory  and may  not be  as effective  as the
 normal  internal sort  such  as used  by the  FMTDTA command  and other
 functions.   An  API  (QLGSORT)  exists to  interface  to  this  normal
 internal sort.

 It is  important to  note that  it is not  the number  of records  in a
 file  that is  critical to performance,  but how  many records  will be
 sequenced.   Good selection  criteria  can often  significantly  reduce
 the number of records that must be sequenced and processed.

 SORTDBF allows the best of both worlds.

   **   For normal  use, specify  a 'To  file' for sorted  data.   While
        this  is  less  efficient   in  sequencing  a  small  number  of
        records,  the difference  is not that  significant.   If a large
        number of records  are to be sequenced,  you get the benefit  of
        the API sort.

        Another advantage  of using a 'To  file' is that  an actual file
        is  created which can  be processed by other  system commands as
        well as TAA  Tools and utilities.   For example,  you could  use
        the PRTDB  TAA Tool  (or PRTDB2)  which is  a generalized  print
        function and make a simple query such as:

              SORTDBF       FROMFILE(xxx) TOFILE(yyy)
              PRTDB2        FILE(yyy)

        If an  OPNQRYF ODP is created, the  only system command that can
        be used is CPYFRMQRYF.   None of the  system utilities (such  as
        Query or  DFU) or  TAA Tool functions  operate on  an ODP.   You
        must write a specific program to read from an ODP.

   **   In  some  cases  you  may  not  want  a sort  because  of  space
        considerations  or you  may want  the best  possible performance
        when  a  small  number  of  records  must  be  sequenced  or  no
        sequencing  is needed.   For example,  you may  want to  use the
        selection  capability of SORTDBF  without performing sequencing.
        Use the special  value TOFILE(*NONE) and  follow the example  in
        the section on 'Using an OPNQRYF ODP'.

 Internal processing of SORTDBF
 ------------------------------

   **   If a  'To file' is  named, DUPFILFMT is  used to create  the 'To
        file'  if  it  does  not  exist.    OPNQRYF  is  run  using  any
        selection and key fields  to create an ODP.   If both the  'From
        file'  and  'To file'  have  the  same  name, the  OVR  used  by
        OPNQRYF  prevents  the use  of  CPYFRMQRYF.   In  addition, some
        files  have internal  field names  that cause  a different level
        ID if  CPYFRMQRYF is  used to  create the  data base  file.   To
        avoid these  problems, the TAATMPSORT  file is created  in QTEMP
        using  DUPFILFMT  of the  From file  and  CPYFRMQRYF is  used to
        copy to TAATMPSORT.

        If  no  key  fields  are   specified,  CPYF  is  used  to   copy
        TAATMPSORT to the 'To file'.  TAATMPSORT is then deleted.

        If key  fields exist, TAATMPSORT  is input  to the API  sort and
        the 'To file' is output.  TAATMPSORT is then deleted.

   **   If  TOFILE(*NONE)   is  specified,  OPNQRYF  is  used  for  both
        selection and sequencing.  ALWCPYDTA(*YES)  is used (no sort  is
        requested).

 SORTDBF escape messages you can monitor for
 -------------------------------------------

       TAA9891    From file has no records.
       TAA9892    From file has records and REPLACE(*NO) specified.
       TAA9893    Selection or key field does not exist.
       TAA9894    No selection or key fields defined.
       TAA9895    No records meet the selection criteria.
       TAA9896    Not authorized to clear the member.
       TAA9897    Not authorized to add a member.

 CPF9898 is  used for many  errors such as  the compare value  specified
 for selection is longer than the the field length.

 Escape messages from based on functions will be re-sent.

 Command parameters                                    *CMD
 ------------------

    FROMFILE      The qualified  name of the file to  be read for input.
                  The  library  value defaults  to  *LIBL.   *CURLIB may
                  also be used.

                  A physical or  logical file (including a  join logical
                  file) may  be named.   Logical files  may have  only a
                  single format.

    TOFILE        The  qualified name  of the  file to  be output.   The
                  library value  defaults to  *LIBL.   *CURLIB may  also
                  be used.

                  The  qualified  'To file/library/member'  must  differ
                  from  the  'From  file/library/member'.    You  cannot
                  replace the  'From file'  with  a selected  or  sorted
                  version.

                  If the file  does not exist,  it will be  created with
                  the same format as the 'From file'.

                  If the  file does exist, it must  have the same format
                  (Level ID) as the 'From file'.

                  *NONE  may be  specified when  an OPNQRYF  function is
                  needed.   This  means that  an  open data  path  (ODP)
                  will  be created  with an  OPNID  as the  name of  the
                  input  file.  You must follow  the use of SORTDBF with
                  CPYFRMQRYF or  a  program  that reads  the  open  data
                  path.

    FROMMBR       The  member  of the  'From  file'  to  be read.    The
                  default is *FIRST.

                  The member must have records.

    TOMBR         The  member  of the  'To  file' to  be  written.   The
                  default is *FIRST.

 If the member does not exist, it is added.

                  If the  member  has  records,  REPLACE(*YES)  must  be
                  specified.

    REPLACE       A  *YES/*NO  parameter  for  whether  to  replace  any
                  existing data in the 'To member'

                  If  the  'To  member'  exists  and has  data  records,
                  REPLACE(*YES)  must be specified.   The user must have
                  both   *OBJMGT   and   *DLT   authority   to   specify
                  REPLACE(*YES).

    TEXT          The text description  to be used for the  new To file.
                  *FROMFILE  is the default  to use the same  text as in
                  the From file.

                  A 50 byte text description may be entered.

                  This parameter  is  ignored  if the  To  file  already
                  exists or no To file is created.

    SELFLD        The field  to perform selection on.   This is  a 'list
                  parameter'  that   allows  up  to  30   fields  to  be
                  described for selection.

                  1) Field name.   The field  name to perform  selection
                  on.  The  default is *NONE.   The field must  exist in
                  the  file unless *NONE  is specified.   Decimal fields
                  (DDS  Data types of  P = Packed, S  = Zoned decimal, B
                  =  Binary,   F   =   Floating  point)   have   certain
                  restrictions as described in the following parts.

                  You  may  use *NONE  if  you  have multiple  selection
                  fields  that  are primed  by  multiple  conditions and
                  not all conditions have  the same number of  selection
                  values.   For example if CODE  = A you want  to select
                  on  FLDB, but if CODE  = B you want  to select on FLDA
                  and FLDB.   You may  prime the  selection fields  with
                  IF logic and then have a single SORTDBF command.

                  2)  Operator.   The  type  of comparison  to  perform.
                  *EQ  is the  default.   *NE, *GT,  *LT, *GE,  *LE, *CT
                  (Contains)   and   *WC   (Wildcard)   are   supported.
                  Decimal fields do not allow *CT or *WC operators.

                  *CT (contains) means  the entire field is  scanned for
                  the value).

                  *WC (Wildcard)  causes an *EQ search  against the data
                  base data with one or more wildcards.

                  Both  'fixed'  and 'floating'  wildcard  functions are
                  supported.   The  simplest  use of  wildcards  is  for
                  either generic requests  or where a position  within a
                  field should be ignored when selecting.

                  When  a generic  request is  needed such  as selecting
                  all values  beginning  with  ABC, just  enter  ABC  or
                  ABC*.   The  default  floating wildcard  character  is
                  '*'.   If  the  floating wildcard  character  does not
                  exist   at  the  end   of  the  value,   it  is  added
                  automatically (there must  be room at  the end of  the
                  value to add a wildcard if none exists).

                  The fixed  form of  wildcard support allows  any value
                  to  exist in  the search  field at a  single position.
                  You would  enter A_C  to select  ABC,  ADC, AEC,  etc.
                  If  the  field   is  longer  than  3   positions,  the
                  floating  wildcard is  automatically added at  the end
                  (if not  specified) so  you would  select values  such
                  as ABC,  ABCDEF, ADC, and  ADCX.   '_' is the  default
                  fixed wildcard character.

                  When *WC  is used, if the length  of the compare value
                  is  equal  to  the length  of  the field  in  the data
                  base, at least  one wild card character  (either fixed
                  or floating) must exist within the compare value.

                  Multiple  fixed wildcards  can  be  specified such  as
                  A_D_F.   Multiple floating wildcards  can be specified
                  such as  A*A meaning  that any  number  of values  may
                  exist  between the  A's.   You would  select AA,  ABA,
                  ABBBA, etc.

                  You  may  specify your  own characters  for  the fixed
                  and  floating  wildcards.    See  the  FLTWLDCRD   and
                  FIXWLDCRD parameters.

                  See  the  previous section  on  'Wildcard  processing'
                  for more details.

                  3) Compare  value.  The compare value  (literal) to be
                  used  to  select data.   Up  to  32 characters  may be
                  entered.   The  length  of  data may  not  exceed  the
                  length  of the  field length  in the  data base.   See
                  the  Translate  option and  *WC  operator  for special
                  handling.

                  For decimal data types,  enter a value left  adjusted.
                  For  example, to  select on  a  5 digit  field with  a
                  value  of 10,  enter 10.   If the  field to  select on
                  has decimal positions,  you can select  using a  whole
                  number  (such  as   10)  or  a  number   with  decimal
                  positions  (such as 10.5).   The decimal  notation (US
                  = '.') should be entered in job decimal format.

                  A  compare  value must  be entered  (a blank  value is
                  invalid).  The  special values *BLANK or  *BLANKS must
                  be  used  for character  fields  to  specify a  'blank
                  value'.

                  The  special values  *ZERO or  *ZEROS may be  used (or
                  enter a  0)  for decimal  fields  to specify  a  'zero
                  value'.

                  Another field name  in the same data  base file cannot
                  be used as a compare value.

                  4)  Translate.   Whether  to translate  the  values in
                  the  data  base   to  upper  case   before  making   a
                  comparison.   The  default is  *XLATE.   Translate  is
                  ignored for  Decimal fields.   If *XLATE is  used, any
                  character  fields  in  the  data  base  are translated
                  using a  translate table (see  the TRNTBL  parameter).

                  For example,  if you enter  a compare value  of 'ABC',
                  the  data will  match in the  data base  for ABC, abc,
                  Abc, AbC,  etc.   Note  that the  default is  for  the
                  situation where  you want  any 'ABC'  value regardless
                  of the  case (upper or lower) of  the data in the data
                  base.  Just enter any  case (upper or lower) into  the
                  command  prompt  without surrounding  it  with  quotes
                  (the  command prompter  will fold  the value  to upper
                  case).

                  Note  that  translation operates  on  the data  in the
                  data base and not on the compare value.

                  If you enter  a compare value with  surrounding quotes
                  such as  'Abc' and  take the default  for translation,
                  you  will not find any  values in the data  base.  All
                  the data  base values  have been  translated to  upper
                  case by default before the comparison is made.

                  If  you only  want  the 'Abc'  values  and not  values
                  like  'ABC'  or 'AbC',  enter  a value  of  'Abc' (use
                  quotes surrounding  the  compare  value)  and  specify
                  *NOXLATE for translation.

                  There  is   some  performance  degradation   by  using
                  translation.   In most  applications this is  of minor
                  concern.    If your  data base  data  is all  the same
                  case,  you  can  save  some  overhead   by  specifying
                  *NOXLATE.

                  5)  And/or.   The  relationship of  this select  field
                  with  the next select field.   The entry allows you to
                  have one  or  more fields  that  form  a group.    All
                  fields  in   the  group   must  match   the  selection
                  criteria to select the record.

                  *AND  is  the default  meaning the  next  selection is
                  considered part  of  the same  group.   The  value  is
                  ignored for the last select field.

                  *OR  may  be  specified  to  start   a  new  group  of
                  selections.   For  example, you  may specify  FLD1 and
                  FLD2  as  one  group and  FLD3  and FLD4  as  a second
                  group.   All And/Or values  would specify *AND  except
                  for FLD2 which would specify *OR.

                  If  you want  either  FLDA or  FLDB  equal to  certain
                  values  as long  as FLDX  is greater than  some value,
                  you must  specify two  groups (FLDA  and  FLDX is  one
                  group, FLDB and FLDX is a second group).

                  For  more   details,  see   the  section   on  'And/Or
                  Relationships'.

    KEYFLD        The  field to  perform sequencing.    This is  a 'list
                  parameter'  that  allows  up   to  30  fields  to   be
                  described for sequencing.

                  1) Field name.   The field name to  perform sequencing
                  on.   The default is  *NONE.  The field  must exist in
                  the data base record unless *NONE is specified.

                  You  may  use *NONE  if you  have multiple  key fields
                  that are  primed by  multiple conditions  and not  all
                  conditions have  the same number  of key fields.   For
                  example  if CODE =  A you want  a key on  FLDB, but if
                  CODE = B  you want a key  on FLDA and  FLDB.  You  may
                  prime the  key fields  with IF logic  and then  have a
                  single SORTDBF command.

                  2) The  order of the key for  this field.  The default
                  is *ASCEND for  ascending sequence.   *DESCEND may  be
                  specified for descending sequence.

                  3) From  position  within field.   The  default is  1.
                  The  From/To positions  may only  be  used when  a 'To
                  file'  is  specified and  a character  field is  to be
                  sequenced.  A value  greater than 1 may be  entered to
                  sequence  on  one or  more  bytes  within a  character
                  field.   For example  if a 10  byte field  has a value
                  in  positions  3-5  that  you  want  to  sequence  on,
                  specify From = 3 and To = 5.

                  4) To position  within a field.  The  default is *END.
                  The  From/To positions  may only  be  used when  a 'To
                  file' is  specified and  a character  field is  to  be
                  sequenced.

    TRNTBL        The   translate   table   to   be   used   for   field
                  translation.   *DFT is the default meaning  to use the
                  translate  table  defined in  the TAATRNTBL  data area
                  in  TAASECURE.   The  shipped  default  is  QSYSTRNTBL
                  (the system  supplied translate table  for translating
                  lower case US English letters to upper case).

                  The  data   area  may  be  changed  with  the  command
                  EDTCONARR  DTAARA(TAASECURE/TAATRNTBL).     For   more
                  information  about this  function,  see  the TAA  Tool
                  RTVTRNTBL.

    FLTWLDCRD     The  Floating  Wildcard  character to  be  used.   The
                  default is '*'.  A blank is not valid.

    FIXWLDCRD     The  Fixed  Wildcard  character  to  be  used.     The
                  default is '_'.  A blank is not valid.

    SORTTBL       The qualified  name of  the sort  table to  use.   The
                  default is  *JOB meaning to  use the sort  sequence of
                  the job.

                  *HEX  may be specified to sort  on only the hex values
                  of the key fields.

                  *LANGIDUNQ may  be entered  to mean the  unique-weight
                  sort  sequence  table  that  is  associated  with  the
                  LANGID parameter.

                  *LANGIDSHR  may be  entered to mean  the shared-weight
                  sort  sequence  table  that  is  associated  with  the
                  LANGID parameter.

    CCSID         The  sort sequence  CCSID to  be used  along with  the
                  LANGID  value  for  retrieving  the national  language
                  sort sequence table for  sorting character data.   The
                  default is *JOB meaning  to use the CCSID of  the job.
                  A  specific CCSID in  the range of  1 to 65535  may be
                  entered.

    LANGID        The  language  ID  to  be used  to  obtain  a national
                  language sort  sequence  table for  sorting  character
                  data.    The  default  is  *JOB  meaning  to  use  the
                  language ID of the job.

    OPNQRYFCMD    A  *YES/*NO/*RQS  parameter  for  whether the  OPNQRYF
                  command that is  generated internally  should be  sent
                  as  a message.    *NO is  the default  to  not send  a
                  message.

                  *YES  may be specified to  assist in understanding how
                  SORTDBF operates or for  problem determination.   Note
                  that  if  TOFILE(xxx)  is  entered,  no  KEYFLDs  will
                  exist on the OPNQRYF statement.

                  *RQS  may  be  specified  to  return a  *RQS  message.
                  This  could allow  you to  prompt for  the command and
                  modify it.   Note that if  TOFILE(xxx) is entered,  no
                  KEYFLDs will exist for the OPNQRYF statement.

 Samples
 -------

 The  following samples  use the  outfile created  from  DSPOBJD (choose
 one  of  your  libraries  as  input)  and  name  the OUTFILE  parameter
 QTEMP/DSPOBJDP such as:

            DSPOBJD  OBJ(xxx/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE) +
                         OUTFILE(QTEMP/DSPOBJDP)

   **   Select for owner JONES.   Write the output to file DSPOBJDP2  in
        QTEMP.

           SORTDBF   FROMFILE(QTEMP/DSPOBJDP) TOFILE(QTEMP/DSPOBJDP2) +
                       SELFLD((ODOBOW *EQ JONES))

   **   Select  for owner  JONES  and a  size  greater  than 50,000  and
        sequence  by  size.   Write  the  output  to  file DSPOBJDP2  in
        QTEMP.  Since the  same output file will  be used (assuming  the
        first example was run), REPLACE = *YES is required.

           SORTDBF   FROMFILE(QTEMP/DSPOBJDP) TOFILE(QTEMP/DSPOBJDP2) +
                       REPLACE(*YES) SELFLD((ODOBOW *EQ JONES) +
                       (ODOBSZ *GT 50000)) KEYFLD((ODOBSZ))

   **   Same  as  previous   except  the  values  to  be   selected  and
        sequenced on are variables.

           DCL       &OWNER *CHAR LEN(10)
           DCL       &SIZE *DEC LEN(10 0)
            .
           SORTDBF   FROMFILE(QTEMP/DSPOBJDP) TOFILE(QTEMP/DSPOBJDP2) +
                       REPLACE(*YES) SELFLD((ODOBOW *EQ &OWNER) +
                       (&SIZE *GT 50000)) KEYFLD((&OWNER))

   **   Same  function  as  previous  except  that any  *JRNRCV  objects
        should  be  selected regardless  of  owner or  size  (becomes an
        'OR' condition).

           SORTDBF   FROMFILE(QTEMP/DSPOBJDP) TOFILE(QTEMP/DSPOBJDP2) +
                       REPLACE(*YES) SELFLD((ODOBOW *EQ JONES) +
                       (ODOBSZ *GT 50000 *XLATE *OR) +
                       (ODOBTP *EQ *JRNRCV)) +
                       KEYFLD((ODOBSZ))

   **   Select on the  generic object name ABC  and sequence on size  in
        descending  sequence.   Write the  output to  file  DSPOBJDP2 in
        QTEMP.

           SORTDBF   FROMFILE(QTEMP/DSPOBJDP) TOFILE(QTEMP/DSPOBJDP2) +
                       REPLACE(*YES) +
                       SELFLD((ODOBNM *WC ABC) +
                       KEYFLD((ODOBSZ *DESCEND))

   **   Select  on  the generic  object name  ABC  and sequence  on size
        within owner.  Write the output to file DSPOBJDP2 in QTEMP.

           SORTDBF   FROMFILE(QTEMP/DSPOBJDP) TOFILE(QTEMP/DSPOBJDP2) +
                       REPLACE(*YES) +
                       SELFLD((ODOBNM *WC ABC) +
                       KEYFLD((ODOBOW)(ODOBSZ))

   **   Sequence  on   positions   2-3  of   the  ODOBTX   field   (text
        description).

           SORTDBF   FROMFILE(QTEMP/DSPOBJDP) TOFILE(QTEMP/DSPOBJDP2) +
                       REPLACE(*YES) +
                       KEYFLD((ODOBTX *ASCEND 2 3))

   **   Select  on any  names  that have  BCD in  positions  2-4 of  the
        object  name.  Use the  OPNQRYF method of  sequencing (an access
        path is built).

           SORTDBF   FROMFILE(QTEMP/DSPOBJDP) TOFILE(*NONE) +
                       SELFLD((ODOBNM *WC _BCD))

 Restrictions
 ------------

 If a Logical keyed  file is used  as the 'From file',  the To file  may
 be created with a different  format by the use of CPYFRMQRYF.   If this
 occurs,  a subsequent  use of SORTDBF  to output  to the  existing file
 will  fail with  a 'level check'.   You must  delete the  'To file' and
 allow SORTDBF to create it.

 See the previous comments for other restrictions.

 Prerequisites
 -------------

 The following TAA Tools must be on your system:

      CHKOBJ3         Check object 3
      DUPFILFMT       Duplicate file format
      EDTVAR          Edit variable
      EXTLST          Extract list
      MOVCHRDEC       Move character to decimal
      RTVDBFA         Retrieve data base file attributes
      RTVFLDA         Retrieve field attributes
      RTVFMT          Retrieve format
      RTVTRNTBL       Retrieve translate table
      RTVVALA         Retrieve value attributes
      SCNVAR          Scan variable
      SNDCOMPMSG      Send completion message
      SNDESCMSG       Send escape message

 Implementation
 --------------

 None, the tool is ready to use.

 Objects used by the tool
 ------------------------

    Object        Type    Attribute      Src member    Src file
    ------        ----    ---------      ----------    ----------

    SORTDBF       *CMD                   TAAQRYB       QATTCMD
    TAAQRYBC      *PGM       CLP         TAAQRYBC      QATTCL
    TAAQRYBR      *PGM       RPG         TAAQRYBR      QATTRPG

Added to TAA Productivity Tools March 15, 2002


Home Page

Last modified on November 19, 2014 © 1995, 2014 - TAA Tools, Inc.