ERserver iSeries Query for iSeries Use Version 5 SC41-5210-04
ERserver iSeries Query for iSeries Use Version 5 SC41-5210-04
Note Before using this information and the product it supports, be sure to read the information in “Notices” on page 261. Fifth Edition (September 2002) This edition applies only to reduced instruction set computer (RISC) systems. © Copyright International Business Machines Corporation 2000, 2002. All rights reserved. US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
Contents About Query for iSeries Use . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Who should read the Query for iSeries Use book . . . . . . . . . . . . . . . . . . . . . ix Part 1. Introduction to Query for iSeries . . . . . . . . . . . . . . . . . . . . 1 Chapter 1. What is Query for iSeries? . Query for iSeries fundamentals . . . . . Files, fields, and record formats in Query Query for iSeries definitions . . . . . Libraries in Query for iSeries . . . . .
Selecting files on the Query for iSeries Select File display . . . . . . . . . . . Selecting file members on the Query for iSeries Select Member display . . . . . . Selecting record formats on the Query for iSeries Select Record Format display . . . Displaying all files selected on the Query for iSeries Display File Selections display . . Joining files in a Query for iSeries query . . . . . . . . . . . . . . . . . . Types of joins in a Query for iSeries query . . . . . . . . . . . . . . . .
Letting Query for iSeries select records . . . . . . . . Selecting the records you want in Query for iSeries . . . . Comparison fields in Query for iSeries . . . . . . . . Comparison values in Query for iSeries . . . . . . . Comparison tests in Query for iSeries . . . . . . . . Using more than one comparison test in Query for iSeries Adding and removing comparisons in Query for iSeries . . . . . . . 91 . 91 . 92 . 93 . 95 . . . . . . . . . . . . . . . . 101 . . . . . . . . . . . . . . . . 103 Chapter 8.
Record on one page in Query for iSeries reports . . . . . . Displaying wrapping widths in Query for iSeries reports . . . . Defining output to the printer in Query for iSeries reports . . . Defining output of Query for iSeries reports to a database file. . Using an existing output file definition in Query for iSeries reports Building a new output file definition in Query for iSeries reports . Using an output database file created by Query for iSeries . . . . . . . . . . . . . . . . . . . . . . . . . . .
Appendix A. Differences between Query for iSeries and Query/36 . . Conceptual Differences . . . . . . . . . . . . . . . . . . . . Operational Differences . . . . . . . . . . . . . . . . . . . . Command differences between System/36 and Query for iSeries commands Migration differences between System/36 and Query for iSeries definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 195 195 197 197 Appendix B.
Running a Query for iSeries query . . . . . . . . . . . . Running a default query in Query for iSeries . . . . . . . . Selecting records at run time in Query for iSeries . . . . . . CCSIDs and Query for iSeries query definition items . . . . . . CCSID and file selections in Query for iSeries . . . . . . . CCSID and join tests in Query for iSeries . . . . . . . . . CCSID and result field expressions in Query for iSeries . . . . CCSID and result field column headings in Query for iSeries . .
About Query for iSeries Use Query for iSeries is a decision support utility you can use to obtain information from the DB2 UDB for iSeries. This book describes how to use Query for iSeries to select, arrange, and analyze information stored in one or more database files to produce reports and other data files. You may need to refer to other IBM books for more specific information about a particular topic. For a list of publications related to this book, see the “Bibliography”.
x Query for iSeries Use V5R2
Part 1. Introduction to Query for iSeries Chapter 1. What is Query for iSeries? . . . . . . . . . . . Query for iSeries fundamentals . . . . . . . . . . . . . . . Files, fields, and record formats in Query for iSeries . . . . . . Double-byte character set (DBCS) fields in Query for iSeries . . UCS2 level 1 character set support in Query for iSeries . . . . Data definition languages and utilities support in Query for iSeries Query for iSeries definitions . . . . . . . . . . . . . . .
2 Query for iSeries Use V5R2
Chapter 1. What is Query for iSeries? Query for iSeries is an IBM® licensed program and a decision support utility that can be used to obtain information from the DB2 Universal Database for iSeries database. It can obtain information from any database files that have been defined on the system using Operating System/400 (OS/400®) data description specifications (DDS), the OS/400 interactive data definition utility (IDDU), or DB2 UDB for iSeries Structured Query Language (SQL).
Another record format, named ZIPADDRESS, might be defined for a file that might be location-oriented and contain only the ZIPCODE, STATE, CITY, and STREETADDR fields, in that order (see Figure 2). ZIPCODE STATE CITY STREETADDR 67891 OR Mushroom Manor 907 Abbey Hwy 67891 OR St. Paul 19821 Metro Hwy. 67890 OR Piney Island 961 S 19th Ave Figure 2. Three Records in NAMEADDR File (Using Record Format ZIPADDRESS) Query retrieves the data you want from the files you choose.
| v CL and DDS use the same terms and descriptions as IDDU, but they provide additional support for files. | Using DDS and CL commands, you can define and create physical files and logical files to indicate how | fields are to be organized in files. | A physical file contains the fields of data, as records, but logical files do not. A physical file contains at | least one record format. The field order in a physical file determines the format of the records.
Figure 3.
Chapter 2. General operating information for Query for iSeries This chapter describes the general operating information for Query, such as getting started with Query, using prompts and lists, how you can check your query definition before it is finished, how to use the function keys, and error recovery procedures. Practicing with Query for iSeries Appendix B, “Practice exercise for Query for iSeries query”, provides practice examples that you can do in order to quickly become familiar with Query.
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).
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.
The number of items shown in the list part of the display varies, depending on what form the list is in and the amount of space used by the prompt part of the display. Sometimes a new display will appear that shows the list. If all items in a list do not fit on the display, you can use the page keys to page forward and backward through the list.
Using Query for iSeries commands A command is a statement used to request a function of the system. This means you need only remember a command that is a few characters long instead of remembering all the individual instructions or taking the time to go through a series of menus. Query has four commands, which can be entered from any command line: v STRQRY brings up the Query menu. v WRKQRY brings up the Work with Queries display.
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.
| 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).
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).
library group are shown. For example, if you typed IN* in the Subset prompt, you might see a list of query names like: INTEREST, INTFEB, INTMARCH, INVENTORY, INVFEB, INVMARCH, and so on. (The queries are shown alphabetically for each library.) You can also specify a complete name (without an *) in the Subset prompt. Continuing the previous example, if you want to see which libraries contain queries named INVENTORY, type that complete query name in the Subset prompt.
Work with Queries Library . . . . . . . . Subset . . . . . . . . Position to . . . . . . QGPL __________ __________ Name, *LIBL, F4 for list Name, generic* Starting character(s) Type options (and Query), press Enter.
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.
v Change or display a query from the Work with Queries display or use the run-time record selection option of the RUNQRY command, and the query was defined with a numeric constant and the decimal separator saved with the query does not match your current decimal separator. v Press F17 from the Select Records display. v Press F17 from the Define Result Fields display. OS/400 date formats are MDY, YMD, DMY, and JUL. The valid OS/400 time format is HHMMSS.
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. On the Exit This Query display, indicate whether you want to save or run the query (or do both), and then press the Enter key.
20 Query for iSeries Use V5R2
Part 2. Defining and using Query for iSeries query definitions Chapter 3. Creating a Query for iSeries query definition . . . . . Starting Query for iSeries query definition . . . . . . . . . . . Selecting definition steps when defining a Query for iSeries query . . Selecting options for a Query for iSeries query definition . . . . . Moving through the Query for iSeries definition displays . . . . . When you return to the Query for iSeries Define the Query display .
Query for iSeries date . . . . . . . . . . . . . . . . . . . . . Query for iSeries time . . . . . . . . . . . . . . . . . . . . . Query for iSeries timestamp . . . . . . . . . . . . . . . . . . Displaying constants format in Query for iSeries . . . . . . . . . . . . Date, time, and timestamp arithmetic operations in Query for iSeries . . . . . Date arithmetic operation in Query for iSeries . . . . . . . . . . . . . Subtracting dates in Query for iSeries . . . . . . . . . . . . . . .
Numeric constants as values in Query for iSeries. . . . . . . . . . . . . . . . Date, time, timestamp constants as values in Query for iSeries . . . . . . . . . . Null values in Query for iSeries . . . . . . . . . . . . . . . . . . . . . . Comparison tests in Query for iSeries . . . . . . . . . . . . . . . . . . . . . Date, time, or timestamp comparisons in Query for iSeries . . . . . . . . . . . . Testing for equal (EQ) and not equal (NE) in Query for iSeries . . . . . . . . . . .
Edit code in Query for iSeries reports . . . . . . . Optional edit code modifier in Query for iSeries reports . Specifying edit words . . . . . . . . . . . . . . Edit word in Query for iSeries reports . . . . . . . Edit word for summary total in Query for iSeries reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 131 131 131 133 Chapter 11.
Using function key F5 when running a Query for iSeries query . . . Running a Query for iSeries query from the Query for iSeries menu . Running a Query for iSeries query from the Exit This Query display . Running a Query for iSeries query from the Work with Queries display Running a Query for iSeries query using the RUNQRY command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 169 169 170 171 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
26 Query for iSeries Use V5R2
Chapter 3. Creating a Query for iSeries query definition This chapter describes the process of creating a query (that is, defining a query definition object) that can be used to query one or more files in the DB2® UDB for iSeries. The chapter begins with selecting option 1 (Create) on the Work with Queries display, and then it introduces the Define the Query display and the 11 possible definition steps you can use to define a query or change part of an existing query definition.
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. The generic name shows in the Subset prompt when the list is shown. When you are creating a query, you can check this list to see what names are already used before you choose a new name.
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. Following are brief descriptions of each of the definition steps.
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.
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. Select and sequence fields and specify sort fields before you reformat columns. 4. Select sort fields before you define report breaks. 5.
32 Query for iSeries Use V5R2
Chapter 4. Specifying and selecting files for a Query for iSeries query This chapter describes how you select and use one or more database files that are to be queried for information. Specifying file selections (the first option on the Define the Query display) is the first of the 11 steps that you can use to define a query.
Specify File Selections Type choices, press Enter. Press F9 to specify an additional file selection. File . . . Library Member . . Format . . F3=Exit F12=Cancel . . . . . . . . . . . . . . . . . . . . . . . . . . . .
v If you type a generic library name (in the form of ABC*) or special library name in this prompt and press 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.
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 T01 as the file ID of your first file.
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. For example, you may have typed the name of a file that you do not have the authority to use. If there is more than one error, you will see the next one after you have corrected the first one.
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. If you want to select a particular library from a list of libraries, press F4 with the cursor positioned in the Library prompt.
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. (Do not add an * after the starting characters in this prompt.
Select Member File ID . . . . . : File . . . . . . . : Library . . . . : T01 EXAMPFILE1 CUSTINV Type option (and Member), press Enter. 1=Select Opt Member _ __________ _ EXAMPMBR1 _ EXAMPMBR2 _ EXAMPMBR3 Bottom F11=Display text F12=Cancel 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.
Select Record Format File ID . . . . . : File . . . . . . . : Library . . . . : T01 EXAMPFILE1 CUSTINV Type option (and Format), press Enter. 1=Select Opt Format _ __________ _ EXAMPFMT1 _ EXAMPFMT2 _ EXAMPFMT3 Bottom F11=Display text F12=Cancel F23=Long comment 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.
Display File Selections ID T01 T02 File EXAMPFILE1 EXAMPFILE2 Library CUSTINV CUSTINV Press Enter to display the join tests. Member *FIRST *FIRST Format EXAMPFILE1 EXAMPFILE2 Bottom F12=Cancel 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.
Types of joins in a Query for iSeries query Specify Type of Join Type choice, press Enter. Type of join . . . . . . . . F3=Exit F12=Cancel F5=Report F13=Layout 1 1=Matched records 2=Matched records with primary file 3=Unmatched records with primary file F10=Process/previous F18=Files 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.
Note: See “CCSID and join tests in Query for iSeries” on page 252 for information on how CCSIDs can affect your join selections. Specify How to Join Files Type comparisons to show how file selections related, press Enter.
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.
- 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 – DBCS-only field with: - DBCS-either - DBCS-open - DBCS-only – DBCS-graphic field with: - DBCS-graphic – UCS2-graphic field with: v v v v - UCS2-graphic Only the data fields specified in the join specifications for each file are used to join the files.
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 A.NAME EQ B.NAME was specified on the Specify How to Join Files display.
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. (The primary file is always the one that was selected first in your query definition.
In our example, the RESIDENTS 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 PHONELIST 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. (The NAME field is used again as the comparison test field in both files.
Correct method: On the Specify File Selections display, type the CUSTOMER file first, the PURCHASE file second, and the ITEM file third. Specify the file IDs A, B, and C, respectively. The join type is 2 (Matched records with primary file). The join tests are: A.NAME B.ITEM EQ EQ B.NAME C.ITEM Query performs the file join in two steps: Step 1: Join the first two files, A and B. Query joins the files in the order listed, starting with file A (CUSTOMER), and file B (PURCHASE).
Incorrect method: On the Specify File Selections display, type the CUSTOMER file first, and specify the file ID as A. Type the ITEM file second (file C), and the PURCHASE file third (file B), which is in the opposite order from the correct method. Note that each file has the same file ID as before. Specify the join type and join tests the same as before. This method does not follow the secondary file sequence rule because file C is not connected by a join test to the file (A) listed above it. Chapter 4.
Query performs the file join in two steps: Step 1: Join the first two files, A and C. Query joins the files in the order listed, starting with file A (CUSTOMER), and file C (ITEM). But there is no join test that compares a field in A to a field in C.
every record in file C. Because there are three records in A and five records in C, the result is 15 records. The join tests are not used in this step. The result of step 1 this time is a working file called AC. Step 2: Join file AC to file B. Query joins each record in file AC to every record in PURCHASE file B for which A.NAME equals B.NAME and B.ITEM equals C.ITEM. If a record in AC (such as Martinez) has no match in B, Query joins it to a default record for file B, which is blank.
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 CUSTNUM exists in files T01 and T02 used in this query, they would be shown as T01.CUSTNUM and T02.CUSTNUM. To find the files associated with the file IDs (such as T01), press F12 to show the Display File Selections display. The Test column shows the test values that determine how the files are to be joined.
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. As a result, this display was shown with the missing fields.
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.
Chapter 5. Defining result fields in Query for iSeries This chapter describes how you define result fields. They need to be defined in your query if the information that you want to present in your report does not exist as a field in your selected file(s). For example, you want your report to show the number of days, but your database file only has a field containing the number of weeks.
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).
DAY 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) − (Subtraction) CHAR TIMESTAMP If any argument can be null, the result field can be null. If any argument is null, the result is null.
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, 60 * (A − (B + C)), the calculations are done for the innermost pair first (in the example, B + C), 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.
For example, if a character constant ’Dr. ’ and a character field named LASTNAME containing the value Smith are concatenated, the result is a field containing the value Dr. Smith. Other examples of character expressions are: NAME ’Mr.’ ’Mr.’ || NAME FIRSTINIT || MIDINIT 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.
Example of a character field substring: If a character field named ALPHA containing the value ABCDEFGHI is used in SUBSTR(ALPHA,4,3), the result is a character field containing DEF. If you do not specify a value for Length, the result is DEFGHI. If the Offset and Length values cause the substring to exceed the right end of the field, you will receive an error message. If any argument can be null, the result field can be null. If any argument is null, the result is null.
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. If the arguments are times, the result is a time. If the arguments are timestamps, the result is a timestamp.
v If the first argument is UCS2 graphic data and the result is mixed data, the result length is (2.5*(n-1)) + 4. 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. The result of the function is the character string that would result if the character string expression was assigned to a varying-length host variable with a length attribute of n, where n is the length attribute of the result.
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. QQ VARGRAPHIC ( expression , ) length DEFAULT QR ,ccsid The VARGRAPHIC function returns a graphic string representation of a string expression. The first argument must be a string expression and must not be bit data.
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’ and X’0F’.
Query for iSeries date A date expression is a three-part value (year, month, and day) designating a point in time under the Gregorian calendar, which is assumed to have been in effect from the year 1 A.D. 1 The year range is 0001 to 9999. The month range is 1 to 12. The day range is 1 to x, where x is 28, 29, 30, or 31, depending on the month. A date starts with a digit and has a length of at least 6 characters. Trailing blanks can be included. Leading zeros can be omitted from the month and day portions.
Valid formats for times are listed in Table 2. Each format is identified by name and includes an associated abbreviation (for use by the CHAR function) and an example of its use. Table 2. Formats for Representations of Time Data Types Format Name Abbreviation Time Format Example International Standards Organization ISO hh.mm.ss 13.30.05 IBM USA Standard USA hh:mm am or pm 1:30 pm IBM European Standard EUR hh.mm.ss 13.30.
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. Display Constants Format Query . . . . . . : Library . . . . : QRY1 QGPL Option . . . . . : CCSID . . . . . . : CHANGE 65535 Use an SAA format to enter a date or time constant, or use the format described by the following values. Use the decimal separator shown. Query definition values Date format . . .
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.
then MONTH(RESULT) = 12 + MONTH(DATE1) - MONTH(DATE2). YEAR(DATE2) is then incremented by 1. YEAR(RESULT) = YEAR(DATE1) - YEAR(DATE2). 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.
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: MM = (MMDDYY/10000)-0.5. Example 1 5728QU1 R03 M00 900824 Query . . . . . . . . Library . . . . . . Query text . . . . . Collating sequence . Processing options * Use rounding . . . Ignore decimal data IBM . . . . . . . . OS/400 Query . .
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(’11:02:26’) − (’10:32:56’) is 2930; a duration of 0 hours, 29 minutes, and 30 seconds.
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. The result of subtracting one timestamp value from another is a timestamp duration.
DATE Query for iSeries function The DATE function returns a date from a value.
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) = DAY(HIREDATE) RESULT(DAY) would equal a value between 1 and 31. DAYS Query for iSeries function The DAYS function returns a numeric representation of a date.
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: QQ MICROSECOND ( expression ) QR 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. If the argument can be null, the result can be null. If the argument is null, the result is null.
MONTH Query for iSeries function The MONTH function returns the month part of a value. The form is: QQ MONTH ( expression ) QR 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.
TIME Query for iSeries function The TIME function returns a time from a value. The form is: QQ TIME ( expression ) QR 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. v If the argument is a timestamp, the result is the time part of the timestamp. v If the argument is a time, the result is that time.
RESULT(x) = TIMESTAMP(DATEFRNK,TIMEFRNK) YEAR Query for iSeries function The YEAR function returns a numeric representation of the year part of a value. The form is: QQ YEAR ( expression ) QR 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.
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. Note: For ease of reading, multiple panel views are merged into single screen images.
Define Result Fields Type definitions using field names or constants and operators, press Enter. Operators: +, -, *, /, SUBSTR, ||, DATE...
Define Result Fields Type definitions using field names or constants and operators, press Enter. Operators: +, -, *, /, SUBSTR, ||, DATE...
– 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.
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. If you do not want any heading for a result field, type *NONE in the first five positions on the first line for that field.
Example of defining a result field in Query for iSeries An example of how you would create a numeric result field called DAYS using the expression WEEKS * 7 and using the report column heading Total Days is shown on the following display. The second input area shows how you would define a character result field called GREETING with a value of ‘Have a nice day‘ and no column heading. Define Result Fields Type definitions using field names or constants and operators, press Enter.
Chapter 6. Selecting and sequencing fields in Query for iSeries This chapter describes how you select fields that you want to include in your query output and how you indicate in what sequence the fields are to appear. They appear in a query report from left to right based on the sequence number that you enter for each field. Query gives you the opportunity to select fields from all your selected files and from all the result fields defined in your query.
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. Only those fields that you have selected so far appear in your output.
Select and Sequence Fields Type sequence number (0-9999) for the names of up to 500 fields to appear in the report, press Enter.
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.
Chapter 7. Selecting records in Query for iSeries This chapter describes how you can perform record selection tests so that your query output contains only selected records, such as records in which the value of a field is equal to a specified value. For example, you want to obtain information from an employee master file about all employees whose age is equal to or greater than 21. By using certain tests, you can select only these records from your file (and thus exclude those who are less than 21).
Select Records Type comparisons, press Enter. Specify OR to start each new group. Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...
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.
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.
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” on page 17).
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. Comparisons of time values and character representations of time values always include seconds. If the character representation omits seconds, zero seconds are assumed.
Records are selected if the data in the field BALDUE is less than or equal to the data in the field CRLIMIT. 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.
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. If the list contains correct representations of a time but in other than SAA format, you may get unexpected results. To prevent unexpected results, the list should be in SAA format.
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. Each % takes the place of zero or more characters in the field.
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. Use the DBCS underscore (__) to indicate that any double-byte character is accepted for that position.
If the test pattern consists of two apostrophes alone (’’ or G’’) or two apostrophes enclosing only DBCS shift-out and shift-in characters (’s/os/i’ or G’s/os/i’), 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.
Select Records Type comparisons, press Enter. Specify OR to start each new group. Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT... AND/OR Field LASTNAME OR CRLIMIT ____ ______________ ____ ______________ Test Value (Field, Number, or ’Characters’) EQ ’Kingsbury’ GE 5000 _____ ______________________________ _____ ______________________________ You can use AND and OR connections together to be even more specific about which records to include in your report.
Select Records Type comparisons, press Enter. Specify OR to start each new group. Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...
104 Query for iSeries Use V5R2
Chapter 8. Selecting sort fields in Query for iSeries This chapter describes how you can select sort fields to control the arrangement of the data in your query output. To Query, a sort field is a field whose contents are used to sort the output records in a particular order.
Select Sort Fields Type sort priority (0-999) and A (Ascending) or D (Descending) for the names of up to 32 fields, press Enter.
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 (CRLIMIT), in descending order (from highest to lowest); your second sort field is state (STATE); your third sort field is last name (LASTNAME); and your last sort field is first name (INIT).
Select Sort Fields Type sort priority (0-999) and A (Ascending) or D (Descending) for the names of up to 32 fields, press Enter.
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”. DBCS data in character fields is sorted by the hexadecimal representation of the data. The collating sequence is not applied to DBCS data in DBCS character fields.
110 Query for iSeries Use V5R2
Chapter 9. Selecting a collating sequence in Query for iSeries This chapter describes how you select the collating sequence for your query. The collating sequence is used for certain operations (such as sorting, comparing, and evaluating) that involve SBCS character data in SBCS, DBCS-open, and DBCS-either character fields. A collating sequence assigns a weight to each alphanumeric and special character so that Query knows how to perform operations on character fields and constants.
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. v A collating sequence that you define.
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. Once you make this analysis, you can select the collating sequence that produces the desired results, or you can define a collating sequence of your own.
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. This is an example, a real table contains all the alphanumeric and special characters.
as in most Query for iSeries language sequences, each pair of uppercase and lowercase letters (such as A and a) 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.
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” on page 248 for more information on how CCSIDs affect the collating sequence. You are shown a list of characters with a sequence number assigned to each character.
Select Translation Table Library . . . . . . . . Subset . . . . . . . . Position to . . . . . . *LIBL __________ __________ Name, *LIBL, F4 for list Name, generic* Starting character(s) Type option (and Table and Library), press Enter.
Select System Sort Sequence Type choices, press Enter. Sort Sequence . . . . . 1 1=Job run 2=Unique 3=Shared Language id . . . . . . *JOBRUN *JOBRUN, language id, F4 for list F3=Exit F12=Cancel F4=Prompt F13=Layout F5=Report F18=Files F10=Process/Previous F23=Save as default 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.
Chapter 10. Specifying report column formatting in Query for iSeries The first part of this chapter describes how you control the format of your query output. You can change the spacing before the report columns, the headings above the columns, and the report field lengths. In the second part of this chapter, the four ways of editing numeric fields are described.
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 INIT field on the following display). Specify Report Column Formatting Type information, press Enter.
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. If you change the value in Len or Dec but decide you want to use the original field definition value, blank out the number you typed.
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”).
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; the second display to be shown depends on which edit option (1 through 4) you select on this display.
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.
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 245.87: Option 1= . 2= , 3= : 4= $ 5=None Example 245.
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 −27.03), or it is to the immediate left of the left currency symbol (such as −$27.03). 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 values in this field.
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 Y or N to indicate whether it is to show a zero or it 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; no 0 is to be shown in this field.
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). Describe Date/Time Field Editing Field . . . . . . : Type choice, press Enter. Date/time separator . . . . . F3=Exit F13=Layout F5=Report F16=Remove edit _ 1=.
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). For certain edit codes you can also specify a modifier character to be used as part of the editing.
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.
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. 1=Asterisk fill Asterisks (*) are to be used as the fill character to replace leading zeros for this field; for example, ****27.03.
Specify Edit Word Field . . . . . : Length . . . . : Decimal . . . . : Heading 1 . . . . : Heading 2 . . . . : Heading 3 . . . . : Type information, press Enter. (Put quotes around edit words.) (Each blank replaced by a digit, each ’&’ with a blank.) Edit word . . . . . ’ , , 0. -’ (These are SAMPLES only; they are not defaults.) Edit word for summary total . . F3=Exit F13=Layout ’ , F5=Report F16=Remove edit , , 0.
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. v Only the system-defined currency symbol (stored in the QCURSYM system value), can be used as a floating currency symbol. Any other symbol specified will not float; it stays fixed in one position.
134 Query for iSeries Use V5R2
Chapter 11. Specifying report summary functions in Query for iSeries reports This chapter describes how to specify the summary functions for each of the selected fields in your query output. Depending on the type of field, you can specify one or more (or all) of the types of summary functions for each field in your report: total, average, minimum value, maximum value, and count.
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). If the total is null, then the average is null. 3=Minimum Shows the lowest character or numeric value in the field for the break level or for the whole column. The minimum of date, time, or timestamp values is based on chronological order.
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.
138 Query for iSeries Use V5R2
Chapter 12. Defining Query for iSeries report breaks This chapter describes how you define the report breaks that you want to occur in your query report. Report breaks are used to break the report into groups of records (or lines) each time the value of a report break field changes. The second part of this chapter describes how, for each of your report breaks, you can control report break formatting and define break text messages.
Define Report Breaks Type break level (1-6) for up to 9 field names, press Enter. (Use as many fields as needed for each break level.
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 CITY and STATE be specified as break fields.
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. But you can still specify that the field be used as a break field so that its value, when a report break occurs, is included in the break text defined for that level of report break.
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. You can also specify some formatting characteristics for break level 0, which is the ‘Final Totals’ break level provided by Query. You can think of break level 0 as the group that includes all of the selected records.
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. The text can be as long as 40 characters. The text appears ahead of any summary lines for this report break.
Chapter 13. Selecting output type and output form in Query for iSeries reports This chapter describes how you select where you want your query output to go, and in what form you want it to be. You can have the output displayed, printed, or put into a database file. You can also specify that the output is to be in detailed form (which includes all of your selected records) or in summary form (which only includes report break data as described in Chapter 12, “Defining Query for iSeries report breaks”).
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.
| 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 N | is specified, the next two prompts are ignored. Wrapping width in Query for iSeries reports For displayed or printed output only, you can specify a value that indicates the maximum width of the lines in the report. Either type a value of 1 through 378 or leave this prompt blank.
Original report: Position to line . . . . . Shift to column . . . Line ....+....1....+....2....+....3....+....4....+....5....+.. INTEGER CHAR DECIMAL NUMERIC 000001 1,000,000,001 aaaaaaaaaa 11,111,111.01 11,111,111.01 Report showing wrapping width 15: Position to line . . . . . Line ....+....1....+ INTEGER CHAR DECIMAL NUMERIC 000001 1,000,000,001 000002 aaaaaaaaaa 000003 11,111,111.01 000004 11,111,111.01 Shift to column . . .
Define Printer Output Type choices, press Enter. Printer device . . . . *PRINT *PRINT, name Form size Length . . . . . . . Width . . . . . . . ___ 132 Blank, 1-255 Blank, 1-378 Start line . . . . . . ___ Blank, 1-255 End line . . . . . . . ___ Blank, 1-255 Line spacing . . . . . 1 1, 2, 3 Print definition . . .
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. Variable-length DBCS values are extended to the full field length.
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. (You can get to that display by typing the Work With Spooled Files (WRKSPLF) command on a command line of any menu.
v If you type *STD, 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. If you leave this prompt blank, the value specified in the printer device file (named QPQUPRFIL) used by Query determines the number of copies to be printed.
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. You can type as many as four full lines of text using all characters (including uppercase and lowercase letters) that can be printed by the printer that you specified on the Define Printer Output display.
Print standard page headings in Query for iSeries reports: Type a Y or N to indicate whether the system’s standard headings are to be printed on each page of your report (except for the cover page). You must specify an N 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.
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.
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.
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.
If you specify *FILE, *FIRST, or *LAST in this prompt, a member is created with the same name as the file if the file is created or replaced or has no members. If *FIRST or *LAST 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.
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 the object is being created into.
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. v If summary functions but no report breaks have been selected, the output is a single record containing summary function values.
Break level ID (1) | | | | Overflow indicator (1) | | County (11) | | City (10) | | | | | | Total salary (11.2) | | | Average salary (8.2) | Count (7.0) 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.
162 Query for iSeries Use V5R2
Chapter 14. Specifying Query for iSeries processing options This chapter describes how you specify processing options for running a query. The processing options you can specify are: v Whether the results of numeric field calculations or field length changes done for your query are to be rounded or dropped off (truncated). v Whether errors and bad data found in numeric fields are to be ignored. v Whether warnings about character substitutions encountered during CCSID conversions are to be ignored.
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 Y in the Rounding prompt on the Specify Processing Options display, all of your numeric field calculation results are rounded (if necessary) when your query is run.
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. For example, some applications write blank into a zoned field when the user leaves a prompt blank. Also, some direct files set all records to hex 40.
166 Query for iSeries Use V5R2
Chapter 15. Exiting and running a Query for iSeries query This chapter describes how to exit query definition and the different ways you can run your query. Ending a Query for iSeries query definition | When you have finished creating a query and have pressed F3 (Exit) on one of the definition displays, the | Exit This Query display is shown. | | | | | | | | | | | | | | | | | | | | | | | || Exit This Query Type choices, press Enter. Save definition . . . Y Y=Yes, N=No Run option . . . . . .
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. Giving authority to others to your Query for iSeries query | You can specify the type of authority that you want to give to other users for this query definition object.
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. To run a query, you can: v Press F5 (Report) while creating, displaying, or changing your query. This runs the query and displays the report. v Choose option 2 (Run an existing query) on the Query menu.
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. The query name must be a unique query name in that library. If not, Query issues a message asking whether you want to replace the existing query.
Work with Queries Type choices, press Enter. Option . . . . . . . Query . . . . . . . Library . . . . . . F3=Exit F4=Prompt _ __________ QGPL 1=Create 2=Change 3=Copy 4=Delete 5=Display 6=Print definition 8=Run in batch 9=Run Name, F4 for list Name, *LIBL, F4 for list F5=Refresh F12=Cancel 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.
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. This produces a report showing the information contained in the first 500 fields of each record.
Chapter 16. Working with Query for iSeries query definitions This chapter describes the ways you can work with your query once it has been created and saved in a library. In addition to running your query: v You can change the results of your query output by making changes to your query definition. v You can copy your query definition to the same or another library. You might do that so that other users can have their own copy to work with.
Define the Query Query . . . . . . : QRY1 Library . . . . . : QGPL Option CCSID . . . . . : . . . . . : Change 37 Type options, press Enter. Press F21 to select all.
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. You can choose more options to make more changes, or you can press F3 to end the process of changing the query. From the Exit This Query display, you can do either or both of the following: v Save the query definition with the changes you have made. v Run the query with the changes you have made.
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. Each query is saved with a CCSID. You cannot change a query if your job CCSID is not compatible with the query CCSID (one of the CCSIDs is 65535 or they are the same).
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.
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 Y if you want the copy to replace a query that has the same name in the library. v Leave the N 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. To rename the query(s), type the new name(s) in the To Query column.
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. Confirm Delete of Queries From library . . . . : MYLIB Press Enter to confirm your choices for 4=Delete. Press F12 to return to change your choices. Query ACCTRECJAN INVENTJAN MAILADDR PAYROLLJAN TESTQUERY1 TESTQUERY2 F9=Reset F12=Cancel F20=Cancel delete in error This display shows the query name(s) you chose to delete.
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. You can display a previously saved query by using option 5 (Display) on the Work with Queries display.
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. v The query name and CCSID. v The library name. v Any text about the query.
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. Output file record format Output record length . . . . Output CCSID value . . . . .
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. If the field is for a summary function, the text shown is the field name (without any added numbers) followed by the type of summary function.
184 Query for iSeries Use V5R2
Part 3. Advanced information about Query for iSeries Chapter 17. Additional information about Query for iSeries for programmers . . . . Files with different record formats in Query for iSeries . . . . . . . . . . . . . . File sharing considerations in Query for iSeries . . . . . . . . . . . . . . . . . Overriding database files in Query for iSeries. . . . . . . . . . . . . . . . . . DBCS considerations when defining result fields in Query for iSeries . . . . . . . . . Joining files in Query for iSeries . .
186 Query for iSeries Use V5R2
Chapter 17. Additional information about Query for iSeries for programmers This chapter provides additional information which may be of interest to programmers. Files with different record formats in Query for iSeries You can use a file that has more than one record format; however, you can use only one record format at a time.
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. (It selects the matches.) v Type 3 join selects only the records in a primary file that have no matching records in the secondary files. (It selects the exceptions.
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. For example: X12 = (1.
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. Define Result Fields Type definitions using field names or constants and operators, press Enter. Operators: +, -, *, /, SUBSTR, ||, DATE...
Define Result Fields Type definitions using field names or constants and operators, press Enter. Operators: +, -, *, /, SUBSTR, ||, DATE...
192 Query for iSeries Use V5R2
Part 4. Appendixes © Copyright IBM Corp.
194 Query for iSeries Use V5R2
Appendix A. Differences between Query for iSeries and Query/36 This appendix describes the differences between Query for iSeries and Query/36. The Query for iSeries differences described in this appendix are limited to the differences that may cause you some inconvenience if you did not know about them before working with Query for iSeries.
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. Query for iSeries supports character fields up to 32 766 bytes in length.
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 QRY WRKQRY QRYRUN RUNQRY REMOVE DLTQRY FROMLIBR SAVOBJ HELP QUERY STRQRY LIBRLIBR CRTDUPOBJ TOLIBR RSTOBJ Migration differences between System/36 and Query for iSeries definitions Query for iSeries definitions cannot be migrated to System/36
198 Query for iSeries Use V5R2
Appendix B. Practice exercise for Query for iSeries query To do an exercise using query, you must first have a file set up with some data. This exercise takes you through the following steps before you actually create a query: v Creating an IDDU definition v Creating a database file v Entering data If you already have a file on the system with data that you can use, you may want to skip directly to the section on creating a query.
7. You will name the file definition first, so select option 3 (File). (The name SYSDIC appears in the Data 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.) Select Definition Type Type choices, press Enter. Definition type . . . . . . . 3 1=Field 2=Record format 3=File Data dictionary . . . . . . .
Create and Select Field Definitions Definition. . . . . . : NAMEADDRR Dictionary. . . . . . : SYSDIC Position to . . . . . . . __________ Field, sequence (0-99999) Type sequence numbers (0-99999), (and Field), press Enter. Type field, press F6 to create. Seq 10 Field End Seq Field End Seq Field End LASTNAME Now press F6. You will complete the description of the LASTNAME field definition (as well as naming and describing the remainder of the field definitions) from the next display. 11.
Create Field Definitions Type information, press Enter to create. Field type (size) . . : 1=Character (1-32766) 2=Numeric (1-31, decimal positions 0-31) 3=DBCS (4-32766, mixed; 1-16383, graphic) 4=Date/Time (no size) More options. . . . .
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.
1. Choose option 3 (Work with database files) from the IDDU menu (or type WRKDBFIDD on a command line). 2. On the Work with Database Files display, specify option 2 (Enter data), NAMEADDR for the file name, and your library for the library name. Work with Database Files Library . . . . . . . Position to . . . . . default___ Name,F4 for list __________ Starting characters Type options (and Database file), press Enter.
WORK WITH DATA IN A FILE Format . . . . : NAMEADDR LASTNAME: FIRSTNAME: ADDRESS1: ADDRESS2: CITY: STATE: ZIP: AMOUNT: Mode . . . . : File . . . . : ENTRY NAMEADDR SIMPSON FRANK 1722 ORANGE STREET RIDGEWAY CA 72430 9998 F3=Exit F9=Insert F5=Refresh F10=Entry F6=Select format F11=Change 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.
End Data Entry Number of records processed Added . . . . . : Changed . . . . : Deleted . . . . : 10 0 0 Type choice, press Enter. End data entry . . . . . . . Y Y=Yes, N=No You do not need to change anything on this display. Press the Enter key. 8. The Work with Database Files display is shown. Work with Database Files Library . . . . . . . Position to . . . . . default__ Name,F4 for list __________ Starting characters Type options (and Database file), press Enter.
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 NAMEADDR in the order the information exists in the file. This exercise asks you to save the query you create in a library. You will probably want to use your own library.
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. Press the Enter key, and the Specify File Selections display is shown. Specify File Selections Type choices, press Enter. Press F9 to specify an additional file selection. File . . . Library Member . . Format . . . . . . . . . . F3=Exit F12=Cancel . . . . . . . . . . .
Specify File Selections Type choices, press Enter. Press F9 to specify an additional file selection. File . . . Library Member . . Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . NAMEADDR YOURLIB *FIRST NAMEADDRR Name, Name, Name, Name, F3=Exit F4=Prompt F5=Report F12=Cancel F13=Layout F24=More keys Select file(s), or press Enter to confirm. F4 for list *LIBL, F4 for list *FIRST, F4 for list *FIRST, F4 for list F9=Add file 7. Press the Enter key.
Exit This Query Type choices, press Enter. Save definition . . . Y Y=Yes, N=No Run option . . . . . . 1 1=Run interactively 2=Run in batch 3=Do not run For a saved definition: Query . . . . . . . Library . . . . . KJOQRY YOURLIB Name Name, F4 for list Text . . . . Authority F4=Prompt . . . . . Lists customer names and addresses *CHANGE F5=Report *LIBCRTAUT, *ALL, *CHANGE, *EXCLUDE, *USE authorization list name F13=Layout F14=Define the query 11.
Work with Queries Type choices, press Enter. Option . . . . . . . Query . . . . . . . Library . . . . . . _ KJOQRY YOURLIB 1=Create, 2=Change, 3=Copy, 4=Delete 5=Display, 6=Print definition 8=Run in batch 9=Run Name, F4 for list Name, *LIBL, F4 for list F3=Exit F4=Prompt F5=Refresh Query option processing completed successfully.
Define the Query Query . . . . . . : KJOQRY Library . . . . . : YOURLIB Option . . . . . : CCSID . . . . . . : CHANGE 37 Type options, press Enter. Press F21 to select all.
Select Records Type comparisons, press Enter. Specify OR to start each new group. Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...
Display Report Report width . . . . . : 117 Position to line . . . . . ______ Shift to column . . . . . . _____ Line ....+....1....+....2....+....3....+....4....+....5....+....6....+....7... ADDRESS2 CITY STATE ZIP AMOUNT 000001 EMMERSON LA 71282 .25 000002 APT 43 EMMERSON LA 71282 .45 000003 VD EMMERSON LA 71282 9.38 ****** ******** End of Report ******** F3=Exit F12=Cancel F19=Left Last column of report. F20=Right F21=Split Bottom F22=Width 80 10.
Exit This Query Type choices, press Enter. Save definition . . . N Y=Yes, N=No Run option . . . . . . 3 1=Run interactively 2=Run in batch 3=Do not run Query . . . . . . . Library . . . . . CEBQRY YOURLIB Name Name, F4 for list Text . . . . . Lists customer names and addresses Authority . . . . . *CHANGE For a saved definition: F4=Prompt F5=Report *LIBCRTAUT, *CHANGE, *ALL *EXCLUDE, *USE authorization list name F13=Layout F14=Define the query 12. Press the Enter key.
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 QRYFILE and puts it in the library QGPL. Note: When you type *ALL for the OBJTYPE parameter, you must include a space after *ALL or you will receive an error message. You must type in the entire command exactly as it appears on the display that follows. MAIN OS/400 Main Menu Select one of the following: 1. 2. 3. 4. 5. 6. 7. 8. 9.
Work with Queries Type choices, press Enter. Option . . . . . . . Query . . . . . . . . Library . . . . . . F3=Exit F4=Prompt 1 1=Create, 2=Change, 3=Copy, 4=Delete 5=Display, 6=Print definition 8=Run in batch 9=Run QNAME YOURLIB Name, F4 for list Name, *LIBL, F4 for list F5=Refresh F12=Cancel (C) COPYRIGHT IBM CORP. 1988, 1991 4.
Specify File Selections Type choices, press Enter. Press F9 to specify an additional file selection. File . . . Library Member . . Format . . . . . . . . . . F3=Exit F12=Cancel . . . . . . . . . . . . . . . . . . . . QRYFILE QGPL *FIRST *FIRST F4=Prompt F13=Layout Name, Name, Name, Name, F4 for list *LIBL, F4 for list *FIRST, F4 for list *FIRST, F4 for list F5=Report F24=More keys F9=Add file The message Select file(s), or press Enter to confirm. appears.
Select and Sequence Fields Type sequence number (0-9999) for the names of up to 500 fields to appear in the report, press Enter. Seq Field ODDCEN ODDDAT ODDTIM ODLBNM ODOBNM ODOBTP ODOBAT ODOBFR ODOBSZ ODOBTX ODOBLK ODOBDM F3=Exit F13=Layout Seq F5=Report F20=Renumber Field ODCCEN ODCDAT ODCTIM ODOBOW ODSCEN ODSDAT ODSTIM ODSCMD ODSSZE ODSSLT ODSDEV ODSV01 Seq F11=Display text F21=Select all Field ODSV02 ODSV03 ODSV04 ODSV05 ODSV06 ODSV07 ODSV08 ODSV09 ODSV10 ODSVMR ODRCEN ODRDAT More...
Select and Sequence Fields Type sequence number (0-9999) for the names of up to 500 fields to appear in the report, press Enter.
Select Records Type comparisons, press Enter. Specify OR to start each new group. Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT... AND/OR Field ODOBTP Test LIST Value (Field, Number, or ’Characters’) ’*FILE’ ’*PGM’ Bottom Field ODLBNM ODOBTP ODOBAT ODOBSZ ODOBTX F3=Exit F12=Cancel Text LIBRARY OBJECT TYPE OBJECT ATTRIBUTE OBJECT SIZE TEXT DESCRIPTION F5=Report F13=Layout F9=Insert F20=Reorganize Len Dec 10 8 10 10 0 50 More... F11=Display names only F24=More keys 8.
Select Records Type comparisons, press Enter. Specify OR to start each new group. Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT... AND/OR Field ODOBTP Test LIST Value (Field, Number, or ’Characters’) ’*FILE’ ’*PGM’ Bottom Field ODLBNM ODOBTP ODOBAT ODOBSZ ODOBTX F3=Exit F12=Cancel Text LIBRARY OBJECT TYPE OBJECT ATTRIBUTE OBJECT SIZE TEXT DESCRIPTION F5=Report F13=Layout Len Dec 10 8 10 10 0 50 F9=Insert F20=Reorganize More...
Select Sort Fields Type sort priority (0-999) and A (Ascending) or D (Descending) for the names of up to 32 fields, press Enter. Sort Prty A/D Field 1 A ODOBTP 2 A ODOBSZ ODLBNM ODOBAT ODOBTX Text OBJECT TYPE OBJECT SIZE LIBRARY OBJECT ATTRIBUTE TEXT DESCRIPTION Len Dec 8 10 0 10 10 50 Bottom F3=Exit F5=Report F13=Layout F18=Files Press Enter to confirm. F11=Display names only F20=Renumber F12=Cancel F24=More keys Press the Enter key again. 10.
Specify Report Column Formatting Type information, press Enter. Column headings: *NONE, aligned text lines Field ODLBNM Column Spacing 0 ODOBTP 2 OBJECT TYPE ODOBAT 2 OBJECT ATTRIBUTE Column Headings NAME OF LIBRARY Len Dec 10 Edit 8 10 More... F3=Exit F13=Layout F5=Report F16=Edit F10=Process/previous F18=Files F12=Cancel F23=Long comment Press the Enter key. 11. The next display you see is the Select Report Summary Functions.
Select Report Summary Functions Type options, press Enter. 1=Total 2=Average 3=Minimum ---Options--- Field ODLBNM ODOBTP ODOBAT 1 2 3 4 5 ODOBSZ ODOBTX 4=Maximum 5=Count Text LIBRARY OBJECT TYPE OBJECT ATTRIBUTE OBJECT SIZE TEXT DESCRIPTION Len Dec 10 8 10 10 0 50 Bottom F3=Exit F12=Cancel F5=Report F13=Layout F10=Process/previous F18=Files F11=Display text only F23=Long comment Press the Enter key. 12. The next display to appear is the Define Report Breaks display.
Define Report Breaks Type break level (1-6) for up to 9 field names, press Enter. (Use as many fields as needed for each break level.) Break Level 1 Sort Prty Field ODLBNM 10 ODOBTP ODOBAT 20 ODOBSZ ODOBTX Text LIBRARY OBJECT TYPE OBJECT ATTRIBUTE OBJECT SIZE TEXT DESCRIPTION Len Dec 10 8 10 10 0 50 Bottom F3=Exit F12=Cancel F5=Report F13=Layout F10=Process/previous F18=Files F11=Display names only F23=Long comment Press the Enter key. 13.
Format Report Break Break level . . . . . . . : 1 Type choices, press Enter. (Type &field in text to have break values inserted.) Skip to new page . . . . . N Y=Yes, N=No Suppress summaries . . . . N Y=Yes, N=No Break text . . . . . . . . Break text for object type Level Field 1 ODOBTP F3=Exit F13=Layout F5=Report F18=Files F10=Process/previous F23=Long comment F12=Cancel Press the Enter key.
Display Report Report width . . . . . : 104 Position to line . . . . . Shift to column . . . . . . Line ....+....1....+....2....+....3....+....4....+....5....+....6....+....7...
Exit This Query Type choices, press Enter. Save definition . . . N Y=Yes, N=No Run option . . . . . . 3 1=Run interactively 2=Run in batch 3=Do not run For a saved definition: Query . . . . . . . Library . . . . . QNAME QGPL Name Name, F4 for list *CHANGE *LIBCRTAUT, *CHANGE, *ALL *EXCLUDE, *USE authorization list name Text . . . . . . . Authority F4=Prompt . . . . . F5=Report F13=Layout F14=Define the query Press the Enter key. 16.
230 Query for iSeries Use V5R2
Appendix C. Query for iSeries performance tips and techniques This appendix provides guidelines for improving the performance of the Query for iSeries product. These guidelines help you better understand how Query works and which key items to keep in mind for performance when designing or changing a query. This appendix does not discuss all variations of queries, but instead provides tips and techniques that help with the majority of queries running on the iSeries system.
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.
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.
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.
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. Define result fields in Query for iSeries Labeled durations are added or subtracted in left to right order. This could make a difference in your results.
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. See “Select/omit access paths in Query for iSeries” on page 232 for more information on this subject.
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.
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. Specify output type 1 (Display) and output form 1 (Detail) for a multi-copy query.
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.
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.
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. Use these guides together to help improve overall system performance.
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. Building access path from file X in Y. This message indicates Query is building an access path from an existing access path.
Appendix D. Preventing users from running Query for iSeries queries interactively The system administrator can restrict users from running queries interactively. When this restriction is applied, users cannot: v Use option 9 (Run) on the Work With Queries display. v Press F5 (Report) to display the report on-screen. v Use option 1 (Run interactively) on the Exit this Query display. v Run the QRYRUN procedure interactively. v Run the Run Query (RUNQRY) command interactively.
244 Query for iSeries Use V5R2
Appendix E. Coded character set identifiers (CCSIDs) in Query for iSeries This appendix contains information about coded character set identifiers or CCSIDs. This information will help you understand CCSIDs and why they are important to Query for iSeries. A CCSID is a 2-byte (unsigned) integer that uniquely identifies an encoding scheme and one or more pairs of character sets and code pages. A CCSID can be assigned to each query definition, data field, and collating sequence.
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.
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. The bulleted items inherit their CCSID from the user profile or job, unless specifically changed. Numbers in parentheses refer to notes explaining how you can determine CCSID values for the item.
- 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. The CCSID of the user-defined collating sequence is shown on the Define Collating Sequence display. 3.
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.
Table 10. How CCSIDs Affect Displaying a Query Job CCSID Query CCSID Constants Processed as Marked with CCSID 37 65535 37 65535 37 37 37 65535 No tag No tag 37 37 65535 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. Table 11.
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.
Table 12. How CCSIDs Affect Run-Time Record Selection Job CCSID Query CCSID Constants Treated as CCSID 37 65535 37 65535 37 37 37 37 65535 No tag No tag 500 37 37 37 65535 37 Not allowed 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.
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. If the conversion fails, an error message is sent to the user.
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. The following tables show what can occur when pairs of items are marked with different CCSIDs, neither of which is 65535. Table 13 shows what happens at definition time if item 1 cannot be converted to the CCSID for Item 2. Table 13.
Table 13. CCSIDs Not Compatible-Definition-Time Consequences (continued) Item 1 Item 2 Consequence Collating sequence Field Field 2/constant Field 1 Field 2/constant Field 1 Cannot perform comparison for record selection. Error diagnosed and displayed on Select Records display or indicated on the Define the Query or Exit this Query display. (Can occur when Query definition constant inherits job CCSID of changing user.) Cannot perform comparison for record selection.
Table 14. CCSIDs Conversion Problems-Run-Time Consequences (continued) Item 1 Item 2 Collating sequence Job Consequence Cannot convert table for performing break, minimum, or maximum for field. The run request fails for printer output. For displayed output, the run request continues, but all values and summaries are shown as replacement strings (+++). Note: For output to display, Query uses a single diagnostic message to warn you to look for unwanted dots and missing edit word text.
Table 16. How CCSIDs Affect Query Use Job CCSID Query CCSID Run Query allowed Change Query allowed Run time record selection Display Query allowed A A 65535 A 65535 A A 65535 A B No tag No tag Yes Yes Yes Yes Yes Yes Yes Yes1 Yes No1 Yes Yes1 Yes Yes1 Yes No1 Yes Yes1 Yes Yes1 Yes No1 Yes Yes1 1. Either a warning or request ending message is issued. Appendix E.
258 Query for iSeries Use V5R2
Bibliography The following OS/400 books contain information you may need. The books are listed with their full title and base order number. v ADTS/400: Data File Utility, SC09-1773-00, provides the application programmer or programmer with information about using the Application Development Tools data file utility (DFU) to create programs to enter data into files, update files, inquire into files, and run DFU programs.
260 Query for iSeries Use V5R2
Notices This information was developed for products and services offered in the U.S.A. IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative for information on the products and services currently available in your area. 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.
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.
Index Special Characters *ALL authority 159, 168 *ALL join 44, 188 *ALL special library name 10 *ALLUSR special library name 10 *CHANGE authority 159, 168 *CURLIB special library name 10 *EXCLUDE authority 159, 168 *LIBCRTAUT authority 159, 168 *LIBL special library name 10 *QRYDFN (query definition) object 5 *USE authority 159, 168 *USRLIBL special library name 10 || (concatenation) operator 60 A access path creating 232 floating point fields 236 keyed sequence definition 231 select/omit 232 size limitati
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 record selection 253 report break 253 result field 252 run-time record selection 251 Select Records display 97 sort field 253 summary function 253 translation table 116 VALUE function results 63 changing query CCSID (coded ch
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 date, time, or timestamp fields 112 default 111 defining your own 115 English language default 113 hexadecimal 113, 114 language sequence 114 performanc
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 Create Field Definitions display (IDDU) 201 Create File Definition display (IDDU) 200 creating access path 232 database file descript
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 argument rules 76 syntax diagram 76 DB2 UDB for iSeries program relational database terms file 5 relationship to OS/400 terms 5 DB2 UDB for iSeries views 237 DBCS (double-byte character set) bracket characters 9 c
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 definition display moving through 30 definition step description 29 selecting on Define the Query display 28 Delete Query (DLTQRY) command 11, 178 deleting file selections
dividing by zero (0) example 83 DLTQRY (Delete Query) command double-byte character set (DBCS) bracket characters 9 character constant shift-in character 60 shift-out character 60 data representation 8 pictograph 4 shift-in character 60 shift-out character 60 sorting character data 111 space requirements 9 symbolic characters 4 duration date 73 labeled 73 time 73 timestamp 74 11, 178 E edit code modifier 131 numeric fields 129 specifying 129 edit word different CCSIDs 132 numeric field 131 specifying 131
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 decimal precision 190 defining 57 differences between Query/36 and Query for iSeries 196 naming 58 tips and techniques 235 selecting for query 87, 235 selection default 87 sequencing for query 87, 235 sort example of how used 1
function (continued) HOUR argument rules 76 syntax diagram 76 introduction 5 MICROSECOND argument rules 77 syntax diagram 77 minimum summary null value 135 MINUTE argument rules 77 syntax diagram 77 MONTH argument rules 78 syntax diagram 78 SECOND argument rules 78 syntax diagram 78 SUBSTR (substring) 61 substring argument rules 60 summary average 135 CCSID (coded character set identifier) count 135 location on report 137 maximum 135 minimum 135 performance recommendations 237 report 237 summarizing columns
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 introduction to Query concepts 3 IS (is) comparison test 95, 96 ISNOT (is not) comparison test 95, 96 ISO (
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 programs 55 long comment using F23 to display 10 LT (less than) comparison test 95, 96, 165 M marking CCSID (coded character set identifier) 247 matched records join use every record 43 matched records with primary f
O 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 specifying 145 detailed 145 display 145 form selecting 145, 237 printer 145, 148 summary only differences between Query/36 and Query for iSeries 196 specifying 145 to database file differences between
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 saving 167 selecting files 33 selecting from list 10 selecting options 29 specifying authority 168 Query for iSeries command 11 definition 3 differences from Query/36 195 error 18 exi
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 date 66 decimal position 85, 188 defining DBCS 187 unique name 57 differences between Query/36 and Query for iSeries 196 example of defining 86 expression character 60 numeric 59 operat
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 record format for file selection Select Record Format display 40 Specify File Selections display 35 sort field for query 105 system sort sequence 117 translation table 116 sel
symbolic character DBCS (double-byte character set) 4 system administrator guidelines 243 system sort sequence 117 T test displaying 53 test pattern DBCS LIKE 99 DBCS-graphic field empty string 100 declaring 98 empty string 98 non-DBCS special characters 99 special characters 98 test, join CCSID (coded character set identifier) code displaying 43 L, T, or Z 43 specifying 43 text, break 144 thousands separator numeric fields 125 time arithmetic operation decrementing 73 incrementing 73 subtraction 73 compar
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 viewing queries on display 8 36 W 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 getting to 7 introduction 12 listing names of queries 14 running query 170 Work with Query (WRK
280 Query for iSeries Use V5R2
Printed in U.S.A.
