IBM SC41-5210-04 manual

iSeries

Query for iSeries Use

Version 5

SC41-5210-04

ERserver



Contents

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