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
|
