Page 1 ERserver iSeries Query for iSeries Use Version 5 SC41-5210-04...
Page 3 ERserver iSeries Query for iSeries Use Version 5 SC41-5210-04...
Page 4 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.
Page 6 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 .
Page 7 Letting Query for iSeries select records ......91 Selecting the records you want in Query for iSeries ....91 Comparison fields in Query for iSeries .
Page 8 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 .
Page 9 Appendix A. Differences between Query for iSeries and Query/36 Conceptual Differences ......195 Operational Differences .
Page 10 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 .
Page 11: About Query For Iseries Use
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”.
Page 12 Query for iSeries Use V5R2...
Page 14 Query for iSeries Use V5R2...
Page 15: Chapter 1. What Is Query For Iseries
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...
Page 16: Double-Byte Character Set (Dbcs) Fields In Query For Iseries
Another record format, named contain only the 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.
Page 17: Query For Iseries Definitions
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.
Page 18 Figure 3. Major Tasks on the Query Menu and the Work with Queries Display Query for iSeries Use V5R2...
Page 20: Using Query For Iseries Function Keys
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).
Page 21: Using Lists In Query For Iseries
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.
Page 22: Selecting Items In A Query For Iseries List
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.
Page 23: Using Query For Iseries Commands
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.
Page 24: Using The Work With Queries Display
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.
Page 25: Choosing Multiple Query For Iseries Tasks
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).
Page 26: Working With A List Of Query For Iseries Queries
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).
Page 27: Positioning A List Of Query For Iseries Queries
library group are shown. For example, if you typed in the Subset prompt, you might see a list of query names like: , 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 , type that complete query name in the Subset prompt.
Page 28: Selecting A Library For Your Query For Iseries Queries
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.
Page 29: Displaying The Format Of Constants In Query For Iseries
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.
Page 30: Query For Iseries Profile Information
v Change or display a query from the Work with Queries display or use the run-time 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.
Page 31 select only the option(s) that need to be changed and then, when you are shown the appropriate display, you need only type the new choices or change the incorrect ones. Everything else you defined earlier remains defined; you do not have to redefine the whole query. If you just want to quit, press F3 (Exit) to go to the Exit This Query display.
Page 32 Query for iSeries Use V5R2...
Page 34 Query for iSeries date ......67 Query for iSeries time ......67 Query for iSeries timestamp Displaying constants format in Query for iSeries .
Page 35 Numeric constants as values in Query for iSeries....94 Date, time, timestamp constants as values in Query for iSeries Null values in Query for iSeries ......95 Comparison tests in Query for iSeries .
Page 36 Edit code in Query for iSeries reports Optional edit code modifier in Query for iSeries reports ....131 Specifying edit words ......131 Edit word in Query for iSeries reports Edit word for summary total in Query for iSeries reports .
Page 37 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 .
Page 38 Query for iSeries Use V5R2...
Page 39: Chapter 3. Creating A Query For Iseries Query Definition
9 alphanumeric characters (A through Z, 0 through 9, $, #, @, ., or _). Note: To use this query in a multilingual environment, use A-Z or 1-9. Because most system-supplied objects on the iSeries system begin with Q, your query names should not start with a Q. © Copyright IBM Corp. 2000, 2002...
Page 40: Selecting Definition Steps When Defining A Query For Iseries Query
If you position the cursor on the Query prompt and press F4 (Prompt) to show a list, it contains the names of all the queries that are in the library (or libraries) indicated by the Library prompt. You can get a smaller list by typing a generic name in the Query prompt before you press F4.
Page 41: Selecting Options For A Query For Iseries Query Definition
Selecting options for a Query for iSeries query definition To select options that you want to use from those listed in the Query Definition Option column, type a 1 in the Opt (option) column beside each of those options, then press the Enter key. The displays for the options you select are then shown one after the other so you can create those parts of the query definition.
Page 42: Moving Through The Query For Iseries Definition Displays
You can specify one, several, or all of the options shown. To select all of them, press F21. Moving through the Query for iSeries definition displays For each option you select, Query shows you the definition displays for that step. (The words shown for each option on the Define the Query display match the title of the first display shown for that definition step.) After you specify your definition values on a display, press the Enter key to have them included in the query definition and to go to the next display in the sequence.
Page 43: Suggested Sequence Of Tasks For Creating Query For Iseries Queries
Suggested sequence of tasks for creating Query for iSeries queries The following is a suggested sequence of tasks to create queries: 1. Define result fields before selecting and sequencing fields. 2. Specify comparisons used for selecting records before selecting and sequencing fields. 3.
Page 44 Query for iSeries Use V5R2...
Page 45: Chapter 4. Specifying And Selecting Files For A Query For Iseries Query
After you have selected your definition steps on the Define the Query display and you press the Enter key or F21 (Select all), the first display that you see is the Specify File Selections display. © Copyright IBM Corp. 2000, 2002...
Page 46: Choosing A File For A Query For Iseries Query
The Specify File Selections display is used to specify one or more database files that you want to query for the data in your report. You can specify as many as 32 files to be queried, and you can assign unique 3-character identifiers to each one.
Page 47: Choosing A Member Or Record Format For A Query For Iseries Query
v If you type a generic library name (in the form of the Enter key, that library group is searched for the file named in the File prompt. If the file is found in one of the libraries, the name of that library replaces the special library or generic name. v If you type a generic library name (in the form of the cursor in this prompt, the specified list of library names is shown on the Select Library display.
Page 48: Confirming Your Options For A Query For Iseries Query
Using file IDs for a Query for iSeries query File IDs are used when you select more than one file. Although Query assigns a file ID value to each file selection, you can specify your own identifier. Note: When you are creating a query, the File ID prompt is not shown for your first file selection. However, if you select more than one file, you will see that Query has assigned the value the file ID of your first file.
Page 49: Selecting Files On The Query For Iseries Select File Display
v If you typed a file name and left blank any of the Library, Member, or Format prompts for the file v If there is a problem with one of the values you typed The cursor is positioned to the blank prompt or the prompt in error, and the message describes the error for that prompt.
Page 50 Specify File Selections display, it is used as a subset value on this display, and only file names starting with those generic characters are included in the list. (You can specify another subset value on this display to change the subset shown.) You can use the Library prompt to see the files that exist in a different library or group of libraries.
Page 51: Selecting File Members On The Query For Iseries Select Member Display
To return to the complete list of file names, blank out this prompt (or put an * in it) and press the Enter key. To move (position) the list of file names so that it starts with a particular name, move the cursor to the Position to prompt and type all of the characters or one or more of the starting characters in the name you want, and press the Enter key.
Page 52: Selecting Record Formats On The Query For Iseries Select Record Format Display
If you came from the Specify File Selections display or Specify Dependent Value Qualifiers display, you can specify which member you want your query to get data from. If you came from the Define Database File Output display, you can specify which member you want the query data to be put into. At the top of the display, the File ID field is shown if you came here from the Specify File Selections display.
Page 53: Displaying All Files Selected On The Query For Iseries Display File Selections Display
At the top of the display, the File ID field shows the file ID of the file that appears in the File field. This file is the one for which you want to choose a record format. The Library field shows the library in which the file is stored.
Page 54: Joining Files In A Query For Iseries Query
If multiple file selections are shown, you can press the Enter key to show the join type and all the join tests for these same files. However, if you came to this display from the Specify File Selections, Change File Selections, Specify Type of Join, or Specify How to Join Files display, the Enter key returns you to the previous display.
Page 55: Types Of Joins In A Query For Iseries Query
Types of joins in a Query for iSeries query There are three types of joins, or three ways that you can select matched or unmatched records from the specified files. You can: v Select only records that have matching records in all the specified files. v Select all primary records, and include all matching secondary records.
Page 56: Rules For Joining Files In A Query For Iseries Query
Note: See “CCSID and join tests in Query for iSeries” on page 252 for information on how CCSIDs can affect your join selections. For each test, you specify two fields to be tested and the test value to be used. Look for a field in one file that contains the same (or similar) information that can be found in a field of the other file, such as a name or identification number.
Page 57 v A field name must be preceded by a 1- to 3-character file identifier if that field name is used in more than one file selected for the query. v You can specify up to 100 join tests. v For a matched-record join, if one or both fields in a join test are null, the records are not joined. v The data in both fields of a join test must be the same type (for example, both character), except that date, time, or timestamp fields can be compared to SBCS character, DBCS-either, or DBCS-open fields that contain a correct representation of a date, time, or timestamp.
Page 58: Examples Of Joining Files In A Query For Iseries Query
- DBCS-either - DBCS-open – DBCS-either field with: - SBCS character - Date - Time - Timestamp - DBCS-either - DBCS-open - DBCS-only – DBCS-open field with: - SBCS Character - Date - Time - Timestamp - DBCS-either - DBCS-open - DBCS-only –...
Page 59: Example: Selecting Matched Records From All Selected Files In A Query For Iseries Query
You can obtain several different results from a file join when you use different combinations of join tests and options on the Specify How to Join Files display. Following are some examples of how you might use the three different types of joins. All of the examples assume that was specified on the Specify How to Join Files display.
Page 60: Example: Selecting Matched Records Using A Primary File In A Query For Iseries Query
Example: Selecting matched records using a primary file in a Query for iSeries query Type a 2 if you want to include in the query output every record in the primary file and all the matching records from all the other (secondary) files, whenever they exist. Every record in the primary file is selected whether or not it has a match.
Page 61: Sequencing Secondary Files For A Primary Join In A Query For Iseries Query
In our example, the file is still the primary file, so only its records that do not have a matching secondary record are included in the query report. The file has two such unmatched records; the residents identified in records 2 and 6 do not have a telephone number, so there are no records for them in the secondary file.
Page 62 Correct method: On the Specify File Selections display, type the file first, the file second, and the file third. Specify the file IDs , , and , respectively. The join type is 2 (Matched records with primary file). The join tests are: Query performs the file join in two steps: Step 1: Join the first two files, and .
Page 63 Incorrect method: On the Specify File Selections display, type the file first, and specify the file ID as . Type the file second (file ), and the file third (file ), which is in the opposite order from the correct method.
Page 64 Query performs the file join in two steps: Step 1: Join the first two files, and . Query joins the files in the order listed, starting with file ), and file ). But there is no join test that compares a field in to a field in .
Page 65: Displaying All Join Tests In A Query For Iseries Query
every record in file . Because there are three records in The join tests are not used in this step. The result of step 1 this time is a working file called Step 2: Join file to file . Query joins each record in file equals .
Page 66: Handling Missing Fields In A Query For Iseries Query
The Field columns show the fields being used to join the files. Each field name can have two parts: the file ID and the actual name of the field, separated by a period. For example, if a field named exists in files used in this query, they would be shown as find the files associated with the file IDs (such as display.
Page 67: Handling Missing Fields During File Selection Process Of A Query For Iseries Query
Handling missing fields during file selection process of a Query for iSeries query While you were changing a query, or were defining a new query that already had file selections specified, you chose the Specify file selections option on the Define the Query display. Then, on the Specify File Selections display, you selected a file or format that does not have all of the report fields that your first selection contained.
Page 68 problems caused by the missing fields. You must also ensure that none of the fields were used in break test values on the Format Report Break display. On the definition displays for the affected steps, the expressions or selection tests that use fields that are now missing are highlighted. v If you press F12 (Cancel), you return to the Work with Queries display without affecting your previous file (and field) selections.
Page 69: Chapter 5. Defining Result Fields In Query For Iseries
To page through the field list, position the cursor in the list part of the display and use the page keys. (A one-word indicator © Copyright IBM Corp. 2000, 2002...
Page 70: Query For Iseries Result Field Name
always appears below and to the right of the list to tell you where you are in the list. More... means that there are more items after, and possibly before, the items currently shown. Bottom means that you are at the end of the list, but there may be more items before those currently shown.) Use F19 (Next group) to reposition the list at the beginning of the next logical group of fields (the cursor can be anywhere when you use F19).
Page 71: Query For Iseries Numeric Expressions
DAYS MONTH YEAR v A time expression performs an operation on a time. Time expressions can contain the following operators or functions: + (Addition) − (Subtraction) CHAR TIME HOUR MINUTE SECOND MICROSECOND v A timestamp expression performs an operation on a timestamp. Timestamp expressions can contain the following operators or functions: + (Addition) −...
Page 72: Query For Iseries Character Expressions
When you do more than one calculation within a numeric expression, use parentheses to tell Query in what order to do the calculations and to make the expression easier to understand. If you use nested parentheses for example, example, ), then the next innermost pair, and so on. If you do not use parentheses, Query first does multiplication and division, left to right, and then addition and subtraction, left to right.
Page 73: Query For Iseries Character Functions
For example, if a character constant are concatenated, the result is a field containing the value expressions are: If any field is null-capable, the resulting field is null-capable. If any field used in a concatenation is null, the resulting expression is null. Except for the case of two DBCS-open fields, if all concatenated values are either fixed-length fields or constants, the result is a fixed-length field.
Page 74: Digits Query For Iseries Function
The argument must be an integer or decimal value. The result of the function is a fixed-length character string. The CCSID of the string is the default SBCS CCSID at the application server. If the argument can be null, the result can be null. If the argument is null, the result is a null value.
Page 75: Varchar Query For Iseries Function
Note: If you are sending queries between countries that use the comma for a decimal point, put a blank after each comma separating values in a list of numeric values. The selected argument is converted, if necessary, to the attributes of the result. The attributes of the result are determined as follows: v If the arguments are dates, the result is a date.
Page 76 v If the first argument is UCS2 graphic data and the result is mixed data, the result length is (2.5*(n-1)) + The third argument, if specified, must be a valid SBCS or mixed CCSID. If it is a SBCS CCSID, the first argument cannot be a DBCS-either or DBCS-only string.
Page 77: Vargraphic Query For Iseries Function
VARGRAPHIC Query for iSeries function The VARGRAPHIC scalar function provides a way to convert: v character data (SBCS and Mixed) to DBCS graphic. v character data (SBCS and Mixed) to UCS2 graphic. v UCS2 graphic to UCS2 graphic. v DBCS graphic to UCS2 graphic. expression The VARGRAPHIC function returns a graphic string representation of a string expression.
Page 78: Date, Time, And Timestamp Expressions In Query For Iseries
The actual length of the result depends on the number of characters in the argument. Each character of the argument determines a character of the result. Regardless of the CCSID, every double-byte code point in the argument is considered a DBCS character, and every single-byte code point in the argument is considered an SBCS character with the exception of the EBCDIC mixed data shift codes X’0E’...
Page 79: Query For Iseries Date
Table 1. Formats for Representations of Date Data Types Format Name Abbreviation International Standards Organization IBM USA Standard IBM European Standard Japanese Industrial Standard Christian era OS/400 format Note about using SAA date format: To prevent confusion about the date value, use the Systems If the OS/400 two-digit year format is used, the range of dates is from 1940 through 2039.
Page 80: Query For Iseries Timestamp
(for use by the CHAR function) and an example of its use. Table 2. Formats for Representations of Time Data Types Format Name International Standards Organization IBM USA Standard IBM European Standard Japanese Industrial Standard Christian era OS/400 format Note: Time separators can be either a period (.), slash (/), comma (,), dash (-), or a blank space.
Page 81: Displaying Constants Format In Query For Iseries
Displaying constants format in Query for iSeries The Display Constants Format display shows you what date or time format you must use if you specify a date or time constant and do not use an SAA format. The Display Constants Format display appears when you: v Change a query from the Work with Queries display and 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.
Page 82: Date Arithmetic Operation In Query For Iseries
Subtraction rules are different from addition rules because a date, time, or timestamp value cannot be subtracted from a duration. Also, subtracting two date, time, or timestamp values is not the same as subtracting a duration from a date, time, or timestamp value. The following rules apply to date, time, and timestamp subtraction: v If the first operand is a date, the second operand must either be a: –...
Page 83: Incrementing And Decrementing Dates In Query For Iseries
For example, the result of DATE(’3/15/2000’) - ’12/31/1999’ is 215, or a duration of 0 years, 2 months, and 15 days. Incrementing and decrementing dates in Query for iSeries The result of adding a duration to or subtracting a duration from a date is itself a date. It must be between January 1, 0001 and December 31, 9999 inclusive.
Page 84 selection on specific year, month, and day values. The marked (*) lines are necessary and a sample report follows. If you require rounding for fields in the report (instead of truncation), you can add to each of the intermediate expressions a minus one half, as follows: Example 2: Working with numeric dates in Query for iSeries: This example shows the reverse in formatting—...
Page 85: Time Arithmetic Operation In Query For Iseries
Time arithmetic operation in Query for iSeries Times can be subtracted, incremented, or decremented. The result of subtracting one time (TIME2) from another (TIME1) is a time duration that specifies the number of hours, minutes, and seconds between the two times. For example, the result of TIME( and 30 seconds.
Page 86: Date, Time, And Timestamp Functions In Query For Iseries
Timestamp duration in Query for iSeries A timestamp duration represents a number of years, months, days, hours, minutes, seconds, and microseconds expressed as a DECIMAL (20, 6) number. To be properly interpreted, the number must have the format yyyymmddhhMMsszzzzzz , where yyyy , mm , dd , hh, MM , ss and zzzzzz represent, respectively, the number of years, months, days, hours, minutes, seconds and microseconds.
Page 87: Date Query For Iseries Function
DATE Query for iSeries function The DATE function returns a date from a value. The form is: expression The argument must be either a: v Timestamp v Date v Positive number or expression less than 3 652 059 v Valid character representation of a date v Character representation with a length of 7 If the argument is a character representation of length 7, it must represent a valid date in the form yyyynnn where yyyy represents the year digits and nnn represents digits between 001 and 366, signifying a day in...
Page 88: Days Query For Iseries Function
If the argument is a date duration or a timestamp duration, the result is the day part of the value, a binary field with a value between −99 and 99. A nonzero result has the same sign as the argument. Example: RESULT(DAY) would equal a value between 1 and 31.
Page 89: Microsecond Query For Iseries Function
The resulting value of HOUR would equal 12. MICROSECOND Query for iSeries function The MICROSECOND function returns the microsecond part of a value. The form is: expression The argument must be either a: v Timestamp v Timestamp duration v Valid character representation of a timestamp The result of the function is a binary field.
Page 90: Month Query For Iseries Function
MONTH Query for iSeries function The MONTH function returns the month part of a value. The form is: expression The argument must be either a: v Date v Timestamp v Date duration v Timestamp duration The result of the function is a binary field. If the argument can be null, the result can be null. If the argument is null, the result is null.
Page 91: Time Query For Iseries Function
TIME Query for iSeries function The TIME function returns a time from a value. The form is: expression The argument must be either a: v Time v Timestamp v Valid character representation of a time The result of the function is a time value. If the argument can be null, the result can be null. If the argument is null, the result is null.
Page 92: Year Query For Iseries Function
YEAR Query for iSeries function The YEAR function returns a numeric representation of the year part of a value. The form is: expression The argument must be either a: v Date v Timestamp v Date duration v Timestamp duration The result of the function is a binary field. If the argument can be null, the result can be null. If the argument is null, the result is null.
Page 93: Converting Date For Output To A Display Or Printer In Query For Iseries
Converting date for output to a display or printer in Query for iSeries You can use the date functions to convert an input date field to a different format. Examples 1 and 2 show two methods of converting a date field from the MMDDYY format to the YYDDD format. Examples 3 and 4 show the reverse conversions, from YYDDD to MMDDYY format.
Page 94: Example 3-Converting From Yyddd To Mmddyy Format In Query For Iseries
The commands to run the query in example 3 are: Example 3—Converting from YYDDD to MMDDYY format in Query for iSeries The following Define Result Fields panel defines a YYDDD to MMDDYY date conversion. The conversion is done completely within the panel. Example 4—Converting from MMDDYY to YYDD format in Query for iSeries This example shows an alternative method for the same conversion done in example 3.
Page 95: General Considerations When Creating An Expression In Query For Iseries
The commands to run the query in example 4 are: General considerations when creating an expression in Query for iSeries Following are some general rules you should use when you are creating expressions: v Character constants that represent dates are evaluated using the date format stored in the query definition.
Page 96 – If your expression performs a division operation, avoid dividing by zero by defining the denominator as the result field just before the result field with a division. Then, for all logical OR groupings of record selection, add a logical AND test stating the denominator must not equal zero. v On the three lines provided, you can split a field name, numeric constant, or character string at the end of one line and continue it at the beginning of the next.
Page 97: Column Headings In Query For Iseries
Column headings in Query for iSeries If you want to specify a column heading to be used for a result field, type the heading you want on the three lines (up to 20 characters each) of the display that correspond to your result field. The heading will appear in your query output exactly as you enter it on the three lines.
Page 98: Example Of Defining A Result Field In Query For Iseries
Example of defining a result field in Query for iSeries An example of how you would create a numeric result field called using the expression and using the report column heading is shown on the following display. The second input area shows how you would define a character result field called with a value of and no column heading.
Page 99: Chapter 6. Selecting And Sequencing Fields In Query For Iseries
(or is the first field in your database file). If you change your mind about the fields you have selected, you can delete a field from the output by removing the number you specified in front of it. To change the sequence of the selected fields, just change the numbers. © Copyright IBM Corp. 2000, 2002...
Page 100 After making new selections or changing old ones, press the Enter key to rearrange them on the display in the new sequence. You can then renumber them with new sequence numbers in added amounts of 10 (10, 20, 30, and so on) by pressing F20. Renumbering can make it easier to add a field within the sequence later.
Page 101 You could then select the remaining fields for output on the report by pressing F21 (Select all). Query would then select and supply sequence numbers for the remaining fields in the order that they appeared in the list. Another way of making your selections is to first specify the same sequence number (a 1, for example) for all of the fields you want to select.
Page 102 After you have confirmed the fields you want by pressing the Enter key a second time without making any changes to your sequence choices, Query completes this step and takes you back to the Define the Query display so that you can continue with other query tasks. If you previously selected more options on the Define the Query display, Query shows you the first display for the next option.
Page 103: Chapter 7. Selecting Records In Query For Iseries
The Select Records display lists fields you can use (including result fields) and asks you to specify your comparisons. This display is shown below with some sample fields from a customer master file shown in the Field column. © Copyright IBM Corp. 2000, 2002...
Page 104: Comparison Fields In Query For Iseries
Note: The value is not limited to the length of the line on the input display. If you need more space, continue on to the next line. You must separate all values with blanks. If one value ends at the end of the line and you continue on to the next line, you must include a blank space before the value on the next line.
Page 105: Comparison Values In Query For Iseries
Comparison values in Query for iSeries The value you compare the field with can be: v Another field v A numeric constant v A character constant (SBCS or DBCS) v A date constant v A time constant v A timestamp constant The field and the value you are comparing must be compatible data types.
Page 106: Fields As Values In Query For Iseries
SBCS character Timestamp DBCS-either DBCS-open v Any field can be compared to the keyword NULL using the IS or ISNOT test to determine if a field value is or is not null. Note: When comparing a date, time, or timestamp field with an SBCS, DBCS-either, or DBCS-open character field, use a character field for which each value can be recognized as an SAA formatted date, time, or timestamp.
Page 107: Comparison Tests In Query For Iseries
Note: If you are sending queries between countries that use the comma for a decimal separator, put a blank after each comma separating the offset and length in the SUBSTR function and between numeric values in a list in the VALUE function. Press F17 to show which decimal separator to use in the query (see “Displaying the format of constants in Query for iSeries”...
Page 108: Date, Time, Or Timestamp Comparisons In Query For Iseries
Date, time, or timestamp comparisons in Query for iSeries A date, time, or timestamp value may be compared either with another value of the same data type or with a character representation of that data type. All comparisons are chronological. The farther a point in time is from January 1, 0001, the greater the value of that point in time.
Page 109: Testing For Values In A List (List Nlist) In Query For Iseries
Records are selected if the data in the field When you test for RANGE, the contents of the field must be within the range of two values (greater than or equal to the first value but less than or equal to the second) for the record to be selected. On the Select Records display, you must specify two values in the Value column, and you must separate them by a blank.
Page 110: Testing For Values That Are Similar (Like Nlike) In Query For Iseries
v Must be date constants if comparing to a date field. If the list contains correct representations of a date but in other than SAA format, you may get unexpected results. To prevent unexpected results, the list should be in SAA format. v Must be time constants if comparing to a time field.
Page 111: Testing For Dbcs Like (Like) And Nlike (Not Like) In Query For Iseries
You can use some special characters to represent the positions in the field that you do not care about: v An underscore (_) means skip one character at that position and do not test for that character. Each underscore takes the place of one character in the field. v A percent sign (%) means skip as many characters as necessary to get to the end of the field or to the next specified character.
Page 112 DBCS-only LIKE, NLIKE (not like) pattern in Query for iSeries: This pattern, which contains only double-byte characters, can be used for any bracketed-DBCS field, but not for a DBCS-graphic field. Start the pattern with a shift-out character and end it with a shift-in character. Use the DBCS percent sign (%%) to skip any number of characters or none at all.
Page 113: Using More Than One Comparison Test In Query For Iseries
If the test pattern consists of two apostrophes alone ( ) or two apostrophes enclosing only DBCS shift-out and shift-in characters ( ), the test pattern is treated as an empty string. These patterns will select records in which the field contains an empty string. Using more than one comparison test in Query for iSeries When you use more than one comparison, you need to tell Query how to connect them by typing a connection in the AND/OR column.
Page 114 You can use AND and OR connections together to be even more specific about which records to include in your report. The following example selects records for customers who placed orders from November 1986, through January 1987. Note that numeric constants are used for months. If written out, the above example looks like this: Because using a combination of AND and OR connections can be confusing, you might want to use F5 to look at your report and make sure your comparisons work the way you expect.
Page 115: Adding And Removing Comparisons In Query For Iseries
Written out, the example would look like this: Note: CCSIDs can affect how records are selected. See “CCSID and record selection tests in Query for iSeries” on page 253 for information on CCSIDs and how they affect record selections. Adding and removing comparisons in Query for iSeries The prompt part of the Select Records display only has room to show six comparisons at a time.
Page 116 Query for iSeries Use V5R2...
Page 117: Chapter 8. Selecting Sort Fields In Query For Iseries
(from highest to lowest value) by typing an A (for ascending) or a D (for descending) in the A/D column. (If you leave the A/D column blank for a field, the sort for that field is done in ascending order.) © Copyright IBM Corp. 2000, 2002...
Page 118 When you press the Enter key after typing your sort priority numbers, Query rearranges the fields on the display in the following order: v If fields were selected on the Select and Sequence Fields display, they are listed in the following order: 1.
Page 119 v The states, and the customers listed for each state, should appear alphabetically. In order to obtain the order you want, your first sort field is credit limit ( ), in descending order (from highest to lowest); your second sort field is state ( );...
Page 120: Additional Sort Considerations In Query For Iseries
Notice that because you left the A/D column blank for the , and fields, Query replaces the blank with an to show that ascending order is used. The sort in this example provides a report that looks something like this: The highest credit limits ( ) are listed first, followed by the next highest.
Page 121 SBCS character data in SBCS, DBCS-open, and DBCS-either fields are sorted in the order of the hexadecimal representation of the characters or an order defined by a selected collating sequence. For more information on collating sequences, see Chapter 9, “Selecting a collating sequence in Query for iSeries”.
Page 122 Query for iSeries Use V5R2...
Page 123: Chapter 9. Selecting A Collating Sequence In Query For Iseries
Selecting records v Joining files v Sorting records v Calculating the minimum and maximum values of a field v Determining when a report break occurs © Copyright IBM Corp. 2000, 2002 option on the Define the Query display,...
Page 124: Collating Sequence And Ccsids In Query For Iseries
To collate means to place items in proper sequence or to check that items are in proper sequence. For Query for iSeries purposes, collating sequences apply to SBCS character data in SBCS, DBCS-open, or DBCS-either fields, not numeric, date, time, timestamp, DBCS-only, or DBCS-graphic fields. The collating sequences you can use are: v The collating sequence provided by Query for iSeries for the language of your country.
Page 125: Selecting A Query For Iseries Collating Sequence
Selecting a Query for iSeries collating sequence To select a collating sequence, you have to understand how the data exists in your files. For example, if both uppercase and lowercase SBCS characters exist in your character fields, you have to decide if you want uppercase and lowercase characters treated the same.
Page 126: Using The Language Collating Sequence For Your Country In Query For Iseries
Table 3. Example of How a Code Table Works (continued). The value in the left column is the first half of the hexadecimal value. The value at the top of each column is the second half of the hexadecimal value. The lowercase a in this table is at ’81’X and the uppercase A is at ’C1’X.
Page 127: Defining Your Own Collating Sequence In Query For Iseries
as in most Query for iSeries language sequences, each pair of uppercase and lowercase letters (such as and ) sort together because they share a collating weight that is distinct from the weights of other letters. The language collating sequence is not saved in the query definition. If you save your query and it is used on a system with a different language, the collating sequence for the other language is used.
Page 128: Selecting A Translation Table In Query For Iseries
The CCSID of the sequence is shown at the top and is always your job CCSID. If the collating sequence CCSID does not match your job CCSID, the previously specified or defaulted sequence is converted before it is shown on the Define Collating Sequence display. See “CCSIDs and collating sequences in Query for iSeries”...
Page 129: Selecting A System Sort Sequence In Query For Iseries
For more information on using lists, see “Using lists in Query for iSeries” on page 9. Selecting a system sort sequence in Query for iSeries You can specify that the query use a system-provided sort sequence associated with a specific language. There are two sort sequences for each language: v One with unique weights for each character in the table.
Page 130 Job run and *JOBRUN are resolved to the sort sequence (SRTSEQ keyword) and language ID (LANGID keyword) from the user’s job when the query is run. These are the defaults for collating sequence option 5 if no sort sequence and language ID are saved in the query user profile. The language ID is not used when: v You choose Job run as the sort sequence option, and v The sort sequence of the job at run time is *HEX or a named translation table.
Page 131: Chapter 10. Specifying Report Column Formatting In Query For Iseries
, it must begin in the first position of the first heading line and must be all uppercase letters. The remainder of the heading must be blank. © Copyright IBM Corp. 2000, 2002 is specified in heading line 1. If you...
Page 132: Length And Decimal Positions In Query For Iseries Reports
Each column heading appears in the report exactly as you type it. Each heading can be up to three 20-character lines, and you can use any characters you want (see the heading for the following display). If you want the headings of all your columns to line up on the lowest heading line, you must type each one on the display that way.
Page 133: Omitting Fields From A Query For Iseries Report
v 0 through 9 (single-precision) or 0 through 17 (double-precision) for floating-point fields For date, time, timestamp and DBCS fields, you cannot change the length to anything but 0, which excludes the field from the report. For numeric fields, if you increase or decrease the value in Dec, you should increase or decrease the value in Len the same amount.
Page 134: Editing Numeric Fields In Query For Iseries Reports
Note: You cannot change the order of the fields on your report during the formatting process. If you determine that you need to change the order of the fields in your output, you must go back to the Define the Query display and choose the Select and sequence fields option (see Chapter 6, “Selecting and sequencing fields in Query for iSeries”).
Page 135 The Define Numeric Field Editing display allows you to specify the type of editing you want to use to edit a numeric field. The editing determines how the values for the field are to appear in your query report. This is the first of two displays used to define the editing;...
Page 136: Describing Numeric Field Editing In Query For Iseries Reports
v The Edit option value is the value last specified in the query definition, the value associated with the field definition in the file definition (if this is a field from a file), or 1, if neither of the first two indicates its value.
Page 137: Decimal Point In Query For Iseries Reports
Decimal point in Query for iSeries reports Type the option number of the character that you want used as the decimal point in this field. The option numbers and characters are shown below, along with examples of the edited results for a numeric value of Option Example Meaning of Option...
Page 138: Right Negative Sign In Query For Iseries Reports
v If you specify option 3 (Floating currency symbol), then the negative sign moves (or floats), depending on how many leading zeros it replaces, so that it is to the immediate left of the first significant digit in the value (such as ), or it is to the immediate left of the left currency symbol (such as Right negative sign in Query for iSeries reports Type from 1 to 6 characters that you want used as the negative sign to appear on the right of negative...
Page 139: Describing Date/Time Field Editing Display In Query For Iseries Reports
The right currency symbol is always in a fixed position. Print zero value in Query for iSeries reports When this field contains a numeric value of zero, type a is to be left blank. Y=Yes A numeric value of 0, when it occurs, is to be shown. N=No A numeric value of 0 is to be shown as all blanks;...
Page 140: Date/Time Separator In Query For Iseries Reports
The Describe Date/Time Field Editing display allows you to specify the type of separator character that you want used for a numeric field that contains a date or time value. The editing value determines how the values for the date or time field are to appear in your query report (if you use option 2 as the edit option for this field).
Page 141: Specifying An Edit Code In Query For Iseries Reports
Specifying an edit code in Query for iSeries reports The Specify Edit Code display allows you to specify which edit code or user-defined edit description you want used to edit each value in a numeric field. The edit code or edit description that you choose determines how the values for the specified field are to appear in your query report (if the report is run when the edit option for the field is 3).
Page 142 Note: For all these edit codes, decimal points are always shown, and leading zeros are always suppressed. Query uses the J edit code as the default edit code whenever an error occurs during numeric field editing or whenever there are no other editing values to use. If an error occurs, an error message is shown along with the edited result produced using the J code.
Page 143: Specifying Edit Words
Optional edit code modifier in Query for iSeries reports Type the character that you want used as the optional modifier for the specified edit code. If you specified one of the edit codes W through Z or one of the edit descriptions 5 through 9 in the Edit code prompt, you cannot specify an optional modifier in this prompt.
Page 144 For example, if you want to define an edit word containing an eleven-digit field with no decimal positions, and you want commas for thousands separators and and a value in the report would appear as You might define formatted for ten or eleven digits on the Specify Report Column Formatting display. The ten digit number The edit word might be Consider the following as you define your edit words.
Page 145: Edit Word For Summary Total In Query For Iseries Reports
v An edit word does not have to have a decimal digit position (to the right of the decimal point) for each decimal digit in the field; only the total length is significant. Query does not check the two edit words to see that they have the same number of decimal positions.
Page 146 Query for iSeries Use V5R2...
Page 147: Chapter 11. Specifying Report Summary Functions In Query For Iseries Reports
Shows the sum of the values in the field for the break level or for the whole column (numeric fields only). Null values are ignored unless all values are null, then the total is null. © Copyright IBM Corp. 2000, 2002 (two numeric fields being multiplied together). These two fields are used field.
Page 148 2=Average Shows the average of the values in the field for the break level or for the whole column (numeric fields only). The average is the total divided by the count (number of values that are not null used to determine the total).
Page 149: Summarizing Columns In Query For Iseries Reports
Summary functions can be specified for as many of these fields as you want, as long as that type of function is valid for that type of field. Summarizing columns in Query for iSeries reports The collating sequence that you select (see Chapter 9, “Selecting a collating sequence in Query for iSeries”) for your query has an effect on different parts of your query definition.
Page 150 Query for iSeries Use V5R2...
Page 151: Chapter 12. Defining Query For Iseries Report Breaks
5 and 6, and the break text and summary values for each of those three levels (6 to 4) would be included at that point in the report. © Copyright IBM Corp. 2000, 2002...
Page 152 An example of when you might need to define five report break levels, would be if you sequenced a group of payroll time records by employee within department, within region, within division, and within company. In this case you would assign the following break levels: v Break level 1 = company v Break level 2 = division v Break level 3 = region...
Page 153 v If no break fields are selected, no summary functions are selected and final summaries are suppressed. Only a count of the records is produced. In a name and address file, for example, you might specify that two fields named specified as break fields.
Page 154 You can exclude break fields from showing in the report in column format and still use their values in the report’s break text. That is, if you specify a 0 in the Len column of the Specify Report Column Formatting display for a field, no column of data for the field appears in the report.
Page 155: Defining Report Break Formatting In Query For Iseries Reports
Defining report break formatting in Query for iSeries reports The Format Report Break display is used to format each of the break levels (1 through 6) that you defined on the Define Report Break display. This display is shown once for each of the break levels that you defined for your query.
Page 156: Break Text In Query For Iseries Reports
N=No Any summary values are not to be suppressed. They are to be included each time that a report break occurs at this break level. Y=Yes All summary values are to be suppressed. They are not to be included for this break level. Break text in Query for iSeries reports Type any text that you want to appear at each report break for this break level.
Page 157: Chapter 13. Selecting Output Type And Output Form In Query For Iseries Reports
(0). Editing values on the total fields are used. Note that database output does not contain certain report characteristics that are used for displayed or printed reports: column headings, column spacing, line wrapping, report break formatting, or printer © Copyright IBM Corp. 2000, 2002...
Page 158: Form Of Output Of Query For Iseries Reports
output definition information. If no field selections are made, Query produces all fields for a database file. Result fields are ordered last instead of first, as they are for printed or displayed output. Even though these characteristics may be defined in the query, they are not used unless your Output type choice specifies either displayed or printed output.
Page 159: Wrapping Width In Query For Iseries Reports
N=No The data for each output record is not to be wrapped when the length of the output record exceeds the width of the report line. Instead, any extra fields on the right end of each record are dropped. If is specified, the next two prompts are ignored.
Page 160: Defining Output To The Printer In Query For Iseries Reports
Alignment for the wrapped fields is uneven in this example, because, although the Column Spacing value for CHAR is 2, one blank appears after the INTEGER heading and field on the first line, and one blank appears before the CHAR heading and field on the second line. The Column Spacing value for DECIMAL is also 2, but both blanks appear after CHAR on the second line and no blanks before DECIMAL on the third line.
Page 161: Printer Device In Query For Iseries Reports
You can also specify whether your query definition is to be printed when the report is printed. If it is, the values specified on this display apply to the printing of both the query report and the query definition. These values do not apply if you print the definition using option 6 on the Work with Queries display (which prints only the definition) or if you specify both database output and print definition (which puts the report in the file and prints the definition).
Page 162: Start Line In Query For Iseries Reports
If you want to increase the left margin of the report (with the left edge being print position 1), go to the Specify Report Column Formatting display and type a larger value in the Column Spacing prompt for the first field to be shown in the report. Note: For DBCS-graphic fields, Query adds DBCS shift-out and shift-in characters before showing the fields in a displayed or printed report.
Page 163 Completing this display does not cause the query to immediately run and print a report. You must still use the Exit This Query display (see Chapter 15, “Exiting and running a Query for iSeries query”). If you have your query output spooled and if it is necessary to work with your spooled output, you can use the Work with Spooled Files display.
Page 164: Defining The Printout Cover Page Of Query For Iseries Reports
v If you type , the system assumes that the standard forms are already in the printer, and no message is sent to the system operator. Copies in Query for iSeries reports: Type a number from 1 through 255 to indicate the number of copies of the report that you want printed.
Page 165: Defining The Page Headings And Footings In Query For Iseries Reports
N=No No cover page is to be printed for your report. If you specify text on the Cover page title prompt, it is saved for future use. Cover page title of Query for iSeries reports: Type the title, or title text, to be printed on your report as a cover page.
Page 166: Defining Output Of Query For Iseries Reports To A Database File
Print standard page headings in Query for iSeries reports: Type a system’s standard headings are to be printed on each page of your report (except for the cover page). You must specify an if you do not want to print the standard headings; your use of the special date, time, and page codes does not control the printing of the standard information.
Page 167: Using An Existing Output File Definition In Query For Iseries Reports
For database file output, some report characteristics (although they may be defined in this query definition) are ignored. If you later change the output type or the output form on the Select Output Type and Output Form display, these defined characteristics can be used as specified. The data from your query is saved in your database file in the order that the fields are specified in your query.
Page 168: Building A New Output File Definition In Query For Iseries Reports
Note: This can cause an unwanted result if a date value outside the range 1940 through 2039 is put into a file field that has a two-digit year format. The value will be shown as pluses (++++++) on a report. To avoid this result, either use the CHAR function to see the data in a four-digit year format when you query the data, or change the attributes of the file field to a four-digit year format.
Page 169: Specifying An Output Database File For Query For Iseries Reports
definition is the only way to determine some of the assigned attributes, such as the allocated length for variable length fields. Changing the format definition is the only way to specify certain attributes, such as a preferred default for null values. Specifying an output database file for Query for iSeries reports File: To specify the database file that is to store your query output, you can type a file name, use the name already shown, or press F4 (Prompt) to see a list of existing file names.
Page 170 If you specify if the file is created or replaced or has no members. is used with a request to put the output into a new member of an existing file containing members, Query for iSeries sends an error message. You need to cancel the request or replace the member named in the message.
Page 171 For all options, if the file does not exist in the specified library, Query tries to create that file. Authority in Query for iSeries reports: Type the value for the kind of object authority that you want to give to other users for your database file. (This authority value is used only if your query creates a new file.) The values that you can specify are: *LIBCRTAUT Library create authority assigns the authority from the create authority (CRTAUT) value in the library...
Page 172: Summary-Only Output Of A Query For Iseries Report To A Database File
Summary-only output of a Query for iSeries report to a database file The data sent to a database file, when summary-only output is selected, is one of the following: v If no summary functions or report breaks have been selected, the output is a single record containing a count of the records selected by your query.
Page 173: Using An Output Database File Created By Query For Iseries
The number in parentheses, behind the explanation of the field, is the output field length and number of decimal positions. A blank break value is shown as a string of periods. Note that the overflow indicator column is blank, indicating all the data fits. Note: Truncation or rounding of averages is not considered overflow.
Page 174 Query for iSeries Use V5R2...
Page 175: Chapter 14. Specifying Query For Iseries Processing Options
(GT, GE, LT, and LE tests have always used the collating weight when applicable.) Also in previous releases, the collating sequence was only applied to SBCS characters in SBCS fields and constants. © Copyright IBM Corp. 2000, 2002...
Page 176: Rounding Numeric Field Values During Query For Iseries Processing
Rounding numeric field values during Query for iSeries processing You can control whether the result of numeric field calculations or field length changes should be rounded or truncated when your query is run. By typing a Options display, all of your numeric field calculation results are rounded (if necessary) when your query is run.
Page 177: Ignoring Character Substitution Warnings During Query For Iseries Processing
Whether you choose to have decimal data errors ignored depends on whether your data contains values that cause decimal data errors. You may want to define your query to ignore decimal data errors if the files it uses have zoned or packed numeric fields that contain data that is not normally interpreted as zoned or packed data.
Page 178 Query for iSeries Use V5R2...
Page 179: Chapter 15. Exiting And Running A Query For Iseries Query
If you change either the query name or the library, you create a new query with that name and library, and the original query remains unchanged. © Copyright IBM Corp. 2000, 2002...
Page 180: Describing The Query For Iseries Query Definition
Describing the Query for iSeries query definition You can also type some descriptive text about the query. The text reminds you what the query is for when it is displayed in a list of queries or printed on the cover page of a report. The text is displayed, for example, on the Work with Queries display whenever the Text column is shown.
Page 181: Running A Query For Iseries Query
Running a Query for iSeries query Running a query consists of telling Query for iSeries to use the query definition to acquire the data you want from selected files, to organize the data into a report format, and then to either display the report, print it, or store it in a database file.
Page 182: Running A Query For Iseries Query From The Work With Queries Display
You can run a query and get the report without saving the definition of the query only if you are running interactively. (The query must be saved to run in batch.) However: v If you are creating a query and choose not to save it, it runs only once and then is lost. To save your query, you need to supply values for the Library and Query prompts.
Page 183: Running A Query For Iseries Query Using The Runqry Command
Query displays a report, prints a report, or puts the data into a database file, depending on what is specified on the Select Output Type and Output Form display. For more information on selecting an output device, see Chapter 13, “Selecting output type and output form in Query for iSeries reports”. Query uses current data each time a query is run.
Page 184 Following are some suggestions for using the RUNQRY command: v If you want to use an existing query without changing the file or files to query, specify only a query and library name (without an input file name). This runs the query exactly as it was defined. v If you want to run a default query on a file, specify only an input file name.
Page 185: Chapter 16. Working With Query For Iseries Query Definitions
Each type of change may require that you select several definition steps on the Define the Query display by typing a in the Opt column next to the definition steps you want to change. © Copyright IBM Corp. 2000, 2002...
Page 186 The options currently defined for your query are shown with a > to the left of the option. You can change these and define additional options. You can choose as many options as you want from this display. Each of these options and their associated displays are described in previous chapters. Each option has its own corresponding display(s), on which you can make specific changes.
Page 187 When you finish making changes on all the displays you want to change, Query returns you to the Define the Query display, where the options now defined for your query are shown with a > to the left of the option.
Page 188: Considerations For Changing Query For Iseries Queries
Considerations for changing Query for iSeries queries To make changes to your query, choose the correct options on the Define the Query display. Refer to the appropriate chapters or appendixes in this guide for information and considerations on how to enter the definitions that you want.
Page 189: Copying A Query For Iseries Query Definition
Table 8. Effect of Collating Sequence and Processing Option on Selection. Either or both NAME1 and NAME2 are DBCS data. Yes means the record under test is selected. No means it is not selected. ColSeq=YES Unique Test Weights NAME1 EQ NAME2 NAME1 GE NAME2 NAME1 GT NAME2 NAME1 NE NAME2...
Page 190: Renaming A Query For Iseries Query Definition
To place the copy in the same library as the original, do not type anything in the To library prompt. Then select a choice in the Replace query prompt: v Type a if you want the copy to replace a query that has the same name in the library. v Leave the in the prompt if you want to copy the query to the library only if no other query with the same name already exists in the library.
Page 191: Displaying A Query For Iseries Query Definition
delete. (For more information about using lists, see “Using lists in Query for iSeries” on page 9.) Then press the Enter key. The Confirm Delete of Queries display appears. This display shows the query name(s) you chose to delete. (The above display shows some sample information.) Carefully check the names.
Page 192: Printing A Query For Iseries Query Definition
If you display a query and decide you want to change it, you have to return to the Work with Queries display and use option 2 (Change). You can print the information you see when you display a query, although the information will be in a different form. See “Printing a Query for iSeries query definition”. You may be able to print the definition of a query that you cannot change or display.
Page 193: Information Printed For A Query For Iseries Query Definition
When you select option 6 (Print definition) on the Work with Queries display and press the Enter key, the query definition is printed immediately. Information printed for a Query for iSeries query definition The printout includes the following information: v A header at the top of each page. This header lists information about the system you are using and when the definition was printed.
Page 194: Example Of Printed Record Format Information For A Query For Iseries Query Definition
Example of printed record format information for a Query for iSeries query definition The following is an example of one particular part of the query definition (the record format layout for the database output file) that can be printed when the query is run. The information shown below is printed only if the query is defined for database file output and, in this case, when it sends summary-only output to the database file.
Page 195 E means DBCS-either G means DBCS-graphic L means Date T means Time Z means Timestamp v The Null column shows whether the field is null capable (Y) or not (blank). v The Data Type column shows the data type for each field. v The Text column shows the comment (if it exists) for each field.
Page 196 Query for iSeries Use V5R2...
Page 198 Query for iSeries Use V5R2...
Page 199: Chapter 17. Additional Information About Query For Iseries For Programmers
DBCS-open field. If you use DBCS-graphic fields or constants in a concatenation operation, all fields and constants must be DBCS-graphic. If you use UCS2-graphic fields in a concatenation operation, all fields must be UCS2-graphic. © Copyright IBM Corp. 2000, 2002...
Page 200: Joining Files In Query For Iseries
Joining files in Query for iSeries For joining files (type of join): v Type 1 join is the same as for Query/36 (on System/36). v Type 2 join selects all the records in a primary file and records in the secondary files that match the primary records.
Page 201: Length And Decimal Positions Used For Internal Numeric Calculations In Query For Iseries
In calculations involving many large fields, you can control your results better by breaking the expression into parts, calculating each part as a separate result field. Then you can use these result fields in an expression to calculate the result field you want. You can specify length and decimal positions for the intermediate result fields to minimize the length of the final result field.
Page 202: Example: Increasing The Decimal Precision For Result Fields In Query For Iseries
Example: Increasing the decimal precision for result fields in Query for iSeries Figure 5 has a result field with a current maximum precision of 31 (length) and 2 (decimal positions). The decimal precision was derived from the calculation 31-29+9-9. Figure 5. Decimal Precision Using Default Note: The presentation length for RESULTA a would be 31,9.
Page 203: Selecting Records (Ignoring Field Case) In Query For Iseries
Figure 6. Changing Decimal Precision Selecting records (ignoring field case) in Query for iSeries To select records ignoring the case, do one of the following: v Define your own collating sequence such that uppercase and lowercase letters have the same weight. v Use a system sort sequence with shared collating weights.
Page 204 Query for iSeries Use V5R2...
Page 206 Query for iSeries Use V5R2...
Page 207: Appendix A. Differences Between Query For Iseries And Query/36
Report breaks for printed requests may be different in spacing. In Query/36, the report skips one line for report breaks unless it is defined to skip to a new page or defined to use double or triple © Copyright IBM Corp. 2000, 2002...
Page 208 spacing. In Query for iSeries, the report skips an extra line for report breaks unless it is defined to skip to a new page. This means the first detail line after a control break has an extra blank line printed between it and the last detail line before the break. Character fields In Query/36, character fields that were defined in IDDU with a length greater than 256 bytes were truncated at 256 bytes.
Page 209: Command Differences Between System/36 And Query For Iseries Commands
Command differences between System/36 and Query for iSeries commands The following table lists the System/36 Query commands and the equivalent OS/400 system commands (if appropriate) plus some system commands that work with queries: System/36 OS/400 System WRKQRY QRYRUN RUNQRY REMOVE DLTQRY FROMLIBR SAVOBJ...
Page 210 Query for iSeries Use V5R2...
Page 211: Appendix B. Practice Exercise For Query For Iseries Query
IDDU main menu. 6. Choose option 1 (Work with data definitions) on the IDDU main menu, and press the Enter key. © Copyright IBM Corp. 2000, 2002 If you press the Enter key, you will ; you can replace...
Page 212 7. You will name the file definition first, so select option 3 (File). (The name dictionary prompt on the following display; what you see on your display is the name of the data dictionary you just created, or the name of the data dictionary you last used. You may change the data dictionary.) Then press the Enter key.
Page 213 Now press F6. You will complete the description of the and describing the remainder of the field definitions) from the next display. 11. Complete the field description with its type, size, and any other descriptors, according to the table shown below. Then name and describe the remainder of the field definitions. Field Name Field Type Character...
Page 214 Press the Enter key without making any changes to the display. 13. When you see the Create and Select Field Definitions display, verify that the field names are arranged in the proper sequence. (When sequence numbers are the same, the arrangement of the field names on the display determines the sequence of use in the record.) If a field is out of sequence, type the necessary sequence numbers to indicate the correct arrangement, and press the Enter key.
Page 215: Query For Iseries Query Exercise: Creating A Database File
you press F3 (Exit) without making any changes to the display. You are finished creating the definitions describing a database file. The instructions to create the file and to enter data into the file are described in the following sections. Query for iSeries query exercise: Creating a database file After you have completed creating the necessary file definitions, you can create the database file using the Work with Database Files display.
Page 216 1. Choose option 3 (Work with database files) from the IDDU menu (or type line). 2. On the Work with Database Files display, specify option 2 (Enter data), your library for the library name. 3. Press the Enter key. The Work with Data in a File display is shown. 4.
Page 217 5. The Work with Data in a File display is shown again, but the fields are blank, ready for you to continue adding more data. 6. Continue adding the following data to the Work with Data in a File display: LASTNAME FIRSTNAME ADDRESS1 Daye...
Page 218: Query For Iseries Query Exercise: Creating And Running A Query
You do not need to change anything on this display. Press the Enter key. 8. The Work with Database Files display is shown. A message on the bottom of the display tells you that data in your file was changed. 9.
Page 219: Query For Iseries Query Exercise: Creating A Query
v Amount After you create and save the query, you run it as it exists and then change the query and run it again. Query for iSeries query exercise: Creating a query In this exercise, you create a query that lists on your display all the information in the file order the information exists in the file.
Page 220 Notice that Query has already supplied a 1 for the Specify file selections option on this display. This is because whenever you create a query, you must specify a file, but you do not have to select any other options on this display. 4.
Page 221 7. Press the Enter key. The Define the Query display is shown with the message The Specify file selections option has a > shown in the Opt column to indicate that it is a previously defined option. 8. Press F3 (Exit). The Exit This Query display is shown. Query supplies a (Yes) in the Save definition prompt and a prompt because it assumes you want to both save the query definition object and run the query.
Page 222 11. Press the Enter key to save the query definition object and run the query. The report produced by the query is shown on your display: The report lists all the information in the file defined to the system using IDDU. Press F20 (Right) to see the right side of the report.
Page 223: Query For Iseries Query Exercise: Changing A Query
Query for iSeries query exercise: Changing a query If you want a report of only those customers who live in the state of Louisiana, you need to change the query to list customers from that state only. 1. Type a 2 (Change) for the Option prompt on the Work with Queries display. 2.
Page 224 6. Press the Enter key. The Select Records display is shown. The fields in the file single-column list format, that is the Text, Len, and Dec prompts are not shown, press F11.) You can move your cursor to any place in the list and press the Page Down key to see more field names. 7.
Page 225 This tells Query for iSeries to test the field named with that value will be included in your report. (If you are using a unique weight system sort sequence table or *HEX, make sure you type it as what the field contains and not the field name. You must also type this value in all uppercase letters because that is the way it is specified in the field.
Page 226 10. Press F3 (Exit), F12 (Cancel), or the Enter key to return to the Select Records display. Then press F3 (Exit). The Exit This Query display is shown. You can use this display to run the query with the changes you made. You can also choose to make the query changes either permanent or for this time only.
Page 227: Query For Iseries Query Exercise: Creating An Advanced Query
12. Press the Enter key. The Work with Queries display is shown. To sign off the system or use it for something other than Query, press F3 (Exit) to return to the menu from which you chose to use Query. Query for iSeries query exercise: Creating an advanced query In the following example, you create a file named (DSPOBJD) command.
Page 228 1. On the command line of the OS/400 Main Menu, type the DSPOBJD command as shown on the following display and press the Enter key. This creates the file named library QGPL. Note: When you type will receive an error message. You must type in the entire command exactly as it appears on the display that follows.
Page 229 4. On the Define the Query display, select definition steps Select and sequence fields, Select records, Select sort fields, Specify report column formatting, Select report summary functions, and Define report breaks by typing a 1 to the left of each of them. (Notice that Specify file selections is already selected for you.) Press the Enter key.
Page 230 The message appears. Press the Enter key again. 6. The next display to appear is the Select and Sequence Fields display. Query for iSeries Use V5R2...
Page 231 If your display is in multiple-column format (that is, if the Text, Len, and Dec columns are not shown), press F11 (Display text) to show some additional information about the fields in the list. Select the fields , and by typing the numbers 1 through 5 to the left of them as shown below.
Page 232 Press the Enter key again. 7. The next display shown is the Select Records display. Here, you specify which records you want included in your report. For your report, you want all records for objects with an object type of either *FILE or *PGM. Type the information in the Field, Test, and Value columns as shown in the following display.
Page 233 8. Now display your report by pressing F5 (Report). The Display Report display appears showing your query report, which is based on the query you have defined up to this point. (The information you see on your display depends on the objects that are currently in QGPL on your system. What you see may not match what is shown in the display that follows.) On the bottom far right side of the display, the message appears.
Page 234 On the Select Records display, press the Enter key. 9. The next display to appear is the Select Sort Fields display. Here you specify which fields you want Query to use to sort the selected records for your report. You want the records sorted first by object type and then by object size, so type a next to and a...
Page 235 Press the Enter key again. 10. The next display is the Specify Report Column Formatting display. It is here that you specify column spacing, column headings, and other report formatting options. Query supplies much of this information for you. Change the column heading for the field so that it reads instead of...
Page 236 Press the Enter key. 11. The next display you see is the Select Report Summary Functions. By specifying options on this display, you can have selected fields summarized in your report—that is, for a selected field, you can specify that a total, average, minimum, maximum, and (or) count be included in your report. Specify that you want all the summary functions (total, average, minimum, maximum, and count) for the field ODOBSZ by typing 1 through 5 next to the field as shown in the following display.
Page 237 Press the Enter key. 12. The next display to appear is the Define Report Breaks display. On this display you specify which fields you want used as break fields. Report breaks are used to break the report into groups of records each time the value of the report break field changes.
Page 238 Press the Enter key. 13. On the next display, Format Report Break, you specify the formatting you want for the report break you defined. Note that the value in the Break level prompt is zero. You can use break level 0 to print the final summary values for all specified summary functions at the end of the report.
Page 239 Press the Enter key. You are finished with all of the definition steps that you selected earlier, so the Define the Query display appears again. (The definition steps you selected earlier are now indicated with a > symbol to the left of them.) 14.
Page 240 On the bottom far right side of the display, the message report does not fit on the display. Use the page keys or F20 (Right) and F19 (Left) to page through the report (both left to right and top to bottom) so that you can see the breaks and summaries in the report.
Page 241 Press the Enter key. 16. The Work with Queries display appears, showing the message Now you can return to the OS/400 Main Menu by pressing F3 (Exit) to complete your work with this example. Appendix B. Practice exercise for Query for iSeries query...
Page 242 Query for iSeries Use V5R2...
Page 243: Appendix C. Query For Iseries Performance Tips And Techniques
Query requires one. These options can be expensive in terms of processing unit, I/O, and storage requirements and can result in longer response times. © Copyright IBM Corp. 2000, 2002...
Page 244: Select/Omit Access Paths In Query For Iseries
Note: Having existing access paths is important because a temporary access path created by Query is not saved. It must be created each time that particular query is run. Select/omit access paths in Query for iSeries Use the CRTLF command to create access paths with select/omit tests specified in the DDS. Specify the select/omit tests in such a way that they match part or all of the selection tests from one or more queries.
Page 245: File Definitions And Data In Query For Iseries
Each time you run a saved query, Query validates the access plan by checking that the files and access paths named in the plan still exist. If it is valid, Query uses that plan to access the data. This can result in a significant performance gain, when compared with running queries without stored access plans, because Query does not have to validate the access path when running a stored query.
Page 246: File Data Considerations For Query For Iseries
stored in the low-order four bits of each byte. The high-order four bits of the low-order byte contain the sign. The high-order four bits of all other bytes contain all 1s (1111). For example, the binary representation of +123 in zoned decimal format is 1111 0001 1111 0010 1111 0011. If a file contains numeric fields defined as zoned (which is common on files migrated from a System/36), the iSeries system converts the fields from zoned to packed for the arithmetic operation and then converts them back to zoned when the resulting value is returned to the program.
Page 247: Define Result Fields In Query For Iseries
retrieve the text for each field. Also, showing the text for each field results in fewer fields being shown on each display so you have to page through more displays to retrieve the fields you need to view. This is also true for query, file, member, and format lists.
Page 248: Select Sort Fields In Query For Iseries
Note: If there is no existing access path matching some of the record selection tests, Query does not build an access path solely for purposes of selection. Query reads each record and selects those that qualify. If you request a particular query often, consider creating an access path with select/omit tests to match that query.
Page 249: Select Collating Sequence In Query For Iseries
input or original file name. This produces a new database file containing the sorted records. The time required to produce the sorted records may vary, depending on whether Query has to build an access path to do the sort. Consider these items before performing this type of operation: v Any changes to the original file are not automatically reflected in the sorted file unless you provide for this with some type of additional maintenance.
Page 250: Specify Processing Options In Query For Iseries
However, if the data you want to view is at the end of the searched records, it may take longer than anticipated to display those records. This may also be true when paging down to access more data (even if the first display did appear quickly) if the additional data is located much further into the file. If Query has to build a large access path prior to retrieving data it takes longer to reach the first display.
Page 251: Performance Tips For Join Operations In Query For Iseries
Option 2—Matched records with primary file A record from the primary file is selected regardless of whether there is a match with any of the secondary files. The selection of primary records is dependent on the select/omit criteria specified on the Select Records display. Only primary records that meet those criteria are selected. Select/omit criteria specified against secondary files may cause a record from the primary file not to be selected.
Page 252: Miscellaneous Tips And Techniques For Query For Iseries
Be careful with using the “NE” comparison between fields from different files on the Specify How to Join Files display. This could result in a large number of records being selected and a large amount of I/O being performed. Query runs more efficiently when the files are ordered from smallest to largest. In this case, smallest means the file from which the fewest records are selected for the join.
Page 253: Query For Iseries Performance Tuning
Query for iSeries performance tuning A properly tuned system provides much better overall performance than one in which performance tuning has not been used. However, there are many factors to consider when properly tuning the system to meet your needs. See the Work Management topic for details. In addition, the Performance Tools for iSeries book explains how to monitor and understand overall system performance.
Page 254: Query For Iseries Debug Mode Messages
display and also the total amount of time it takes the query to run. If a significant portion of the query run time is spent building an access path, attempt to use the tips and techniques previously listed on using access paths. Query running.
Page 255: Appendix D. Preventing Users From Running Query For Iseries Queries Interactively
QSYS in the library list of the users who need to run queries interactively. Note: If you add a new release of the operating system, you may need to redefine the RUNQRY command parameters. © Copyright IBM Corp. 2000, 2002...
Page 256 Query for iSeries Use V5R2...
Page 257: Appendix E. Coded Character Set Identifiers (Ccsids) In Query For Iseries
The problem does not end with displaying and printing. If data is collated using a sequence prepared from characters in a different code page, or if data in different code pages is compared without being converted, the result will seem incorrect. © Copyright IBM Corp. 2000, 2002...
Page 258 Figure 7. Working with and without CCSID Conversion. Conversion, when necessary and possible, is done automatically by the system. Query for iSeries recognizes when conversion of data, collating sequences, and text is needed, and performs the conversion. The system notifies you if the conversion fails. You may not be able to work on a query or run a saved query depending on the CCSIDs involved.
Page 259: Ccsid Marking In Query For Iseries
CCSID marking in Query for iSeries The following bulleted objects contain CCSID tags used by query processing. Each item inherits its CCSID from the containing item unless marked with an asterisk. An asterisk indicates that the object has its own CCSID tag.
Page 260: Ccsids And Collating Sequences In Query For Iseries
- Text about the member * - Data (the CCSID tag or tags are in the format definition) Notes: 1. The CCSID of the query definition is shown on displays that show the query name, like the Define the Query display. 2.
Page 261: Query For Iseries Language Sequences
Note: The query profile, user-defined collating sequence default, and CCSID are only updated if you press F23 from the Define Collating Sequence display to save the sequence. Query for iSeries language sequences Following are the language sequences that are supported by Query for iSeries. Table 9.
Page 262: Changing A Query For Iseries Query
Table 10. How CCSIDs Affect Displaying a Query Job CCSID 65535 65535 Changing a Query for iSeries query You can change the query definition if the process (job) CCSID and the query definition CCSID match, if either is marked with a CCSID of 65535 (do not convert), or if the query definition is not marked. Table 11 shows when a query can be changed and how the constants are treated.
Page 263: Getting A List Of Formats Or Members With Text In Query For Iseries
For example, the language sequence is used to initialize the Define Collating Sequence display if there is no previously defined sequence for the query and the default sequence from the query-user’s profile is converted with warnings about the use of substitution characters. Query for iSeries warns you on the Select Collating Sequence display when the sequence previously defined for this query cannot be converted.
Page 264: Ccsids And Query For Iseries Query Definition Items
Table 12. How CCSIDs Affect Run-Time Record Selection Job CCSID 65535 65535 CCSIDs and Query for iSeries query definition items Query for iSeries warns you about some CCSID compatibility problems as you are working on a query definition but some problems are not diagnosed until the query is run. To understand the results, you need to know: v How CCSIDs are assigned to constants and expressions v The order in which conversions and comparisons are performed...
Page 265: Ccsid And Result Field Column Headings In Query For Iseries
CCSID and result field column headings in Query for iSeries A column heading in a result field definition is converted, if necessary, to the job or document CCSID when it is passed back to the document or shown in a report. Dots (...) are shown if the conversion fails. CCSID and sort fields in Query for iSeries If the CCSID of the sort field is different from the collating sequence CCSID, the sequence is converted to the field CCSID.
Page 266: Ccsid Compatibility Considerations In Query For Iseries
CCSID compatibility considerations in Query for iSeries CCSID marking makes it possible for conversions to be performed before presentation of marked material or use of marked material in comparisons, but can also restrict the use of various combinations of marked items.
Page 267 Table 13. CCSIDs Not Compatible-Definition-Time Consequences (continued) Item 1 Item 2 Collating sequence Field Field 2/constant Field 1 Field 2/constant Field 1 1. This can happen when you specify that character substitution warnings not be ignored on the Specify Processing Options display.
Page 268 Table 14. CCSIDs Conversion Problems-Run-Time Consequences (continued) Item 1 Item 2 Collating sequence Note: For output to display, Query uses a single diagnostic message to warn you to look for unwanted dots and missing edit word text. There is no warning when you print a report. 1.
Page 269 Table 16. How CCSIDs Affect Query Use Job CCSID Query CCSID 65535 65535 65535 No tag No tag 1. Either a warning or request ending message is issued. Run Query Change Query allowed allowed Appendix E. Coded character set identifiers (CCSIDs) in Query for iSeries Run time record Display Query selection...
Page 270 Query for iSeries Use V5R2...
Page 271: Bibliography
Printer Device Programming, SC41-5713-05, provides the application programmer and system operator with information to understand and control printing. v IBM Personal Computer Disk Operating System Reference SC21-8090, provides personal computer users with information on how to use DOS on their personal computer.
Page 272 Query for iSeries Use V5R2...
Page 273: Notices
Any reference to an IBM product, program, or service is not intended to state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or service that does not infringe any IBM intellectual property right may be used instead.
Page 274: Trademarks
The licensed program described in this information and all licensed material available for it are provided by IBM under terms of the IBM Customer Agreement, IBM International Program License Agreement, or any equivalent agreement between us. If you are viewing this information softcopy, the photographs and color illustrations may not appear.
Page 275: Index
105 assumed values 7 authority for query database file output 159 © Copyright IBM Corp. 2000, 2002 authority (continued) giving to others for your query 168 security 159 authorization list name authority 159, 168 average summary function...
Page 276 CCSID (coded character set identifier) (continued) definition 246 displaying 17 displaying query 249 edit word 132 file selection 252 footing 253 join tests 252 language code page 256 marking 247, 254 page heading 253 query options list 256 running 256 table 256 query display table 249...
Page 277 coded character set identifier (CCSID) (continued) sort field 253 summary function 253 translation table 116 collating sequence 87, 111, 112, 249 affecting query 112, 165 CCSID (coded character set identifier) 114 collating sequence 256 default 248 hexadecimal 248 language 248 translation table 248 user-defined 248 considerations when changing for query 176...
Page 278 constant (continued) graphic migration 241 numeric record selection test 94 result field 59 control language (CL) 5 creating database files 5 Copy Queries display 177 copying query definition 173, 177 count summary function null value 136 cover page CCSID (coded character set identifier) 253 defining for printout 152 Create and Select Field Definitions display (IDDU) 200, 202...
Page 279 date, time, and timestamp field (continued) migrating N to N-1 241 date, time, and timestamp value average summary function 136 comparing to null 96 IS, ISNOT test 96 total summary function 135 date/time field editing 127 separator 128 DAY function argument rules 75 syntax diagram 75 DAYS function...
Page 280 definition (continued) changing 30, 173 changing (example) 211 copying 177 copying before changing 173 creating 27, 30 creating (example) 206 deleting 179 displaying 179 DLTQRY command 178 information printed 181 migrating restriction 197 naming 27 printing 180 renaming 178 running 8 saving 167 selecting files 33 specifying authority 168...
Page 281 EQ (equal) comparison test 95, 96, 165 error handling 18 ignoring in numeric field 164 Specify File Selections display 36 EUR (IBM European standard) date and time 67 example changing query definition 211 code page 114 connecting record selection test 101...
Page 282 field (continued) length and decimal position in report 120 missing from file definition during file selection process 55 starting to change or display a query 55 name numeric 59 null 8 numeric editing 122 using in result fields 59 omitting from report 121 result creating 57 DBCS 187...
Page 283 HOUR function argument rules 76 syntax diagram 76 how a collating sequence affects Query for iSeries 112 how to join files 43 IBM European standard (EUR) date and time 67 IBM USA standard (USA) date and time 67 ID (identifier)
Page 284 increasing the decimal precision 190 information for programmers, advanced 187 interactive data definition utility (IDDU) data dictionary 4 definition 199 example creating IDDU definition 199 introduction 4 main (system) menu 199 interactive processing limiting 243 restricting 243 internal numeric calculations length and decimal positions 189 International Standards Organization (ISO) date and time 67...
Page 285 list (continued) selecting items how 10 library name 10 queries 10 query name 10 selecting items from 16 library 14 subset Work with Queries display 14 LIST (list) comparison test 95, 97, 165 LOCAL time standard date and time 67 logical file definition 5 using with Query or DB2 UDB for iSeries...
Page 286 object DB2 UDB for iSeries program 5 query definition 5 omitting field from report 121 OO (double-byte O) 8 operator concatenation (||) 60 null value 60 numeric 58 optimization definition 231 OR condition specifying selection tests 236 OR connection record selection test 101 output database file defining 154...
Page 287 query definition (continued) DLTQRY command 11, 178 Query for iSeries Utilities menu 11 Work with Queries display 178 displaying 179 exiting 167 information printed 181 migrating restriction 197 naming 27 naming restrictions multilingual environment 27 printing 180 Query for iSeries menu deleting from 11 renaming 178 running 8...
Page 288 report (continued) summary function 237 specifying 135 types 135 using F13 to display layout 8 using F5 to display 8, 169 restriction migration definitions 197 result field adding 86 CCSID (coded character set identifier) 252 character 60 character constant 60, 66 column heading 85 concatenation (||) operation 60 creating 57...
Page 289 selecting (continued) items from list 10 library for file selection 34 matched records primary file 48 selected files 47 members for file selection Select Member display 39 Specify File Selections display 35 options query definition 29 output type and output form 145 queries from a list Work with Queries display 14 record for query 91...
Page 290 UCS2 level 1 support 4 unmatched records with primary file join field length restrictions 44 records in primary file without matching records 43 USA (IBM USA standard) date and time 67 user-defined collating sequence 115 using collating sequence 165 value...
Page 291 value length date 120 time 120 timestamp 120 VARCHAR function 63 VARGRAPHIC function 65 variable-length field release restrictions 241 verifying choice creating or changing query definition 36 viewing queries on display 8 word, edit numeric field 131 Work with Database Files display (IDDU) 203 Work with File Definitions display (IDDU) 202 Work with File Definitions Display (IDDU) 200 Work with Queries display...
Page 292 Query for iSeries Use V5R2...
Page 294 Printed in U.S.A.

This manual is also suitable for:

@server iseries

IBM ISERIES SC41-5210-04 User Manual

About Query for Iseries Use

Part 1. Introduction to Query for Iseries

Chapter 1. What Is Query for Iseries

Chapter 2. General Operating Information for Query for Iseries

Part 2. Defining and Using Query for Iseries Query Definitions

Chapter 3. Creating a Query for Iseries Query Definition

Chapter 4. Specifying and Selecting Files for a Query for Iseries Query

Chapter 5. Defining Result Fields in Query for Iseries

Chapter 6. Selecting and Sequencing Fields in Query for Iseries

Chapter 7. Selecting Records in Query for Iseries

Chapter 8. Selecting Sort Fields in Query for Iseries

Chapter 9. Selecting a Collating Sequence in Query for Iseries

Chapter 10. Specifying Report Column Formatting in Query for Iseries

Chapter 11. Specifying Report Summary Functions in Query for Iseries Reports

Chapter 12. Defining Query for Iseries Report Breaks

Chapter 13. Selecting Output Type and Output Form in Query for Iseries Reports

Chapter 14. Specifying Query for Iseries Processing Options

Chapter 15. Exiting and Running a Query for Iseries Query

Chapter 16. Working with Query for Iseries Query Definitions

Part 3. Advanced Information about Query for Iseries

Chapter 17. Additional Information about Query for Iseries for Programmers

Part 4. Appendixes

Appendix A. Differences between Query for Iseries and Query/36

Appendix B. Practice Exercise for Query for Iseries Query

Quick Links

Related Manuals for IBM ISERIES SC41-5210-04

Summary of Contents for IBM ISERIES SC41-5210-04