The TAA Query tool provides a simple query method allowing for
selection, sequencing, and printing. A data base file holds the
specifications which are created from simple prompts. The same query
can be used again and/or modified. OPNQRYF is used to perform
selection and sequencing which provides performance as fast as any
query function on the system. A generalized print approach is used
to print the data.
The intent of the tool is to provide for simple query functions that
can be achieved with a minimum of screens and education. All the
screens support Help text to assist in understanding what functions
exist and what may be entered. Queries may be re-executed, submitted
to batch, changed, etc. Feedback is provided in terms of when the
last activity occurred on the query, the number of times run, the
last user who ran the query, etc.
To begin, you must first create the data base files that will hold
the query definitions.
CRTTAAQRYF QRYFILLIB(xxx)
A physical file (QRYFILP) and two logical files (QRYFILL and QRYFILM)
are created in the specified library. Other users could use (but not
change) any queries defined in the file (See the later discussion on
Security and Authorization).
The WRKTAAQRY command can then be used to begin working with queries:
WRKTAAQRY QRYFILLIB(xxx)
A subfile of any existing queries would appear. Options exist such
as display, execute, and submit to batch.
F6 may be used to create a new query. This causes a display to
appear with the name of the file to be queried and various high level
options such as whether DSPSPLF should be used after the query is
executed.
When Enter is pressed after the options display, the fields in the
described file are shown in a subfile. Options exist such as to
specify which fields should be printed as well as sorting and control
level fields. Only fields that print can be used for sorting,
control levels, or selection. Fields can be re-positioned to allow
them to print left to right across the page.
When Enter is pressed without making any changes, another display
appears with just the fields designated for printing. Simple
selection criteria may be specified such as an equal or not equal
condition along with a compare value. Both the 'contains' (*CT) and
'wildcard' (*WC) functions are supported.
OPNQRYF is used to perform the selection and sequencing function.
This provides as fast an approach as exists on the system.
A generalized print program is used to print the selected data. The
specified fields print left to right across the page up to a maximum
of 378 bytes. While you may not have a printer to support this
width, DSPSPLF may be used to display the data. If you intend to
print the data, a smaller width may be specified. See the later
section on hard copy output.
Any decimal fields (if not specified with a Y or X edit code) are
automatically summed for each control level specified. The final
total automatically includes a total, an average, a maximum, and a
minimum for each decimal field that is summed.
Performance
-----------
Because OPNQRYF is used for the selection and sequencing requests,
existing access paths may be used to improve the performance. This
front half of TAAQRY will operate as fast as any query function on
the system.
The function of printing the results of OPNQRYF (the back half of
TAAQRY) is done thru a generalized print program. This is not an
efficient function and you should attempt to minimize the number of
records to be printed by using good selection criteria.
The default allows users to run interactively. You may prevent this
function and/or allow only authorized users to be able to run
interactively. See the section on Run Interactively.
Temporary Queries
-----------------
The default when creating a query is to use an assigned temporary
name. Temporary queries will appear in the subfile and may be run
again or changed just like a permanent query. The naming convention
causes the most recently defined temporary query to appear first.
The intent of the 'Temporary Query' concept is to allow you to
quickly define one time usage queries without attempting to provide a
formal naming structure, text description, etc.
A temporary query may be changed to a permanent query by using the
rename or change option.
Modifying an existing query
---------------------------
An existing query may be modified by selecting the Change option when
the first subfile is displayed. The previous prompts along with the
current values will be displayed and changes can be made.
Hard copy output
----------------
The minimum printer width is 132. The maximum width is 378 (DSPSPLF
maximum).
Prior to opening the printer file, an OVRPRTF command is used which
specifies the actual width of the print line based on the last
position of the last field (minimum of 132).
Objects
-------
There are no Query objects created when a Query is defined. Each
query is a separate record in the QRYFILP physical file. When a
query is run, certain functions are interpretively executed. For
example, if the file has moved to a different library and *LIBL is
specified for the file, the query will run properly if the file is on
the library list.
If a field used in the Query no longer exists, the Query will not run
and the Change function must be used. If a field is changed in
attributes, the Query function will run properly in most cases. Some
changes may prevent execution.
Security and Authorization
---------------------------
Authorization is controlled both at the QRYFILP level and by
individual Query.
** Authorization to the QRYFILP file.
When CRTTAAQRYF is used to create the QRYFILP file, the AUT
parameter allows for *CHANGE, *USE, or *EXCLUDE to determine
the public access to the file. The default is *USE. The
normal system authorization functions such as EDTOBJAUT may be
used to change the public authority and authorize specific
individuals.
A user with all rights to the QRYFILP file (such as the owner
or an *ALLOBJ special authority user) can do any function with
any Query defined in the file. This overrides the
authorization function to an individual Query. A user with
'all rights' to QRYFILP may also change the defaults for new
queries (see the section on 'Changing Defaults'.
A user with *CHANGE authority can add new queries and may be
able to change or run an existing Query (the authority of the
individual Query determines what is allowed).
A user with *USE authority may only run or display a Query.
** Authorization to individual Queries. When a query is created,
the current user becomes the owner of the Query. An option
exists to authorize the Query for:
-- *ALL. This is the default and allows any function on
the Query (assuming the user has *CHANGE authority to
the QRYFILP file).
-- *USE. The user can only run or display the Query. If
the user has *CHANGE authority to the QRYFILP file, he
can also copy the Query (use the existing Query as a
base for a new Query). If copy is used, the current
owner becomes the owner of the new Query.
-- *NONE. Only the owner of the Query can perform
functions on the Query. When *NONE is specified, users
who are not the owner of the Query will not see the
Query using WRKTAAQRY and cannot run the Query using
RUNTAAQRY. (Note the exception of a user having all
rights to the QRYFILP file).
Field types supported
---------------------
Only character fields (type A in the Data Base) and decimal fields of
type Packed, Zoned, or Binary (P, S, and B in the Data Base) are
supported. Some restrictions exist with Binary Fields.
File types supported
--------------------
A physical file or a single format logical file may be used along
with any designated member. The TAAQRY function cannot dynamically
join together files. You may, however, specify a join logical file
to be processed.
Valid decimal data
------------------
Because OPNQRYF is used to map decimal fields, the tool requires
valid decimal data to operate successfully. Unlike processing with
RPG, a zoned decimal field cannot have a blank value. If invalid
data exists, the program will fail with a 'Data mapping' error.
Selecting by comparing fields within the same record
----------------------------------------------------
You can select on fields within the same record by using an '&' to
precede the field name in the compare value such as:
FLDA *GT &FLDB
Both FLDA and FLDB must be selected for printing. If FLDA is
character, FLDB must also be character. If FLDA is decimal, FLDB
must also be decimal. *WC and *CT are not valid when comparing
against a field name.
Copying Queries
---------------
The CPYTAAQRY command may be used to copy one or more permanent
Queries from one QRYFILP file to another.
Override considerations
-----------------------
You should not attempt to override the file named to be queried.
Run Interactively
-----------------
By default, any user may run a query interactively. You can prevent
interactive use by entering a *NO into an Application Value (*USRSPC
object) in TAASECURE. As an *ALLOBJ user, enter:
EDTAPPVAL APPVAL(TAASECURE/TAAQRY)
Enter *NO to prevent interactive execution.
You may allow exceptions to this condition by entering specific user
profile names into a data area in TAASECURE. As an *ALLOBJ user,
enter:
EDTCONARR DTAARA(TAASECURE/TAAQRY2)
Any names entered will be allowed to run a query interactively.
Note that if the Application Value is *YES, no checking occurs of the
names in the TAAQRY2 data area (all users may run interactively).
Changing Defaults
-----------------
The general attributes associated with each query such as:
Line width
Type of column headings
are assigned defaults when a new query is started. Original default
values are used.
The defaults may be changed. A user who has *ALL authority to the
QRYFILP file will see the F9 key on the initial WRKTAAQRY display.
If F9 is used, a display appears which allows the defaults to be set
for any new queries.
The defaults are stored in a special record in the file. If the file
is cleared, the default information is lost and the original defaults
will be used until changed.
Escape messages you can monitor for
-----------------------------------
The WRKTAAQRY command handles most error messages by placing text on
the message line on the subfile displays. F23 may be used if there
is more than one line of text.
RUNTAAQRY will produce the following escape messages:
TAA9891 The file is not externally described
TAA9892 The file has more than a single format
CPYTAAQRY will produce the following escape messages:
TAA9895 No queries were copied
TAA9896 Duplicate Query IDs were found
Wildcard (*WC Operator)
-----------------------
OPNQRYF supports two forms of wildcard (Fixed and Floating). Both
forms are supported within TAAQRY.
The wildcard function may be used on either character, packed, or
signed numeric field types (Binary is excluded). Decimal fields are
mapped to internal character fields before the selection is
performed.
The default characters used are:
'_' = Fixed wildcard
'*' = Floating wildcard
Either or both defaults may be changed as an attribute on the Change
Display for a specific query. This allows you to scan for a '_' or
'*' in your data.
When *WC is used, at least one of the wildcards must be specified in
the value.
Because of potential misuse with wildcard functions, TAAQRY
implicitly does the following:
** Data base character fields are translated to upper case before
making any comparison. It is possible to specify 'N' for 'no
translation'.
** If the comparison value for a character field is a shorter
length than the field being compared, a floating wildcard is
added at the end of the value (unless it is specified). For
example, if you entered A_C and the field is greater than 3
bytes, the actual comparison is A_C* meaning that any
character after a C would be selected.
The only method of avoiding the implicit wildcard at the end
of the compare value would be to make the compare value the
same length as the field such as A_C__ for a 5 position field.
However, this would cause the same selection as A_C*.
** For decimal fields, the OPNQRYF %DIGITS function is used to
map a decimal field to an internal character field. Because
the internal character field is longer than the compare value,
a floating wildcard is added internally (it is not seen on the
selection display).
A typical use of the *WC function on a decimal field is when
you have a date in a decimal format. In this case you would
normally specify all positions for the selection. For
example, if the date is 6 digits in a MMDDYY format, you could
select all of March 2009 with a value of 03__09. If the date
is defined as 7 digits with the high order always 0, then you
would want to select with a value of 003__09.
If a decimal field is defined as LEN(5 0) and contains a value
of 100, 00100 will be placed in the internal character field
by the %DIGITS function. You must consider the leading zeros
when using *WC (as opposed to using an operator like *GT where
you describe a decimal comparison). For example, if a decimal
field contains Zip Code and is defined as 5 digits and you
want all Zip Codes that begin with 05, you would specify 05*.
Fixed The fixed form allows any character to be selected.
It would normally be used when you specify the
compare value as the same length as the data base
field (either character or decimal).
A_C Where any character in the 2nd pos is selected.
ABC, ADC, etc would all be selected.
A__C Any characters in the 2nd and 3rd pos are selected.
ABBC, ADDC, etc would all be selected.
03__09 Any day of March 2009 for a MMDDYY month format.
The field can be character, packed, or zoned.
When the Fixed form is used and the length of the
compare is the same as the field length, the
specific positions within the compare value are the
only positions where any value will be selected.
Floating The floating form allows multiple characters (a
variable number) to be selected.
ABC* Any value beginning with ABC is selected.
This is the normal generic type function.
ABC, ABCD, ABCDEF would all be selected.
A*C Any value between the A an C would be selected.
If a 3 position field is being compared, this
acts like the Fixed form of A_C.
If the field being compared is longer than 3
bytes, then any value between the A and C
would be selected such as ABC, ABBC, ABBBC, etc.
TAAQRY implicitly adds an * to the end of the
value (unless one is specified) when either
wildcard character is embedded within the value.
Thus the comparison is actually A*C*.
*AB Any value containing AB. Both ABxxx and xxABxx
would be selected. Because the comparison is
actually *AB*, this form of the floating
wildcard acts like the Contains (*CT) operator.
Combinations of fixed and floating wildcards may be used:
A_C* This would cause a selection of ABC, ABCD, etc.
Translation Table
-----------------
By default, all comparisons against character fields are translated
to upper case. This avoids a typical error such as using the *CT
(contains) function on a name field. For example,
NAME *CT JO
If the data base NAME field has values such as 'John' no records will
be found unless the data base field is translated to upper case.
Translation occurs by specifying the name of a translate table on the
generated OPNQRYF command. The translate table used is found by the
RTVTRNTBL TAA Tool. This retrieves the value stored in the data area
TAATRNTBL in TAASECURE. This is shipped with a value of QSYSTRNTBL
in QSYS which is the system supplied table for converting English
lower case to upper case.
If your data is in English, there is no need to make a change.
If you use a different language, see the discussion with the
RTVTRNTBL tool.
There is some performance loss (not as much as it might appear) for
translating before making a comparison. If you know your data is all
upper case, you can avoid the translation overhead by specifying 'N'
in the 'Xlat' field for the selection subfile.
Use with CHGQRYA
----------------
The CHGQRYA command may be used to help control the amount of system
resources used by any query running on the system.
The TAAQRY tool does not perform any special function for CHGQRYA.
To control a user, the CHGQRYA command must be issued for the user's
job. The QRYTIMLMT parameter may be specified to control the number
of seconds that a query will take to run. If the system estimates a
larger time for the query, message CPA4259 is sent as an inquiry to
the job. The message allows a C (Cancel) or I (Ignore) response.
The time limit will control the estimate to run the OPNQRYF command.
It does not control the time it takes to print and/or display the
results.
If 'C' for Cancel is used, the running of the Query will end. If
WRKTAAQRY is used, the user will see a general error message that
refers to low level messages. If RUNTAAQRY is used, a general escape
message occurs that refers to low level messages.
CRTTAAQRYF command *CMD
------------------
The CRTTAAQRYF command creates the required control files in a
specific library.
QRYFILLIB The library to contain the TAA Query files. One
physical file (QRYFILP) and two logical files
(QRYFILL and QRYFILM) will be created.
SRCLIB The source library to use for the QATTDDS file
source. The default is *TAAARC. If a full license
exists, the source is used from the TAA Archive. If
a demonstration license exists, the source is used
from the QATTDDS file in the TAATOOL library.
A specific user library may be named, but the source
file must be QATTDDS.
AUT The public authorization to the file. The default
is *USE meaning other users can use (run) any
defined queries, but cannot change any definitions.
*CHANGE may specified to allow other users to
change, define, and run any queries.
*EXCLUDE may be specified to prevent other users
from any access to the created files.
DLTTAAQRYF command *CMD
------------------
The DLTTAAQRYF command deletes the required control files in a
specific library.
QRYFILLIB The library containing the TAA Query files.
WRKTAAQRY command *CMD
-----------------
The WRKTAAQRY command displays a subfile of any existing queries and
allows various options along with creating a new query.
QRYID The ID of the Query to be worked with. A name is
assigned from prior use of WRKTAAQRY. The default
is *PROMPT which means a subfile appears with any
previously entered queries. If a name is entered,
the subfile begins with the first record equal to
the name. If the name does not exist, the subfile
begins with the first record previous.
QRYFILLIB The library containing the TAA Query files. The
default is *LIBL. *CURLIB may also be used.
LOGQRY A *YES/*NO parameter for whether any queries that
are run will log the OPNQRYF command. *NO is the
default meaning no logging occurs.
*YES causes logging of the OPNQRYF command. This is
intended as a debugging aid. Logging occurs by
sending a message containing the OPNQRYF command to
be executed to the current program's message queue.
RUNTAAQRY command parameters *CMD
----------------------------
The RUNTAAQRY command executes a query that has already been defined
by WRKTAAQRY. If you are not authorized to run interactively, a
prompt for SBMJOB will occur.
QRYID The ID of the Query to be run.
OUTPUT Whether to display or print the output. The default
is * which causes a display of the results if run
interactively.
*PRINT causes the output to be printed (spooled).
*QRYDFN causes the attribute of the query in the
QRYFILP file to be used.
QRYFILLIB The library to contain the QRYFIL files. The
default is *LIBL. *CURLIB may also be used.
TITLE The title used on each page of Query output. The
default is *SAME meaning the value assigned for the
'Query text description' is used.
A value other than *SAME will cause the title to be
changed for this output of the query. It does not
make a permanent change to the Query text
description.
LOGQRY A *YES/*NO parameter for whether any queries that
are run will log the OPNQRYF command. *NO is the
default meaning no logging occurs.
*YES causes logging of the OPNQRYF command. This is
intended as a debugging aid.
CPYTAAQRY command parameters *CMD
----------------------------
The CPYTAAQRY command copies one or more permanent Queries from one
QRYFILP file to another.
QRYID The ID of the Query to be copied. A specific name,
a generic name, or the special value *ALLPERM may be
specified. Only permanent Queries may be copied.
FRMQRYFLIB The library containing the QRYFILP file to be copied
from.
TOQRYFLIB The library containing the QRYFILP file to be copied
to. The Query ID to be copied must not already
exist in the To library.
Restrictions
------------
** Only a physical file or a single format logical file may be
used. A join logical file may be used, but TAA Query will not
dynamically join multiple files.
** If the file has more than 199 fields, only the first 199 may
be used.
** Only character and decimal fields are supported. This
includes Data Base types of A, P, S, and B.
** A decimal field cannot exceed 21 whole digits nor 9 decimal
positions.
** A maximum of 40 fields may be printed including a maximum of
30 character fields and 20 decimal fields.
** If two fields in the data base are compared, both fields must
be character or both fields must be decimal. *WC and *CT are
not valid when comparing fields in the data base.
** A maximum of 9 fields may be used for sequencing.
** A maximum of 3 control fields may be specified. Up to 3
fields may be used for a single control field.
** A maximum of 25 selection statements may be made.
** Because OPNQRYF is used to map decimal fields, the tool
requires valid decimal data to operate successfully. Unlike
processing with RPG, a zoned decimal field cannot have a blank
value.
** Derived fields are not supported. For example, you cannot add
FLDA to FLDB to produce a derived (new) field that will be
printed. Accumulation of any decimal field is automatic and
control breaks may be specified.
Prerequisites
-------------
The following TAA Tools must be on your system:
ADJVAR Adjust variable
CHKACTOBJ Check active object
CHKGENERC Check generic
CHKNAM Check name
CHKOBJ3 Check object 3
CPYTAADDS TAA Archive
DSPERRMSG Display error message
EDTVAR Edit variable
FILEFDBCK File feedback
HLRMVMSG HLL Remove message
LOCKMSG Lock message
RPGSTSDS RPG Status data structure
RTVFLDARR Retrieve field array
RTVOBJD2 Retrieve object description 2
RTVSBMJOB Retrieve submitted job
RTVSYSVAL3 Retrieve system value 3
RTVTRNTBL Retrieve Translate Table
SNDCOMPMSG Send completion message
SNDDIAGMSG Send diagnostic message
SNDESCMSG Send escape message
SNDSTSMSG Send status message
TAAEXC TAA Execute command
UPDPFILE Update PFILE keyword
Implementation
--------------
The tool is ready to use, but you must first use CRTTAAQRYF to create
the required files.
Objects used by the tool
------------------------
Object Type Attribute Src member Src file
------ ---- --------- ---------- ----------
CRTTAAQRYF *CMD TAADBIU2 QATTCMD
WRKTAAQRY *CMD TAADBIU3 QATTCMD
DLTTAAQRYF *CMD TAADBIU4 QATTCMD
RUNTAAQRY *CMD TAADBIU5 QATTCMD
CPYTAAQRY *CMD TAADBIU6 QATTCMD
TAADBIUC2 *PGM CLP TAADBIUC2 QATTCL
TAADBIUC3 *PGM CLP TAADBIUC3 QATTCL
TAADBIUC4 *PGM CLP TAADBIUC4 QATTCL
TAADBIUC5 *PGM CLP TAADBIUC5 QATTCL
TAADBIUC6 *PGM CLP TAADBIUC6 QATTCL
TAADBIUC11 *PGM CLP TAADBIUC11 QATTCL
TAADBIUC13 *PGM CLP TAADBIUC13 QATTCL
TAADBIUC21 *PGM CLP TAADBIUC21 QATTCL
TAADBIUR *PGM RPG TAADBIUR QATTRPG
TAADBIUR3 *PGM RPG TAADBIUR3 QATTRPG
TAADBIUR5 *PGM RPG TAADBIUR5 QATTRPG
TAADBIUR6 *PGM RPG TAADBIUR6 QATTRPG
TAADBIUR11 *PGM RPG TAADBIUR11 QATTRPG
TAADBIUR12 *PGM RPG TAADBIUR12 QATTRPG
TAADBIUR13 *PGM RPG TAADBIUR13 QATTRPG
TAADBIUD *FILE DSPF TAADBIUD QATTDDS
TAADBIUE *FILE DSPF TAADBIUE QATTDDS
TAADBIUP *FILE PF TAADBIUP QATTDDS
TAADBIUL *FILE LF TAADBIUL QATTDDS
TAADBIUM *FILE LF TAADBIUM QATTDDS
TAADBIUN *FILE LF No source
TAADBIUO *FILE LF No source
TAADBIUQ *FILE LF TAADBIUQ QATTDDS
TAAQRY *USRSPC
TAAQRY2 *DTAARA
The TAADBIUN and TAADBIUO files are only used to compile against and
have the same format as TAADBIUP.
TAAQRY and TAAQRY2 are in the TAASECURE library.
Structure
---------
CRTTAAQRYF Cmd
TAADBIUC2 CL pgm
DLTTAAQRYF Cmd
TAADBIUC4 CL pgm
WRKTAAQRY Cmd
TAADBIUC3 CL pgm
TAADBIUC21 CL Pgm - Accesses TAASECURE
TAADBIUR3 RPG Pgm
TAADBIUP Physical file
TAADBIUL Logical file
TAADBIUM Logical file
TAADBIUE Display file
TAADBIUC13 CL pgm - Builds OPNQRYF command
TAADBIUC15 CL pgm to sbm job
TAADBIUQ Physical file
TAADBIUR RPG Pgm - Field and selection subfiles
TAADBIUD Display file
TAADBIUC11 CL pgm - Runs OPNQRYF
TAADBIUC15 CL pgm - Prompts for SBMJOB
TAADBIUR11 RPG Pgm - Print the results
TAADBIUQ Physical file
TAADBIUR12 RPG Pgm - Check specs
TAADBIUR13 RPG Pgm - Update run info
RUNTAAQRY Cmd
TAADBIUC5 CL pgm
TAADBIUC13 CL pgm (See previous sub structure)
CPYTAAQRY Cmd
TAADBIUC6 CL pgm
TAADBIUR6 RPG pgm
|
