IBM SC41-5210-04 User Manual
ERserver
iSeries
Query for iSeries Use
Version 5
SC41-5210-04
ER s e r v e r
iSeries
Query for iSeries Use
Version 5
SC41-5210-04
Note
Before using this information and the product it supports, be sure to read the information in
“Notices” on page 261.
Fifth Edition (September 2002)
This edition applies only to reduced instruction set computer (RISC) systems.
© Copyright International Business Machines Corporation 2000, 2002. All rights reserved.
US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract
with IBM Corp.
Contents
About Query for iSeries Use ............................ix
Who should read the Query for iSeries Use book .....................ix
Part 1. Introduction to Query for iSeries ....................1
Chapter 1. What is Query for iSeries? ........................3
Query for iSeries fundamentals ............................3
Files, fields, and record formats in Query for iSeries ...................3
Query for iSeries definitions ............................5
Libraries in Query for iSeries ............................5
Major functions of Query for iSeries ..........................5
Chapter 2. General operating information for Query for iSeries ..............7
Practicing with Query for iSeries ...........................7
Getting started with Query for iSeries .........................7
When you are finished working with Query for iSeries ..................7
Telling Query for iSeries what you want .........................7
Using prompts and default values in Query for iSeries ..................7
Using Query for iSeries function keys ........................8
Using lists in Query for iSeries ...........................9
Using Query for iSeries commands ..........................11
Using the Query Utilities menu............................11
Using the Work with Queries display .........................12
Choosing a single Query for iSeries task .......................12
Choosing multiple Query for iSeries tasks ......................13
Working with a list of Query for iSeries queries ....................14
Selecting a library for your Query for iSeries queries ..................16
Displaying the format of constants in Query for iSeries ...................17
Query for iSeries profile information ..........................18
Handling Query for iSeries messages and errors .....................18
Changing your Query for iSeries queries .......................18
Part 2. Defining and using Query for iSeries query definitions .........21
Chapter 3. Creating a Query for iSeries query definition .................27
Starting Query for iSeries query definition .......................27
Selecting definition steps when defining a Query for iSeries query ..............28
Selecting options for a Query for iSeries query definition .................29
Moving through the Query for iSeries definition displays .................30
When you return to the Query for iSeries Define the Query display .............30
Considerations for creating Query for iSeries queries ...................30
Selecting files, records, and fields for Query for iSeries .................30
Suggested sequence of tasks for creating Query for iSeries queries .............31
Chapter 4. Specifying and selecting files for a Query for iSeries query ...........33
Specifying file selections for a Query for iSeries query ...................33
Choosing a file for a Query for iSeries query .....................34
Choosing a library for a Query for iSeries query ....................34
Choosing a member or record format for a Query for iSeries query .............35
Selecting multiple files for a Query for iSeries query...................35
Confirming your options for a Query for iSeries query ..................36
Handling errors on the Query for iSeries Specify File Selections display ...........36
© Copyright IBM Corp. 2000, 2002 iii
Selecting files on the Query for iSeries Select File display .................37
Selecting file members on the Query for iSeries Select Member display ............39
Selecting record formats on the Query for iSeries Select Record Format display .........40
Displaying all files selected on the Query for iSeries Display File Selections display ........41
Joining files in a Query for iSeries query ........................42
Types of joins in a Query for iSeries query ......................43
How to join files in a Query for iSeries query .....................43
Rules for joining files in a Query for iSeries query ...................44
Examples of joining files in a Query for iSeries query ..................46
Sequencing secondary files for a primary join in a Query for iSeries query ..........49
Displaying all join tests in a Query for iSeries query ....................53
Handling missing fields in a Query for iSeries query ....................54
Handling missing fields during file selection process of a Query for iSeries query ........55
Handling missing fields when starting to change or display a Query for iSeries query ......55
Chapter 5. Defining result fields in Query for iSeries ..................57
Creating results fields in Query for iSeries .......................57
Query for iSeries result field name .........................58
Query for iSeries expressions ...........................58
Query for iSeries numeric expressions ........................59
Query for iSeries character expressions .......................60
Query for iSeries character functions ........................61
Date, time, and timestamp expressions in Query for iSeries ................66
Displaying constants format in Query for iSeries ....................69
Date, time, and timestamp arithmetic operations in Query for iSeries .............69
Date arithmetic operation in Query for iSeries .....................70
Time arithmetic operation in Query for iSeries .....................73
Durations in Query for iSeries ...........................73
Date, time, and timestamp functions in Query for iSeries ..................74
CHAR Query for iSeries function ..........................74
DATE Query for iSeries function ..........................75
DAY Query for iSeries function ..........................75
DAYS Query for iSeries function ..........................76
HOUR Query for iSeries function ..........................76
MICROSECOND Query for iSeries function ......................77
MINUTE Query for iSeries function .........................77
MONTH Query for iSeries function .........................78
SECOND Query for iSeries function.........................78
TIME Query for iSeries function ..........................79
TIMESTAMP Query for iSeries function .......................79
YEAR Query for iSeries function ..........................80
Additional date, time, and timestamp functions in Query for iSeries .............80
Converting date formats in Query for iSeries ......................80
Converting date for output to a database file in Query for iSeries ..............80
Converting date for output to a display or printer in Query for iSeries ............81
General considerations when creating an expression in Query for iSeries ............83
Column headings in Query for iSeries .........................85
Length and decimal positions in Query for iSeries ....................85
Example of defining a result field in Query for iSeries ..................86
Adding or removing result fields in Query for iSeries ...................86
Chapter 6. Selecting and sequencing fields in Query for iSeries ..............87
Letting Query for iSeries select and sequence fields....................87
Selecting fields and specifying their sequence in Query for iSeries ..............87
Chapter 7. Selecting records in Query for iSeries ...................91
iv Query for iSeries Use V5R2
Letting Query for iSeries select records ........................91
Selecting the records you want in Query for iSeries ....................91
Comparison fields in Query for iSeries ........................92
Comparison values in Query for iSeries .......................93
Comparison tests in Query for iSeries ........................95
Using more than one comparison test in Query for iSeries ................101
Adding and removing comparisons in Query for iSeries .................103
Chapter 8. Selecting sort fields in Query for iSeries ..................105
Letting Query for iSeries determine the order of records for you ...............105
Selecting the sort fields you want to use in Query for iSeries ................105
Additional sort considerations in Query for iSeries ...................108
Chapter 9. Selecting a collating sequence in Query for iSeries ..............111
Letting Query for iSeries select a collating sequence ...................111
Setting your default collating sequence in Query for iSeries................111
Purpose of a collating sequence in Query for iSeries ...................111
Collating sequence and CCSIDs in Query for iSeries ..................112
How a collating sequence affects Query for iSeries....................112
Selecting a Query for iSeries collating sequence ....................113
Using the hexadecimal collating sequence in Query for iSeries ..............113
Using the language collating sequence for your country in Query for iSeries .........114
Defining your own collating sequence in Query for iSeries ................115
Selecting a translation table in Query for iSeries....................116
Selecting a system sort sequence in Query for iSeries .................117
Chapter 10. Specifying report column formatting in Query for iSeries ...........119
Formatting the columns of the Query for iSeriesquery report ................119
Column spacing in Query for iSeries reports .....................119
Column headings in Query for iSeries reports.....................119
Length and decimal positions in Query for iSeries reports ................120
Omitting fields from a Query for iSeries report ....................121
Editing numeric fields in Query for iSeries reports ....................122
Defining numeric field editing in Query for iSeries reports ................122
Describing numeric field editing in Query for iSeries reports ...............124
Describing date/time field editing display in Query for iSeries reports ............127
Specifying an edit code in Query for iSeries reports ..................129
Specifying edit words .............................131
Chapter 11. Specifying report summary functions in Query for iSeries reports .......135
Types of summary functions in Query for iSeries reports .................135
Summarizing columns in Query for iSeries reports ....................137
Location of column summary values in Query for iSeries reports...............137
Chapter 12. Defining Query for iSeries report breaks..................139
Defining a Query for iSeries report break .......................139
Defining report break formatting in Query for iSeries reports ................143
Skip to new page in Query for iSeries reports ....................143
Suppress summaries in Query for iSeries reports ...................143
Break text in Query for iSeries reports .......................144
Chapter 13. Selecting output type and output form in Query for iSeries reports .......145
Selecting the output type and form you want in Query for iSeries reports ...........145
Form of output of Query for iSeries reports .....................146
Line wrapping in Query for iSeries reports ......................146
Wrapping width in Query for iSeries reports .....................147
Contents v
Record on one page in Query for iSeries reports ...................147
Displaying wrapping widths in Query for iSeries reports .................147
Defining output to the printer in Query for iSeries reports ................148
Defining output of Query for iSeries reports to a database file...............154
Using an existing output file definition in Query for iSeries reports .............155
Building a new output file definition in Query for iSeries reports ..............156
Using an output database file created by Query for iSeries ................161
Chapter 14. Specifying Query for iSeries processing options ..............163
Purpose of Query for iSeries processing options ....................163
Rounding numeric field values during Query for iSeries processing ..............164
Ignoring decimal data errors during Query for iSeries processing ..............164
Ignoring character substitution warnings during Query for iSeries processing ..........165
Using collating sequence for all character comparisons during Query for iSeries processing . . . 165
Chapter 15. Exiting and running a Query for iSeries query ...............167
Ending a Query for iSeries query definition ......................167
Saving a Query for iSeries query definition......................167
Running a Query for iSeries query..........................169
Using function key F5 when running a Query for iSeries query ..............169
Running a Query for iSeries query from the Query for iSeries menu ............169
Running a Query for iSeries query from the Exit This Query display ............169
Running a Query for iSeries query from the Work with Queries display ...........170
Running a Query for iSeries query using the RUNQRY command .............171
Chapter 16. Working with Query for iSeries query definitions ..............173
Changing a Query for iSeries query definition......................173
Starting changes by copying a Query for iSeries query definition .............173
Changes you can make to a Query for iSeries query ..................173
Considerations for changing Query for iSeries queries .................176
Copying a Query for iSeries query definition ......................177
Renaming a Query for iSeries query definition .....................178
Deleting a Query for iSeries query definition ......................178
Displaying a Query for iSeries query definition .....................179
Printing a Query for iSeries query definition ......................180
Information printed for a Query for iSeries query definition ................181
Example of printed record format information for a Query for iSeries query definition ......182
Part 3. Advanced information about Query for iSeries ............185
Chapter 17. Additional information about Query for iSeries for programmers ........187
Files with different record formats in Query for iSeries ..................187
File sharing considerations in Query for iSeries .....................187
Overriding database files in Query for iSeries......................187
DBCS considerations when defining result fields in Query for iSeries .............187
Joining files in Query for iSeries ..........................188
Using *ALL in Query for iSeries ..........................188
Using fields other than sort fields for report breaks in Query for iSeries ............188
Result field length and decimal positions in Query for iSeries ................188
Tips for dealing with presentation length and decimal positions in Query for iSeries.......188
Length and decimal positions used for internal numeric calculations in Query for iSeries .....189
Example: Increasing the decimal precision for result fields in Query for iSeries ........190
Selecting records (ignoring field case) in Query for iSeries ................191
Part 4. Appendixes .............................193
vi Query for iSeries Use V5R2
Appendix A. Differences between Query for iSeries and Query/36 ............195
Conceptual Differences ..............................195
Operational Differences ..............................195
Command differences between System/36 and Query for iSeries commands ..........197
Migration differences between System/36 and Query for iSeries definitions ...........197
Appendix B. Practice exercise for Query for iSeries query ...............199
Query for iSeries query exercise: Creating an IDDU definition................199
Query for iSeries query exercise: Creating a database file .................203
Query for iSeries query exercise: Entering data .....................203
Query for iSeries query exercise: Creating and running a query ...............206
Query for iSeries query exercise: Creating a query...................207
Query for iSeries query exercise: Changing a query ...................211
Query for iSeries query exercise: Creating an advanced query ...............215
Appendix C. Query for iSeries performance tips and techniques .............231
Introduction to Query for iSeries query processing ....................231
Keyed sequence access paths in Query for iSeries ..................231
Select/omit access paths in Query for iSeries.....................232
Access plans in Query for iSeries .........................232
File definitions and data in Query for iSeries ......................233
File definitions in Query for iSeries.........................233
File data considerations for Query for iSeries .....................234
Defining queries for Query for iSeries ........................234
Query for iSeries performance when designing or changing queries ............234
Define result fields in Query for iSeries .......................235
Select and sequence fields in Query for iSeries ....................235
Select records in Query for iSeries.........................235
Select sort fields in Query for iSeries ........................236
Select collating sequence in Query for iSeries ....................237
Specify report summary functions in Query for iSeries .................237
Select output type and output form in Query for iSeries .................237
Specify processing options in Query for iSeries ....................238
Using join operations in Query for iSeries .......................238
Performance tips for join operations in Query for iSeries.................239
Miscellaneous tips and techniques for Query for iSeries ..................240
Batch processing for Query for iSeries .......................240
Query for iSeries performance tuning ........................241
Query for iSeries migration considerations N to N-1 ..................241
Query for iSeries status messages..........................241
Query for iSeries debug mode messages .......................242
Appendix D. Preventing users from running Query for iSeries queries interactively......243
Appendix E. Coded character set identifiers (CCSIDs) in Query for iSeries .........245
CCSID marking in Query for iSeries .........................247
CCSIDs and collating sequences in Query for iSeries...................248
Query for iSeries language sequences .......................249
CCSID conversions for Query for iSeries options and functions ...............249
Displaying a Query for iSeries query ........................249
Changing a Query for iSeries query ........................250
Getting a list of objects with text in Query for iSeries ..................250
Defining a Query for iSeries query .........................250
Defining a collating sequence in Query for iSeries ...................250
Getting a list of formats or members with text in Query for iSeries .............251
Saving a Query for iSeries query definition......................251
Contents vii
Running a Query for iSeries query.........................251
Running a default query in Query for iSeries .....................251
Selecting records at run time in Query for iSeries ...................251
CCSIDs and Query for iSeries query definition items ...................252
CCSID and file selections in Query for iSeries ....................252
CCSID and join tests in Query for iSeries ......................252
CCSID and result field expressions in Query for iSeries .................252
CCSID and result field column headings in Query for iSeries ...............253
CCSID and sort fields in Query for iSeries ......................253
CCSID and record selection tests in Query for iSeries .................253
CCSID and summary functions in Query for iSeries ..................253
CCSID and column formatting and editing in Query for iSeries ..............253
CCSID and report breaks in Query for iSeries ....................253
CCSID and break and final text in Query for iSeries ..................253
CCSID and cover page, page headings and footings in Query for iSeries ..........253
CCSID compatibility considerations in Query for iSeries ..................254
Bibliography .................................259
Notices ...................................261
Trademarks ..................................262
Index ....................................263
viii Query for iSeries Use V5R2
About Query for iSeries Use
Query for iSeries is a decision support utility you can use to obtain information from the DB2 UDB for
iSeries. This book describes how to use Query for iSeries to select, arrange, and analyze information
stored in one or more database files to produce reports and other data files.
You may need to refer to other IBM books for more specific information about a particular topic.
For a list of publications related to this book, see the “Bibliography”.
Who should read the Query for iSeries Use book
This book is intended for people creating query reports and managing data on the iSeries system.
Before you use this book, you must be familiar with the introductory material for using the iSeries system.
You do not have to understand how to use a high-level programming language to use Query for iSeries.
© Copyright IBM Corp. 2000, 2002 ix
x Query for iSeries Use V5R2
Part 1. Introduction to Query for iSeries
Chapter 1. What is Query for iSeries? ........................3
Query for iSeries fundamentals ............................3
Files, fields, and record formats in Query for iSeries ...................3
Double-byte character set (DBCS) fields in Query for iSeries ...............4
UCS2 level 1 character set support in Query for iSeries .................4
Data definition languages and utilities support in Query for iSeries .............4
Query for iSeries definitions ............................5
Libraries in Query for iSeries ............................5
Major functions of Query for iSeries ..........................5
Chapter 2. General operating information for Query for iSeries ..............7
Practicing with Query for iSeries ...........................7
Getting started with Query for iSeries .........................7
When you are finished working with Query for iSeries ..................7
Telling Query for iSeries what you want .........................7
Using prompts and default values in Query for iSeries ..................7
Using Query for iSeries function keys ........................8
Checking the results before printing the Query for iSeries report..............8
Printing what you see on your Query for iSeries display .................9
Using lists in Query for iSeries ...........................9
Displaying a Query for iSeries list .........................9
Selecting items in a Query for iSeries list......................10
Special library names in Query for iSeries .....................10
Using Query for iSeries commands ..........................11
Using the Query Utilities menu............................11
Using the Work with Queries display .........................12
Choosing a single Query for iSeries task .......................12
Specifying a Query for iSeries query and its library ..................13
Choosing multiple Query for iSeries tasks ......................13
Working with a list of Query for iSeries queries ....................14
Selecting a Query for iSeries query name from a list .................14
Using Query for iSeries list subsets ........................14
Positioning a list of Query for iSeries queries ....................15
Using F11 to display additional information about Query for iSeries queries .........15
Selecting a library for your Query for iSeries queries ..................16
Other considerations when you are trying to locate or use a particular Query for iSeries query 17
Displaying the format of constants in Query for iSeries ...................17
Query for iSeries profile information ..........................18
Handling Query for iSeries messages and errors .....................18
Changing your Query for iSeries queries .......................18
© Copyright IBM Corp. 2000, 2002 1
2 Query for iSeries Use V5R2
Chapter 1. What is Query for iSeries?
Query for iSeries is an IBM®licensed program and a decision support utility that can be used to obtain
information from the DB2 Universal Database for iSeries database. It can obtain information from any
database files that have been defined on the system using Operating System/400 (OS/400
description specifications (DDS), the OS/400 interactive data definition utility (IDDU), or DB2 UDB for
iSeries Structured Query Language (SQL).
You use Query to select, arrange, and analyze information (data) stored in one or more database files to
produce reports and other data files. You can create your own query definitions and then run them, you
can run existing queries that you did not create, or you can even run a default query against a particular
database file (using an unnamed query). You determine what data the query is to retrieve, the format of
the report, and whether it should be displayed, printed, or sent to another database file.
You can use Query to obtain information from a single file or a combined set of up to 32 files. You can
select all the fields, or a few of the fields and organize them as you want them to appear in the type of
output chosen. You can have all records in the files included in the output, or you can select only a few to
be included, using record selection tests. These and other functions are described in detail later.
|
This chapter begins by introducing basic information about Query, and then it introduces all the major
|
tasks (such as creating, displaying, or running query) that can be done using Query.
Query for iSeries fundamentals
®
) data
Several elements on your system organize and store information, or data, so that you and other system
users can work with it to get the results that you need. The following topics introduce those elements, tell
you about them and how they relate to you and Query, and direct you to other publications where you can
find more information.
Files, fields, and record formats in Query for iSeries
Information, or data, is organized and stored on your system in various forms, primarily in objects called
database files (usually referred to as just files). A file contains individual units of information, called
records, that each contain related pieces of data. Each piece of information in a record is called a field,
and how the fields are organized is defined in a record format (often just called a format).
When you run a query to produce a report, Query uses the files, fields, and record formats to get the
information you want from the database, in the form of records, and uses those records to produce a
query report.
For example, an employee name and address file named NAMEADDR might contain a group of records that
identify one employee in each record (see Figure 1). Each record has several fields that contain the name
and address of an employee. The fields in each record might have names like NAME, STREETADDR, CITY,
STATE, and ZIPCODE. This order of the fields might be specified in a record format also named NAMEADDR.
NAME STREETADDR CITY STATE ZIPCODE
Susan P Gantner 907 Abbey Hwy Mushroom Manor OR 67891
Perry C Swenson 19821 Metro Hwy. St. Paul OR 67891
Matt F Thomas 961 S 19th Ave Piney Island OR 67890
Figure 1. Three Records in NAMEADDR File (Using Record Format NAMEADDR)
© Copyright IBM Corp. 2000, 2002 3
Another record format, named ZIPADDRESS, might be defined for a file that might be location-oriented and
contain only the ZIPCODE, STATE, CITY, and STREETADDR fields, in that order (see Figure 2).
ZIPCODE STATE CITY STREETADDR
67891 OR Mushroom Manor 907 Abbey Hwy
67891 OR St. Paul 19821 Metro Hwy.
67890 OR Piney Island 961 S 19th Ave
Figure 2. Three Records in NAMEADDR File (Using Record Format ZIPADDRESS)
Query retrieves the data you want from the files you choose. It uses certain fields (and record formats)
from those files to select, sort, calculate, and summarize that data in the form you want. It also produces
the query reports containing that data.
Double-byte character set (DBCS) fields in Query for iSeries
Some countries use pictographs or symbolic characters in their language. DBCS fields must be used for
such data. As a general rule, if your national language uses single-byte character set (SBCS) characters,
your files do not contain DBCS data. You can ignore any on-line help information that refers to DBCS data.
Notes:
1. To properly display DBCS data, you need a DBCS-capable display.
2. In Query, the following naming convention is used for DBCS data:
v Character data refers to both SBCS and DBCS character data.
v DBCS data refers to any type of DBCS data, including bracketed-DBCS and DBCS-graphic data
types.
v Bracketed DBCS refers to DBCS-open, DBCS-either, or DBCS-only data types.
UCS2 level 1 character set support in Query for iSeries
UCS2 Level 1 is a 16-bit encoding for graphic characters. When doing business in a worldwide
environment you need the ability to enter and process data from more than one national language. For
example, a list of customer names may contain German, Greek, English, and Thai characters that must be
printed or displayed on the same device at the same time.
™
Query for iSeries
treats UCS2-graphic data the same as GRAPHIC or VARGRAPHIC data. A
UCS2-graphic field is a DBCS-graphic field tagged with a UCS2 CCSID.
The VARCHAR and VARGRAPHIC functions help you write queries that include UCS2 data.
Data definition languages and utilities support in Query for iSeries
Query can query data in files that are created using different data definition languages or products.
Although the description given above applies to all the files on your system, the names or concepts that
might be used depend on the programming language or product (like IDDU, DDS, and DB2 UDB for
iSeries) that is used to define the files.
|
If you are not a programmer and you want to create a file that you can query from Query, you may want to
|
use IDDU to create the file. If a programmer can create the file for you, he might use IDDU, DDS and
|
control language (CL), or the DB2 UDB for iSeries program to create the file. Consider the following:
|
v IDDU is a menu-driven utility used to define files, fields, and record formats, to store all those definitions
|
in a data dictionary, and to create the files so they can be used to store data. A file defined using IDDU
|
can have more than one format. For more information about IDDU, see the IDDU Use book.
|
v The data file utility (DFU) is used to add, change, and delete data in a database file. You can use DFU
|
directly, or you can use some of its function through the Enter data option of the IDDU Work with
|
Database Files display. For more information about DFU, see the ADTS/400: Data File Utility book.
4 Query for iSeries Use V5R2
v CL and DDS use the same terms and descriptions as IDDU, but they provide additional support for files.
|
Using DDS and CL commands, you can define and create physical files and logical files to indicate how
|
fields are to be organized in files.
|
A physical file contains the fields of data, as records, but logical files do not. A physical file contains at
|
least one record format. The field order in a physical file determines the format of the records.
|
A logical file gives a different view of the data stored in one physical file or in several physical files. A
|
logical file does not contain data. That is, a logical file lets you see information in records that are stored
|
in physical files as though the records actually existed that way. This is accomplished without having to
|
duplicate and store that data on the system in that logical view (thus, the name logical file). A logical file
|
can use a subset of the fields in one physical file, a composite of all or some of the fields in several
|
physical files, or even a mixture of fields from physical and other logical files. Join logical files can have
|
only one record format, but nonjoin logical files can have more than one record format.
|
For more information about physical files, logical files, CL, and DDS, refer to the CL Programming
|
book.,
|
v The DB2 UDB for iSeries program uses a relational model of data; that is, all data is perceived as
|
existing in tables. On the iSeries system, DB2 UDB for iSeries objects are created and maintained as
|
OS/400 objects. The following table shows the relationship between OS/400 terms and DB2 UDB for
|
iSeries relational database terms:
|
|
OS/400 Term DB2 UDB for iSeries Term
||
Library Collection. Consists of a library, journal, journal receiver, data dictionary, and DB2 UDB for
||
|
|
Physical file Table. A set of columns and rows.
||
Record Row. The horizontal part of a table containing a serial collection of columns.
||
Field Column. The vertical part of a table of one data type.
||
Logical file View. A subset of columns and rows of one or more tables.
||
|
iSeries catalog. A collection groups related objects and allows the user to find the objects by
name.
Query for iSeries definitions
|
You use a query to get information from database files to produce a report. The file or files that a query
uses to get the information and what is to be done with that information are defined and stored in a query
definition. A query definition is an object (with type *QRYDFN) and it contains all the details that Query
needs to find and use the files in the way you specify, and to produce the results that you expect.
To create a query definition, you follow a sequence of displays that guide you through the process of
defining and saving a query definition. Once you have defined your query (and even while you are defining
your query), you can run it (as a query) to produce the reports that you need.
Libraries in Query for iSeries
A library is a place on the system to store objects, including the query definitions and files that you use in
Query for iSeries. Therefore, when you are working with queries and files, you may need to specify the
library where a query or file is stored.
Major functions of Query for iSeries
Two primary Query displays, the Query menu and the Work with Queries display, are the starting point for
the major tasks that you can do using Query. (See Figure 3 on page 6.) Another important display is the
Define the Query display, which starts all the tasks in a query definition.
v The Query menu allows you to start working with queries, run a query, or delete a query. It also allows
|
you to start working with files.
|
v The Work with Queries display allows you to do specific tasks with one or more queries. You can
|
create, change, display, copy, delete, or run a query, or print a query definition.
|
Chapter 1. What is Query for iSeries? 5
Figure 3. Major Tasks on the Query Menu and the Work with Queries Display
6 Query for iSeries Use V5R2
Chapter 2. General operating information for Query for iSeries
This chapter describes the general operating information for Query, such as getting started with Query,
using prompts and lists, how you can check your query definition before it is finished, how to use the
function keys, and error recovery procedures.
Practicing with Query for iSeries
Appendix B, “Practice exercise for Query for iSeries query”, provides practice examples that you can do in
order to quickly become familiar with Query.
Getting started with Query for iSeries
You can access Query in a variety of ways. To use Query, you can do any of the following:
v Use the Work With Query (WRKQRY) command by typing WRKQRY and pressing the Enter key. This
|
causes the Work with Queries display to appear.
|
v Use the Start Query (STRQRY) command by typing STRQRY and pressing the Enter key. This causes the
|
Query menu to appear. Typing GO QUERY and pressing the Enter key has the same effect.
|
From the Query menu, you can choose to work with queries, run a query, delete a query, work with files,
or do an office-related task.
From the Work with Queries display you can:
v Create, change, copy, delete, display, print, or run a query if you came from the Query menu or the
WRKQRY command.
v Create, change, copy, delete, or display a query definition.
|
v Create, change, copy, delete, or display a query definition.
|
When you are finished working with Query for iSeries
When you are finished working with Query, you can exit from the Work with Queries display by pressing
either F3 (Exit) or F12 (Cancel).
Telling Query for iSeries what you want
When you work with a query, Query shows you a series of displays that ask (or prompt) you for:
v What information you want Query to get from a file or files
v Whether you want the information printed or displayed as a report or output as data in another file
v How you want the report to look
Query gathers all of this information from what you enter on the displays.
Using prompts and default values in Query for iSeries
Prompts are the system’s way of asking you what it needs to know in order to fulfill your request. You
need only “fill in the blank.” Often, the prompt even lists the possible correct choices, so you only need to
type the choice that fits what you are doing. If you are not sure what information you are supposed to
supply at a prompt, you can move the cursor to the prompt and press the Help key.
Also, when a display appears, some of the prompts are already filled in. These values are called default
values—they are the values assumed by the system if you do not specify anything yourself. You can leave
a default value the way it is, or if you want, you can change it to another value by moving the cursor to the
prompt and typing over the default value.
© Copyright IBM Corp. 2000, 2002 7
Using Query for iSeries function keys
Function keys like Enter, Help, and Print can be used at any time with any query display. However, not all
the function keys (such as F3, F4, and F11) apply to all displays. Each display shows, at the bottom of the
display, which function keys are commonly used with that display (all the available keys may not be
shown). If you want to know how all the available keys work on a particular display, move the cursor to the
function key section of that display and press the Help key.
Checking the results before printing the Query for iSeries report
Function keys F5 (Report) and F13 (Layout) can be used to check the results of your work at many points
in the query definition process. That is, during query definition, you can use these keys to see if you are
getting the output results you expected. If so, you can continue on with confidence; if not, you can make
whatever changes are necessary to get what you want before you move on to the next definition step. In
some situations, looking at the layout may be more useful than looking at the report, and vice versa (for
example, F13 can be used when there is no data in a file that is used by the query).
Before printing a query report, remember the following:
v F5 (Report) uses all the information you have defined so far in this and previous definition steps to run
the query and display the results. The results are shown on the Display Report display in report form.
Note: If Query finds errors in numeric fields when your query is run, an error message might be
displayed or the field value might be shown in the output as plus signs (++++). Plus signs are
also used to indicate division by zero and overflow (when the overflow occurs as data is being
taken from the file). You can specify that Query ignore any numeric field errors—see “Ignoring
decimal data errors during Query for iSeries processing” on page 164 for more information.
Asterisks (*****) are shown if the field length and/or number of decimal positions was changed
on the Specify Report Column Formatting display to a size too small for the value to be shown.
When the query is run, if Query finds conversion errors either in the date, time, or timestamp
fields, or in character fields due to the coded character set identifier (CCSID), plus signs (++++)
are shown in the output. Plus signs are also shown if the data is not good or overflows.
Field, break text, minimum, maximum, average, and total values can be null and are represented by a
dash (-) on the display.
v F13 (Layout) uses all the information that you have defined so far to show the column heading and field
layout without actually running the report.
On the Display Report Layout display, Xs show SBCS character data and 9s show numeric data. The
current date, time, or timestamp shows where date, time, or timestamp data is.
If floating-point fields are present:
v 9‘s and 0’s, followed by E +029, represent single-precision data.
v 9‘s and 0’s, followed by E +299, represent double-precision data.
v You may also see 1.00 for a floating-point field if the data is smaller than the size selected by Query
(which is a length of four with two decimal positions).
If you are using a DBCS-capable display and if DBCS fields are present, you may see:
v Double-byte J’s, which represent DBCS-only data. DBCS-only fields contain only DBCS data.
v Double-byte O’s, which represent DBCS-open (mixed) data. DBCS-open (mixed) fields contain DBCS
data, SBCS data, or a mixture of both types of data.
v Double-byte E’s, which represent DBCS-either data. DBCS-either fields contain either all SBCS data or
all DBCS data, but not both in the same field.
v Double-byte G’s, which represent DBCS-graphic data. DBCS-graphic fields contain only DBCS-graphic
data.
8 Query for iSeries Use V5R2
DBCS characters take twice the amount of space of SBCS characters. In addition, a shift-out character
precedes DBCS data, and a shift-in character follows the data. The shift-out and shift-in characters are
called DBCS bracket characters. DBCS-graphic data is stored in the database without bracket characters;
however, the bracket characters are present when DBCS-graphic data is displayed or printed.
Although you must use a DBCS display to type or read data contained in DBCS fields, you do not need to
use a DBCS display to display the layout of a report that uses DBCS fields. However, the layout may not
be readable.
You can look at the displayed results and determine whether the spacing between columns, the length of
each field, and the column headings produce the results you want.
Note: The displayed report or report layout contains all the entries or changes that you have made this
far, including those typed just before you pressed F5 or F13.
Printing what you see on your Query for iSeries display
You can use the Print key to print a copy of what you see on any Query display at any time. You may find
this useful during query definition, for example, when you type many values in the fields on a display, such
as on the Define Result Fields display or on the Select Records display. The printed copy of the displayed
information is printed on the printer that is associated with your display station.
You can also use the Print key, when needed, to print the displayed output of the function keys F5
(Report) and F13 (Layout). However, only what is shown on the display is printed. If your report or layout
is wider than the display and you have not specified that line wrapping be used, the far right side of the
report is cut off and is not printed. In this case, you could use the shift function keys to move text lines to
the right and then press the Print key again to obtain a printout of the remainder (or possibly just more) of
the report.
Using lists in Query for iSeries
For many displays, Query provides lists of items that can be used to complete the prompts. Those items
might include:
v Libraries you are authorized to use
v Queries you are authorized to use
v Files you are authorized to use
v Record formats for a specified file
v Members for a specified file
v Fields available for your query
v Translation tables
The lists eliminate the need to memorize names, and they also reduce the number of potential typing
errors.
Displaying a Query for iSeries list
You can obtain a list of an item (for example, a list of files or a list of libraries) by moving the cursor to the
prompt and pressing F4 (Prompt) so that the list appears. Note that you cannot display a list for just any
item on a display; the items for which lists are available are signified by a comment next to the prompt that
you can use F4 to obtain a list.
You could also obtain a particular subset of a list of items by typing a generic name or a special library
name in the prompt or prompts before you press F4. (For more information on special library names, see
“Special library names in Query for iSeries” on page 10.) Typing a generic name (such as ABC*)ina
prompt that allows generic names limits the items in the list by choosing only the items that begin with
those characters (ABC).
Chapter 2. General operating information for Query for iSeries 9
The number of items shown in the list part of the display varies, depending on what form the list is in and
the amount of space used by the prompt part of the display. Sometimes a new display will appear that
shows the list. If all items in a list do not fit on the display, you can use the page keys to page forward and
backward through the list.
Selecting items in a Query for iSeries list
You can select an item from a list by doing one of the following:
v Typing the name and an option number in the first position in the list.
v Selecting an item directly from the list by typing a number next to the name in the list. By selecting an
item directly from the list by typing a number next to it, you often can select more than one item and
thus save a lot of time and typing.
You may be able to display, from a list, a long comment about a file, a record format, a file member, or a
field. Long comments may provide extensive information about their content. If a long comment is defined
for a file, a record format, a file member, or a field, you can move the cursor to its name and press F23 to
display the long comment.
Note: If the field is a result field, the entire expression defining the result field is shown as a long
comment.
Special library names in Query for iSeries
You can specify any of the following special library names to search the associated libraries for query
definitions, files, or translation tables:
Special Library Name
Description
*CURLIB The current library being used for your job. It is the only library searched. If no current
library is found, QGPL is searched.
*LIBL All the libraries in the library list for your job. They are searched in the order they are
listed.
*USRLIBL All the user libraries that are in the user part of the library list for your job. They are
searched in the order they are listed.
*ALLUSR All the user libraries on the system that you have the authority to use and other libraries
that you have authority to use whose names begin with the letter Q. *ALLUSR does not
list certain System/36
in alphabetical order.
*ALL All the libraries on the system that you have the authority to use. They are searched in
alphabetical order.
GENERIC* All libraries starting with characters that precede an asterisk that you have authority to use
are searched. For example, you can specify STA* to search all libraries starting with STA.
If you specify *CURLIB in the Library prompt, Query changes it to the name of the library currently being
used in your job. That library name is displayed in place of *CURLIB.
Note: You can use only those libraries for which you have the necessary authority. If you specify a special
library name that contains libraries you do not have authority for, you will see queries in only those
libraries in that list for which you do have the authority.
™
libraries that begin with the # character. The libraries are searched
10 Query for iSeries Use V5R2
Using Query for iSeries commands
A command is a statement used to request a function of the system. This means you need only
remember a command that is a few characters long instead of remembering all the individual instructions
or taking the time to go through a series of menus.
Query has four commands, which can be entered from any command line:
v STRQRY brings up the Query menu.
v WRKQRY brings up the Work with Queries display.
v Run Query (RUNQRY) runs an existing query, or you can use it to run a default query. This command
can also be embedded in CL programs so that you could, for example, run several queries overnight.
v Delete Query (DLTQRY) deletes one or several query definitions.
For detailed information about how you can use these commands, see the CL Reference information in
the iSeries Information Center.
Using the Query Utilities menu
The Query Utilities menu allows you to select Query tasks for working with queries, running queries that
have previously been defined, deleting query definitions, or working with files.
QUERY Query Utilities
Select one of the following:
Query for iSeries
1. Work with queries
2. Run an existing query
3. Delete a query
SQL/400
10. Start SQL/400 Query Manager
Query management
20. Work with query management forms
21. Work with query management queries
22. Start a query
23. Analyze a Query for iSeries definition
Selection or command
===>
F3=EXIT F4=PROMPT F9=RETRIEVE F12=CANCEL F13=USER SUPPORT
F16=OS/400 MAIN MENU
(C) COPYRIGHT IBM CORP. 1980, 1991.
More...
If you select option 1 (Work with queries) and press the Enter key, the Work with Queries display is
shown. On that display, you can specify which query you want to work with and in what way. The tasks
that can be started using that display (including additional ways for running and deleting queries) are
introduced in “Using the Work with Queries display” on page 12.
If you select option 2 (Run an existing query) on the Query Utilities menu, you are shown the prompt
displays for the Run Query (RUNQRY) command. You can use this command to run a query and produce
a query report of the data selected from one or more database files. You can use this command to run an
existing query (one that is defined and stored by name on the system) or to run a “default” query (one that
is unnamed and that uses mostly system-supplied values). If you do not know the name of a query or
library, use option 1 (Work with queries) so that you can get a list of queries and libraries.
Chapter 2. General operating information for Query for iSeries 11
If you select option 3 (Delete a query) on the Query Utilities menu, you are shown the prompt display for
the Delete Query (DLTQRY) command. You can use this command to delete a query definition. If you do
not know the name of a query or library, use option 1 (Work with queries) so that you can get a list of
queries and libraries.
There are other tasks that you can do that are not part of Query but may be related to it or may be
convenient to get to from the Query Utilities menu:
v If you select option 30 to work with files, the Files (FILE) menu is displayed. From that menu, you can
do a file-related task (display, copy, delete, save, restore, or edit) or you can use a different file-related
utility such as IDDU or DFU, or a licensed program, such as the DB2 UDB for iSeries program. For
example, you can use IDDU to define and create files, and you can use either IDDU or DFU to type
data into the files.
v If you select option 31 to do an office-related task, the Office Tasks (OFCTSK) menu is displayed. From
that menu, you can choose to work with documents or folders, with office security, or with OfficeVision
or Client Access.
Using the Work with Queries display
The Work with Queries display is the major starting point for working with one or more queries at a time.
Using this display, you can select (and, therefore, start) one or more of the following primary tasks:
|
v Create a new query definition.
|
v Change, copy, display, or delete an existing query definition.
|
v Print the definition of a query.
|
v Run a query to select data from files and produce a report using that data.
Part 2 of this book describes these primary Query tasks. Creating query definitions is described in
Chapters 3 through 14, running queries is described in Chapter 15, and the remaining tasks (changing,
copying, displaying, printing, and deleting query definitions) are described in Chapter 16.
Choosing a single Query for iSeries task
To work with a query, you need to determine which query you want to work with, and you need to select
the task you want to do. To select a task, you can type the number of the task you want to do in the
Option prompt on the Work with Queries display, or if a list of query names is shown, you can type the
number of the task in the Opt column (as shown in Figure 4 on page 13). These are the tasks that you can
choose from:
Query Task Description of Task
1=Create Creates (defines) a new query. When this option is processed, the Define the Query
display is shown so you can start defining a query.
2=Change Changes an existing query definition. The Define the Query display is shown for this
option also.
3=Copy Copies an existing query definition. The Copy Queries display is shown.
4=Delete Deletes an existing query definition. The Confirm Delete of Queries display is shown.
5=Display Shows the definition of a query without being able to change it. (To change the query,
choose option 2.) The Define the Query display is shown.
6=Print definition
|
|
Prints a query definition. For more information, see “Printing a Query for iSeries query
definition” on page 180.
8=Run in batch
|
|
Runs a query in batch, reducing the resource drain caused by running the query
interactively.
12 Query for iSeries Use V5R2
9=Run Runs a query. Query displays a report, prints a report, or puts the data into a database
|
|
|
file, depending on what is specified in the query definition. For information about running
queries, see Chapter 15.
Specifying a Query for iSeries query and its library
To identify the query you want to work with, you can type the name of the query (in the Query prompt) and
the name of the library (in the Library prompt) in which it is to be stored (or is already stored). Or you can
look at a list of query names or library names and then select the name you want— see “Choosing
multiple Query for iSeries tasks”.
If you specify the name of a query, Query assumes that it already exists and searches for it in the library
specified in the Library prompt. If you specified a special library name (such as *LIBL) or a generic library
name (such as ABC*), the first query found with the name you specified is used; libraries are searched in
alphabetical order (except for *LIBL and *USRLIBL).
If you are creating a query, you do not need to name it unless you want to save it for later use. For more
information on creating queries, see Chapter 3, “Creating a Query for iSeries query definition”.
Choosing multiple Query for iSeries tasks
If you position the cursor on the Query prompt and press F4 (Prompt) to show a list, the list contains the
names of all the queries that are in the library (or libraries) indicated by the Library prompt. Only the
queries and libraries for which you have the necessary authority are shown. The following display shows
some sample query names.
Work with Queries
Library........ QGPL Name, *LIBL, F4 for list
Subset ........ __________ Name, generic*
Position to...... __________ Starting character(s)
Type options (and Query), press Enter.
1=Create 2=Change 3=Copy 4=Delete 5=Display 6=Print
8=Run in batch 9=Run
Opt Query Opt Query
_ __________
_ ACCTPAYFEB _ PAYROLLMAR
_ ACCTPAYMAR
_ ACCTRECFEB
_ ACCTRECMAR
_ INTFEB
_ INTMAR
_ INVFEB
_ INVMAR
_ PAYROLLFEB
F3=Exit F4=Prompt F5=Refresh F11=Display text
F12=Cancel F19=Next group
Figure 4. Work with Queries Display (Multiple-Column List)
You can type any option number (except a 1) beside the name of each query that you want to select from
|
the list. If selecting multiple queries, you can select and mix the options according to the types of work you
|
need to do. For example, if you need to run a query, delete another query, and copy a third query, you can
|
page through the list looking for the query names and select all three options on this display at one time.
|
You can also type an option number (including 1) and a query name in the top position of the list as well.
|
Chapter 2. General operating information for Query for iSeries 13
You can select as many as 30 queries from the list, as well as type a query name and option in the first
list position of this display. Query processes the requests in the order that they appear in the list, starting
first with the option and query that you typed in the first list position (in the input fields of the Option and
Query columns).
When you select multiple options and the list contains multiple columns of names only (such as in Figure 4
on page 13), the processing order of the columns is top to bottom and left to right. That is, Query
processes all the selected queries in the leftmost column first, then the second column, and so on.
(However, if the selected options include queries that are to be copied or deleted, Copy Queries or
Confirm Delete of Queries displays are shown when the first query with one of those options is found.
Then Query groups together all others with the same option, by library, for you to verify what you want
done.)
Working with a list of Query for iSeries queries
When you work with a list (in this case, a list of queries), Query provides several functions to help you.
Here are some possibilities:
v To see all the queries in a different library or group of libraries, you can press the Enter key after
changing the value in the Library prompt and typing blanks or an asterisk (*) in the Subset prompt. You
are shown all the queries that you have the authority to use that exist in that library or group of libraries.
(You indicate which libraries are to be checked by specifying a library name, a special library name
[such as *LIBL], or a generic library name [in the form of ABC*]intheLibrary prompt.)
v To see a particular subset (a smaller group) of query names, you can type a generic name (in the form
of ABC*)intheSubset prompt and press the Enter key. If you know at least the starting characters in the
name you are looking for, this function can reduce the time needed to locate it.
v To see if more than one library contains a specific query name, you can type that query name in the
Subset prompt and type *ALL in the Library prompt before you press the Enter key. You are shown all
the queries by that name for which you have the authority to use.
v To position the list to a specific name, type the name (or the starting characters in the name) in the
Position to prompt and press the Enter key. This function can also reduce the time needed to locate a
name.
v You can also use a combination of these things at the same time. For example, you could specify a
different library name, generic library name, or special library name in the Library prompt, a subset
value (of query names) in the Subset prompt, a specific query name or the starting characters of a
query name in the Position to prompt, and then press the Enter key to show the desired subset list of
query names.
These functions are described in the following topics.
Selecting a Query for iSeries query name from a list
If you are working with a list of queries, you can select a query by using one of the following methods:
v You can use the top position in the list to type the name of a query (and library name, if the Library
column is shown) you want to create or use. (To select an existing query, it must be available for
use—see “Other considerations when you are trying to locate or use a particular Query for iSeries
query” on page 17.)
If you are creating a query, you can check this list to see what names are already used before you type
a new name. Then, type the new name in the first list position (in its input field) and type a 1 next to it.
v You can work with a query (or queries) in the list by typing an option next to the query(s). (To select a
query, it must be available for use—see “Other considerations when you are trying to locate or use a
particular Query for iSeries query” on page 17.)
Using Query for iSeries list subsets
You can use the Subset prompt to see a smaller group (subset) of query names. To do so, type the
starting characters (in the form of ABC*) that identify the group of queries that you want to display, and
press the Enter key. All the queries whose names start with those characters in the specified library or
14 Query for iSeries Use V5R2
library group are shown. For example, if you typed IN* in the Subset prompt, you might see a list of query
names like: INTEREST, INTFEB, INTMARCH, INVENTORY, INVFEB, INVMARCH, and so on. (The queries are
shown alphabetically for each library.)
You can also specify a complete name (without an *) in the Subset prompt. Continuing the previous
example, if you want to see which libraries contain queries named INVENTORY, type that complete query
name in the Subset prompt.
If you want to return to the full list previously shown, blank out the subset value (or type an * as the subset
value) and press the Enter key again.
Positioning a list of Query for iSeries queries
When a list of query names is shown, they are shown in alphabetical order within each library.
You can use the special values *TOP or *BOT to position to the top or bottom of the list. If it is a long list,
you may want to move (change the position within) the list so that it starts with a particular query name.
You can do this by typing one of the following in the Position to prompt:
v All of the characters in an existing query name
v One or more of the starting characters in the name
When you press the Enter key, Query moves the list so that the fully specified name (or, in a list for a
specific library, the first name with the specified starting characters) is now shown at the top of the list.
If there is no Library column present and if there is no query name in the list that starts with those
characters, Query moves the list to the name closest to, and in front of, the position that the name would
have been in. However, if there is a Library column present and there is no query name that exactly
matches the value in the Position to prompt, the list is not repositioned.
Two other methods are also available to move through a long list of queries:
v You can use the page up and page down keys to go forward or backward through the list.
v You can use F19 (Next group) to advance to the start of the next library’s list of query names.
Using F11 to display additional information about Query for iSeries queries
When a list is displayed, you can use F11 (Display text) to alternate between showing a list of just the item
names (such as queries, fields, and so on) and showing a list of both the item’s name and the text that
describes each item. When you press F11, the text—if any was specified when the item was created or
changed—is shown next to each item name in the list. Some additional information may also be shown;
for example, when you display the text for a list of queries, the date that each of the queries was created
or last changed is also displayed.
Following is an example of how the Work with Queries display might appear when F11 is used.
Chapter 2. General operating information for Query for iSeries 15
Work with Queries
Library........ QGPL Name, *LIBL, F4 for list
Subset ........ __________ Name, generic*
Position to...... __________ Starting character(s)
Type options (and Query), press Enter.
1=Create 2=Change 3=Copy 4=Delete 5=Display 6=Print
8=Run in batch 9=Run
Opt Query Text Changed
_ __________
_ ACCTPAYFEB Accounts payable for February 02-29-88
_ ACCTPAYMAR Accounts payable for March 03-31-88
_ ACCTRECFEB Accounts receivable for February 02-29-88
_ ACCTRECMAR Accounts receivable for March 03-31-88
_ INTFEB Customer acct interest accumulated - Feb. 02-29-88
_ INTMAR Customer acct interest accumulated - March 03-31-88
_ INVFEB Ending inventory for February 02-29-88
_ INVMAR Ending inventory for March 03-31-88
_ PAYROLLFEB Total payroll, by day, for February 02-29-88
F3=Exit F4=Prompt F5=Refresh F11=Display names only
F12=Cancel F19=Next group
More
After you press F11, all lists on all the Query displays that use F11 are shown in the form you chose until
you press F11 again.
Selecting a library for your Query for iSeries queries
The library name indicates where you want to store a new query or where an existing query is already
stored. (If you are creating a query, you do not need to specify a library name if you do not intend to save
the query.) Also, if you decide later during query definition that you do want to save the query, you are
given another opportunity to specify (a query name and) a library name at the end of definition time.
If this is the first time you are using Query, your current library is supplied as the library name. If you do
not have a current library, QGPL is used. When you specify a different library name, a special library
name, or a generic library name, Query supplies that name the next time you use this display. You can
use the same value each time, or you can change it to a different library or library group. (See “Query for
iSeries profile information” on page 18 for more information.)
To look at or use a different library, or a list of libraries from which you can choose, do either of the
following:
v Type the name of the library, a generic library name, or a special library name in the Library prompt and
press the Enter key. A list of all the queries in that library or group of libraries for which you have the
authority to use is shown. If there are multiple libraries, the queries are listed in alphabetical order within
each library. The libraries themselves are shown in alphabetical order if *ALL, *ALLUSR, or a generic
library name is specified. If you specify *LIBL or *USRLIBL, the libraries are shown in the order that they
are listed in the library list.
If you specify a library name or special value that contains no query definitions (or none that you have
authority to use), a message is displayed indicating there were no queries.
If the list of queries is not empty but one of the libraries in the group is being used or is damaged, a
message is displayed indicating that the list may be incomplete.
v Move the cursor to the Library prompt and either leave the prompt blank or type a generic name or a
special library name, then press F4 to list the libraries. A list of the libraries that you are authorized to
use is shown.
16 Query for iSeries Use V5R2
Other considerations when you are trying to locate or use a particular Query for iSeries query
You should be aware of other considerations when you are trying to locate or use a particular query. To
use a query:
v You must have the authority needed to use the query definition. You may need to either ask the owner
of the query, the administrator, or security officer to give you the type of authority that is necessary. For
more information about the types of authority, see “Giving authority to others to your Query for iSeries
query” on page 168.
v You must have the authority needed to use the library containing the query definition.
v The query (or the library) must not be in use in such a way that it cannot be shared. For example, if the
owner is making changes to the definition of the query or if the system operator is saving the library that
contains the query, you may need to wait a short time before you can use the query. If the Text and
Changed columns are blank in a query list with the text displayed, the query is being used.
Displaying the format of constants in Query for iSeries
If you create or change a query and specify a date or time constant in an OS/400 format or a numeric
constant with a decimal separator, that format must match the format description or separator shown on
the Display Constants Format display. To check what OS/400 format or separator you must use, press F17
from either the Select Records display or the Define Result Fields display.
Note: If you are sending queries between countries that use the comma for a decimal separator, put a
blank after each comma separating arguments in a function, such as SUBSTR or VALUE.
Display Constants Format
Query......: QRY1 Option .....: CHANGE
Library....: QGPL CCSID......: 65535
Use an SAA format to enter a date or time constant, or
use the format described by the following values.
Use the decimal separator shown.
Query definition values
Date format....: *MDY
Date separator . . : /
Time format....: *HMS
Time separator . . : :
Decimal separator . : .
Press Enter to continue.
F12=CANCEL
The Display Constants Format display appears when you:
v Change a query from the Work with Queries display and either at least one date or time constant is
specified in the query definition in an OS/400 format other than the date or time format specified for
your job.
v Display a query from the Work with Queries display and your job format for the date or time is different
from an OS/400 date or time format constant that is specified in the query definition.
v Use the run-time record selection option of the RUNQRY command and your job format for the date
or time is different from an OS/400 date or time format constant specified in the query definition.
v Use the run-time record selection option with the QRYRUN procedure and your job format for the date
or time is different from an OS/400 date or time format constant specified in the query definition.
Chapter 2. General operating information for Query for iSeries 17
v Change or display a query from the Work with Queries display or use the run-time record selection
option of the RUNQRY command, and the query was defined with a numeric constant and the decimal
separator saved with the query does not match your current decimal separator.
v Press F17 from the Select Records display.
v Press F17 from the Define Result Fields display.
OS/400 date formats are MDY, YMD, DMY, and JUL. The valid OS/400 time format is HHMMSS.
Query for iSeries profile information
Query creates and maintains a query profile for each user. The profile is automatically created the first
time you use the Query utility program, and its values are updated automatically whenever you specify
certain values or press certain keys. You do not have to worry about creating or changing this profile.
You may notice that some prompts on some of the displays are already filled in when the display is first
presented to you. These values may have been obtained from your profile, because Query uses it to make
your definition tasks easier by providing certain information at the appropriate time.
The values that are stored in your Query profile are:
v The library you last used on the Work with Queries display
v The library you last used to specify your first file selection on the Specify File Selections display
v The library you last used to specify where database file output was to be stored
v Your list column mode (whether displays are to show names and text or just names only)
v Your report/layout display width (80 or 132)
v The current collating sequence option
v Your collating sequence and coded character set identifier (CCSID)
v The collating sequence table and library name
Handling Query for iSeries messages and errors
You should not worry about making errors while working with Query. Query either prompts you for needed
input or issues an error message. You can correct your errors by responding to either of these. If you want
to obtain help for any message that Query issues to you, move the cursor to the message line and press
the Help key or F1.
Some of the things that may go wrong are:
v You specify a value that is not listed as a possible choice.
v You are not authorized to use a query, file, or table that you specified, or a file is currently being used.
v You change your mind after entering something.
Changing your Query for iSeries queries
Whether you change your mind about something you have already entered because it was a mistake or
because you thought of a better way to get what you want, you will find it is very easy to change things in
Query.
If you are still creating or changing your query, you can go back to previous displays by using F12. If you
press F12 on a display, all of the new entries or changes you made on that display are ignored. Pressing
F10 on a display keeps all of your new entries or changes and shows you the previous display. You can
also return to the Define the Query display and choose the option(s) that you want to change or add.
If you define and save the query before you notice that the results are not what you want, just choose
option 2 (Change) on the Work with Queries display and change the query. When changing a query, you
18 Query for iSeries Use V5R2
Loading...