Instruction/ maintenance manual of the product ISERIES SC41-5210-04 IBM
Go to page of 294
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.
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? .....
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 ..
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 .
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 .
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 .
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 .
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.
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 .
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.
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.
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.
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.
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.
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 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.
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.
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.
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).
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.
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.
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.
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 .
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 .
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 .
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 .
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 .
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 ......
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 .
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.
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.
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 .
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.
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.
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 .
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.
v If you type a generic library name (in the form of ABC* ) or special library name in this prompt and press the Enter key , that library group is searched for the file named in the File prompt. If the file is found in one of the libraries, the name of that library replaces the special library or generic name.
Using file IDs for a Query for iSeries query File IDs are used when you select more than one file. Although Query assigns a file ID value to each file selection, you can specify your own identifier . Note: When you are creating a query , the File ID prompt is not shown for your first file selection.
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.
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.
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, .
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 F.
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 B.
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.
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 prima.
Note: See “ CCSID and join tests in Query for iSeries ” on page 252 for information on how CCSIDs can affect your join selections. Specify How to Join Files Type comparisons to show how file selections related, press Enter.
v A field name must be preceded by a 1- to 3-character file identifier if that field name is used in more than one file selected for the query . v 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.
- 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 i.
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.
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.
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.
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.
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.
Query performs the file join in two steps: Step 1: Join the first two files, A and C . Query joins the files in the order listed, starting with file A ( CUSTOMER ), and file C ( ITEM ). But there is no join test that compares a field in A to a field in C .
every record in file C . Because there are three records in A and five records in C , the result is 15 records. The join tests are not used in this step. The result of step 1 this time is a working file called AC . Step 2: Join file AC to file B . Query joins each record in file AC to every record in PURCHASE file B for which A.
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.
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 .
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.
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).
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.
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.
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.
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.
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 .
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.
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.
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 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.
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.
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.
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 .
Subtraction rules are different from addition rules because a date, time, or timestamp value cannot be subtracted from a duration. Also, subtracting two date, time, or timestamp values is not the same as subtracting a duration from a date, time, or timestamp value.
then MONTH(RESULT) = 12 + MONTH(DATE1) - MONTH(DATE2). YEAR(DATE2) is then incremented by 1. YEAR(RESULT) = YEAR(DATE1) - YEAR(DATE2). For example, the result of DA TE( ’ 3/15/2000 ’ )- ’ 12/31/1999 ’ is 215, or a duration of 0 years, 2 months, and 15 days.
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.
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.
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 .
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 .
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.
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 ) .
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.
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.
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.
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.
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(MMDD.
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(YYD.
– 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.
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.
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 .
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.
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 .
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 ____ DATELASTP.
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.
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.
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 ’Character.
Comparison values in Query for iSeries The value you compare the field with can be: v Another field v A numeric constant v A character constant (SBCS or DBCS) v A date constant v A time constant v A timestamp constant The field and the value you are comparing must be compatible data types.
SBCS character 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: 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.
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.
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.
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.
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.
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 .
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.
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 ’Character.
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 ’Character.
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 .
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 ___ _ STRE.
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 (fro.
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 2.
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 ” .
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.
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.
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.
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.
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.
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 .
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.
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=Pro.
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.
Each column heading appears in the report exactly as you type it. Each heading can be up to three 20-character lines, and you can use any characters you want (see the heading for the INIT field on the following display). Specify Report Column Formatting Type information, press Enter.
v 0 through 9 (single-precision) or 0 through 17 (double-precision) for floating-point fields For date, time, timestamp and DBCS fields, you cannot change the length to anything but 0, which excludes the field from the report.
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 .
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.
v The Edit option value is the value last specified in the query definition, the value associated with the field definition in the file definition (if this is a field from a file), or 1, if neither of the first two indicates its value.
Decimal point in Query for iSeries reports 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.
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.
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.
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).
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.
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.
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.
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 .
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.
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.
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.
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 colla.
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.
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.
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.
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.
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 .
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.
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.
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.
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.
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 .
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.
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.
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 ” ).
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.
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.
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).
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.
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.
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.
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.
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.
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 .
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.
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).
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.
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.
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.
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.
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.
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.
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 .
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.
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.
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.
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 .
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.
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.
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 .
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.
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.
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.
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.
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.
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 .
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.
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.
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.
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.
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.
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.
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.
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 .
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 .
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.
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.
Create Field Definitions Type information, press Enter to create. Field type (size )..: 1=Character (1-32766) 2=Numeric (1-31, decimal positions 0-31) 3=DBCS (4-32766, mixed; 1-16383, graphic) 4=Date/Time (no size) More options .
you press F3 (Exit) without making any changes to the display . 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.
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.
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.
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.
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.
Notice that Query has already supplied a 1 for the Specify file selections option on this display . This is because whenever you create a query , you must specify a file, but you do not have to select any other options on this display . 4. Press the Enter key , and the Specify File Selections display is shown.
Specify File Selections Type choices, press Enter. Press F9 to specify an additional file selection. 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.
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 .
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.
Define the Query Quer y......: KJOQRY Option .....: CHANGE Librar y.....: YOURLIB CCSI D......: 3 7 Type options, press Enter. Press F21 to select all.
Select Records Type comparisons, press Enter. Specify OR to start each new group. Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT... AND/OR Field Test Value (Field, Number, or ’Character.
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 .
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 .
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.
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.
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.
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.
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 ODO.
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 ’Character.
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 ’Character.
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 OB.
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.
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.
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 O.
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.
Display Report Report widt h.....: 1 0 4 Position to line ..... Shift to column ...... Line ....+....1....+....2....+....3....+....4....+....5....+....
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 .
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 .
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.
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.
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.
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.
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 .
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.
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.
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 .
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.
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.
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 .
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 .
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.
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.
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.
- 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 .
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 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 f.
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.
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.
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 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.
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 .
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.
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.
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 .
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.
The licensed program described in this information and all licensed material available for it are provided by IBM under terms of the IBM Customer Agreement, IBM International Program License Agreement, or any equivalent agreement between us. If you are viewing this information softcopy , the photographs and color illustrations may not appear .
Index Special Characters *ALL authority 159, 168 *ALL join 44, 188 *ALL special library name 10 *ALLUSR special library name 10 *CHANGE authority 159, 168 *CURLIB special library name 10 *EXCLUDE auth.
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.
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 s.
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.
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 dat.
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 informati.
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 repre.
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 editin.
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 M.
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 introduc.
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 ti.
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 con.
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 na.
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 char.
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 dis.
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 d.
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 viewi.
280 Query for iSeries Use V5R2.
.
Printed in U.S.A. SC41-5210-04.
An important point after buying a device IBM ISERIES SC41-5210-04 (or even before the purchase) is to read its user manual. We should do this for several simple reasons:
If you have not bought IBM ISERIES SC41-5210-04 yet, this is a good time to familiarize yourself with the basic data on the product. First of all view first pages of the manual, you can find above. You should find there the most important technical data IBM ISERIES SC41-5210-04 - thus you can check whether the hardware meets your expectations. When delving into next pages of the user manual, IBM ISERIES SC41-5210-04 you will learn all the available features of the product, as well as information on its operation. The information that you get IBM ISERIES SC41-5210-04 will certainly help you make a decision on the purchase.
If you already are a holder of IBM ISERIES SC41-5210-04, but have not read the manual yet, you should do it for the reasons described above. You will learn then if you properly used the available features, and whether you have not made any mistakes, which can shorten the lifetime IBM ISERIES SC41-5210-04.
However, one of the most important roles played by the user manual is to help in solving problems with IBM ISERIES SC41-5210-04. Almost always you will find there Troubleshooting, which are the most frequently occurring failures and malfunctions of the device IBM ISERIES SC41-5210-04 along with tips on how to solve them. Even if you fail to solve the problem, the manual will show you a further procedure – contact to the customer service center or the nearest service center
