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, variable  length 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 Up to Top