ERserver
Page
 ERserver
 Fifth Edition September
 Contents
 Selecting and sequencing fields in Query for iSeries
Defining result fields in Query for iSeries
Selecting records in Query for iSeries
 Defining Query for iSeries report breaks
Selecting sort fields in Query for iSeries
Selecting a collating sequence in Query for iSeries
Specifying report column formatting in Query for iSeries
 Working with Query for iSeries query definitions
Part 3. Advanced information about Query for iSeries
Specifying Query for iSeries processing options
Exiting and running a Query for iSeries query
 Appendix B. Practice exercise for Query for iSeries query
 Viii Query for iSeries Use V5R2
 Who should read the Query for iSeries Use book
About Query for iSeries Use
 Query for iSeries Use V5R2
 Part 1. Introduction to Query for iSeries
 Query for iSeries Use V5R2
 State
What is Query for iSeries?
Query for iSeries fundamentals
Files, fields, and record formats in Query for iSeries
 UCS2 level 1 character set support in Query for iSeries
Double-byte character set Dbcs fields in Query for iSeries
 Libraries in Query for iSeries
Query for iSeries definitions
Major functions of Query for iSeries
 Query for iSeries Use V5R2
 General operating information for Query for iSeries
 Using Query for iSeries function keys
 Printing what you see on your Query for iSeries display
Using lists in Query for iSeries
Displaying a Query for iSeries list
 Special library names in Query for iSeries
Selecting items in a Query for iSeries list
Special Library Name Description
 Using the Query Utilities menu
Using Query for iSeries commands
Query
 Choosing a single Query for iSeries task
Using the Work with Queries display
 Qgpl
Choosing multiple Query for iSeries tasks
Specifying a Query for iSeries query and its library
=Run
 Query for iSeries Use V5R2
 Positioning a list of Query for iSeries queries
 Acctpaymar
Acctpayfeb
Acctrecfeb
 Qgpl Ccsid
F12=CANCEL
Displaying the format of constants in Query for iSeries
QRY1
 Query for iSeries profile information
Handling Query for iSeries messages and errors
Changing your Query for iSeries queries
 General operating information for Query for iSeries
 Query for iSeries Use V5R2
 Creating a Query for iSeries query definition
 Selecting and sequencing fields in Query for iSeries
 Selecting sort fields in Query for iSeries
 Defining Query for iSeries report breaks
 Working with Query for iSeries query definitions
 Query for iSeries Use V5R2
 Starting Query for iSeries query definition
Creating a Query for iSeries query definition
 Define the Query Option Create Library Qgpl
 Selecting options for a Query for iSeries query definition
 Considerations for creating Query for iSeries queries
Moving through the Query for iSeries definition displays
Selecting files, records, and fields for Query for iSeries
 Creating a Query for iSeries query definition
 Query for iSeries Use V5R2
 Specifying file selections for a Query for iSeries query
Specifying and selecting files for a Query for iSeries query
 Choosing a library for a Query for iSeries query
Choosing a file for a Query for iSeries query
First
 Exampfile
Selecting multiple files for a Query for iSeries query
 Confirming your options for a Query for iSeries query
Using file IDs for a Query for iSeries query
Removing a option for a Query for iSeries query
 Custinv
Selecting files on the Query for iSeries Select File display
 Custinv Custrelmar
Cust
Custinv Custrelfeb
Custinv Custreljan
 Specifying and selecting files for a Query for iSeries query
 EXAMPMBR1 EXAMPMBR2 EXAMPMBR3
 EXAMPFMT1 EXAMPFMT2 EXAMPFMT3
 EXAMPFILE1 Custinv First
Joining files in a Query for iSeries query
EXAMPFILE2 Custinv First
 Types of joins in a Query for iSeries query
How to join files in a Query for iSeries query
 NBR Name Phone Addr
Rules for joining files in a Query for iSeries query
ZIP NBR
 Specifying and selecting files for a Query for iSeries query
 Examples of joining files in a Query for iSeries query
 Specifying and selecting files for a Query for iSeries query
 Query for iSeries Use V5R2
 Specifying and selecting files for a Query for iSeries query
 Correct method
 Incorrect method
 Query for iSeries Use V5R2
 Displaying all join tests in a Query for iSeries query
 Handling missing fields in a Query for iSeries query
Page
 Query for iSeries Use V5R2
 Creating results fields in Query for iSeries
Defining result fields in Query for iSeries
 Query for iSeries expressions
Query for iSeries result field name
Page
 Query for iSeries concatenation operation
Query for iSeries character expressions
 Substr Query for iSeries function
Query for iSeries character functions
 Value Query for iSeries function
Digits Query for iSeries function
 Varchar Query for iSeries function
 Reschar
 Result Ccsid Description Character
Vargraphic Query for iSeries function
 Date, time, and timestamp expressions in Query for iSeries
 Query for iSeries time
Query for iSeries date
 Format Name Abbreviation Time Format Example
Query for iSeries timestamp
 Displaying constants format in Query for iSeries
 Subtracting dates in Query for iSeries
Date arithmetic operation in Query for iSeries
 SUBSTRCHARDATE,1,2’/’
Incrementing and decrementing dates in Query for iSeries
Working with numeric dates in Query for iSeries
SUBSTRCHARDATE,5,2’/’
 Yymmdd
Mmddyy Qtemp
 Durations in Query for iSeries
Time arithmetic operation in Query for iSeries
 Char Query for iSeries function
Date, time, and timestamp functions in Query for iSeries
Timestamp duration in Query for iSeries
 DAY Query for iSeries function
Date Query for iSeries function
 Hour Query for iSeries function
Days Query for iSeries function
 Minute Query for iSeries function
Microsecond Query for iSeries function
 Second Query for iSeries function
Month Query for iSeries function
 Timestamp Query for iSeries function
Time Query for iSeries function
 Converting date formats in Query for iSeries
Year Query for iSeries function
 Mmddyy
 Yyddd
 Commands to run the query in example 4 are
 Odqysd NE Omcrmm EQ Omstat EQ ’50’ Odstat NE ’99’
 Length and decimal positions in Query for iSeries
Column headings in Query for iSeries
 Adding or removing result fields in Query for iSeries
Example of defining a result field in Query for iSeries
 Firstname
Selecting and sequencing fields in Query for iSeries
Letting Query for iSeries select and sequence fields
Acctnumber Pastdue Company
 Acctnumber Pastdue Company
 Lastname Streetaddr City State Zipcode Telenumber Crlimit
Company Baldue Acctnumber
 Query for iSeries Use V5R2
 Letting Query for iSeries select records
Selecting records in Query for iSeries
Selecting the records you want in Query for iSeries
 Comparison fields in Query for iSeries
 Comparison values in Query for iSeries
 Character constants as values in Query for iSeries
Fields as values in Query for iSeries
Numeric constants as values in Query for iSeries
 Null values in Query for iSeries
Comparison tests in Query for iSeries
Test Description
 Date, time, or timestamp comparisons in Query for iSeries
Testing for equal EQ and not equal NE in Query for iSeries
 Testing for values in a list List Nlist in Query for iSeries
 Lastname List
Itemno List
 ’ANNE EVA’
Lastname Like
 Example 1 DBCSFLD1 Like ’s/oDD%%HH%%s/i’
 Using more than one comparison test in Query for iSeries
 Month List Year
AND/OR
Year Baldue
 Year List
Adding and removing comparisons in Query for iSeries
 Query for iSeries Use V5R2
 105
Selecting sort fields in Query for iSeries
 Lastname
 Account number
 Additional sort considerations in Query for iSeries
 Sorted report looks like this
 Query for iSeries Use V5R2
 Letting Query for iSeries select a collating sequence
Selecting a collating sequence in Query for iSeries
Purpose of a collating sequence in Query for iSeries
 How a collating sequence affects Query for iSeries
Collating sequence and CCSIDs in Query for iSeries
 Selecting a Query for iSeries collating sequence
 Query for iSeries Use V5R2
 Defining your own collating sequence in Query for iSeries
 Selecting a translation table in Query for iSeries
 Selecting a system sort sequence in Query for iSeries
 F12=Cancel F13=Layout F18=Files F23=Save as default
 Column headings in Query for iSeries reports
Specifying report column formatting in Query for iSeries
Formatting the columns of the Query for iSeriesquery report
Column spacing in Query for iSeries reports
 Lastname Init
Length and decimal positions in Query for iSeries reports
Starttime
 Omitting fields from a Query for iSeries report
 Defining numeric field editing in Query for iSeries reports
Editing numeric fields in Query for iSeries reports
 =Edit word
=Numeric editing choices
=Date or time editing choice
=Edit code
 F10=Process/previous F12=Cancel F13=Layout
 Left negative sign in Query for iSeries reports
Decimal point in Query for iSeries reports
Thousands separator in Query for iSeries reports
Show negative sign in Query for iSeries reports
 Right negative sign in Query for iSeries reports
Show currency symbol in Query for iSeries reports
Left currency symbol in Query for iSeries reports
Right currency symbol in Query for iSeries reports
 Single leading zero in Query for iSeries reports
Print zero value in Query for iSeries reports
Replace leading zeros in Query for iSeries reports
Replace with option in Query for iSeries reports
 Date/time separator in Query for iSeries reports
 Edit code in Query for iSeries reports
Specifying an edit code in Query for iSeries reports
 Internal
 =Asterisk fill
Specifying edit words
Optional edit code modifier in Query for iSeries reports
Edit word in Query for iSeries reports
 F13=Layout F16=Remove edit F18=Files
 Edit word for summary total in Query for iSeries reports
 Query for iSeries Use V5R2
 MAX
Types of summary functions in Query for iSeries reports
=Total
Total
 =Count
=Average
=Minimum
=Maximum
 Name Total DUE
Summarizing columns in Query for iSeries reports
Final Totals
 Query for iSeries Use V5R2
 Defining a Query for iSeries report break
Defining Query for iSeries report breaks
 Region
Division
 Records in file
 Field
 Suppress summaries in Query for iSeries reports
Skip to new page in Query for iSeries reports
Company Department Division Employee Region
 Break text in Query for iSeries reports
 =Printer =Database file
145
 =Summary only
Form of output of Query for iSeries reports
Line wrapping in Query for iSeries reports
=Detail
 Record on one page in Query for iSeries reports
Wrapping width in Query for iSeries reports
Displaying wrapping widths in Query for iSeries reports
 Integer
Defining output to the printer in Query for iSeries reports
Decimal Numeric
 Form width
Printer device in Query for iSeries reports
Form size in Query for iSeries reports
Form length
 Print definition in Query for iSeries reports
Start line in Query for iSeries reports
End line in Query for iSeries reports
Line spacing in Query for iSeries reports
 Define Spooled Output
 Specify Cover
 Heading Footing F3=Exit F5=Report
 =Yes
 File
Qqryout
Libcrtaut LIBCRTAUT, *CHANGE, *ALL EXCLUDE, *USE
 Query for iSeries Use V5R2
Page
 =Replace member
=New file
=Replace file
=New member
 Exclude
Authorization list name
 Los Angeles Arcadia
 Using an output database file created by Query for iSeries
 Query for iSeries Use V5R2
 Purpose of Query for iSeries processing options
Specifying Query for iSeries processing options
 Query for iSeries Use V5R2
 Specifying Query for iSeries processing options
 Query for iSeries Use V5R2
 Storing the Query for iSeries query definition
Exiting and running a Query for iSeries query
Ending a Query for iSeries query definition
Saving a Query for iSeries query definition
 Giving authority to others to your Query for iSeries query
Describing the Query for iSeries query definition
 Libcrtaut
Running a Query for iSeries query
 Query for iSeries Use V5R2
 Running a Query for iSeries query using the Runqry command
 Query for iSeries Use V5R2
 Changing a Query for iSeries query definition
Working with Query for iSeries query definitions
Changes you can make to a Query for iSeries query
 Define the Query
 Prompt Changed Effect
 Considerations for changing Query for iSeries queries
 Acctrecfeb Inventfeb Mailaddr
Copying a Query for iSeries query definition
 Deleting a Query for iSeries query definition
Renaming a Query for iSeries query definition
 Displaying a Query for iSeries query definition
 Printing a Query for iSeries query definition
 Information printed for a Query for iSeries query definition
 Breaklvl
 Working with Query for iSeries query definitions
 Query for iSeries Use V5R2
 Part 3. Advanced information about Query for iSeries
 Query for iSeries Use V5R2
 File sharing considerations in Query for iSeries
Files with different record formats in Query for iSeries
Overriding database files in Query for iSeries
 Joining files in Query for iSeries
Using *ALL in Query for iSeries
 Multiplication in Query for iSeries
Addition and subtraction in Query for iSeries
Division in Query for iSeries
 PRN299B
PRN299A
 Changing Decimal Precision
Selecting records ignoring field case in Query for iSeries
 Query for iSeries Use V5R2
 Part 4. Appendixes
 Query for iSeries Use V5R2
 Operational Differences
Conceptual Differences
 Calculation results
Library default
Character fields
Sort sequence Result fields
 System/36 OS/400 System
 Query for iSeries Use V5R2
 Iddu
Appendix B. Practice exercise for Query for iSeries query
 Nameaddr
Sysdic
 Amount
ADDRESS1
ADDRESS2
ZIP
 ADDRESS1 ADDRESS2 City State ZIP Amount
 USE, *EXCLUDE
Query for iSeries query exercise Creating a database file
Query for iSeries query exercise Entering data
LIBCRTAUT, *ALL, *CHANGE
 Entry
Work with Data in a File
Lastname Firstname ADDRESS1 ADDRESS2 City State ZIP Amount
 Lastname Firstname ADDRESS1 ADDRESS2 City State ZIP Amount
 =Yes, N=No
 Kjoqry
Query for iSeries query exercise Creating a query
Yourlib
 Nameaddrr
Bottom
 Yourlib Ccsid
Create
 APT C
Change LIBCRTAUT, *ALL, *CHANGE EXCLUDE, *USE
Simpson Frank
Daye BEN
 Query for iSeries query exercise Changing a query
 Change
Press the Enter key. The Select Records display is shown
 Press F20 to display the right portion of the report
 APT
ADDRESS2 City
Change LIBCRTAUT, *CHANGE, *ALL EXCLUDE, *USE
 Cebqry
Query for iSeries query exercise Creating an advanced query
 === Wrkqry
Main
 Qname
 Qlidobjd
Qryfile
 Odlbnm Odobow
Oddcen Odccen
Odddat Odcdat
Oddtim Odctim
 Oddtim Display Time Odobnm Object Odobfr
 CLP
Odobtp List
Library Object
Attribute Size
 Odobsz Object Size Odobtx
Odlbnm Library Odobtp Object Type Odobat
 Odlbnm Library Odobtp Object Type Odobat Attribute
 Odobsz Object Size Odobtx Text Description
Odlbnm Name Library Odobtp Object Type Odobat Attribute
 3 4
 Break text
 Qname
 Qgpl File Dspf
Name Object
Qgpl File Tapf
 Qgpl
 Query for iSeries Use V5R2
 Introduction to Query for iSeries query processing
Keyed sequence access paths in Query for iSeries
 Access plans in Query for iSeries
Select/omit access paths in Query for iSeries
 File definitions and data in Query for iSeries
Updating access plans in Query for iSeries
File definitions in Query for iSeries
 Defining queries for Query for iSeries
File data considerations for Query for iSeries
 Select and sequence fields in Query for iSeries
Define result fields in Query for iSeries
Select records in Query for iSeries
 Select sort fields in Query for iSeries
 Specify report summary functions in Query for iSeries
Select collating sequence 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
Option 1-Matched records
 Option 2-Matched records with primary file
Performance tips for join operations in Query for iSeries
Option 3-Unmatched records with primary file
 Batch processing for Query for iSeries
Miscellaneous tips and techniques for Query for iSeries
 Query for iSeries status messages
Query for iSeries performance tuning
Query running. Building access path for file X in Y
Query for iSeries migration considerations N to N-1
 Query running. Sorting copy of file *N in *N
Query for iSeries debug mode messages
Query running. Building access path from file X in Y
Query running. Creating copy of file X in Y
 243
 Query for iSeries Use V5R2
 245
 Query for iSeries Use V5R2
 Ccsid marking in Query for iSeries
 CCSIDs and collating sequences in Query for iSeries
 Displaying a Query for iSeries query
Query for iSeries language sequences
 Defining a collating sequence in Query for iSeries
Changing a Query for iSeries query
Getting a list of objects with text in Query for iSeries
Defining a Query for iSeries query
 Selecting records at run time in Query for iSeries
Running a default query in Query for iSeries
 Ccsid and result field expressions in Query for iSeries
CCSIDs and Query for iSeries query definition items
Ccsid and file selections in Query for iSeries
Ccsid and join tests in Query for iSeries
 Ccsid and summary functions in Query for iSeries
Ccsid and result field column headings in Query for iSeries
Ccsid and sort fields in Query for iSeries
Ccsid and record selection tests in Query for iSeries
 Consequence
Ccsid compatibility considerations in Query for iSeries
 CCSIDs Conversion Problems-Run-Time Consequences
 For displayed output, the run request continues, but all
 How CCSIDs Affect Query Use
 Query for iSeries Use V5R2
 259
Bibliography
 Query for iSeries Use V5R2
 261
 DB2
Trademarks
 Date
Index Special Characters
 Character substitution warning
 Command
 Currentdate ISO JIS Local
 Dec column data type
 Definition Display
 Example
 Field
 Function
 189
 List
 Heading Ccsid coded character set identifier 253 Defining
 Dbcs Like
 Report
 Sort Field
 Currenttime Currenttimezone ISO JIS Local
 Work with File Definitions display Iddu 202
 Query for iSeries Use V5R2
Page
 SC41-5210-04