IBM SC41-5210-04 - Manual

Selecting files on the Query for iSeries Select File display . . . . . . . . . . . . . . . . . 37 Selecting file members on the Query for iSeries Select Member display . . . . . . . . . . . . 39 Selecting record formats on the Query for iSeries Select Record Format display . . . . . . . . . 40Displa...

Letting Query for iSeries select records . . . . . . . . . . . . . . . . . . . . . . . . 91Selecting the records you want in Query for iSeries . . . . . . . . . . . . . . . . . . . . 91 Comparison fields in Query for iSeries . . . . . . . . . . . . . . . . . . . . . . . . 92Comparison values in Quer...

Record on one page in Query for iSeries reports . . . . . . . . . . . . . . . . . . . 147 Displaying wrapping widths in Query for iSeries reports . . . . . . . . . . . . . . . . . 147Defining output to the printer in Query for iSeries reports . . . . . . . . . . . . . . . . 148 Defining output of Qu...

Appendix A. Differences between Query for iSeries and Query/36 . . . . . . . . . . . . 195 Conceptual Differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195Operational Differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195Command differences between System/3...

Running a Query for iSeries query . . . . . . . . . . . . . . . . . . . . . . . . . 251Running a default query in Query for iSeries . . . . . . . . . . . . . . . . . . . . . 251Selecting records at run time in Query for iSeries . . . . . . . . . . . . . . . . . . . 251 CCSIDs and Query for iSeries q...

About Query for iSeries Use Query for iSeries is a decision support utility you can use to obtain information from the DB2 UDB foriSeries. This book describes how to use Query for iSeries to select, arrange, and analyze informationstored in one or more database files to produce reports and other dat...

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 anydatabase files that have been defined on the system using...

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 for...

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 howfields are to be organized in files. A physical file contains the fields of data, as records, b...

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 thefunction keys, and error recover...

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 allthe function keys (such as F3, F4, and F11) apply to all displays. Each display shows, at the bottom of thedisplay, which function keys are commonly used wi...

DBCS characters take twice the amount of space of SBCS characters. In addition, a shift-out characterprecedes DBCS data, and a shift-in character follows the data. The shift-out and shift-in characters arecalled DBCS bracket characters. DBCS-graphic data is stored in the database without bracket cha...

The number of items shown in the list part of the display varies, depending on what form the list is in andthe amount of space used by the prompt part of the display. Sometimes a new display will appear thatshows the list. If all items in a list do not fit on the display, you can use the page keys t...

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 instructionsor taking the time to go through a series of menus. Query has four comma...

If you select option 3 (Delete a query) on the Query Utilities menu, you are shown the prompt display forthe Delete Query (DLTQRY) command. You can use this command to delete a query definition. If you donot know the name of a query or library, use option 1 (Work with queries) so that you can get a ...

9=Run Runs a query. Query displays a report, prints a report, or puts the data into a databasefile, depending on what is specified in the query definition. For information about runningqueries, see Chapter 15. Specifying a Query for iSeries query and its library To identify the query you want to wor...

You can select as many as 30 queries from the list, as well as type a query name and option in the firstlist position of this display. Query processes the requests in the order that they appear in the list, startingfirst with the option and query that you typed in the first list position (in the inp...

library group are shown. For example, if you typed IN* in the Subset prompt, you might see a list of query names like: INTEREST, INTFEB, INTMARCH, INVENTORY, INVFEB, INVMARCH , and so on. (The queries are shown alphabetically for each library.) You can also specify a complete name (without an *) in ...

Work with Queries Library . . . . . . . . QGPL Name, *LIBL, F4 for list Subset . . . . . . . . __________ Name, generic* Position to . . . . . . __________ 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 ...

Other considerations when you are trying to locate or use a particular Query foriSeries query You should be aware of other considerations when you are trying to locate or use a particular query. Touse a query: v You must have the authority needed to use the query definition. You may need to either a...

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 decimalseparator saved with the query does not match your current decimal separator. v Press F17 from the Select...

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 canbe 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 ...

Selecting options for a Query for iSeries query definition To select options that you want to use from those listed in the Query Definition Option column, type a 1 in the Opt (option) column beside each of those options, then press the Enter key. The displays for the options you select are then show...

You can specify one, several, or all of the options shown. To select all of them, press F21. Moving through the Query for iSeries definition displays For each option you select, Query shows you the definition displays for that step. (The words shown foreach option on the Define the Query display mat...

Chapter 4. Specifying and selecting files for a Query foriSeries query This chapter describes how you select and use one or more database files that are to be queried forinformation. Specifying file selections (the first option on the Define the Query display) is the first of the 11steps that you ca...

Specify File Selections Type choices, press Enter. Press F9 to specify an additional file selection. File . . . . . . . . . . __________ Name, F4 for list Library . . . . . . . QGPL Name, *LIBL, F4 for list Mem ber . . . . . . . . . *FIRST Name, *FIRST, F4 for list Format . . . . . . . . . *FIRST Na...

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 gen...

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 fileselection, 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. Ho...

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 jointe...

Types of joins in a Query for iSeries query Specify Type of Join Type choice, press Enter. Type of join . . . . . . . . 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...

Note: See “CCSID and join tests in Query for iSeries” on page 252 for information on how CCSIDs canaffect 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 ______________ _____ ___________...

- DBCS-either - DBCS-open – DBCS-either field with: - SBCS character - Date - Time - Timestamp - DBCS-either - DBCS-open - DBCS-only – DBCS-open field with: - SBCS Character - Date - Time - Timestamp - DBCS-either - DBCS-open - DBCS-only – DBCS-only field with: - DBCS-either - DBCS-open - DBCS-only ...

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

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 i...

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.CU...

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 theinformation that you want to present in your report does not exist as a field in your selected file(s). Forexample, you want your report to show the...

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

DAY DAYS MONTH YEAR v A time expression performs an operation on a time. Time expressions can contain the following operators or functions: + (Addition) − (Subtraction) CHAR TIME HOUR MINUTE SECOND MICROSECOND v A timestamp expression performs an operation on a timestamp. Timestamp expressions can c...

When you do more than one calculation within a numeric expression, use parentheses to tell Query inwhat order to do the calculations and to make the expression easier to understand. If you use nestedparentheses for example, 60 * (A − (B + C) ), the calculations are done for the innermost pair first ...

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

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 substrin...

Note: If you are sending queries between countries that use the comma for a decimal point, put a blankafter 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 resultare determined as follo...

VARGRAPHIC Query for iSeries function The VARGRAPHIC scalar function provides a way to convert: v character data (SBCS and Mixed) to DBCS graphic. v character data (SBCS and Mixed) to UCS2 graphic. v UCS2 graphic to UCS2 graphic. v DBCS graphic to UCS2 graphic. The VARGRAPHIC function returns a grap...

The actual length of the result depends on the number of characters in the argument. Each character ofthe argument determines a character of the result. Regardless of the CCSID, every double-byte code pointin the argument is considered a DBCS character, and every single-byte code point in the argume...

Query for iSeries date A date expression is a three-part value (year, month, and day) designating a point in time under the Gregorian calendar, which is assumed to have been in effect from the year 1 A.D. 1 The year range is 0001 to 9999. The month range is 1 to 12. The day range is 1 to x , where x...

Valid formats for times are listed in Table 2. Each format is identified by name and includes an associatedabbreviation (for use by the CHAR function) and an example of its use. Table 2. Formats for Representations of Time Data Types Format Name Abbreviation Time Format Example International Standar...

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 adate or time constant and do not use an SAA format. Display Constants Format Query . . . . . . : QRY1 Option . . . . . : CHANGE Library . . . . : QGPL...

Subtraction rules are different from addition rules because a date, time, or timestamp value cannot besubtracted from a duration. Also, subtracting two date, time, or timestamp values is not the same assubtracting a duration from a date, time, or timestamp value. The following rules apply to date, t...

then MONTH(RESULT) = 12 + MONTH(DATE1) - MONTH(DATE2). YEAR(DATE2) is then incremented by 1. YEAR(RESULT) = YEAR(DATE1) - YEAR(DATE2). For example, the result of DATE(’3/15/2000’) - ’12/31/1999’ is 215, or a duration of 0 years, 2 months, and15 days. Incrementing and decrementing dates in Query for ...

Time arithmetic operation in Query for iSeries Times can be subtracted, incremented, or decremented. The result of subtracting one time (TIME2) fromanother (TIME1) is a time duration that specifies the number of hours, minutes, and seconds between thetwo times. For example, the result of TIME( ’11:0...

Timestamp duration in Query for iSeries A timestamp duration represents a number of years, months, days, hours, minutes, seconds, andmicroseconds expressed as a DECIMAL (20, 6) number. To be properly interpreted, the number must havethe format yyyymmddhhMMsszzzzzz , where yyyy , mm , dd , hh , MM , ...

DATE Query for iSeries function The DATE function returns a date from a value. The form is: QQ DATE ( expression ) QR The argument must be either a: v Timestamp v Date v Positive number or expression less than 3 652 059 v Valid character representation of a date v Character representation with a len...

If the argument is a date duration or a timestamp duration, the result is the day part of the value, a binaryfield with a value between −99 and 99. A nonzero result has the same sign as the argument. Example: RESULT(DAY) = DAY(HIREDATE) RESULT(DAY) would equal a value between 1 and 31. DAYS Query fo...

The resulting value of HOUR would equal 12. MICROSECOND Query for iSeries function The MICROSECOND function returns the microsecond part of a value. The form is: QQ MICROSECOND ( expression ) QR The argument must be either a: v Timestamp v Timestamp duration v Valid character representation of a tim...

MONTH Query for iSeries function The MONTH function returns the month part of a value. The form is: QQ MONTH ( expression ) QR The argument must be either a: v Date v Timestamp v Date duration v Timestamp duration The result of the function is a binary field. If the argument can be null, the result ...

TIME Query for iSeries function The TIME function returns a time from a value. The form is: QQ TIME ( expression ) QR The argument must be either a: v Time v Timestamp v Valid character representation of a time The result of the function is a time value. If the argument can be null, the result can b...

RESULT(x) = TIMESTAMP(DATEFRNK,TIMEFRNK) YEAR Query for iSeries function The YEAR function returns a numeric representation of the year part of a value. The form is: QQ YEAR ( expression ) QR The argument must be either a: v Date v Timestamp v Date duration v Timestamp duration The result of the fun...

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

Chapter 6. Selecting and sequencing fields in Query foriSeries This chapter describes how you select fields that you want to include in your query output and how youindicate in what sequence the fields are to appear. They appear in a query report from left to right basedon the sequence number that y...

Chapter 7. Selecting records in Query for iSeries This chapter describes how you can perform record selection tests so that your query output contains onlyselected 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...

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 follo...

SBCS character Timestamp DBCS-either DBCS-open v Any field can be compared to the keyword NULL using the IS or ISNOT test to determine if a field valueis or is not null. Note: When comparing a date, time, or timestamp field with an SBCS, DBCS-either, or DBCS-opencharacter field, use a character fiel...

Note: If you are sending queries between countries that use the comma for a decimal separator, put ablank after each comma separating the offset and length in the SUBSTR function and betweennumeric values in a list in the VALUE function. Press F17 to show which decimal separator to usein the query (...

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 witha character representation of that data type. All comparisons are chronological. The farther a point in timeis from January 1, 0001, the g...

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 thanor equal to the first value but less than or equal to the second) for the record to be ...

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. Usin...

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 ____ ______________ _____ ____________...

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 queryoutput. To Query, a sort field is a field whose contents are used to sort the output records in a particularorder. For example, if you wante...

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 midd...

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 isused for certain operations (such as sorting, comparing, and evaluating) that involve SBCS character datain SBCS, DBCS-open, and DBCS-e...

To collate means to place items in proper sequence or to check that items are in proper sequence. ForQuery for iSeries purposes, collating sequences apply to SBCS character data in SBCS, DBCS-open, orDBCS-either fields, not numeric, date, time, timestamp, DBCS-only, or DBCS-graphic fields. The colla...

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 y...

The CCSID of the sequence is shown at the top and is always your job CCSID. If the collating sequenceCCSID does not match your job CCSID, the previously specified or defaulted sequence is convertedbefore it is shown on the Define Collating Sequence display. See “CCSIDs and collating sequences inQuer...

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

Each column heading appears in the report exactly as you type it. Each heading can be up to three20-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:...

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, whichexcludes the field from the report. For numeric fields, if you increase or decrease the value in Dec , you sho...

Note: You cannot change the order of the fields on your report during the formatting process. If youdetermine that you need to change the order of the fields in your output, you must go back to theDefine the Query display and choose the Select and sequence fields option (see Chapter 6, “Selecting an...

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 itsvalue. Generally, when you first come to the other four numeric field ed...

v If you specify option 3 (Floating currency symbol), then the negative sign moves (or floats), dependingon how many leading zeros it replaces, so that it is to the immediate left of the first significant digit inthe value (such as −27.03 ), or it is to the immediate left of the left currency symbol...

The right currency symbol is always in a fixed position. Print zero value in Query for iSeries reports When this field contains a numeric value of zero, type a Y or N to indicate whether it is to show a zero or it is to be left blank. Y=Yes A numeric value of 0, when it occurs, is to be shown. N=No ...

The Describe Date/Time Field Editing display allows you to specify the type of separator character thatyou want used for a numeric field that contains a date or time value. The editing value determines how thevalues for the date or time field are to appear in your query report (if you use option 2 a...

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 youwant used to edit each value in a numeric field. The edit code or edit description that you choosedetermines how the values for the specified f...

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 editingor whenever there are no other editing values to use. If an error occurs, an error messag...

Optional edit code modifier in Query for iSeries reports Type the character that you want used as the optional modifier for the specified edit code. If you specifiedone 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 m...

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 tosee that they have the same number of decimal positions. v Only the system-defined cu...

Summary functions can be specified for as many of these fields as you want, as long as that type offunction 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 foriSeries”) f...

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

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 definedon the Define Report Break display. This display is shown once for each of the break levels that youdefined for your query. You can a...

N=No Any summary values are not to be suppressed. They are to be included each time that a report breakoccurs at this break level. Y=Yes All summary values are to be suppressed. They are not to be included for this break level. Break text in Query for iSeries reports Type any text that you want to a...

output definition information. If no field selections are made, Query produces all fields for a databasefile. 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 Out...

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

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

Define Printer Output Type choices, press Enter. Printer device . . . . *PRINT *PRINT, name Formsize Length . . . . . . . ___ Blank, 1-255 Width . . . . . . . 132 Blank, 1-378 Start line . . . . . . ___ Blank, 1-255 End line . . . . . . . ___ Blank, 1-255 Line spacing . . . . . 1 1, 2, 3 Print defin...

If you want to increase the left margin of the report (with the left edge being print position 1), go to theSpecify 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 shif...

v If you type *STD , the system assumes that the standard forms are already in the printer, and no message is sent to the system operator. Copies in Query for iSeries reports: Type a number from 1 through 255 to indicate the number of copies of the report that you want printed. If you leave this pro...

Print standard page headings in Query for iSeries reports: Type a Y or N to indicate whether the system’s standard headings are to be printed on each page of your report (except for the cover page). You must specify an N if you do not want to print the standard headings; your use of the special date...

definition is the only way to determine some of the assigned attributes, such as the allocated length forvariable length fields. Changing the format definition is the only way to specify certain attributes, such as apreferred default for null values. Specifying an output database file for Query for ...

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 ofdecimal positions. A blank break value is s...

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 display...

Describing the Query for iSeries query definition You can also type some descriptive text about the query. The text reminds you what the query is for whenit is displayed in a list of queries or printed on the cover page of a report. The text is displayed, forexample, on the Work with Queries display...

Running a Query for iSeries query Running a query consists of telling Query for iSeries to use the query definition to acquire the data youwant from selected files, to organize the data into a report format, and then to either display the report,print it, or store it in a database file. To run a que...

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 Library . . . . . . QGPL Name, *LIBL, F4 for list F3=Exit F4=Prompt F5=Refresh F12=Cancel Query dis...

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 alibrary. In addition to running your query: v You can change the results of your query output by making changes to your query definition. v...

Considerations for changing Query for iSeries queries To make changes to your query, choose the correct options on the Define the Query display. Refer to theappropriate chapters or appendixes in this guide for information and considerations on how to enter thedefinitions that you want. Each query is...

Table 8. Effect of Collating Sequence and Processing Option on Selection. Either or both NAME1 and NAME2 areDBCS data. Yes means the record under test is selected. No means it is not selected. Test ColSeq=YES UniqueWeights ColSeq=YES SharedWeights ColSeq=NO UniqueWeights ColSeq=NO SharedWeights NAME...

To place the copy in the same library as the original, do not type anything in the To library prompt. Then select a choice in the Replace query prompt: v Type a Y if you want the copy to replace a query that has the same name in the library. v Leave the N in the prompt if you want to copy the query ...

delete. (For more information about using lists, see “Using lists in Query for iSeries” on page 9.) Thenpress the Enter key. The Confirm Delete of Queries display appears. ConfirmDelete of Queries Fromlibrary . . . . : MYLIB Press Enter to confirmyour choices for 4=Delete. Press F12 to return to cha...

If you display a query and decide you want to change it, you have to return to the Work with Queriesdisplay and use option 2 (Change). You can print the information you see when you display a query,although the information will be in a different form. See “Printing a Query for iSeries query definiti...

When you select option 6 (Print definition) on the Work with Queries display and press the Enter key, thequery 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 hea...

In calculations involving many large fields, you can control your results better by breaking the expressioninto parts, calculating each part as a separate result field. Then you can use these result fields in anexpression to calculate the result field you want. You can specify length and decimal pos...

Selecting records (ignoring field case) in Query for iSeries To select records ignoring the case, do one of the following: v Define your own collating sequence such that uppercase and lowercase letters have the same weight. v Use a system sort sequence with shared collating weights. You must also se...

Appendix B. Practice exercise for Query for iSeries query To do an exercise using query, you must first have a file set up with some data. This exercise takes youthrough the following steps before you actually create a query: v Creating an IDDU definition v Creating a database file v Entering data I...

you press F3 (Exit) without making any changes to the display. You are finished creating the definitions describing a database file. The instructions to create the file and to enter data into the file are described in the following sections. Query for iSeries query exercise: Creating a database file...

End Data Entry Number of records processed Added . . . . . : 10 Changed . . . . : 0 Deleted . . . . : 0 Type choice, press Enter. End data entry . . . . . . . Y Y=Yes, N=No You do not need to change anything on this display. Press the Enter key. 8. The Work with Database Files display is shown. Work...

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

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 Library . . . . . . YOURLIB Name, *LIBL, F4 for list F3=Exit F4=Prompt F5=Refresh F12=Cancel Query ...

Exit This Query Type choices, press Enter. Save definition . . . N Y=Yes, N=No Run option . . . . . . 3 1=Run interactively 2=Run in batch 3=Do not run For a saved definition: Query . . . . . . . CEBQRY Name Library . . . . . YOURLIB Name, F4 for list Text . . . . . Lists customer names and addresse...

Appendix C. Query for iSeries performance tips andtechniques This appendix provides guidelines for improving the performance of the Query for iSeries product. Theseguidelines help you better understand how Query works and which key items to keep in mind forperformance when designing or changing a qu...

Note: Having existing access paths is important because a temporary access path created by Query isnot 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 ...

Each time you run a saved query, Query validates the access plan by checking that the files and accesspaths 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 acce...

stored in the low-order four bits of each byte. The high-order four bits of the low-order byte contain thesign. The high-order four bits of all other bytes contain all 1s (1111). For example, the binaryrepresentation of +123 in zoned decimal format is 1111 0001 1111 0010 1111 0011. If a file contain...

retrieve the text for each field. Also, showing the text for each field results in fewer fields being shown oneach display so you have to page through more displays to retrieve the fields you need to view. This isalso true for query, file, member, and format lists. Define result fields in Query for ...

Note: If there is no existing access path matching some of the record selection tests, Query does notbuild an access path solely for purposes of selection. Query reads each record and selects thosethat qualify. If you request a particular query often, consider creating an access path with select/omi...

input or original file name. This produces a new database file containing the sorted records. The timerequired to produce the sorted records may vary, depending on whether Query has to build an accesspath to do the sort. Consider these items before performing this type of operation: v Any changes to...

However, if the data you want to view is at the end of the searched records, it may take longer thananticipated to display those records. This may also be true when paging down to access more data (evenif the first display did appear quickly) if the additional data is located much further into the f...

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 thesecondary files. The selection of primary records is dependent on the select/omit criteria specifiedon the Select Records display. Only primary records that mee...

Be careful with using the “NE” comparison between fields from different files on the Specify How to JoinFiles display. This could result in a large number of records being selected and a large amount of I/Obeing performed. Query runs more efficiently when the files are ordered from smallest to large...

Query for iSeries performance tuning A properly tuned system provides much better overall performance than one in which performance tuninghas not been used. However, there are many factors to consider when properly tuning the system to meetyour needs. See the Work Management topic for details. In ad...

display and also the total amount of time it takes the query to run. If a significant portion of thequery run time is spent building an access path, attempt to use the tips and techniques previouslylisted on using access paths. Query running. Building access path from file X in Y. This message indic...

Appendix D. Preventing users from running Query for iSeriesqueries interactively The system administrator can restrict users from running queries interactively. When this restriction isapplied, users cannot: v Use option 9 (Run) on the Work With Queries display. v Press F5 (Report) to display the re...

Appendix E. Coded character set identifiers (CCSIDs) inQuery for iSeries This appendix contains information about coded character set identifiers or CCSIDs. This information willhelp you understand CCSIDs and why they are important to Query for iSeries. A CCSID is a 2-byte (unsigned) integer that un...

CCSID marking in Query for iSeries The following bulleted objects contain CCSID tags used by query processing. Each item inherits its CCSIDfrom the containing item unless marked with an asterisk. An asterisk indicates that the object has its ownCCSID tag. The bulleted items inherit their CCSID from ...

- Text about the member * - Data (the CCSID tag or tags are in the format definition) Notes: 1. The CCSID of the query definition is shown on displays that show the query name, like the Define the Query display. 2. The CCSID of the user-defined collating sequence is shown on the Define Collating Seq...

Note: The query profile, user-defined collating sequence default, and CCSID are only updated if you press F23 from the Define Collating Sequence display to save the sequence. Query for iSeries language sequences Following are the language sequences that are supported by Query for iSeries. Table 9. L...

Table 10. How CCSIDs Affect Displaying a Query Job CCSID Query CCSID Constants Processed as Markedwith CCSID 37 37 37 65535 37 37 37 65535 65535 65535 No tag 65535 37 No tag 65535 Changing a Query for iSeries query You can change the query definition if the process (job) CCSID and the query definiti...

Table 12. How CCSIDs Affect Run-Time Record Selection Job CCSID Query CCSID Constants Treated 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 pr...

CCSID compatibility considerations in Query for iSeries CCSID marking makes it possible for conversions to be performed before presentation of marked materialor use of marked material in comparisons, but can also restrict the use of various combinations of markeditems. The following tables show what...

Bibliography The following OS/400 books contain informationyou may need. The books are listed with their fulltitle and base order number. v ADTS/400: Data File Utility , SC09-1773-00, provides the application programmer orprogrammer with information about using theApplication Development Tools data ...

Notices This information was developed for products and services offered in the U.S.A. IBM may not offer theproducts, services, or features discussed in this document in other countries. Consult your local IBMrepresentative for information on the products and services currently available in your are...

The licensed program described in this information and all licensed material available for it are provided byIBM under terms of the IBM Customer Agreement, IBM International Program License Agreement, or anyequivalent agreement between us. If you are viewing this information softcopy, the photograph...