IBM SC41-5210-04 manuel d'utilisation

iSeries Query for iSeries Use V ersion 5 SC41-5210-04 E Rs e rv e r [...]

[...]

iSeries Query for iSeries Use V ersion 5 SC41-5210-04 ER s e r v e r [...]

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 Restr[...]

Contents About Query for iSeries Use ............................ i x Who should read the Query for iSeries Use book ..................... i x Part 1. Introduction to Query for iSeries .................... 1 Chapter 1. What is Query for iSeries? ........................ 3 Query for iSeries fundamentals ............................ 3 Files, fields, [...]

Selecting files on the Query for iSeries Select File display ................. 3 7 Selecting file members on the Query for iSeries Select Member display ............ 3 9 Selecting record formats on the Query for iSeries Select Record Format display ......... 4 0 Displaying all files selected on the Query for iSeries Display File Selections display [...]

Letting Query for iSeries select records ........................ 9 1 Selecting the records you want in Query for iSeries .................... 9 1 Comparison fields in Query for iSeries ........................ 9 2 Comparison values in Query for iSeries ....................... 9 3 Comparison tests in Query for iSeries ........................ 9 5 U[...]

Record on one page in Query for iSeries reports ................... 1 4 7 Displaying wrapping widths in Query for iSeries reports ................. 1 4 7 Defining output to the printer in Query for iSeries reports ................ 1 4 8 Defining output of Query for iSeries reports to a database file ............... 1 5 4 Using an existing output fi[...]

Appendix A. Differences between Query for iSeries and Query/36 ............ 1 9 5 Conceptual Differences .............................. 1 9 5 Operational Differences .............................. 1 9 5 Command differences between System/36 and Query for iSeries commands .......... 1 9 7 Migration differences between System/36 and Query for iSeries[...]

Running a Query for iSeries query ......................... 2 5 1 Running a default query in Query for iSeries ..................... 2 5 1 Selecting records at run time in Query for iSeries ................... 2 5 1 CCSIDs and Query for iSeries query definition items ................... 2 5 2 CCSID and file selections 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. Y ou may need to refer to other IBM boo[...]

x Query for iSeries Use V5R2[...]

Part 1. Introduction to Query for iSeries Chapter 1. What is Query for iSeries? ........................ 3 Query for iSeries fundamentals ............................ 3 Files, fields, and record formats in Query for iSeries ................... 3 Double-byte character set (DBCS) fields in Query for iSeries ............... 4 UCS2 level 1 character se[...]

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 descript[...]

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). Query retrieves the data you want from the files you choose. It uses certain fields (and record formats) from those files to select, sort, calculate,[...]

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 contain[...]

Figure 3. Major T asks on the Query Menu and the Work with Queries Display 6 Query for iSeries Use V5R2[...]

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 iSerie[...]

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 F1 1) 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[...]

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 pr[...]

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: [...]

If you select option 3 (Delete a query) on the Query Utilities menu, you are shown the prompt display for the Delete Query (DL TQRY) command. Y ou 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. There are othe[...]

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 T o identify the query you want to work with, you can type the name of the query (in[...]

Y ou 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). Wh[...]

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 .) Y ou can also specify a complete name (without an *) in the Subset prompt. Continuing the previous examp[...]

Work with Queries Librar y........ QGPL Name, *LIBL, F4 for list Subset ........ __________ Name, generic* Position t o...... __________ Starting character(s) Type options (and Query), press Enter. 1=Create 2=Change 3=Copy 4=Delete 5=Display 6=Print 8=Run in batch 9=Run Opt Query Text Changed _ __________ _ ACCTPAYFEB Accounts payable for February [...]

Other considerations when you are trying to locate or use a particular Query for iSeries query Y ou should be aware of other considerations when you are trying to locate or use a particular query . T o use a query: v Y ou must have the authority needed to use the query definition. Y ou may need to either ask the owner of the query , the administrat[...]

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 R[...]

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 . [...]

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 ................. 2 7 Starting Query for iSeries query definition ....................... 2 7 Selecting definition steps when defining a Query for iSeries query .............. 2 8 Selecting options for a Query for iSeries query de[...]

Query for iSeries date ............................. 6 7 Query for iSeries time ............................. 6 7 Query for iSeries timestamp .......................... 6 8 Displaying constants format in Query for iSeries .................... 6 9 Date, time, and timestamp arithmetic operations in Query for iSeries ............. 6 9 Date arithmetic [...]

Numeric constants as values in Query for iSeries ................... 9 4 Date, time, timestamp constants as values in Query for iSeries ............. 9 5 Null values in Query for iSeries ......................... 9 5 Comparison tests in Query for iSeries ........................ 9 5 Date, time, or timestamp comparisons in Query for iSeries ........[...]

Edit code in Query for iSeries reports ...................... 1 2 9 Optional edit code modifier in Query for iSeries reports ................ 1 3 1 Specifying edit words ............................. 1 3 1 Edit word in Query for iSeries reports ...................... 1 3 1 Edit word for summary total in Query for iSeries reports ................ 1 [...]

Using function key F5 when running a Query for iSeries query .............. 1 6 9 Running a Query for iSeries query from the Query for iSeries menu ............ 1 6 9 Running a Query for iSeries query from the Exit This Query display ............ 1 6 9 Running a Query for iSeries query from the Work with Queries display ........... 1 7 0 Running a [...]

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 Def[...]

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. Y ou 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 [...]

Selecting options for a Query for iSeries query definition T o select options that you want to use from those listed in the Query Definition Option column, typ ea1i n 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 par[...]

Y ou can specify one, several, or all of the options shown. T o 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 tha[...]

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 yo[...]

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 1 1 steps that you can use to define a query . This step includes s[...]

Specify File Selections Type choices, press Enter. Press F9 to specify an additional file selection. F i l e.......... __________ Name, F4 for list Library ....... QGPL Name, *LIBL, F4 for list M e m b e r......... *FIRST Name, *FIRST, F4 for list Forma t......... *FIRST Name, *FIRST, F4 for list BOTTOM F3=Exit F4=Prompt F5=Report F9=Add file F12=C[...]

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. v If you type a generic library 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 w[...]

v If you typed a file name and left blank any of the Library , Member ,o r 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 [...]

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. (Y ou can specify another subset value on this display to change the subset shown.) Y ou can use the Library prompt to see the files that exist in a dif ferent library or group of libr[...]

T o return to the complete list of file names, blank out this prompt (or put an * in it) and press the Enter key . T o 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[...]

Select Member File ID .....: T 0 1 F i l e.......: EXAMPFILE1 Library ....: 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 V alue Qualifiers display , you can specify which[...]

Select Record Format File ID .....: T 0 1 F i l e.......: EXAMPFILE1 Library ....: 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 Fil[...]

Display File Selections ID File Library Member Format T01 EXAMPFILE1 CUSTINV *FIRST EXAMPFILE1 T02 EXAMPFILE2 CUSTINV *FIRST EXAMPFILE2 Bottom Press Enter to display the join tests. 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. However , if you came t[...]

T ypes of joins in a Query for iSeries query Specify Type of Join Type choice, press Enter. Type of joi n........ 1 1=Matched records 2=Matched records with primary file 3=Unmatched records with primary file F3=Exit F5=Report F10=Process/previous F12=Cancel F13=Layout F18=Files There are three types of joins, or three ways that you can select match[...]

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. Tests: EQ, NE, LE, GE, LT, GT Field Test Field ______________ _____ ______________ ______________ _____ ______________ ___[...]

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 Y ou can specify up to 100 join tests. v For a matched-record join, if one or both fields in a join test are null, the records are not joined. v The data in both fields of a join test must be the same ty[...]

- DBCS-either - DBCS-open – DBCS-either field with: - SBCS character - Date - T ime - T imestamp - DBCS-either - DBCS-open - DBCS-only – DBCS-open field with: - SBCS Character - Date - T ime - T imestamp - DBCS-either - DBCS-open - DBCS-only – DBCS-only field with: - DBCS-either - DBCS-open - DBCS-only – DBCS-graphic field with: - DBCS-grap[...]

Y ou can obtain several dif ferent 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 disp[...]

Example: Selecting matched records using a primary file in a Query for iSeries query T y p ea2i fy o u 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 f[...]

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 [...]

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 EQ B.NAME B.ITEM EQ C.ITEM Query performs the file join in two steps: Step 1:[...]

Incorrect method: On the Specify File Selections display , type the CUSTOMER file first, and specify the file ID as A . T ype 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[...]

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 . Query joins every record in file A (one record at a time) to 52 Query for iSeries Use V5R2[...]

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 a[...]

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 .T o find the files associated with the fil[...]

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[...]

problems caused by the missing fields. Y ou 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 [...]

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[...]

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 begin[...]

DA Y DA YS MONTH YEAR v A time expression performs an operation on a time. T ime 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. T imestamp expressions can contain the following operators or functions:[...]

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, 6 0*( A−( B+C ) ), the calculations are done for the innermost pair first (in the example, B+C ), then the next innermost [...]

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.[...]

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 r[...]

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[...]

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 cha[...]

V ARGRAPHIC Query for iSeries function The V ARGRAPHIC 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. The V ARGRAPHIC function returns a graphic string representation of a string expressio[...]

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 exc[...]

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 ef fect 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. [...]

V alid formats for times are listed in T able 2. Each format is identified by name and includes an associated abbreviation (for use by the CHAR function) and an example of its use. T able 2. Formats for Representations of Time Data T ypes Format Name Abbreviation Time Format Example International Standards Organization ISO hh.mm.ss 13.30.05 IBM USA[...]

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 Quer y......: QRY1 Option .....: CHANGE Librar y....: QGPL CCSI D......: 65535 Use an SAA format to enter a date or time c[...]

Subtraction rules are different from addition rules because a date, time, or timestamp value cannot be subtracted from a duration. Also, subtracting two date, time, or timestamp values is not the same as subtracting a duration from a date, time, or timestamp value. The following rules apply to date, time, and timestamp subtraction: v If the first o[...]

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 DA TE( ’ 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 dura[...]

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 IBM OS/400 Query 10[...]

Time arithmetic operation in Query for iSeries T imes 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 d[...]

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 . T o be properly interpreted, the number must have the format yyyymmddhhMMsszzzzzz , where yyyy , mm , dd , hh , MM , ss and zzzzzz represent, respectively , the nu[...]

DA TE Query for iSeries function The DA TE function returns a date from a value. The form is:  DATE ( expression )  The argument must be either a: v T imestamp v Date v Positive number or expression less than 3 652 059 v V alid character representation of a date v Character representation with a length of 7 If the argument is a characte[...]

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) RESUL T(DA Y) would equal a value between 1 and 31. DA YS Query for iSeries function The DA YS function retur[...]

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:  MICROSECOND ( expression )  The argument must be either a: v T imestamp v T imestamp duration v V alid character representation of a timestamp The result of the function is a [...]

MONTH Query for iSeries function The MONTH function returns the month part of a value. The form is:  MONTH ( expression )  The argument must be either a: v Date v T imestamp v Date duration v T imestamp 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, th[...]

TIME Query for iSeries function The TIME function returns a time from a value. The form is:  TIME ( expression )  The argument must be either a: v T ime v T imestamp v V alid 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 re[...]

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:  YEAR ( expression )  The argument must be either a: v Date v T imestamp v Date duration v T imestamp duration The result of the function is a binary field. If the argument[...]

Converting date for output to a display or printer in Query for iSeries Y ou can use the date functions to convert an input date field to a dif ferent 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 [...]

Define Result Fields Type definitions using field names or constants and operators, press Enter. Operators: +, -, *, /, SUBSTR, ||, DATE... Field Expression Column Heading Len Dec YYDDD_____ DATE(MMDDYY)_____________________ ____________________ _____ __ _________________________________ ____________________ _________________________________ ______[...]

Define Result Fields Type definitions using field names or constants and operators, press Enter. Operators: +, -, *, /, SUBSTR, ||, DATE... Field Expression Column Heading Len Dec MMYYDD_____ DATE(YYDDD)_____________________ ____________________ _____ __ _________________________________ ____________________ _________________________________ ______[...]

– 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 na[...]

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 headi[...]

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 ‘H[...]

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 o[...]

After making new selections or changing old ones, press the Enter key to rearrange them on the display in the new sequence. Y ou 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 se[...]

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 Seq Field 10 COMPANY ____ BALDUE 20 ACCTNUMBER ____ DATELASTPD 30 PASTDUE ____ CURRENTDAT ____ FIRSTNAME ____ YEAR ____ MIDDLEINIT ____ MONTH ____ LASTNAME ____ STREETADDR ____ CITY ____ STATE ____ ZIPCODE ____ [...]

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 [...]

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 [...]

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 Test Value (Field, Number, or ’Characters’) ______________ _____ ______________________________ ____ ______________ _____ ______________________________ ____ ______________ _____ _________[...]

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. The following are valid comparisons for record selection te[...]

SBCS character T imestamp 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[...]

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 V ALUE function. Press F17 to show which decimal separator to use in the query (see “ Displaying the format of constants in[...]

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. Comparis[...]

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 mus[...]

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. T o 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 bu[...]

Y ou 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 o[...]

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 no[...]

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[...]

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 Test Value (Field, Number, or ’Characters’) LASTNAME EQ ’Kingsbury’ OR CRLIMIT GE 5000 ____ ______________ _____ ______________________________ ____ ______________ _____ ______________[...]

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 Test Value (Field, Number, or ’Characters’) BALDUE RANGE 900 1000 AND STATE EQ ’RI’ AND YEAR LIST 86 87 ____ ______________ _____ ______________________________ ____ ______________ ___[...]

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. T o Query , a sort field is a field whose contents are used to sort the output records in a particular order . For example, if you wanted to use a customer master file to obtain a l[...]

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 ___ _ ACCTNUMBER 30 _ INIT 20 _ LASTNAME ___ _ STREETADDR ___ _ STATE ___ _ CITY ___ _ ZIPCODE 10 D CRLIMIT Bottom F3=Exit F5=Report F11=Display text F12=Cancel F13=Layout F18=Files F20=Renumber F24=Mo[...]

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 na[...]

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 Text Len Dec 10 D CRLIMIT Credit limit amount 7 2 20 A STATE State abbreviation 2 30 A LASTNAME Customer last name 20 40 A INIT Customer first and middle initial 2 ___ _ ACCTNUMBER Account number 6 ___[...]

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 so[...]

11 0 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 as[...]

T o 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 collatin[...]

Selecting a Query for iSeries collating sequence T o 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 [...]

T able 3. Example of How a Code T able 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 ai nt h i st a b l ei sa t ’ 81 ’ X and the uppercase A is at ’ C1 ’ X. This is an example, a real table contains al[...]

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 differ[...]

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 info[...]

Select Translation Table Library ........ *LIBL Name, *LIBL, F4 for list Subset ........ __________ Name, generic* Position to ...... __________ Starting character(s) Type option (and Table and Library), press Enter. 1=Select Opt Table Library Text _ __________ _________ _ QASCII QSYS EBCDIC TO ASCII TRANSLATE TABLE _ QEBCDIC QSYS ASCII TO EBCDIC T[...]

Select System Sort Sequence Type choices, press Enter. Sort Sequenc e.....1 1=Job run 2=Unique 3=Shared Language i d...... *JOBRUN *JOBRUN, language id, F4 for list F3=Exit F4=Prompt F5=Report F10=Process/Previous F12=Cancel F13=Layout F18=Files F23=Save as default Job run and *JOBRUN are resolved to the sort sequence (SRTSEQ keyword) and language [...]

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. Y ou 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 fiel[...]

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. Column headings: *NONE, aligned text lines Column Field Spacing C[...]

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 sam[...]

Note: Y ou 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) yo[...]

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. Generally , when you first come to the other four numeric field editing displays (identified on the Edit option pr[...]

Decimal point in Query for iSeries reports T ype 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 Example Meaning of Option 1= . 245.87 Decimal point is a period 2= , 245,87 De[...]

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 [...]

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=Y es 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[...]

The Describe Date/T ime 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 Dat[...]

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 [...]

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 [...]

Optional edit code modifier in Query for iSeries reports T ype 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 Asterisk[...]

Specify Edit Word Fiel d.....: Headin g1....: Length ....: Headin g2....: Decima l....: Headin g3....: Type information, press Enter. (Put quotes around edit words.) (Each blank replaced by a digit, each ’&’ with a blank.) Edit wor d..... ’ , , 0 . - ’ (These are SAMPLES only; they are not defaults.) Edit word for summary total . . ’ [...]

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[...]

134 Query for iSeries Use V5R2[...]

Chapter 1 1. 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 [...]

2=A verage 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 [...]

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 dif ferent[...]

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 r[...]

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 Sort Level Prty Field 1 1 COMPANY 2 2 DIVISION 3 3 REGION 4 4 DEPARTMENT 5 5 EMPLOYEE Bottom F3=Exit F5=Report F10=Process/previous F11=Display text F12=Cancel F13=Layout F18=Files F23=Long comment An example [...]

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. If STATE was defined as the break field for break level 1, CITY was defined as[...]

Y ou 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 specif ya0i nt h e 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 b[...]

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 . Y ou can also specify some formatting characteristics f[...]

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=Y es All summary values are to be suppressed. They are not to be included for this break level. Break text in Query for iSeries reports T ype any text that you want to appear at each report break for this break level[...]

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. Y ou can have the output displayed, printed, or put into a database file. Y ou can also specify that the output is to be in detailed form (which includes all[...]

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 pr[...]

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 sp[...]

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 ..... Shift to column . . . Line ....+....1....+ INTEGER CHAR DECIMAL NUMERIC 000001 [...]

Define Printer Output Type choices, press Enter. Printer devic e.... *PRINT *PRINT, name Form size Lengt h....... _ _ _ Blank, 1-255 Width ....... 1 3 2 Blank, 1-378 Start lin e...... _ _ _ Blank, 1-255 E n dl i n e....... _ _ _ Blank, 1-255 Line spacin g..... 1 1 ,2 ,3 Print definitio n... N Y=Yes, N=No F3=Exit F5=Report F10=Process/previous F12=C[...]

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 [...]

Completing this display does not cause the query to immediately run and print a report. Y ou 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 displ[...]

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: T ype 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 d[...]

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: T ype the title, or title text, to be printed on your report as a cover page. Y ou can type as many as four full lines of text using all characters (including uppercase a[...]

Print standard page headings in Query for iSeries reports: T ype 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). Y ou 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 [...]

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 T ype 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 [...]

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. T o 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 attribut[...]

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: T o specify the database f[...]

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. Y ou need to cancel the reques[...]

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: T ype 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 sp[...]

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 re[...]

Break level ID (1) | | | | | ||| | | Overflow indicator (1) Total salary (11.2) | || | | County (11) | Average salary (8.2) || City (10) 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 i[...]

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[...]

Rounding numeric field values during Query for iSeries processing Y ou 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 [...]

Whether you choose to have decimal data errors ignored depends on whether your data contains values that cause decimal data errors. Y ou 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 ap[...]

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 Thi[...]

Describing the Query for iSeries query definition Y ou 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 T ext column is shown. Giving au[...]

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. T o run a query , you can: v Press F5 (Report) while creatin[...]

Y ou 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. T o save your query , you need to supply values for the Library and Query prompts.[...]

Work with Queries Type choices, press Enter. Option ....... _ 1=Create 2=Change 3=Copy 4=Delete 5=Display 6=Print definition 8=Run in batch 9=Run Query ....... __________ Name, F4 for list Librar y...... QGPL Name, *LIBL, F4 for list F3=Exit F4=Prompt F5=Refresh F12=Cancel Query displays a report, prints a report, or puts the data into a database f[...]

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 pro[...]

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 Y ou can change the results of your query output by making changes to your query definition. v Y ou can copy your query definition to the sam[...]

Define the Query Quer y......: QRY1 Option .....: Change Librar y.....: QGPL CCSID .....: 3 7 Type options, press Enter. Press F21 to select all. 1=Select Opt Query Definition Option > Specify file selections _ Define result fields _ > Select and sequence fields _ Select records _ Select sort fields _ Select collating sequence _ > Specify [...]

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 wit ha>t ot h e left of the option. Y ou 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 [...]

Considerations for changing Query for iSeries queries T o 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. Y ou cannot change a que[...]

T able 8. Effect of Collating Sequence and Processing Option on Selection. Either or both NAME1 and NAME2 are DBCS data. Y es means the record under test is selected. No means it is not selected. T est ColSeq=YES Unique Weights ColSeq=YES Shared Weights ColSeq=NO Unique Weights ColSeq=NO Shared Weights NAME1 EQ NAME2 No Y es No No NAME1 GE NAME2 Y [...]

T o place the copy in the same library as the original, do not type anything in the T o library prompt. Then select a choice in the Replace query prompt: v T ype 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[...]

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 INVENTJA[...]

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). Y ou 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 ” .Y o u may be able to print the defi[...]

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 [...]

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 outp[...]

E means DBCS-either G means DBCS-graphic L means Date T means T ime Z means Timestamp v The Null column shows whether the field is null capable (Y) or not (blank). v The Data T ype column shows the data type for each field. v The T ext column shows the comment (if it exists) for each field. If the field is for a summary function, the text shown is [...]

184 Query for iSeries Use V5R2[...]

Part 3. Advanced information about Query for iSeries Chapter 17. Additional information about Query for iSeries for programmers ........ 1 8 7 Files with different record formats in Query for iSeries .................. 1 8 7 File sharing considerations in Query for iSeries ..................... 1 8 7 Overriding database 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 Y ou can use a file that has more than one record format; however , you can use only one record format at a time. If an application [...]

Joining files in Query for iSeries For joining files (type of join): v T ype 1 join is the same as for Query/36 (on System/36). v T ype 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 T ype 3 join selects only the records in a primary file that have no m[...]

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. Y ou can specify length and decimal positions for the intermediate result fields to mi[...]

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 . Note: The presentation length for RESUL T A a would be 31,9. Y ou can see this by pressing ″ F13=[...]

Selecting records (ignoring field case) in Query for iSeries T o select records ignoring the case, do one of the following: v Define your own collating sequence such that uppercase and lowercase letters have the same weight. v Use a system sort sequence with shared collating weights. Y ou must also set the Use collating sequence for all character c[...]

192 Query for iSeries Use V5R2[...]

Part 4. Appendixes © Copyright IBM Corp. 2000, 2002 193[...]

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 dif ferences that may cause you some inconvenience if you did not know about them before working with Query for iSeries. This a[...]

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 lengt[...]

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 QR Y WRKQRY QR YRUN RUNQRY REMOVE DL TQRY FROMLIBR SA VOBJ HELP QUER Y STRQRY LIBRLIBR CRTDUP[...]

198 Query for iSeries Use V5R2[...]

Appendix B. Practice exercise for Query for iSeries query T o 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 da[...]

7. Y ou 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. Y ou may change the data dictionary .) Select Definition Type Typ[...]

Create and Select Field Definitions Definition ......: NAMEADDRR Dictionary ......: SYSDIC Position t o...... . __________ Field, sequence (0-99999) Type sequence numbers (0-99999), (and Field), press Enter. Type field, press F6 to create. Seq Field End Seq Field End Seq Field End 10 LASTNAME Now press F6. Y ou will complete the description of the [...]

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 .....: Y=Yes -------Field--------- Dec More Name Type Size Pos Opt Text __________ _ _____ __ _ __________________________[...]

you press F3 (Exit) without making any changes to the display . Y ou 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[...]

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 Librar y....... default___ Name,F4 for list Position t o..... __________ Start[...]

WORK WITH DATA IN A FILE Mod e....: ENTRY Forma t....: NAMEADDR Fil e....: NAMEADDR LASTNAME: SIMPSON FIRSTNAME: FRANK ADDRESS1: 1722 ORANGE STREET ADDRESS2: CITY: RIDGEWAY STATE: CA ZIP: 72430 AMOUNT: 9998 F3=Exit F5=Refresh F6=Select format F9=Insert F10=Entry F11=Change 5. The Work with Data in a File display is shown again, but the fields are b[...]

End Data Entry Number of records processed Added .....: 1 0 Changed ....: 0 Deleted ....: 0 Type choice, press Enter. End data entry ....... Y Y=Yes, N=No Y ou 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 Librar y....... default__ Name,F4 for list P[...]

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[...]

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, [...]

Specify File Selections Type choices, press Enter. Press F9 to specify an additional file selection. F i l e.......... NAMEADDR Name, F4 for list Library ....... YOURLIB Name, *LIBL, F4 for list M e m b e r......... *FIRST Name, *FIRST, F4 for list Forma t......... NAMEADDRR Name, *FIRST, F4 for list F3=Exit F4=Prompt F5=Report F9=Add file F12=Canc[...]

Exit This Query Type choices, press Enter. Save definition . . . Y Y=Yes, N=No Run optio n...... 1 1=Run interactively 2=Run in batch 3=Do not run For a saved definition: Query ....... KJOQRY Name Library ..... YOURLIB Name, F4 for list Text .... Lists customer names and addresses Authority ..... *CHANGE *LIBCRTAUT, *ALL, *CHANGE, *EXCLUDE, *USE au[...]

Work with Queries Type choices, press Enter. Option ....... _ 1=Create, 2=Change, 3=Copy, 4=Delete 5=Display, 6=Print definition 8=Run in batch 9=Run Query ....... KJOQRY Name, F4 for list Librar y...... YOURLIB Name, *LIBL, F4 for list F3=Exit F4=Prompt F5=Refresh F12=Cancel Query option processing completed successfully. Query for iSeries query e[...]

Define the Query Quer y......: KJOQRY Option .....: CHANGE Librar y.....: YOURLIB CCSI D......: 3 7 Type options, press Enter. Press F21 to select all. 1=Select Opt Query Definition Option > Specify file selections _ Define result fields _ Select and sequence fields 1 Select records _ Select sort fields _ Select collating sequence _ Specify repo[...]

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 Test Value (Field, Number, or ’Characters’) STATE EQ ’LA’ ____ ______________ _____ ______________________________ ____ ______________ _____ ______________________________ ____ _______[...]

Display Report Report widt h.....: 1 1 7 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 ******** Bot[...]

Exit This Query Type choices, press Enter. Save definition . . . N Y=Yes, N=No Run optio n...... 3 1=Run interactively 2=Run in batch 3=Do not run For a saved definition: Query ....... CEBQRY Name Library ..... YOURLIB Name, F4 for list Text ..... Lists customer names and addresses Authority ..... *CHANGE *LIBCRTAUT, *CHANGE, *ALL *EXCLUDE, *USE au[...]

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. Y ou must type in the[...]

Work with Queries Type choices, press Enter. Option ....... 1 1=Create, 2=Change, 3=Copy, 4=Delete 5=Display, 6=Print definition 8=Run in batch 9=Run Quer y........ QNAME Name, F4 for list Librar y...... YOURLIB Name, *LIBL, F4 for list F3=Exit F4=Prompt F5=Refresh F12=Cancel (C) COPYRIGHT IBM CORP. 1988, 1991 4. On the Define the Query display , s[...]

Specify File Selections Type choices, press Enter. Press F9 to specify an additional file selection. F i l e.......... QRYFILE Name, F4 for list Library ....... QGPL Name, *LIBL, F4 for list M e m b e r......... *FIRST Name, *FIRST, F4 for list Forma t......... *FIRST Name, *FIRST, F4 for list F3=Exit F4=Prompt F5=Report F9=Add file F12=Cancel F13=[...]

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 Seq Field Seq Field ODDCEN ODCCEN ODSV02 ODDDAT ODCDAT ODSV03 ODDTIM ODCTIM ODSV04 ODLBNM ODOBOW ODSV05 ODOBNM ODSCEN ODSV06 ODOBTP ODSDAT ODSV07 ODOBAT ODSTIM ODSV08 ODOBFR ODSCMD ODSV09 ODOBSZ ODSSZE ODSV10 OD[...]

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 Text Len Dec 1 ODLBNM LIBRARY 10 2 ODOBTP OBJECT TYPE 8 3 ODOBAT OBJECT ATTRIBUTE 10 4 ODOBSZ OBJECT SIZE 10 0 5 ODOBTX TEXT DESCRIPTION 50 ODDCEN DISPLAY CENTURY 1 ODDDAT Display date: Format- MMDDYY 6 ODDTIM D[...]

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 Test Value (Field, Number, or ’Characters’) ODOBTP LIST ’*FILE’ ’*PGM’ Bottom Field Text Len Dec ODLBNM LIBRARY 10 ODOBTP OBJECT TYPE 8 ODOBAT OBJECT ATTRIBUTE 10 ODOBSZ OBJECT SIZ[...]

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 Test Value (Field, Number, or ’Characters’) ODOBTP LIST ’*FILE’ ’*PGM’ Bottom Field Text Len Dec ODLBNM LIBRARY 10 ODOBTP OBJECT TYPE 8 ODOBAT OBJECT ATTRIBUTE 10 ODOBSZ OBJECT SIZ[...]

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 Text Len Dec 1 A ODOBTP OBJECT TYPE 8 2 A ODOBSZ OBJECT SIZE 10 0 ODLBNM LIBRARY 10 ODOBAT OBJECT ATTRIBUTE 10 ODOBTX TEXT DESCRIPTION 50 Bottom F3=Exit F5=Report F11=Display names only F12=Cancel F13=[...]

Specify Report Column Formatting Type information, press Enter. Column headings: *NONE, aligned text lines Column Field Spacing Column Headings Len Dec Edit ODLBNM 0 NAME OF 10 LIBRARY ODOBTP 2 OBJECT 8 TYPE ODOBAT 2 OBJECT 10 ATTRIBUTE More... F3=Exit F5=Report F10=Process/previous F12=Cancel F13=Layout F16=Edit F18=Files F23=Long comment Press th[...]

Select Report Summary Functions Type options, press Enter. 1=Total 2=Average 3=Minimum 4=Maximum 5=Count ---Options--- Field Text Len Dec ODLBNM LIBRARY 10 ODOBTP OBJECT TYPE 8 ODOBAT OBJECT ATTRIBUTE 10 12345 ODOBSZ OBJECT SIZE 10 0 ODOBTX TEXT DESCRIPTION 50 Bottom F3=Exit F5=Report F10=Process/previous F11=Display text only F12=Cancel F13=Layout[...]

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 Sort Level Prty Field Text Len Dec ODLBNM LIBRARY 10 1 10 ODOBTP OBJECT TYPE 8 ODOBAT OBJECT ATTRIBUTE 10 20 ODOBSZ OBJECT SIZE 10 0 ODOBTX TEXT DESCRIPTION 50 Bottom F3=Exit F5=Report F10=Process/previous F11[...]

Format Report Break Break level .......: 1 Type choices, press Enter. (Type &field in text to have break values inserted.) Skip to new pag e..... N Y=Yes, N=No Suppress summarie s.... N Y=Yes, N=No Break tex t........ Break text for object type Level Field 1 ODOBTP F3=Exit F5=Report F10=Process/previous F12=Cancel F13=Layout F18=Files F23=Long [...]

Display Report Report widt h.....: 1 0 4 Position to line ..... Shift to column ...... Line ....+....1....+....2....+....3....+....4....+....5....+....6....+....7... NAME OF OBJECT OBJECT OBJECT TEXT DESCRIPTION OBJECT TYPE ATTRIBUTE SIZE 000001 QGPL *FILE PF 1,024 Default source dat 000002 QGPL *FILE PF 1,024 Default source dat 000003 QGPL *FILE P[...]

Exit This Query Type choices, press Enter. Save definition . . . N Y=Yes, N=No Run optio n...... 3 1=Run interactively 2=Run in batch 3=Do not run For a saved definition: Query ....... QNAME Name Library ..... QGPL Name, F4 for list Text ....... Authority ..... *CHANGE *LIBCRTAUT, *CHANGE, *ALL *EXCLUDE, *USE authorization list name F4=Prompt F5=Re[...]

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 variat[...]

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 [...]

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 valida[...]

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 (1 1 1 1). For example, the binary representation of +123 in zoned decimal format is 1 1 1 1 0001 1 1 1 1 0010 1 1 1 1 001 1. If a file contains numeric fields defined as zoned ([...]

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 subtra[...]

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/om[...]

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 reflec[...]

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 [...]

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 [...]

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[...]

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 T ools for iSeries book[...]

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 e[...]

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 interactiv[...]

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 [...]

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. Y ou may not be able to work on a query or run a saved query depending on the CCSIDs involved. Four conditions can result from CCSID comparisons: v They match identically , whic[...]

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 cha[...]

- T ext 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. The CCSID of the character da[...]

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. T able 9. Language Code-Page CCSIDs Country or V ersion Code[...]

T able 10. How CCSIDs Affect Displaying a Query Job CCSID Query CCSID Constants Processed as Marked with CCSID 37 37 37 65535 37 37 37 65535 65535 65535 No tag 65535 37 No tag 65535 Changing a Query for iSeries query Y ou can change the query definition if the process (job) CCSID and the query definition CCSID match, if either is marked with a CCSI[...]

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 w[...]

T able 12. How CCSIDs Affect Run-T ime Record Selection Job CCSID Query CCSID Constants T reated as CCSID 37 37 37 65535 37 37 37 65535 37 65535 No tag 65535 37 No tag 37 37 500 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[...]

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 di[...]

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 d[...]

T able 13. CCSIDs Not Compatible-Definition-Time Consequences (continued) Item 1 Item 2 Consequence Collating sequence Field 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 [...]

T able 14. CCSIDs Conversion Problems-Run-Time Consequences (continued) Item 1 Item 2 Consequence Collating sequence Job 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 (+[...]

T able 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 Y e sY e sY e sY e s A 65535 Y es Y es 1 Ye s 1 Ye s 1 65535 A Y es Y es Y es Y es ABY e s N o 1 No 1 No 1 65535 No tag Y es Y es Y es Y es A No tag Y es Y es 1 Ye s 1 Ye s 1 1. Either a warning or [...]

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 T ools data file utility (DFU) to create programs to ent[...]

260 Query for iSeries Use V5R2[...]

Notices This information was developed for products and services offered in the U.S.A. IBM may not of fer 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[...]

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 . T ra[...]

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 *LIBCRT AUT authority 159, 168 *LIBL special library name 10 *QRYDFN (query definition) object 5 *USE authority 159, 168 *USRLIBL speci[...]

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[...]

coded character set identifier (CCSID) (continued) sort field 253 summary function 253 translation table 1 16 collating sequence 87, 1 1 1, 1 12, 249 affecting query 1 12, 165 CCSID (coded character set identifier) 1 14 collating sequence 256 default 248 hexadecimal 248 language 248 translation table 248 user-defined 248 considerations when changin[...]

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 Definit[...]

date, time, and timestamp field (continued) migrating Nt oN - 1 2 4 1 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 DA Y function argument rules 75 syntax diagram 75 DA YS function argument rules 76 syntax diagram 76 DB2 UDB fo[...]

definition (continued) changing 30, 173 changing (example) 21 1 copying 177 copying before changing 173 creating 27, 30 creating (example) 206 deleting 179 displaying 179 DL TQRY command 178 information printed 181 migrating restriction 197 naming 27 printing 180 renaming 178 running 8 saving 167 selecting files 33 specifying authority 168 definiti[...]

dividing by zero (0) example 83 DL TQRY (Delete Query) command 1 1, 178 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 1 1 1 space requirements 9 symbolic characters 4 duration[...]

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 an[...]

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 [...]

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 Inter[...]

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 L T (l[...]

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 14[...]

query definition (continued) DL TQRY command 1 1, 178 Query for iSeries Utilities menu 1 1 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 1 1 renaming 178 running 8 saving 167 select[...]

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 positio[...]

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[...]

symbolic character DBCS (double-byte character set) 4 system administrator guidelines 243 system sort sequence 1 17 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) 252 code displaying[...]

value length date 120 time 120 timestamp 120 V ARCHAR function 63 V ARGRAPHIC function 65 variable-length field release restrictions 241 verifying choice creating or changing query definition 36 viewing queries on display 8 W word, edit numeric field 131 Work with Database Files display (IDDU) 203 Work with File Definitions display (IDDU) 202 Work [...]

280 Query for iSeries Use V5R2[...]

[...]

 Printed in U.S.A. SC41-5210-04[...]