Main
Page
Page
Page
Contents
Part 1. Introduction to Query for iSeries ....................1
Part 2. Defining and using Query for iSeries query definitions .........21
iv
Page
Part 3. Advanced information about Query for iSeries ............185
Part 4. Appendixes .............................193
vi
Page
viii
About Query for iSeries Use
Who should read the Query for iSeries Use book
Page
Part 1. Introduction to Query for iSeries
Page
Chapter 1. What is Query for iSeries?
Query for iSeries fundamentals
Files, fields, and record formats in Query for iSeries
Double-byte character set (DBCS) fields in Query for iSeries
UCS2 level 1 character set support in Query for iSeries
Data definition languages and utilities support in Query for iSeries
4
Query for iSeries definitions
Libraries in Query for iSeries
Major functions of Query for iSeries
Page
Chapter 2. General operating information for Query for iSeries
Practicing with Query for iSeries
Getting started with Query for iSeries
When you are finished working with Query for iSeries
Telling Query for iSeries what you want
Using Query for iSeries function keys
Checking the results before printing the Query for iSeries report
8
Printing what you see on your Query for iSeries display
Using lists in Query for iSeries
Displaying a Query for iSeries list
Selecting items in a Query for iSeries list
Special library names in Query for iSeries
10
Using Query for iSeries commands
Using the Query Utilities menu
Using the Work with Queries display
Choosing a single Query for iSeries task
12
Specifying a Query for iSeries query and its library
Choosing multiple Query for iSeries tasks
Working with a list of Query for iSeries queries
Selecting a Query for iSeries query name from a list
14
Using Query for iSeries list subsets
Positioning a list of Query for iSeries queries
Using F11 to display additional information about Query for iSeries queries
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
Displaying the format of constants in Query for iSeries
Query for iSeries profile information
Handling Query for iSeries messages and errors
18
Changing your Query for iSeries queries
Page
Page
Part 2. Defining and using Query for iSeries query definitions
22
Page
24
Page
Page
Chapter 3. Creating a Query for iSeries query definition
Starting Query for iSeries query definition
Selecting definition steps when defining a Query for iSeries query
28
Selecting options for a Query for iSeries query definition
Moving through the Query for iSeries definition displays
When you return to the Query for iSeries Define the Query display
30
Considerations for creating Query for iSeries queries
Selecting files, records, and fields for Query for iSeries
Suggested sequence of tasks for creating Query for iSeries queries
Page
Chapter 4. Specifying and selecting files for a Query for iSeries query
Specifying file selections for a Query for iSeries query
Choosing a file for a Query for iSeries query
Choosing a library for a Query for iSeries query
34
Choosing a member or record format for a Query for iSeries query
Selecting multiple files for a Query for iSeries query
Using file IDs for a Query for iSeries query
Confirming your options for a Query for iSeries query
36
Removing a option for a Query for iSeries query
Handling errors on the Query for iSeries Specify File Selections display
Selecting files on the Query for iSeries Select File display
38
Selecting file members on the Query for iSeries Select Member display
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
Joining files in a Query for iSeries query
42
Types of joins in a Query for iSeries query
How to join files in a Query for iSeries query
Rules for joining files in a Query for iSeries query
44
Page
Examples of joining files in a Query for iSeries query
46
Example: Selecting matched records from all selected files in a Query for iSeries query
Example: Selecting matched records using a primary file in a Query for iSeries query
Example: Selecting unmatched primary file records in a Query for iSeries query
48
Sequencing secondary files for a primary join in a Query for iSeries query
Example: Sequencing secondary files in a Query for iSeries query
50
Page
Page
Displaying all join tests in a Query for iSeries query
Handling missing fields in a Query for iSeries query
54
Handling missing fields during file selection process of a Query for iSeries query
Handling missing fields when starting to change or display a Query for iSeries query
Page
Chapter 5. Defining result fields in Query for iSeries
Creating results fields in Query for iSeries
Query for iSeries result field name
Query for iSeries expressions
58
Query for iSeries numeric expressions
Query for iSeries character expressions
Query for iSeries concatenation operation
60
Query for iSeries character functions
SUBSTR Query for iSeries function
DIGITS Query for iSeries function
VALUE Query for iSeries function
62
VARCHAR Query for iSeries function
64
VARGRAPHIC Query for iSeries function
Date, time, and timestamp expressions in Query for iSeries
66
Query for iSeries date
Query for iSeries time
Query for iSeries timestamp
68
Displaying constants format in Query for iSeries
Date, time, and timestamp arithmetic operations in Query for iSeries
Date arithmetic operation in Query for iSeries
Subtracting dates in Query for iSeries
70
Incrementing and decrementing dates in Query for iSeries
Converting a numeric field to a date field in Query for iSeries
Working with numeric dates in Query for iSeries
72
Time arithmetic operation in Query for iSeries
Incrementing and decrementing times in Query for iSeries
Timestamp arithmetic operation in Query for iSeries
Durations in Query for iSeries
Labeled duration in Query for iSeries
Date, time, and timestamp functions in Query for iSeries
74
CHAR Query for iSeries function
DATE Query for iSeries function
DAY Query for iSeries function
DAYS Query for iSeries function
HOUR Query for iSeries function
76
MICROSECOND Query for iSeries function
MINUTE Query for iSeries function
MONTH Query for iSeries function
SECOND Query for iSeries function
78
TIME Query for iSeries function
TIMESTAMP Query for iSeries function
YEAR Query for iSeries function
Additional date, time, and timestamp functions in Query for iSeries
80
Converting date formats in Query for iSeries
Converting date for output to a database file in Query for iSeries
Converting date for output to a display or printer in Query for iSeries
Example 1Converting from MMDDYY to YYDDD format in Query for iSeries
Example 2Converting from MMDDYY to YYDDD format in Query for iSeries
The commands to run the query in example 3 are:
CHGJOB DATFMT(*JUL) RUNQRY QRY(YYDDD)
Example 3Converting from YYDDD to MMDDYY format in Query for iSeries
82
Example 4Converting from MMDDYY to YYDD format in Query for iSeries
General considerations when creating an expression in Query for
84
Column headings in Query for iSeries
Length and decimal positions in Query for iSeries
Example of defining a result field in Query for iSeries
Adding or removing result fields in Query for iSeries
86
Chapter 6. Selecting and sequencing fields in Query for iSeries
Letting Query for iSeries select and sequence fields
Selecting fields and specifying their sequence in Query for iSeries
88
Page
Page
Chapter 7. Selecting records in Query for iSeries
Letting Query for iSeries select records
Selecting the records you want in Query for iSeries
Comparison fields in Query for iSeries
92
Comparison values in Query for iSeries
Fields as values in Query for iSeries
Character constants as values in Query for iSeries
94
Numeric constants as values in Query for iSeries
Date, time, timestamp constants as values in Query for iSeries
Comparison tests in Query for iSeries
Date, time, or timestamp comparisons in Query for iSeries
Testing for equal (EQ) and not equal (NE) in Query for iSeries
96
Testing for IS Null (IS) and ISNOT Null (ISNOT) in Query for iSeries
Testing for greater (GT or GE), less (LT or LE), and range (RANGE) in Query for iSeries
Testing for values in a list (LIST NLIST) in Query for iSeries
Testing for values that are similar (LIKE NLIKE) in Query for iSeries
98
Testing for DBCS LIKE (like) and NLIKE (not like) in Query for iSeries
100
Using more than one comparison test in Query for iSeries
102
Adding and removing comparisons in Query for iSeries
Page
Chapter 8. Selecting sort fields in Query for iSeries
Letting Query for iSeries determine the order of records for you
Selecting the sort fields you want to use in Query for iSeries
106
Page
Additional sort considerations in Query for iSeries
108
Page
Page
Chapter 9. Selecting a collating sequence in Query for iSeries
Letting Query for iSeries select a collating sequence
Setting your default collating sequence in Query for iSeries
Purpose of a collating sequence in Query for iSeries
Collating sequence and CCSIDs in Query for iSeries
How a collating sequence affects Query for iSeries
112
Selecting a Query for iSeries collating sequence
Using the hexadecimal collating sequence in Query for iSeries
Using the language collating sequence for your country in Query for
114
Defining your own collating sequence in Query for iSeries
Selecting a translation table in Query for iSeries
116
Selecting a system sort sequence in Query for iSeries
118
Chapter 10. Specifying report column formatting in Query for iSeries
Formatting the columns of the Query for iSeriesquery report
Column spacing in Query for iSeries reports
Column headings in Query for iSeries reports
Length and decimal positions in Query for iSeries reports
120
Omitting fields from a Query for iSeries report
Editing numeric fields in Query for iSeries reports
Defining numeric field editing in Query for iSeries reports
122
Page
Describing numeric field editing in Query for iSeries reports
124
Decimal point in Query for iSeries reports
Thousands separator in Query for iSeries reports
Show negative sign in Query for iSeries reports
Left negative sign in Query for iSeries reports
Right negative sign in Query for iSeries reports
Show currency symbol in Query for iSeries reports
126
Left currency symbol in Query for iSeries reports
Right currency symbol in Query for iSeries reports
Describing date/time field editing display in Query for iSeries reports
Date/time separator in Query for iSeries reports
128
Specifying an edit code in Query for iSeries reports
Edit code in Query for iSeries reports
130
Optional edit code modifier in Query for iSeries reports
Specifying edit words
Edit word in Query for iSeries reports
132
Edit word for summary total in Query for iSeries reports
Page
Chapter 11. Specifying report summary functions in Query for iSeries reports
Types of summary functions in Query for iSeries reports
136
Summarizing columns in Query for iSeries reports
Location of column summary values in Query for iSeries reports
Page
Chapter 12. Defining Query for iSeries report breaks
Defining a Query for iSeries report break
140
Page
142
Defining report break formatting in Query for iSeries reports
Skip to new page in Query for iSeries reports
Suppress summaries in Query for iSeries reports
Break text in Query for iSeries reports
144
Chapter 13. Selecting output type and output form in Query for iSeries reports
Selecting the output type and form you want in Query for iSeries reports
Form of output of Query for iSeries reports
Line wrapping in Query for iSeries reports
146
Wrapping width in Query for iSeries reports
Record on one page in Query for iSeries reports
Displaying wrapping widths in Query for iSeries reports
Defining output to the printer in Query for iSeries reports
148
Printer device in Query for iSeries reports
Form size in Query for iSeries reports
Start line in Query for iSeries reports
End line in Query for iSeries reports
150
Line spacing in Query for iSeries reports
Print definition in Query for iSeries reports
Page
Defining the printout cover page of Query for iSeries reports
152
Defining the page headings and footings in Query for iSeries reports
Defining output of Query for iSeries reports to a database file
154
Using an existing output file definition in Query for iSeries reports
Building a new output file definition in Query for iSeries reports
156
Specifying an output database file for Query for iSeries reports
158
Page
Summary-only output of a Query for iSeries report to a database file
160
Using an output database file created by Query for iSeries
Page
Chapter 14. Specifying Query for iSeries processing options
Purpose of Query for iSeries processing options
Rounding numeric field values during Query for iSeries processing
Ignoring decimal data errors during Query for iSeries processing
164
Ignoring character substitution warnings during Query for iSeries processing
Using collating sequence for all character comparisons during Query for iSeries processing
Page
Chapter 15. Exiting and running a Query for iSeries query
Ending a Query for iSeries query definition
Saving a Query for iSeries query definition
Storing the Query for iSeries query definition
Describing the Query for iSeries query definition
Giving authority to others to your Query for iSeries query
168
Running a Query for iSeries query
Using function key F5 when running a Query for iSeries query
Running a Query for iSeries query from the Query for iSeries menu
Running a Query for iSeries query from the Exit This Query display
Running a Query for iSeries query from the Work with Queries display
170
Running a Query for iSeries query using the RUNQRY command
172
Chapter 16. Working with Query for iSeries query definitions
Changing a Query for iSeries query definition
Starting changes by copying a Query for iSeries query definition
Changes you can make to a Query for iSeries query
174
Page
Considerations for changing Query for iSeries queries
Changing your collating sequence on Query for iSeries queries
176
Copying a Query for iSeries query definition
Renaming a Query for iSeries query definition
Deleting a Query for iSeries query definition
178
Displaying a Query for iSeries query definition
Printing a Query for iSeries query definition
180
Information printed for a Query for iSeries query definition
Example of printed record format information for a Query for iSeries query definition
182
Page
Page
Part 3. Advanced information about Query for iSeries
Page
Chapter 17. Additional information about Query for iSeries for programmers
Files with different record formats in Query for iSeries
File sharing considerations in Query for iSeries
Overriding database files in Query for iSeries
DBCS considerations when defining result fields in Query for iSeries
Joining files in Query for iSeries
Using *ALL in Query for iSeries
188
Using fields other than sort fields for report breaks in Query for
Result field length and decimal positions in Query for iSeries
Length and decimal positions used for internal numeric calculations in Query for iSeries
Addition and subtraction in Query for iSeries
Multiplication in Query for iSeries
Division in Query for iSeries
Example: Increasing the decimal precision for result fields in Query for
190
Selecting records (ignoring field case) in Query for iSeries
Page
Page
Page
Appendix A. Differences between Query for iSeries and Query/36
Conceptual Differences
Operational Differences
196
Page
Page
Appendix B. Practice exercise for Query for iSeries query
Query for iSeries query exercise: Creating an IDDU definition
200
Page
202
Query for iSeries query exercise: Creating a database file
Query for iSeries query exercise: Entering data
204
Page
Query for iSeries query exercise: Creating and running a query
206
Query for iSeries query exercise: Creating a query
208
Page
210
Query for iSeries query exercise: Changing a query
6. Press the Enter key. The Select Records display is shown.
212
Page
214
Query for iSeries query exercise: Creating an advanced query
216
Page
The message Select file(s), or press Enter to confirm. appears.
218
6. The next display to appear is the Select and Sequence Fields display.
Page
220
Page
222
Page
224
Page
226
Page
228
Page
Page
Appendix C. Query for iSeries performance tips and techniques
Introduction to Query for iSeries query processing
Keyed sequence access paths in Query for iSeries
Select/omit access paths in Query for iSeries
Considerations for creating access paths in Query for iSeries
232
Access plans in Query for iSeries
Updating access plans in Query for iSeries
File definitions and data in Query for iSeries
File definitions in Query for iSeries
File data considerations for Query for iSeries
Defining queries for Query for iSeries
234
Query for iSeries performance when designing or changing queries
Define result fields in Query for iSeries
Select and sequence fields in Query for iSeries
Select records in Query for iSeries
Select sort fields in Query for iSeries
236
Select collating sequence in Query for iSeries
Specify report summary functions in Query for iSeries
Select output type and output form in Query for iSeries
Specify processing options in Query for iSeries
Using join operations in Query for iSeries
238
Performance tips for join operations in Query for iSeries
Miscellaneous tips and techniques for Query for iSeries
Batch processing for Query for iSeries
240
Query for iSeries performance tuning
Query for iSeries migration considerations N to N-1
Query for iSeries status messages
Query for iSeries debug mode messages
242
Appendix D. Preventing users from running Query for iSeries queries interactively
Page
Appendix E. Coded character set identifiers (CCSIDs) in Query for iSeries
246
CCSID marking in Query for iSeries
CCSIDs and collating sequences in Query for iSeries
248
Query for iSeries language sequences
CCSID conversions for Query for iSeries options and functions
Displaying a Query for iSeries query
Changing a Query for iSeries query
Getting a list of objects with text in Query for iSeries
250
Defining a Query for iSeries query
Defining a collating sequence in Query for iSeries
Getting a list of formats or members with text in Query for iSeries
Saving a Query for iSeries query definition
Running a Query for iSeries query
Running a default query in Query for iSeries
Selecting records at run time in Query for iSeries
CCSIDs and Query for iSeries query definition items
CCSID and file selections in Query for iSeries
252
CCSID and join tests in Query for iSeries
CCSID and result field expressions in Query for iSeries
Page
CCSID compatibility considerations in Query for iSeries
254
Table14 shows what happens at run time if Item 1 cannot be converted to the CCSID for Item 2.
256
Page
Page
Bibliography
Page
Notices
Trademarks
262
Index Special Characters
A
B
C
264
Page
D
266
Page
268
E
F
270
G
H
I
J
K
272
L
M
N
O
P
274
Q
R
S
276
Page
T
U
278
V
W
Y
Z