manualshive.com logo in svg

IBM @server iSeries, User Manual, Page: 93 / 294

Share
Download
IBM @server iSeries User Manual Download Page 93

Converting date for output to a display or printer in Query for iSeries

You can use the date functions to convert an input date field to a different format. Examples 1 and 2 show
two methods of converting a date field from the MMDDYY format to the YYDDD format. Examples 3 and 4
show the reverse conversions, from YYDDD to MMDDYY format.

Note:

For ease of reading, multiple panel views are merged into single screen images.

Example 1—Converting from MMDDYY to YYDDD format in Query for iSeries

The following Define Result Fields panel defines an MMDDYY to YYDDD date conversion. The conversion
is done completely within the panel.

Define Result Fields

Type definitions using field names or constants and operators, press Enter.

Operators: +, -, *, /, SUBSTR, ||, DATE...

Field

Expression

Column Heading

Len Dec

YY________ SUBSTR(CHAR(MMDDYY),7,2)_________ ____________________ _____ __

_________________________________ ____________________

_________________________________ ____________________

CHARJAN01_ ’01/01/’ || YY___________________ ____________________ _____ __

_________________________________ ____________________

_________________________________ ____________________

JAN01_____ DATE(CHARJAN01)__________________ ____________________ _____ __

_________________________________ ____________________

_________________________________ ____________________

DDD_______ SUBSTR(DIGITS(DAYS(MMDDYY)-DAYS(J ____________________ _____ __

AN01)+1),10,3)___________________ ____________________

_________________________________ ____________________

YYDDD_____ YY_||_’/’_||_DDD_________________ ____________________ _____ __

_________________________________ ____________________

_________________________________ ____________________

_______________________________________________________________________________

Field

Text

Len Dec

MMDDYY

Date field in MMDDYY format

8

L

Bottom

F3=Exit

F5=Report

F9=Insert

F11=Display names only

F12=Cancel

F13=Layout

F20=Reorganize

F24=More keys

Example 2—Converting from MMDDYY to YYDDD format in Query for iSeries

This examples shows an alternative method for the same conversion done in example 1. The conversion
is done completely within the panel. This method requires that the date format for the current job must be
set to YYDDD. When this circumstance exists, the DATE function automatically does the conversion.

Chapter 5. Defining result fields in Query for iSeries

81

Summary of Contents for @server iSeries

Page 1: ...iSeries Query for iSeries Use Version 5 SC41 5210 04 ERserver ...

Page 2: ......

Page 3: ...iSeries Query for iSeries Use Version 5 SC41 5210 04 ERserver ...

Page 4: ... 261 Fifth Edition September 2002 This edition applies only to reduced instruction set computer RISC systems Copyright International Business Machines Corporation 2000 2002 All rights reserved US Government Users Restricted Rights Use duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp ...

Page 5: ...iSeries 17 Query for iSeries profile information 18 Handling Query for iSeries messages and errors 18 Changing your Query for iSeries queries 18 Part 2 Defining and using Query for iSeries query definitions 21 Chapter 3 Creating a Query for iSeries query definition 27 Starting Query for iSeries query definition 27 Selecting definition steps when defining a Query for iSeries query 28 Selecting opti...

Page 6: ...for iSeries 69 Date time and timestamp arithmetic operations in Query for iSeries 69 Date arithmetic operation in Query for iSeries 70 Time arithmetic operation in Query for iSeries 73 Durations in Query for iSeries 73 Date time and timestamp functions in Query for iSeries 74 CHAR Query for iSeries function 74 DATE Query for iSeries function 75 DAY Query for iSeries function 75 DAYS Query for iSer...

Page 7: ... column formatting in Query for iSeries 119 Formatting the columns of the Query for iSeriesquery report 119 Column spacing in Query for iSeries reports 119 Column headings in Query for iSeries reports 119 Length and decimal positions in Query for iSeries reports 120 Omitting fields from a Query for iSeries report 121 Editing numeric fields in Query for iSeries reports 122 Defining numeric field ed...

Page 8: ...ery for iSeries query definitions 173 Changing a Query for iSeries query definition 173 Starting changes by copying a Query for iSeries query definition 173 Changes you can make to a Query for iSeries query 173 Considerations for changing Query for iSeries queries 176 Copying a Query for iSeries query definition 177 Renaming a Query for iSeries query definition 178 Deleting a Query for iSeries que...

Page 9: ...ry for iSeries 235 Select and sequence fields in Query for iSeries 235 Select records in Query for iSeries 235 Select sort fields in Query for iSeries 236 Select collating sequence in Query for iSeries 237 Specify report summary functions in Query for iSeries 237 Select output type and output form in Query for iSeries 237 Specify processing options in Query for iSeries 238 Using join operations in...

Page 10: ...mn headings in Query for iSeries 253 CCSID and sort fields in Query for iSeries 253 CCSID and record selection tests in Query for iSeries 253 CCSID and summary functions in Query for iSeries 253 CCSID and column formatting and editing in Query for iSeries 253 CCSID and report breaks in Query for iSeries 253 CCSID and break and final text in Query for iSeries 253 CCSID and cover page page headings ...

Page 11: ...fer to other IBM books for more specific information about a particular topic For a list of publications related to this book see the Bibliography Who should read the Query for iSeries Use book This book is intended for people creating query reports and managing data on the iSeries system Before you use this book you must be familiar with the introductory material for using the iSeries system You ...

Page 12: ...x Query for iSeries Use V5R2 ...

Page 13: ... for iSeries display 9 Using lists in Query for iSeries 9 Displaying a Query for iSeries list 9 Selecting items in a Query for iSeries list 10 Special library names in Query for iSeries 10 Using Query for iSeries commands 11 Using the Query Utilities menu 11 Using the Work with Queries display 12 Choosing a single Query for iSeries task 12 Specifying a Query for iSeries query and its library 13 Ch...

Page 14: ...2 Query for iSeries Use V5R2 ...

Page 15: ...be done using Query Query for iSeries fundamentals Several elements on your system organize and store information or data so that you and other system users can work with it to get the results that you need The following topics introduce those elements tell you about them and how they relate to you and Query and direct you to other publications where you can find more information Files fields and ...

Page 16: ...e Query for iSeries treats UCS2 graphic data the same as GRAPHIC or VARGRAPHIC data A UCS2 graphic field is a DBCS graphic field tagged with a UCS2 CCSID The VARCHAR and VARGRAPHIC functions help you write queries that include UCS2 data Data definition languages and utilities support in Query for iSeries Query can query data in files that are created using different data definition languages or pr...

Page 17: ...d objects and allows the user to find the objects by name Physical file Table A set of columns and rows Record Row The horizontal part of a table containing a serial collection of columns Field Column The vertical part of a table of one data type Logical file View A subset of columns and rows of one or more tables Query for iSeries definitions You use a query to get information from database files...

Page 18: ...Figure 3 Major Tasks on the Query Menu and the Work with Queries Display 6 Query for iSeries Use V5R2 ...

Page 19: ...n v Create change copy delete or display a query definition When you are finished working with Query for iSeries When you are finished working with Query you can exit from the Work with Queries display by pressing either F3 Exit or F12 Cancel Telling Query for iSeries what you want When you work with a query Query shows you a series of displays that ask or prompt you for v What information you wan...

Page 20: ...that Query ignore any numeric field errors see Ignoring decimal data errors during Query for iSeries processing on page 164 for more information Asterisks are shown if the field length and or number of decimal positions was changed on the Specify Report Column Formatting display to a size too small for the value to be shown When the query is run if Query finds conversion errors either in the date ...

Page 21: ... display is printed If your report or layout is wider than the display and you have not specified that line wrapping be used the far right side of the report is cut off and is not printed In this case you could use the shift function keys to move text lines to the right and then press the Print key again to obtain a printout of the remainder or possibly just more of the report Using lists in Query...

Page 22: ...sociated libraries for query definitions files or translation tables Special Library Name Description CURLIB The current library being used for your job It is the only library searched If no current library is found QGPL is searched LIBL All the libraries in the library list for your job They are searched in the order they are listed USRLIBL All the user libraries that are in the user part of the ...

Page 23: ...te a query SQL 400 10 Start SQL 400 Query Manager Query management 20 Work with query management forms 21 Work with query management queries 22 Start a query 23 Analyze a Query for iSeries definition More Selection or command F3 EXIT F4 PROMPT F9 RETRIEVE F12 CANCEL F13 USER SUPPORT F16 OS 400 MAIN MENU C COPYRIGHT IBM CORP 1980 1991 If you select option 1 Work with queries and press the Enter key...

Page 24: ...v Run a query to select data from files and produce a report using that data Part 2 of this book describes these primary Query tasks Creating query definitions is described in Chapters 3 through 14 running queries is described in Chapter 15 and the remaining tasks changing copying displaying printing and deleting query definitions are described in Chapter 16 Choosing a single Query for iSeries tas...

Page 25: ...e cursor on the Query prompt and press F4 Prompt to show a list the list contains the names of all the queries that are in the library or libraries indicated by the Library prompt Only the queries and libraries for which you have the necessary authority are shown The following display shows some sample query names You can type any option number except a 1 beside the name of each query that you wan...

Page 26: ...ame you can type that query name in the Subset prompt and type ALL in the Library prompt before you press the Enter key You are shown all the queries by that name for which you have the authority to use v To position the list to a specific name type the name or the starting characters in the name in the Position to prompt and press the Enter key This function can also reduce the time needed to loc...

Page 27: ...ied starting characters is now shown at the top of the list If there is no Library column present and if there is no query name in the list that starts with those characters Query moves the list to the name closest to and in front of the position that the name would have been in However if there is a Library column present and there is no query name that exactly matches the value in the Position t...

Page 28: ...library is supplied as the library name If you do not have a current library QGPL is used When you specify a different library name a special library name or a generic library name Query supplies that name the next time you use this display You can use the same value each time or you can change it to a different library or library group See Query for iSeries profile information on page 18 for more...

Page 29: ...parator you must use press F17 from either the Select Records display or the Define Result Fields display Note If you are sending queries between countries that use the comma for a decimal separator put a blank after each comma separating arguments in a function such as SUBSTR or VALUE Display Constants Format Query QRY1 Option CHANGE Library QGPL CCSID 65535 Use an SAA format to enter a date or t...

Page 30: ...isplay width 80 or 132 v The current collating sequence option v Your collating sequence and coded character set identifier CCSID v The collating sequence table and library name Handling Query for iSeries messages and errors You should not worry about making errors while working with Query Query either prompts you for needed input or issues an error message You can correct your errors by respondin...

Page 31: ... you do not have to redefine the whole query If you just want to quit press F3 Exit to go to the Exit This Query display On the Exit This Query display indicate whether you want to save or run the query or do both and then press the Enter key Afterwards if you have not selected other options the Work with Queries display appears Press F3 to exit Query Chapter 2 General operating information for Qu...

Page 32: ...20 Query for iSeries Use V5R2 ...

Page 33: ...es selected on the Query for iSeries Display File Selections display 41 Joining files in a Query for iSeries query 42 Types of joins in a Query for iSeries query 43 How to join files in a Query for iSeries query 43 Rules for joining files in a Query for iSeries query 44 Examples of joining files in a Query for iSeries query 46 Example Selecting matched records from all selected files in a Query fo...

Page 34: ...TAMP Query for iSeries function 79 YEAR Query for iSeries function 80 Additional date time and timestamp functions in Query for iSeries 80 Converting date formats in Query for iSeries 80 Converting date for output to a database file in Query for iSeries 80 Converting date for output to a display or printer in Query for iSeries 81 Example 1 Converting from MMDDYY to YYDDD format in Query for iSerie...

Page 35: ...Query for iSeries 113 Using the language collating sequence for your country in Query for iSeries 114 Defining your own collating sequence in Query for iSeries 115 Selecting a translation table in Query for iSeries 116 Selecting a system sort sequence in Query for iSeries 117 Chapter 10 Specifying report column formatting in Query for iSeries 119 Formatting the columns of the Query for iSeriesquer...

Page 36: ...eries reports 150 End line in Query for iSeries reports 150 Line spacing in Query for iSeries reports 150 Print definition in Query for iSeries reports 150 Specifying spooled output overrides in Query for iSeries reports 150 Defining the printout cover page of Query for iSeries reports 152 Defining the page headings and footings in Query for iSeries reports 153 Defining output of Query for iSeries...

Page 37: ...Query for iSeries query definition 173 Changes you can make to a Query for iSeries query 173 Considerations for changing Query for iSeries queries 176 Changing your collating sequence on Query for iSeries queries 176 Copying a Query for iSeries query definition 177 Renaming a Query for iSeries query definition 178 Deleting a Query for iSeries query definition 178 Displaying a Query for iSeries que...

Page 38: ...26 Query for iSeries Use V5R2 ...

Page 39: ...ook at a list of query names or library names to select the query name and the library name you want to use For example you might specify CUSNAMQRY as the name of a query definition that you would use to query the CUSTNAME file If you do not specify a library name the query is stored in the library identified in the Library prompt the QGPL library in this example Work with Queries Type choices pre...

Page 40: ...ded when you need to use most of them Each step you select is a separate process that shows you one or more displays as you need them Define the Query Query Option Create Library QGPL CCSID 37 Type options press Enter Press F21 to select all 1 Select Opt Query Definition Option 1 Specify file selections _ Define result fields _ Select and sequence fields _ Select records _ Select sort fields _ Sel...

Page 41: ...ption to specify what fields to sort on so that your output records appear in a particular order such as alphabetically or in descending or ascending order Select collating sequence Use this option to select a collating sequence for your query The collating sequence you select can affect many different things in your query including record selection and the order of records when they are sorted Th...

Page 42: ...or run the query at this time When you press F3 the Exit this Query display is shown so you can specify both choices If you want to look at or change more definition steps first you can type a 1 next to each option as before and press the Enter key If there are any definition errors in the options you used they are highlighted in reverse image when you return to this display you should correct the...

Page 43: ...g fields 3 Select and sequence fields and specify sort fields before you reformat columns 4 Select sort fields before you define report breaks 5 Press F5 to view your query results and F13 to view your report layout before and after you reformat columns You can use a query to quickly sort records in a data file Simply select the sort fields choose database as the output device and specify the outp...

Page 44: ...32 Query for iSeries Use V5R2 ...

Page 45: ...ry attempts to keep whatever parts of the definition that are still valid For example if a field in a file being removed from the definition also exists in a file being added as a replacement the field s uses in other parts of the definition such as part of the sort definition are kept However it is your responsibility to determine whether the field in the replacement file contains the kind of inf...

Page 46: ...the needed information The following special values are shown in the prompts for each file selection group when it is first shown FIRST for members and record formats and ID for file IDs if shown These and others that you may specify like LIBL for libraries are changed when you press the Enter key they are changed to the actual values that will be used when the query is run Special values for memb...

Page 47: ...y If you want to include additional files for your query press F9 Add file each time you want another file selection If you have completed the previous file selections for this query a new group of prompts is shown for you to fill in the File prompt is blank and the other prompts show default values that you can change However if you have not filled in a file name for one of the file selections F9...

Page 48: ...Query keeps any definition values already assigned for field selection sort field selection and so on You must change the ID if used for join tests result field expressions record selection values and break text However if you move a file ID to a different selection group all the definition values you specified are lost even if all of the names in the from and to file selections are the same Confi...

Page 49: ... TESTFILE_A _ TESTFILE_B F4 Prompt F11 Display text F12 Cancel F24 More keys Bottom At the top of the display a field named File ID may or may not appear The File ID field is shown only if you came from the Specify File Selections display and pressed F4 Prompt in a nonblank File prompt If the file ID is shown it is the file ID of the file selection group that the cursor was positioned to on the pr...

Page 50: ... If you typed in a generic library name or special library name a Library column appears on the display so that you are able to determine in which library a particular file resides Select File Library CUST Name LIBL F4 for list Subset __________ Name generic Position to __________ Starting character s Type option and File and Library press Enter 1 Select Opt File Library Opt File Library _ _______...

Page 51: ... those previously selected for this query Note that if join logical files are used you are only able to select less than 32 files because each file used in the join logical file is counted as one of the 32 files For example if three physical files were joined into one join logical file this join logical file is counted as three files not one After you have made your file selections press the Enter...

Page 52: ...y a member name in the top position that member must also exist at this time If you specify FIRST or LAST the member name on the previous display is changed to that value Then either the first or the last member that exists in the file at the time the query is run is the member that is used If you came from the Define Database File Output display you can also specify FILE in the first position in ...

Page 53: ...ible that some record formats cannot be used with certain file members but this is not determined until the Specify File Selections display is processed You can use F11 to alternate between showing a list of only record format names to showing a list of both record format names and text describing the record formats When you press the Enter key you return to the Specify File Selections display and...

Page 54: ...specify the type of join you want to use There are three ways that you can join the files for selecting the records you want v The Specify How to Join Files display allows you to indicate the fields to be used to join the files You specify which fields are to be used and how they are to be compared with fields in the other files For a record to be selected the join specifications for all the files...

Page 55: ... tests specified on the Specify How to Join Files display They also all use the other specifications in the query definition the same way to select records and produce the output Use the second join type type 2 if you want to use every record in the primary file whether or not it has a matching record in the secondary file s Use the third join type type 3 if you want to see which records in the pr...

Page 56: ...E so the file identifiers A and B are included with the field names Rules for joining files in a Query for iSeries query Note To indicate that you do not want to specify join tests use the ALL join All records from one file are joined to all records in the secondary file or files without any kind of selection The rules for join tests are v For two files to be selectively joined they must have at l...

Page 57: ...rmat you may get unexpected results When you run a query that uses a non SAA format for date time or timestamp values in character fields and query has no setting for a non SAA literal date value use the CHGJOB command to make sure your job date format and separator match the format and separator in the character field values If some of the fields contain bracketed double byte characters you can u...

Page 58: ...ou can type ALL in the first four spaces of the left field but only in the first field name position rather than specifying any join test If ALL is used each record in the first file is joined to every record in the other files For example if a file of 2000 records is joined to a file of 3000 records using ALL the result is a joined file of 6000000 records Using ALL can significantly degrade the p...

Page 59: ...a match with at least one record in each and every one of the other selected files That is for a record to be selected all the files must have a matching record as determined by the join specification s A match occurs when for each file used in the query the contents of its fields selected for comparison match the test condition s given on the Specify How to Join Files display This type option 1 h...

Page 60: ... fields If these fields are included as output fields in the query report the substituted characters or values are used in the report In the example the PHONE field shows blanks because it was coded as a character field In the case where the fields are null capable the specified default values are used as data for that secondary file s selected fields If a default value is not specified a null val...

Page 61: ...low the secondary file sequence rule Use join tests to connect each secondary file to a file listed above it on the Specify File Selections display For instance when joining four files use a join test to connect the second file to the first and use another test to join the third file to the first or second file The fourth file can be connected to any of the other files Example Sequencing secondary...

Page 62: ...y is join type 2 primary matched every record in the primary file A is included in the join If a record in file A such as Martinez has no match in B Query joins it to a default record for file B which has blank values for fields The result of step 1 in our example is a file called AB Note that AB is a working file used to build the joined file that you want in your report You cannot see file AB St...

Page 63: ...le B which is in the opposite order from the correct method Note that each file has the same file ID as before Specify the join type and join tests the same as before This method does not follow the secondary file sequence rule because file C is not connected by a join test to the file A listed above it Chapter 4 Specifying and selecting files for a Query for iSeries query 51 ...

Page 64: ... two files A and C Query joins the files in the order listed starting with file A CUSTOMER and file C ITEM But there is no join test that compares a field in A to a field in C Query joins every record in file A one record at a time to 52 Query for iSeries Use V5R2 ...

Page 65: ...o specify files is CUSTOMER PURCHASE and ITEM with PURCHASE in the middle because it is the connection between the CUSTOMER and ITEM files This logical order is also the correct order Displaying all join tests in a Query for iSeries query When you press the Enter key on the Display File Selections display see Displaying all files selected on the Query for iSeries Display File Selections display on...

Page 66: ...r to continue recovery of query definitions F12 Cancel This display is shown at least once for each file selection that has missing fields Once you are aware that fields are missing you may want to press F12 Cancel to bypass seeing any additional displays of missing fields Or if you want to see which of the other file selections may have missing fields use the Enter key to see each display one aft...

Page 67: ... reverse image You must select each of these definition steps such as Define result fields and Select records and correct the problems caused by the missing fields On the definition displays for the affected steps the expressions or selection tests that use fields that are now missing are highlighted Handling missing fields when starting to change or display a Query for iSeries query When you star...

Page 68: ...se fields that are now missing are highlighted v If you press F12 Cancel you return to the Work with Queries display without affecting your previous file and field selections You can press the Enter key to go again assuming you were there once to the Change File Selections display and select another file Or you can press F3 Exit to stop working with queries leave Query and then work with the files...

Page 69: ...ate step to obtain a final result For example you might define a result field only for the purpose of selecting records and you do not want the result field to appear on the report Creating results fields in Query for iSeries The Define Result Fields display appears if you typed a 1 next to the Define result fields option on the Define the Query display You use the Define Result Fields display to ...

Page 70: ...habetic character A through Z or and use no more than 9 alphanumeric characters A through Z 0 through 9 or _ for the remaining characters Do not use blanks within the name Note Use A Z or 1 9 if this query is sent to other systems or used in a multilingual environment v Do not use any names from the list on the lower part of the display or the names of other result fields Enter an expression that ...

Page 71: ...usly defined numeric result fields For example you can multiply two numeric fields and put the total in a result field named AMOUNT AMOUNT QUANTITY PRICE v Numeric constants any numbers such as 4 or 12 5 The rules for using numeric constants are The total length can be no more than 31 digits For a single precision floating point constant the total length can be no more than nine digits For a doubl...

Page 72: ...as ABCdef123 or This text includes blanks and special characters The rules for using character constants are The character constant must be enclosed in apostrophes Any combination of letters numbers or special characters for example or can be used If two apostrophes are used within a character string the two apostrophes become a single apostrophe in the output For example October s Profits becomes...

Page 73: ...ctions Character expressions can include the character functions SUBSTR DIGITS and VALUE SUBSTR Query for iSeries function The SUBSTR function returns part of a character value The form is QQ SUBSTR value offset length QR v Value is the name of a character field including result fields that are already defined a character expression or a character constant It can be an SBCS or DBCS field an SBCS c...

Page 74: ...sult of the function is a fixed length character string The CCSID of the string is the default SBCS CCSID at the application server If the argument can be null the result can be null If the argument is null the result is a null value The result is a string of digits that represents the absolute value of the argument without regard to its scale The result does not include a sign or a decimal point ...

Page 75: ...s a mixed CCSID v CCSID of a file field has precedence over result fields or constants v CCSID of result fields has precedence over constants v CCSID assigned to the result will be the CCSID of the first of the two values if both concatenated values are fields from the file or both are result fields v CCSID 65535 is assigned to a DBCS graphic constant if the job CCSID is a single byte CCSID with n...

Page 76: ...n the CCSID of the result is the job CCSID If the job CCSID is mixed then the result type is mixed If the job CCSID is SBCS then the result is SBCS character If the ccsid is specified v The result is dependent on the specified CCSID If ccsid is a SBCS CCSID then the result type is SBCS character If ccsid is a mixed CCSID then the result type is mixed character v The CCSID of the result is the ccsi...

Page 77: ...ing scheme v If the string expression is data in a native encoding scheme S is that string expression If the third argument is specified the CCSID of the result is the third argument It must be a DBCS or UCS2 CCSID The CCSID cannot be 65535 If the third argument is not specified the CCSID of the result is determined by a mixed CCSID let M denote that mixed CCSID M is determined as follows v If the...

Page 78: ... the argument Date time and timestamp expressions in Query for iSeries Date time and timestamp data types and values can be assigned to result fields A date time or timestamp expression for a result field can contain the following alone or in combination v Date time or timestamp field names names of fields that contain date time or timestamp values v Character constants SBCS or DBCS characters enc...

Page 79: ...bout the date value use the Systems Application Architecture SAA date formats ISO USA EUR or JIS when specifying date constants in a multilingual environment or when a query is to be displayed or changed by different people If the OS 400 two digit year format is used the range of dates is from 1940 through 2039 Any year from 40 through 99 is assumed to have a century of 19 Any year from 00 through...

Page 80: ...00 through 00 59 00 01 00 am through 11 59 am 01 00 00 through 11 59 00 12 00 pm noon through 11 59 pm 12 00 00 through 23 59 00 12 00 am midnight 24 00 00 00 00 am midnight 00 00 00 The system always uses 00 00 00 Only the user can enter 24 00 00 Query for iSeries timestamp A timestamp expression is a seven part value year month day hour minute second and microsecond that designates a date and ti...

Page 81: ...mand and your job format for the date or time is different from an OS 400 date or time format constant specified in the query definition v Use the run time record selection option with the QRYRUN procedure and your job format for the date or time is different from an OS 400 date or time format constant specified in the query definition v Press F17 from the Select Records display v Press F17 from t...

Page 82: ...tamp Character representation of a timestamp Duration v If the second operand is a timestamp the first operand must either be a Timestamp Character representation of a timestamp Date arithmetic operation in Query for iSeries Dates can be subtracted added to incremented or subtracted from decremented Subtracting dates in Query for iSeries The result of subtracting one date DATE2 from another DATE1 ...

Page 83: ...ven date and the same number of months is subtracted from the result the final date is not necessarily the same as the original date Converting a numeric field to a date field in Query for iSeries The following is a way to convert a numeric field that contains a date to a date data type field In this example the job date format is YMD and the job date separator is A file contains a numeric date fi...

Page 84: ...100 2 0 YYMMDD yy 10000 mm 100 dd 6 0 E N D O F Q U E R Y P R I N T MM DD YY YYMMDD MMDDYY 08 31 90 900831 083190 E N D O F R E P O R T Example 2 Working with numeric dates in Query for iSeries This example shows the reverse in formatting converting a numeric YYMMDD to MMDDYY The marked lines are necessary and a sample report follows Example 2 Query YYMMDD01 Library CRP Query text YYMMDD to MMDDYY...

Page 85: ...f time There are four types labeled date time and timestamp Labeled duration in Query for iSeries A labeled duration represents a specific unit of time expressed as a number followed by one of the following duration keywords YEAR YEARS MONTH MONTHS DAY DAYS HOUR HOURS MINUTE MINUTES SECOND SECONDS MICROSECOND MICROSECONDS A labeled duration can only be used as an operand if the other operand is of...

Page 86: ...rgument can be null the result can be null If the first argument is null the result is null Other rules depend on the data type of the first argument as follows v If the first argument is a date A format should be specified especially if The query is to be used by different users The query is to be sent to a different machine A date has a two digit year format attribute and the values may not be i...

Page 87: ... If the argument is a character value the result is the date represented by the character string Note If the OS 400 two digit year format is used the range of dates is 1940 through 2039 Any year from 40 through 99 is assumed to have a century of 19 Any year 00 through 39 is assumed to have a century of 20 If a value outside of that range is in a field with a two digit year format it will be shown ...

Page 88: ... occur if the DATE function were applied to the argument Example RESULT DAY DAYS CURRDATE DAYS HIREDATE RESULT DAY equals a number representing the number of days between these two dates HOUR Query for iSeries function The HOUR function returns the hour part of a value The form is QQ HOUR expression QR The argument must be either a v Time v Timestamp v Time duration v Timestamp duration The result...

Page 89: ...y field with a value between 999999 and 999999 Example MICROSECOND TIMESTAMP where TIMESTAMP 1991 10 22 12 15 23 123456 The resulting value of MICROSECOND equals 123456 MINUTE Query for iSeries function The MINUTE function returns the minute part of a value The form is QQ MINUTE expression QR The argument must be either a v Time v Timestamp v Time duration v Timestamp duration The result of the fu...

Page 90: ...MONTH equals 10 SECOND Query for iSeries function The SECOND function returns the seconds part of a value The form is QQ SECOND expression QR The argument must be either a v Time v Timestamp v Time duration v Timestamp duration The result of the function is a binary field If the argument can be null the result can be null If the argument is null the result is null If the argument is a time or time...

Page 91: ...d character representation of a timestamp Character string of length 14 Note A character string of length 14 must be a string of digits that represents a valid date and time in the form yyyyMMddhhmmss where yyyy is the year MM is the month dd is the day hh is the hour mm is the minute and ss is the seconds v If both arguments are specified the first argument must be a date or a valid character rep...

Page 92: ...T function used with DATE TIME TIMESTAMP or TIMEZONE returns the current system value corresponding to the function used The TIMEZONE function is used to allow an easy conversion to Greenwich Mean Time GMT by subtracting CURRENT TIMEZONE from a local time value Note If these functions are used more than once within a Query definition all values are based on a single clock reading If your job date ...

Page 93: ..._____ _________________________________ ____________________ JAN01_____ DATE CHARJAN01 __________________ ____________________ _____ __ _________________________________ ____________________ _________________________________ ____________________ DDD_______ SUBSTR DIGITS DAYS MMDDYY DAYS J ____________________ _____ __ AN01 1 10 3 ___________________ ____________________ ___________________________...

Page 94: ...perators press Enter Operators SUBSTR DATE Field Expression Column Heading Len Dec YY________ SUBSTR DIGITS YEAR YYDDD 9 2 __ ____________________ _____ __ _________________________________ ____________________ _________________________________ ____________________ MM________ SUBSTR DIGITS MONTH YYDDD 9 2 _ ____________________ _____ __ _________________________________ ____________________ ______...

Page 95: ...sent dates are evaluated using the date format stored in the query definition v Your expression can have a single value or it can use as many operands and operators needed to fit in the three lines provided on the Define Result Fields display v Do not use a result field in select records tests or as a sort field if it may have a division by zero or overflow result v You can avoid dividing by zero ...

Page 96: ...his use the CHAR function around the date time or timestamp field and the DIGITS function for numeric fields Query determines the data type of the result field according to the following If the first value in the expression is a numeric field name a numeric constant like the number 7 or 3 14 or a function that returns a numeric value then the result field is numeric If the SUBSTR function is used ...

Page 97: ...imal is the number of decimal positions to the right of the decimal point If you want to type your own value for the length to prevent unexpected results you should first read Length and decimal positions in Query for iSeries reports on page 120 and Result field length and decimal positions in Query for iSeries on page 188 Also observe the following v If you specify a value for Len you must also s...

Page 98: ...s Adding or removing result fields in Query for iSeries Adding a result field to your query definition requires positioning the cursor to the top half of the display and using F9 to insert a blank set of result field definition lines After you have entered the information necessary to define your new result field or fields they are inserted after the set where the cursor was positioned when you pr...

Page 99: ...he Define the Query display You can press F12 Cancel to return you to the previous display if you have changed your mind and now want Query to select and sequence fields for you Anything you typed on the display is ignored Selecting fields and specifying their sequence in Query for iSeries The Select and Sequence Fields display is shown below with some sample fields from a customer master file sho...

Page 100: ...splay in the sequence specified If no fields are selected the fields chosen as sort fields if any for this query are listed first in the order they were given priority on the Select Sort Fields display See Chapter 8 Selecting sort fields in Query for iSeries 2 Any result fields that have been defined for this query but have not been selected 3 All other fields in the order they exist in the record...

Page 101: ...ADDR 80 CITY 90 STATE 100 ZIPCODE 110 TELENUMBER 120 CRLIMIT Bottom F3 Exit F5 Report F11 Display text F12 Cancel F13 Layout F20 Renumber F21 Select all F24 More keys Another way of making your selections is to first specify the same sequence number a 1 for example for all of the fields you want to select Press the Enter key and Query pulls all of those fields to the top of the list You can then s...

Page 102: ...ges to your sequence choices Query completes this step and takes you back to the Define the Query display so that you can continue with other query tasks If you previously selected more options on the Define the Query display Query shows you the first display for the next option 90 Query for iSeries Use V5R2 ...

Page 103: ...2 Cancel to return to the previous display if you have changed your mind and now want all records in your output Anything you typed on this display is ignored Selecting the records you want in Query for iSeries Usually your reports are based on records that have something in common such as a report that lists only those items in an inventory that are in short supply Selecting records by creating c...

Page 104: ...xt line A comparison has all of the following v A field to be compared v A test v One or more values to compare the field with You can specify up to a maximum of 100 comparisons Each comparison can use as many lines up to 30 characters per line as needed up to a maximum of 100 lines total for all comparisons The Select Records display provides six input lines at a time for you to enter comparisons...

Page 105: ...racter field with SBCS character DBCS either DBCS open v DBCS either field with SBCS character DBCS either DBCS open DBCS only v DBCS open field with SBCS character DBCS either DBCS open DBCS only v DBCS only field with DBCS either DBCS open DBCS only v DBCS graphic field with DBCS graphic v UCS2 graphic field with UCS2 graphic v Numeric field with a numeric field v Date field with SBCS character ...

Page 106: ...r of decimal positions Character constants as values in Query for iSeries A character constant is simply characters enclosed in apostrophes for example xxx The apostrophes are important because Query assumes that any group of characters not enclosed by apostrophes is a field name In DBCS character constants shift out and shift in characters must surround DBCS data within the apostrophes DBCS graph...

Page 107: ...ld is less than the value GE Greater than or equal Use this test to determine if the contents of the field is greater than or equal to the value LE Less than or equal Use this test to determine if the contents of the field is less than or equal to the value RANGE Range Use this test to determine if the contents of the field is within a range that you have specified for the value LIST List Use this...

Page 108: ...does not matter to Query if the field called NAME is defined to be longer than five characters Query looks for all records that do not exactly match SMITH SMITHSON Smith and NEISMITH would all be selected as names that are not equal to SMITH Testing for IS Null IS and ISNOT Null ISNOT in Query for iSeries You use the IS and ISNOT tests to determine if the contents of any field is or is not null An...

Page 109: ...If you do not select a different one the hexadecimal collating sequence is used and v Most special characters are less than letters v Lowercase letters are less than uppercase v Letters are less than numbers For example the characters A 8 and b would sort from low to high as follows b A 8 For more information about selecting or defining a collating sequence see Chapter 9 Selecting a collating sequ...

Page 110: ...cify OR to start each new group Tests EQ NE LE GE LT GT RANGE LIST NLIST LIKE NLIKE IS ISNOT AND OR Field Test Value Field Number or Characters ITEMNO LIST 04567 00976 85432 ____ ______________ _____ ______________________________ ____ ______________ _____ ______________________________ Example 2 Testing for values in a list LIST NLIST in Query for iSeries The following tests the field LASTNAME fo...

Page 111: ...sition of the field you could specify A But note that if an 8 character field contained ANNA the LIKE test would fail because the last position in the field is a blank If you are testing for an E followed by an A two positions after it and the field length is at least 3 you could specify E_A Note that if an 8 character field contained any of the following the LIKE test would be true ANNE A EVAN E ...

Page 112: ...ition Examples In the following examples represents the DBCS percent sign __ represents the DBCS underscore s o represents the shift out character s i represents the shift in character and a pair of the same SBCS characters such as DD represent a single DBCS character Example 1 DBCSFLD1 LIKE s o__DD__ HH s i Example 1 selects only records in which the second character in field DBCSFLD1 is DD the f...

Page 113: ...____ ______________ _____ ______________________________ ____ ______________ _____ ______________________________ ____ ______________ _____ ______________________________ ____ ______________ _____ ______________________________ Bottom ________________________________________________________________________ ACCTNUMBER STREETADDR CRLIMIT YEAR COMPANY CITY BALDUE MONTH FIRSTNAME STATE PASTDUE MIDDLEI...

Page 114: ...onnections can be confusing you might want to use F5 to look at your report and make sure your comparisons work the way you expect If a combination of AND and OR connections seems particularly complex you should probably look at the comparisons closely to see if you can use fewer connections to make the same record selection For example following are two ways of selecting records for customers fro...

Page 115: ...o add another comparison between two comparisons that you have already typed you can press F9 to insert a blank line where you need it so that you do not have to retype everything The new line is added after the line on which the cursor is positioned when you press F9 You can also press F9 instead of the Page Down key to add lines as you type in comparisons If you change your mind about a comparis...

Page 116: ...104 Query for iSeries Use V5R2 ...

Page 117: ... sort fields option on the Define the Query display the Select Sort Fields display is not shown while you work with your query definition and the data is not sorted when you run the query If you typed a 1 next to the Select sort fields option on the Define the Query display the Select Sort Fields display is shown during query definition Press F12 Cancel to return to the previous display if you hav...

Page 118: ...or the selected files Fields from the first file are listed first followed by those in the second file and so on Query then displays a message Press Enter to confirm so that you have a second chance to review your sort fields and change them if you want To remove a sort field from your selections blank out the number in front of that field name It is not necessary to renumber the other fields To c...

Page 119: ...ount 7 2 Bottom F3 Exit F5 Report F11 Display names only F12 Cancel F13 Layout F18 Files F20 Renumber F24 More keys Notice that a D has been typed in the A D column for the CRLIMIT field The STATE LASTNAME and INIT fields will all be sorted in ascending order which is the order you want Also notice that this display shows additional information including the text length and decimal positions for e...

Page 120: ...ttawa MN 56342 5000 00 938472 EJ Henning 1 Oil Lane Oiltown TX 75217 5000 00 029384 MA Brown 904 38th St New York NY 12201 1000 00 397267 OS Tyron 1039 20th Ave Falls NY 14841 1000 00 475938 MA Doe P O Box 90834 Reading CA 95685 700 00 930484 BJ Hubbard 10 Colusa Junction CA 91722 700 00 192837 CE Lee 98 Elm St Falls NY 14841 700 00 392859 LL Vine 18940 Main St Tombstone VT 05046 700 00 389572 RS ...

Page 121: ...e collating sequence is not applied to DBCS data in DBCS character fields UCS2 graphic data is sorted by the hexidecimal representation of the data The collating sequence is not applied to UCS2 graphic data in UCS2 graphic fields Date time and timestamp fields are sorted in chronological order For all data types null values are sorted last if the sort is in ascending order and are represented by a...

Page 122: ...110 Query for iSeries Use V5R2 ...

Page 123: ...nce provided on most systems is the only one needed If you select the Select collating sequence option on the Define the Query display when you are creating or changing a query and save your collating sequence option as a default in your query profile all your queries can use this same collating sequence option without you having to specify it in each query definition This does not mean that you m...

Page 124: ...a comparisons A CCSID is saved with a collating sequence so that the collating sequence can be converted for use with data in a different code page Conversion of a collating sequence is a matter of rearranging the numbers in the table so that the appropriate collating weight is found for each character How a collating sequence affects Query for iSeries Several definition steps in Query for iSeries...

Page 125: ...4 Use translation table Table __________ Name F4 for list Library __________ Name LIBL F4 for list F3 Exit F4 Prompt F5 Report F10 Process previous F12 Cancel F13 Layout F17 Job sequence F24 More keys Using the hexadecimal collating sequence in Query for iSeries The standard collating sequence is the hexadecimal collating sequence For any given CCSID all of the character set characters alphanumeri...

Page 126: ...quence display appears Press F14 hexadecimal sequence Query for iSeries shows you the characters and their sequence numbers in the hexadecimal collating sequence Each character with a hexadecimal value in the range 40 X to FE X is listed You can use the page keys to page through the list of characters Press F11 to show the hexadecimal value of each character under the Hex column Use F12 to return ...

Page 127: ...collating sequence type a 3 in the Collating sequence option prompt on the Select Collating Sequence display and press the Enter key Select Collating Sequence The selected collating sequence will be used for character fields when sorting selecting records joining files finding minimum and maximum values and determining when a control break has occurred Type choices press Enter Collating sequence o...

Page 128: ...eside those characters whose sequence you want to change Each character must have a sequence number You can specify the same sequence number for two or more characters these characters are treated as equal when operations are performed on them After you have renumbered the characters in the sequence you want press the Enter key The list of characters is shown in the sequence you specified Make sur...

Page 129: ...t sequences for each language v One with unique weights for each character in the table v One with shared weights for various characters Note The system provides the system sort sequences as translation tables in library QSYS You can also select them by name using option 4 Translation table on the Select Collating Sequence display The system sort sequences are different than the Query for iSeries ...

Page 130: ...as a unique weight Shared means that some characters share the same weight For example in the shared sequence for English uppercase and lowercase pairs of letters share the same weight Note If a translation table or sort sequence you selected via option 4 or 5 changes the changed version becomes the one used for your query the next time you change or display the query The changed table is also use...

Page 131: ...o a database file Summary only output is described in Chapter 13 Selecting output type and output form in Query for iSeries reports Column spacing in Query for iSeries reports If you want to change the column spacing for a field type a number from 0 through 99 to indicate the number of spaces you want to appear to the left of that field column Unless you change the column spacing for fields Query ...

Page 132: ...e Len column on the Define Result Fields display Length has a different meaning for each data type v For SBCS character fields length is the total number of characters in the field v For DBCS only DBCS open or DBCS either fields length is the total number of bytes in the field including shift out and shift in characters v For DBCS graphic fields length is the total number of DBCS characters in the...

Page 133: ... means DBCS graphic v L means Date v T means Time v Z means Timestamp Changing the Dec value affects only how the field appears in your query output The actual value is not changed in the field definition If you want to change the actual number of decimal positions for a result field you should make the changes on the Define Result Fields display rather than at this time For numeric fields you can...

Page 134: ...eld and press F16 Edit to go to the Define Numeric Field Editing display Depending on the choice you make on this display Query will present the appropriate display to allow you to describe the remainder of your edit definition When no numeric editing override values are saved for a field in your query definition the editing actually used for the field comes first from the original field definitio...

Page 135: ...ng override values are already defined in this query for the field If an asterisk was shown there then the result of the current editing in effect for the field is shown in the Sample field on this display and the type of editing is indicated by the edit option currently specified in the Edit option prompt If no asterisk was shown there then the sample field is edited here according to the editing...

Page 136: ...F13 Layout F16 Remove edit F18 Files This display allows you to specify the editing characteristics that you want used for a numeric field The editing determines how the values for the field are to appear in your query report if the report is run when the edit option for the field is 1 You can select the characters to be used for the decimal point thousands separator a negative sign s and a curren...

Page 137: ...rom 1 to 6 characters for the negative sign s You can leave one prompt blank If you do no sign shows on that side of the field and no space is taken in the report N No A negative sign is not to be shown when the value in the field is negative If the next two prompts contain negative sign definitions they are saved in this query but not used Left negative sign in Query for iSeries reports Type from...

Page 138: ...with the value in the field If the next two prompts contain currency symbol definitions they will be saved in this query but not used Left currency symbol in Query for iSeries reports Type from 1 to 6 characters that you want used as the currency symbol to appear on the left of values in this field Any character that can be printed or displayed can be used but you should not use an asterisk zero o...

Page 139: ...e the option number of the character or symbol that you want used to replace any leading zeros in the values for this field 1 Blanks Blank spaces are to be used to replace leading zeros 27 03 and 03 2 Asterisks Asterisks are to be used to replace leading zeros 27 03 and 03 3 Floating currency symbol The currency symbol is to be used to replace leading zeros The symbol floats so that it appears to ...

Page 140: ...g display Any number less than six digits long loses one date time position on the left for each digit that it is short zero values become blanks If the leading digit on the left is zero it is replaced with a blank for example if your date time separator is a slash the value 000829 becomes 0 08 29 To show dates without having the leading zero truncated use the edit word option with a leading zero ...

Page 141: ...uery for iSeries reports Type the character that identifies the edit code or edit description that you want used to edit the values in this field You can specify any one of the following numbers or letters for edit codes 1 through 4 A through D J through Q or W through Z Or you can specify any one of the user defined edit descriptions 5 through 9 Most of the edit codes are shown in the following t...

Page 142: ...549134 X F0F5F4F9F1F3F4 Packed 7 549134 0549134 X 0549134F Binary 7 549134 0549134 X 008610E Zoned 5 2 319 34 3194N X F3F1F9F4D5 Packed 5 2 319 34 3194N X 31945D Binary 5 2 319 34 3194N X 000F8337 v Edit code Y suppresses the farthest left zero of a date field that is three to six digits long or eight digits long and it suppresses the two farthest left zeros of a date field that is seven digits lo...

Page 143: ...ing enclosed in apostrophes appears as the default for the detailed values edit word for a field that does not have an edit word specified in its definition To remove only the edit word used for this field s summary totals blank out the Edit word for summary total prompt If you want to remove both edit words defined for this field press F16 Remove edit Note that F16 removes all editing values defi...

Page 144: ...or will be specified on the Select Report Summary Functions display to get summary totals for the field the J edit code is used to edit the field if you do not define an edit word in the Edit word for summary total prompt v The edit word for summary function totals if used must have three more blank positions than the field length indicates but its length cannot exceed 31 digit positions The editi...

Page 145: ...ummary totals for this field you must type the character string that you want used as the summary total edit word for this field The character string must be enclosed in quotation marks and must have a blank position for each digit in the field plus three more than the length of the field shown at the top of the display up to a maximum of 31 positions To specify a blank space in the report result ...

Page 146: ...134 Query for iSeries Use V5R2 ...

Page 147: ...xample of how part of a report might look for a customer named Z Z Smith ITEM QUANTITY ITEMCOST ITEMTOT Bolt 12 10 1 20 Hammer 2 8 50 17 00 Ruler 1 2 00 2 00 Screw 6 05 30 Totals for Z Z Smith TOTAL 21 20 50 MAX 12 8 50 17 00 COUNT 4 Each summary function result for each field column is calculated and included as a summary value similar to a subtotal at every defined report break and as a final su...

Page 148: ...e Len and Dec column of the Specify Report Column Formatting display Also rounding or truncation is done in these calculations depending on what you specify on the Specify Processing Options display see Chapter 14 Specifying Query for iSeries processing options Select Report Summary Functions Type options press Enter 1 Total 2 Average 3 Minimum 4 Maximum 5 Count Options Field 5 _ _ _ _ ITEM 1 4 _ ...

Page 149: ...ly below the column s for which they are specified at the end of the report and or after each report break In a report that lists summary information only summary values are shown directly below the column headings after any line of break fields for report breaks Specifying whether you want detail or summary information is described in Chapter 13 Selecting output type and output form in Query for ...

Page 150: ...138 Query for iSeries Use V5R2 ...

Page 151: ...hapter 11 Specifying report summary functions in Query for iSeries reports for details If you only wanted final totals at the end of the report you would not have to define any report breaks in this example When a report break occurs for each group of records selected by the query break text if any is shown following a blank line The break text is then followed by a separate line for each type of ...

Page 152: ...e assigned to the lower sort priority numbers If you specify a field as a break field that is not also a sort field you may get extra report breaks in your report because if the field is not part of the sorting step records that belong in the same group may not be grouped together and will cause the extra breaks Break definitions are ignored for detail output to a database file Refer to Building a...

Page 153: ... additional spacing or text Break level 0 is another level provided by Query that you can use This break level which occurs only at the end of the report is used to print the final summary values for all the specified summary functions at the end of the report Break level 0 has default break text FINAL TOTALS but it can be changed or filled with blanks if you do not want any final total descriptio...

Page 154: ...nly change in a field is from A to a for example Collating sequences are not used for DBCS character data in DBCS fields v For numeric fields the actual data in the fields is used for determining report breaks not the edited form of the data v For date time and timestamp fields chronological equivalency determines report breaks v For both character and numeric fields the original definition size o...

Page 155: ...ummaries N Y Yes N No Break text Company Level Field Level Field 1 COMPANY 4 DEPARTMENT 2 DIVISION 5 EMPLOYEE 3 REGION F3 Exit F5 Report F10 Process previous F12 Cancel F13 Layout F18 Files F23 Long comment Skip to new page in Query for iSeries reports Type a Y or N to specify for this break level whether printing is to start on a new page after a report break occurs for this break level The summa...

Page 156: ...field name You can also type characters directly in front of a field name The field names can be uppercase lowercase or a mixture LNAME lname or Lname Blanks at the end of field values such as Glendale are dropped Blanks in front of field values are also dropped Note Any changed lengths except 0 specified on the Specify Report Column Formatting display are used However if 0 was specified for a fie...

Page 157: ...nted in detailed or summary form If you select this option the Define Printer Output display is shown when you press the Enter key There you can specify the printer to be used and other characteristics of the printed output Other displays related to the printed output are also shown Note If a query is run in batch output may be sent to a different printer than one you specify depending on the defa...

Page 158: ...ks totals averages maximums minimums or counts 1 Detail For displayed and printed output the report is to contain both the selected data records and all summary information For database file output only the selected records are put into the file summary information is not 2 Summary only The report is to contain only the summary information provided at report breaks and at the end of the report fin...

Page 159: ...indicate whether you want all of each record to be on the same page in the report Y Yes Each record is to have all its fields kept together on the same page If there is not enough room left on the current page to contain all of its fields the complete record is displayed or printed on the next page N No Records that start near the end of one page are split as necessary and continued at the top of ...

Page 160: ...the columns on the line v If the wrapping width is too small Query may truncate or discard information that would otherwise have appeared in the column such as a column heading or a count summary or even drop an entire column from the report Values from numeric date time or timestamp fields are never partially shown A column is dropped from the report rather than present it with no part of the val...

Page 161: ...ou specify depending on the default printer specifications for batch Form size in Query for iSeries reports This set of prompts specifies the length and width of the printer forms that are to be used to print your query report If you leave these prompts blank the length and width values used are those specified in the printer device file named QPQUPRFIL which is the only printer device file that Q...

Page 162: ...ed in the printer device file named QPQUPRFIL used by Query determines the ending line number Line spacing in Query for iSeries reports Type a number 1 2 or 3 that indicates the kind of line spacing between records that you want used for your report This value controls the amount of separation blank lines between all but wrapped lines which are always single spaced A value of 2 or 3 can make the r...

Page 163: ...ether the output is to be spooled When your query is run it creates your report and sends it as a spooled printer file to an output queue You can specify that either the output be printed as soon as it can be scheduled or that it be held on the output queue until it is released at a later time Y Yes The output for your report is to be spooled and sent to an output queue When the report is printed ...

Page 164: ...ue The report is not printed until the spooled file is released for printing at a later time N No The output for your report is not held it is printed as soon as it can be scheduled Defining the printout cover page of Query for iSeries reports The Specify Cover Page display is another printer output definition display used to continue defining the print characteristics of your query report On this...

Page 165: ...at cause the system date the system time and the current report page number to be printed exactly where you want them in your text These codes are provided so you can use them instead of the standard headings and format them however you wish date The system date printed in the job s standard date format time The system time printed in the job s standard time format page The current page number of ...

Page 166: ... printed at the top of all the pages of your report except for the cover page You can type as many as three full lines of text using all characters including uppercase and lowercase letters that can be printed by the printer that you specified on the Define Printer Output display You can also use the special codes date time and page wherever you want in your heading text If you press F5 Report or ...

Page 167: ...running a query that gets data from that file Although you can specify a file you have already selected as an input file for this query you cannot run the query without using file selection override v If summary only output is being used and an overflow error occurs in a summary for a numeric field 9s are put in the summary field instead of the data If the field contains floating point data then 0...

Page 168: ... for break fields in summary output definitions Query for iSeries creates field names to avoid duplication and to reflect the nature of the function applied in summary output Text and column headings for summary function fields are created from input field names and translatable summary function captions The CCSID assigned depends on where the name originated The query definition CCSID is used for...

Page 169: ...on or replace the existing file The file name must begin with an alphabetic character A through Z or and it can be followed by no more than nine alphanumeric characters A through Z 0 through 9 or _ for example NAMEADDR or INVEN_12 Because most system supplied objects on the iSeries system begin with Q your file names should not start with a Q Also if you have systems in countries that use differen...

Page 170: ...by the data from your query 4 Replace member Replaces the existing member named in the Member prompt with the data from your query All the data in that member is deleted and your data is added If a member by that name did not exist the member is created and the data is put in it 5 Add to member Adds the data from your query at the end of the data in the existing member named in the Member prompt I...

Page 171: ... use the file USE Use authority allows other users to read records in the file authorization list name If you specify the name of an authorization list its authority is used to control the ability that users have to use the file For more information see the iSeries Security Reference book The following table shows for each predefined type of authority what can be done with the file Type of Create ...

Page 172: ...record The indicator position is blank if overflow has not occurred An asterisk appears if any calculated field overflows The field that overflowed is filled with the maximum value for a field of that type and size a string of asterisks appears for the value in a printed or displayed report The output data is grouped together by field with the report break values followed by any summary function v...

Page 173: ...e record break level 0 containing a count of the records selected by the query For example 0 0000008 If this query had summary functions but no report breaks specified the output would be a single record break level 0 containing summary function values for the records selected by the query For example 0 00030000000 03750000 0000008 If this query had report breaks but no summary functions specified...

Page 174: ...162 Query for iSeries Use V5R2 ...

Page 175: ...such as if your query calculates the approximate percentage of customers who purchased more than 10 000 worth of your company s products last year If you work in an environment in which CCSID conversions are necessary and might involve character substitution you can choose to either ignore or see the character substitution warnings depending on the data and reports you need You can select whether ...

Page 176: ... data errors prompt Query stops processing if it encounters a decimal data error The rounding option and the ignore decimal data errors option function only for numeric data that can be calculated See Specify processing options in Query for iSeries on page 238 for a more detailed description of the ignore decimal data errors option Rounding and truncation are not done just on result fields they ar...

Page 177: ... CCSID conversion usually occurs when the user is running in a multilingual environment A unique character may be substituted during character conversion for any character in the source coding representation that does not have a match in the target coding representation Y Yes Character substitution is ignored and no error messages are issued Leave the default Y if your system contains all files wi...

Page 178: ...166 Query for iSeries Use V5R2 ...

Page 179: ...s a query definition object whose type is QRYDFN If this is a new object you are its owner and you can specify which other users can use your query and in what way Like other objects you can display information about it using the Display Object Description DSPOBJD command Storing the Query for iSeries query definition Query supplies a Y for the Save definition prompt on the Exit This Query display...

Page 180: ...erations on the object except those limited to the owner or controlled by authorization list management rights A user can do anything with the query including deleting it except for transferring it to a new owner or changing the type of authority EXCLUDE Exclude authority prevents other users from doing anything with the query definition Unless given specific types of authority no user except its ...

Page 181: ...ies menu You can run an existing query by selecting option 2 Run an existing query on the Query menu This causes the RUNQRY Run Query command help display to appear For more information on using the RUNQRY command see the CL Reference information in the iSeries Information Center and Running a Query for iSeries query using the RUNQRY command on page 171 of this book Running a Query for iSeries que...

Page 182: ...nts the report on a printer or places the data from the query in a database file For more information on selecting an output device see Chapter 13 Selecting output type and output form in Query for iSeries reports If you choose to submit the query to batch processing type a 2 Run in batch in the Run option prompt and press the Enter key The query is submitted to the job queue specified in the job ...

Page 183: ...re you have the current definitions when you want to run the query from the Work with Queries display by selecting option 2 Change instead of option 9 Run You can then save the query without actually making any changes by doing the following v Press F3 on the Define the Query display v Choose to save the definition and run the query on the Exit This Query display IDDU definitions are explained in ...

Page 184: ...the input file name parameter override the file name s specified in the query definition Therefore if multiple files and members are defined in the query definition and you want to change one or two of them you must specify SAME for the file selections that do not change and specify the values for the ones you want to override When you run a saved query you might not get the results you expected T...

Page 185: ...uery display for which you want to change query definition values When the definition step displays appear the prompts are already filled in To change the query all you need to do is change or delete the values in the prompts When you have finished changing a query you can run it save it run it and save it and so on Starting changes by copying a Query for iSeries query definition You may find it h...

Page 186: ...y once you see it just press the Enter key without changing anything and continue If you have changed the display and decide you would rather have it the way it was press F12 Cancel Pressing F12 does not undo the result of pressing F23 Save as default Also changes Query must keep as a result of your pressing the Enter key or F10 or F3 to get to a different display cannot be canceled later by press...

Page 187: ...ompt F5 Report F13 Layout F14 Define the query You can change any combination of the Query Library and Text prompts If you save the query definition these changes have the following effects Query Name and or Library Name Changed Query Name and Library Name Unchanged Existing query is not changed Existing query is changed New query created with new name and or in different library New query definit...

Page 188: ...ce values regardless of how you set the processing option A change to your collating sequence can change the records that these tests select A change to the collating sequence can also affect your query s minimum and maximum summary values the location of report breaks and the order of selected records if you use a field containing SBCS character data for sorting For example you have 2 collating s...

Page 189: ...nother library by copying the query to the other library and then deleting the query from the original library You can copy a previously saved query by using option 3 Copy on the Work with Queries display You can type the name of the query and its library on the display or you can from a list of queries type a 3 next to the query s that you want to copy For more information about using lists see U...

Page 190: ...queries including the query in error v Press F20 Cancel copy in error without pressing the Enter key The query in error is not copied To continue copying the remaining queries press the Enter key If an error is found while copying one of the remaining queries another error message is shown and the list is shown again with that query listed first and highlighted Renaming a Query for iSeries query d...

Page 191: ... of queries being deleted is shown and an error message appears on the bottom of the display The query that has the error is highlighted and appears at the top of the list Any queries that were before that query in the list have already been successfully deleted You can do either of the following v Press F9 Reset without pressing the Enter key The delete process is canceled for all remaining queri...

Page 192: ...at the same options again or choose to display different options Whenever you decide you are finished displaying this query definition you can either 1 Press F12 as many times as necessary to return to the Work with Queries display 2 Press F3 to work with any remaining queries if you selected more than one query from a list on the Work with Queries display You return to the Work with Queries displ...

Page 193: ...le information and the field layout are shown v The form of output selected detailed or summary only and wrapping specifications If you print the definition using option 6 Print definition on the Work with Queries display the printout includes information about any output type selected for the query For example if database file output was selected when the query was created and the query was later...

Page 194: ...e only in this simple case Actual naming varies with different queries v The Begin column shows the starting byte of the field within the record For example the field named ZD3 starts at byte 28 v The Len and Dec columns show the length and decimal positions for each field For example the field named PK2 has a length of eight with two decimal positions Note What is shown on the report is the print...

Page 195: ...a type for each field v The Text column shows the comment if it exists for each field If the field is for a summary function the text shown is the field name without any added numbers followed by the type of summary function v The CCSID column if shown appears when multiple CCSIDs are represented and shows the CCSID for each field Chapter 16 Working with Query for iSeries query definitions 183 ...

Page 196: ...184 Query for iSeries Use V5R2 ...

Page 197: ...s 188 Using fields other than sort fields for report breaks in Query for iSeries 188 Result field length and decimal positions in Query for iSeries 188 Tips for dealing with presentation length and decimal positions in Query for iSeries 188 Length and decimal positions used for internal numeric calculations in Query for iSeries 189 Addition and subtraction in Query for iSeries 189 Multiplication i...

Page 198: ...186 Query for iSeries Use V5R2 ...

Page 199: ...read and update a file at the same time Query is creating a report from the file and two or more Query definitions can refer to the same file at the same time Overriding database files in Query for iSeries Your query run results cannot be predicted if you use the Override with Database File OVRDBF command to override a database file Query allows you to use the OVRDBF command but problems may occur...

Page 200: ...k field while an arbitrarily assigned also unique customer ID is used for sorting Since customer name is a break field it can be inserted in break text or placed in a summary only database file Result field length and decimal positions in Query for iSeries Query determines the presentation length and number of decimal positions for result fields when it creates them in the report These values are ...

Page 201: ... positions used for internal numeric calculations in Query for iSeries The following formulas define the maximum length and decimal positions used internally by Query for decimal calculations for numeric result fields The maximum length and decimal positions do not necessarily correspond with the presentation length and decimal positions assigned by Query The symbols p and d denote the length and ...

Page 202: ... derived from the calculation 31 26 9 5 Define Result Fields Type definitions using field names or constants and operators press Enter Operators SUBSTR DATE Field Expression Column Heading Len Dec RESULTA___ PRN299A PRN299B__________________ ____________________ __ __ _________________________________ ____________________ _________________________________ ____________________ __________ __________...

Page 203: ...lt Fields Type definitions using field names or constants and operators press Enter Operators SUBSTR DATE Field Expression Column Heading Len Dec RESULT269_ PRN299A__________________________ ____________________ 26 _9 _________________________________ ____________________ _________________________________ ____________________ RESULT295_ PRN299B__________________________ ____________________ 29 _5 ...

Page 204: ...192 Query for iSeries Use V5R2 ...

Page 205: ...Part 4 Appendixes Copyright IBM Corp 2000 2002 193 ...

Page 206: ...194 Query for iSeries Use V5R2 ...

Page 207: ...rent on the iSeries system along with an explanation of the differences as they apply to each listed item Linked files If an IDDU defined file that is migrated from System 36 is not linked you can still run a query against the file However the file will show as a one field file that is if you were to run a default query against the file the file would appear as though all of the fields were run to...

Page 208: ...is being sent to a file Query for iSeries builds the file as a database file with a definition It is no longer necessary to build the IDDU definition and link that definition to the file as was needed on Query 36 unless the QRY or QRYRUN procedure was used to run the query Summary only output When sending summary only output to a file using Query for iSeries each report break field whose break lev...

Page 209: ... plus some system commands that work with queries System 36 OS 400 System QRY WRKQRY QRYRUN RUNQRY REMOVE DLTQRY FROMLIBR SAVOBJ HELP QUERY STRQRY LIBRLIBR CRTDUPOBJ TOLIBR RSTOBJ Migration differences between System 36 and Query for iSeries definitions Query for iSeries definitions cannot be migrated to System 36 Appendix A Differences between Query for iSeries and Query 36 197 ...

Page 210: ...198 Query for iSeries Use V5R2 ...

Page 211: ...sts in a library you can use If a data dictionary exists that you can use go to step 6 3 Choose option 1 Create on the display type the name of the library that will contain the dictionary and press the Enter key If you attempt to create a data dictionary and the named library does not exist a message is displayed that says Library not found Press Enter to create If you press the Enter key you wil...

Page 212: ...nition Then press the Enter key 9 When you see the Create File Definition display select option 2 Create single format and accept the system supplied record format name the system uses the file definition name and adds the letter R to the end You can type brief descriptive information in the Text prompt as we have Do not specify anything in the Long comment prompt Create File Definition Definition...

Page 213: ...0 First name ADDRESS1 Character 20 Address line 1 ADDRESS2 Character 20 Address line 2 CITY Character 15 City STATE Character 2 State ZIP Character 9 Zip code AMOUNT Numeric 2 decimal positions 6 Amount 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 Mo...

Page 214: ... press Enter Type field press F6 to create Seq Field End Seq Field End Seq Field End Seq Field End 10 LASTNAME 15 10 FIRSTNAME 25 10 ADDRESS1 45 10 ADDRESS2 65 10 CITY 80 10 STATE 82 10 ZIP 91 10 AMOUNT 95 verify that the field names are arranged in the proper sequence When sequence numbers are the same the arrangement of the field names on the display determines the sequence of use in the record ...

Page 215: ...ame the new file and specify the library that will contain the file Work with Database Files Library default___ Name F4 for list Position to __________ Starting characters Type options and Database file press Enter 1 Create 2 Enter data Database Database Database Database Opt File Opt File Opt File Opt File 3 Press the Enter key The Create Database File display is shown with the names of the new f...

Page 216: ...ss Enter 1 Create 2 Enter data Database Database Database Database Opt File Opt File Opt File Opt File 3 Press the Enter key The Work with Data in a File display is shown WORK WITH DATA IN A FILE Mode ENTRY Format NAMEADDR File NAMEADDR LASTNAME FIRSTNAME ADDRESS1 ADDRESS2 CITY STATE ZIP AMOUNT F3 Exit F5 Refresh F6 Select format F9 Insert F10 Entry F11 Change 4 Type the information shown on the f...

Page 217: ...UNT Daye Ben 1312 Elm Street Apt C Marshill AK 77901 456 Patterson Tammy 4 Ridgeview Court Marshill AK 77901 501 Barker Rick 1432 Le Grand Blvd Emmerson LA 71282 938 Sedgewick Lillian 200 Park Lane Emmerson LA 71282 25 Skoggen Linda Circle Court NE Truman NC 57432 764 Golinero Sebastin 7196 Thomas Street Highpoint MN 55909 00 Lien Sue 469 Jackson Street Ridgeway CA 72430 00 Sedgewick Leona 21st Ca...

Page 218: ...dictionaries Data for file NAMEADDR in YOURLIB was changed A message on the bottom of the display tells you that data in your file was changed 9 Press F3 Exit and you see the menu that you started from An audit log is automatically printed showing all the data you entered Query for iSeries query exercise Creating and running a query In the following exercise you create and change a query that prod...

Page 219: ...d of xxx and the name of your library for the Library prompt 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 Query KJOQRY Name F4 for list Library YOURLIB Name LIBL F4 for list F3 Exit F4 Prompt F5 Refresh F12 Cancel 3 Press the Enter key The Define the Query display is shown Define the Query Query KJOQRY Optio...

Page 220: ... F13 Layout F24 More keys This display is where you tell Query what database file s you want to query for the data in your report 5 Type NAMEADDR for the File prompt the name for the library that contains the file for the Library prompt and leave the Format and Member prompts as they are shown Specify File Selections Type choices press Enter Press F9 to specify an additional file selection File NA...

Page 221: ...ct and sequence fields _ Select records _ Select sort fields _ Select collating sequence _ Specify report column formatting _ Select report summary functions _ Define report breaks _ Select output type and output form _ Specify processing options F3 Exit F5 Report F12 Cancel F13 Layout F18 Files F21 Select all Select options or press F3 to save or run the query The Specify file selections option h...

Page 222: ...ADDRESS2 000001 SIMPSON FRANK 1722 ORANGE STREET 000002 DAYE BEN 1312 ELM STREET APT C 000003 SEDGEWICK LILLIAN 200 PARK LANE 000004 LIEN SUE 469 JACKSON STREET 000005 PATTERSON TAMMY 4 RIDGEVIEW COURT 000006 SKOGGEN LINDA CIRCLE COURT NE 000007 SEDGEWICK LEONA 21ST CANNERY ROW APT 43 000008 BARKER RICK 1432 LE GRAND BLVD 000009 GOLINERO SEBASTIN 7196 THOMAS STREET 000010 SKOGGEN CHARLES 401 ABBEY...

Page 223: ...prompt on the Work with Queries display 2 Type xxxQRY where xxx are your initials for the Query prompt if xxxQRY is not shown as the query name 3 Type the library name you used in the previous exercise for the Library prompt if that is not the library name already shown Work with Queries Type choices press Enter Option 2 1 Create 2 Change 3 Copy 4 Delete 5 Display 6 Print definition 8 Run in batch...

Page 224: ...______ _____ ______________________________ ____ ______________ _____ ______________________________ ____ ______________ _____ ______________________________ ____ ______________ _____ ______________________________ ____ ______________ _____ ______________________________ ____ ______________ _____ ______________________________ Bottom Field Text Len Dec LASTNAME Last name 15 FIRSTNAME First name 10...

Page 225: ... HEX make sure you type it as LA because the apostrophes tell the program that LA is what the field contains and not the field name You must also type this value in all uppercase letters because that is the way it is specified in the field If you have any doubt about the case of the data in the database you should use a shared weight table 8 Press F5 Report to run the query and show the report F5 ...

Page 226: ...n interactively 2 Run in batch 3 Do not run For a saved definition Query KJOQRY Name Library YOURLIB Name F4 for list Text Lists customer names and addresses Authority CHANGE LIBCRTAUT CHANGE ALL EXCLUDE USE authorization list name F4 Prompt F5 Report F13 Layout F14 Define the query You can use this display to run the query with the changes you made You can also choose to make the query changes ei...

Page 227: ...fresh F12 Cancel Query option processing completed successfully To sign off the system or use it for something other than Query press F3 Exit to return to the menu from which you chose to use Query Query for iSeries query exercise Creating an advanced query In the following example you create a file named QRYFILE by using the Display Object Description DSPOBJD command The file contains description...

Page 228: ...SPOBJD OBJ QGPL ALL OBJTYPE ALL OUTPUT OUTFILE OUTFILE QGPL QRYFIL E F3 Exit F4 Prompt F9 Retrieve F12 Cancel F13 User support F23 Set initial menu C COPYRIGHT IBM CORP 1980 1991 2 Type WRKQRY on the command line and press the Enter key MAIN OS 400 Main Menu System RCH38342 Select one of the following 1 User tasks 2 Office tasks 3 General system tasks 4 Files libraries and folders 5 Programming 6 ...

Page 229: ... options press Enter Press F21 to select all 1 Select Opt Query Definition Option 1 Specify file selections Define result fields 1 Select and sequence fields 1 Select records 1 Select sort fields Select collating sequence 1 Specify report column formatting 1 Select report summary functions 1 Define report breaks Select output type and output form Specify processing options F3 Exit F5 Report F12 Ca...

Page 230: ...s The message Select file s or press Enter to confirm appears Specify File Selections Type choices press Enter Press F9 to add File QRYFILE Name F4 for list Library QGPL Name LIBL F4 for list Member FIRST Name FIRST F4 for list Format QLIDOBJD Name FIRST F4 for list F3 Exit F4 Prompt F5 Report F9 Add file F12 Cancel F13 Layout F24 More keys Select file s or press Enter to confirm Press the Enter k...

Page 231: ...at will appear in your query report in the order that you specify ODLBNM will appear first followed by ODOBPT and so on Note You must select fields that you plan to sort on 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 ODDCEN DISPLAY CENTURY 1 ODDDAT Display date Format MMDDYY 6 x ODDTIM DISPLAY T...

Page 232: ... to confirm Press the Enter key again 7 The next display shown is the Select Records display Here you specify which records you want included in your report 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 Bottom Field Text Len Dec ODLBNM LIBRARY 10 ODOBTP OBJECT TYPE 8...

Page 233: ...B R Example Page 2 000002 QGPL PGM CLP 16 384 B R Example page 2 000003 QGPL FILE PF 8 192 000004 QGPL FILE PF 1 024 Default source data ba 000005 QGPL FILE PF 1 024 Default source data ba 000006 QGPL FILE PF 16 384 Default source data ba 000007 QGPL FILE DKTF 2 560 Default diskette data 000008 QGPL FILE DKTF 2 560 Default source diskett 000009 QGPL FILE PF 1 024 Default source data ba 000010 QGPL...

Page 234: ...e to sort the selected records for your report You want the records sorted first by object type and then by object size so type a 1 next to ODOBTP and a 2 next to ODOBSZ as shown below 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 ODLBNM LIBRARY 10 1 ODOBTP OBJECT TYPE 8 ODOBAT OBJECT ATTRIB...

Page 235: ...cify Report Column Formatting display It is here that you specify column spacing column headings and other report formatting options Specify Report Column Formatting Type information press Enter Column headings NONE aligned text lines Column Field Spacing Column Headings Len Dec Edit ODLBNM 0 LIBRARY 10 ODOBTP 2 OBJECT 8 TYPE ODOBAT 2 OBJECT 10 ATTRIBUTE More F3 Exit F5 Report F10 Process previous...

Page 236: ...ected field you can specify that a total average minimum maximum and or count be included in your report Select Report Summary Functions Type options press Enter 1 Total 2 Average 3 Minimum 4 Maximum 5 Count Options Field Text Len Dec _ _ _ _ _ ODLBNM LIBRARY 10 _ _ _ _ _ ODOBTP OBJECT TYPE 8 _ _ _ _ _ ODOBAT OBJECT ATTRIBUTE 10 _ _ _ _ _ ODOBSZ OBJECT SIZE 10 0 _ _ _ _ _ ODOBTX TEXT DESCRIPTION 5...

Page 237: ...d as break fields Report breaks are used to break the report into groups of records each time the value of the report break field changes 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 10 ODOBTP OBJECT TYPE 8 ODOBAT OBJECT ATTRIBUTE 10 20 ODOBSZ OBJECT SIZE 10...

Page 238: ...zero You can use break level 0 to print the final summary values for all specified summary functions at the end of the report For this example do not change anything on this display Just press the Enter key Format Report Break Break level 0 Type choices press Enter Type field in text to have break values inserted Suppress summaries N Y Yes N No Break text FINAL TOTALS Level Field 1 ODOBTP F3 Exit ...

Page 239: ...tions press Enter Press F21 to select all 1 Select Opt Query Definition Option Specify file selections Define result fields Select and sequence fields Select records Select sort fields Select collating sequence Specify report column formatting Select report summary functions Define report breaks Select output type and output form Specify processing options F3 Exit F5 Report F12 Cancel F13 Layout F...

Page 240: ... means that all of the report does not fit on the display Use the page keys or F20 Right and F19 Left to page through the report both left to right and top to bottom so that you can see the breaks and summaries in the report When you have finished looking at the report press F3 Exit to return to the Define the Query display Define the Query Query QNAME Option CREATE Library QGPL CCSID 37 Type opti...

Page 241: ...6 The Work with Queries display appears showing the message Query option processing completed successfully 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 QNAME Name F4 for list Library QGPL Name LIBL F4 for list F3 Exit F4 Prompt F5 Refresh F12 Cancel Query option processing completed successfully Now you ...

Page 242: ...230 Query for iSeries Use V5R2 ...

Page 243: ...ning a query called optimization Query determines the fastest way to process a query An access plan results and is used to perform the actual I O for the query Optimization includes factors such as file size selection tests and sort tests However the main performance element for both optimization and I O is the use of keyed sequence access paths for the files selected by the query Keyed sequence a...

Page 244: ...cess paths that Query can use and other general tips on how to define your Queries to improve performance see Defining queries for Query for iSeries on page 234 Creating a minimum number of access paths is important for these reasons v Any change to a field in a database results in updating all access paths keyed on that field as well This can be expensive in terms of performance for a large numbe...

Page 245: ...be significant for both the individual query response time and the system in general especially if the query is run often Updating access plans in Query for iSeries To update an access plan for a saved query enter the change option for that query and save it again You do not need to make any changes This allows Query to update the access plan to reflect any changes If you have a large number of sa...

Page 246: ...cessed on the iSeries system You should identify and correct decimal data errors to obtain optimal query performance See Defining queries for Query for iSeries on page 234 for more information You can identify and correct numeric field errors by using the Programmer s Tool Kit PRPQ that is available for the iSeries system The files you analyze or correct with this tool kit must be externally descr...

Page 247: ...I O by Query select only the fields you need Also additional unneeded fields make a report less readable If you want to use most of the fields use F21 Select All to show all fields then delete the sequence numbers from the fields you do not need Note If you select no fields Query for iSeries as a default picks up to the first 500 fields in the file Avoid this type of operation because it causes un...

Page 248: ...re the access path build time is excessively long Query attempts to use an existing access path if all the sort fields from the query match the high order key fields from the access path This way you can avoid excessive building of access paths for queries with sort tests As an example assume file Z has fields A B C and D Also assume there are six access paths built over this file that have the fo...

Page 249: ...ng sequence other than hexadecimal to hexadecimal A unique weight sort sequence table might require less processing than a shared weight table because it can be ignored for comparisons that do not involve evaluating relative order Specify report summary functions in Query for iSeries Access paths do not help performance for summary functions so you do not need to consider creating access paths sol...

Page 250: ...nding on the environment in which you are running the query If the query is run in the System 36 environment Query uses Y and decimal data errors are ignored If the query is run in the System 38 environment or OS 400 environment Query uses N and decimal data errors are not ignored The System 36 environment uses Y because decimal data errors were always ignored on the System 36 Because access paths...

Page 251: ...ormance tips for join operations in Query for iSeries For all join operations Query requires the use of an access path over each of the secondary files in the join If no usable access paths exist Query builds them as needed For this reason if a particular join query is run often or if several join queries use the same sort or join selection tests consider building access paths that match these val...

Page 252: ...tion tests run Query against the file or files requiring the record selection and put the output to a database file option 3 on the Select Output Type and Output Form display Use this output file to join with the other files However this approach may result in using old data since the output to the database file may be an older version by the time the join query using this file is run Also since n...

Page 253: ...o the performance optimization messages that are available in debug mode use STRDBG before running your query These messages may help you determine how you can change the query definition so it will run faster Query for iSeries migration considerations N to N 1 When query creates a database file that includes a date time timestamp variable length or null capable field a bit is set that states that...

Page 254: ...ex SQL View is specified If the temporary file is created for a join as described above try to avoid this type of operation Refer to Using join operations in Query for iSeries on page 238 for more information on this item If this message occurs because the total length of the sort fields is greater than 2000 bytes this may be reason for concern For more information on this refer to Select sort fie...

Page 255: ...erent run capabilities v The RUNQRY command is changed to no longer support being run interactively To prevent a user from running a query interactively v Use the Change Command CHGCMD command to remove INTERACT from the ALLOW where allowed to run parameter ALLOW PGM is not allowed in RUNQRY v Change the authority for the RUNQRY command to state which users have authority to this command Note To a...

Page 256: ...244 Query for iSeries Use V5R2 ...

Page 257: ... column heading text description edit word or long comment for a field CCSID marked data can be converted so that it looks the same in languages that use the same character set or superset of the character set see Figure 7 on page 246 Data will not look the same without conversion if the code pages differ The hexadecimal value for a graphic character a character that is displayed or printed in one...

Page 258: ...e compatible with substitution which means conversion is possible but substitution characters might be used Substitution characters depending on where they occur can give unpredictable results particularly in sorting and testing character fields v They are incompatible which means conversion is impossible This appendix explains how CCSIDs affect what Query for iSeries does in specific circumstance...

Page 259: ...al totals text found at break level 0 Cover page text Page heading text Page footing text Collating sequence user defined or snapshot of translation table or system sort sequence 2 7 8 v Translation table and system sort sequence Collating sequence 8 9 v Query user profile Collating sequence user defined default 7 8 v Query program object containing preconfigured language sequences That is the mod...

Page 260: ...ated as though marked with the latter CCSID 8 Some diagnostic messages about CCSID conversion problems show the incompatible CCSIDs 9 The CCSID of the collating sequence resolved for the current collating choices is shown in a message at the bottom of the Select Collating Sequence display CCSIDs and collating sequences in Query for iSeries Within Query for iSeries you can specify hexadecimal the d...

Page 261: ...talian MNCS 00500 500 United Kingdom 00285 285 United States English 00037 37 Notes 1 MNCS means multinational character set 2 NLV means national language version CCSID conversions for Query for iSeries options and functions You may get a diagnostic message or unexpected result when you select a Query for iSeries option or press a function key that requires a conversion from one CCSID to another T...

Page 262: ...ry for iSeries query You may see CCSID related diagnostic messages in a definition you are either creating changing or displaying See CCSIDs and Query for iSeries query definition items on page 252 for more information Defining a collating sequence in Query for iSeries If a collating sequence CCSID does not match your job CCSID and neither CCSID is 65535 the collating sequence CCSID is converted t...

Page 263: ...ed instead This happens regardless of the method used to run the query Note The job CCSID is the CCSID of the job It can be changed by the command CHGJOB CCSID Neither data nor text is converted when the output is directed to an existing file If a field receiving data has a different CCSID output processing stops with a diagnostic before any records are added If you have done an override to a data...

Page 264: ...inarily be diagnosed at definition time CCSID and join tests in Query for iSeries If fields in a join test have different CCSIDs a conversion to a compatible CCSID is done If the two fields do not have the same CCSID and cannot be converted or if any collating sequence to be used cannot be converted you see an error message CCSID and result field expressions in Query for iSeries Query for iSeries ...

Page 265: ...ed it is converted as needed to the CCSID in which the data is being evaluated If a conversion error occurs while the collating sequence is being converted you see an error message This happens only while a query is running CCSID and column formatting and editing in Query for iSeries Column headings and edit words are converted if necessary to the document or job CCSID when passed back to a docume...

Page 266: ...bstitution character involvement is always treated as an error in this case The consequence depends on the origin of the collating sequence and what you are doing sequence origin consequence user defined after a warning prompt bypassed in display mode user defined after a warning prompt initialized from other source for first showing in change mode language sequence F15 request rejected user defin...

Page 267: ...arison for record selection Run request rejected 1 Field data Collating sequence Cannot perform comparison involving dependent value Run request fails No column list results returned Field 2 constant Field 1 Cannot calculate expression for key field when building access plan Run request fails 1 Field 2 Field 1 Cannot convert data to CCSID of receiving record field Run request fails No records adde...

Page 268: ...he error could occur for the tested field or test value Run ended output is incomplete 1 Field 2 constant Field 1 Cannot calculate result field value for output Replacement string displayed or printed for value and for any subsequent break minimum or maximum value for the field Output to database ended output is incomplete possibly some but not all records added 1 Field data Job Cannot convert sel...

Page 269: ...rd selection Display Query allowed A A Yes Yes Yes Yes A 65535 Yes Yes1 Yes1 Yes1 65535 A Yes Yes Yes Yes A B Yes No1 No1 No1 65535 No tag Yes Yes Yes Yes A No tag Yes Yes1 Yes1 Yes1 1 Either a warning or request ending message is issued Appendix E Coded character set identifiers CCSIDs in Query for iSeries 257 ...

Page 270: ...258 Query for iSeries Use V5R2 ...

Page 271: ... and libraries control language CL programming controlling flow and communicating between programs working with objects in CL programs and creating CL programs v The Control Language CL Reference information in the Programming category of the iSeries Information Center provides the application programmer with a description of the OS 400 control language CL and its commands Each command description...

Page 272: ...260 Query for iSeries Use V5R2 ...

Page 273: ...sistent with local law INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION AS IS WITHOUT WARRANTY OF ANY KIND EITHER EXPRESS OR IMPLIED INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF NON INFRINGEMENT MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE Some states do not allow disclaimer of express or implied warranties in certain transactions therefore this statement may ...

Page 274: ...rmation softcopy the photographs and color illustrations may not appear Trademarks The following terms are trademarks of International Business Machines Corporation in the United States other countries or both Application System 400 AS 400 DB2 e logo IBM iSeries iSeries 400 Operating System 400 OS 400 SAA System 36 Systems Application Architecture 400 Other company product and service names may be...

Page 275: ...t 159 authority continued giving to others for your query 168 security 159 authorization list name authority 159 168 average summary function date time and timestamp value 136 null value 136 B batch processing choosing a query task 12 performance recommendations 240 bibliography 259 bracket characters DBCS double byte character set 9 bracketed DBCS data Display Report Layout display 8 length restr...

Page 276: ...bstring example 61 valid comparisons 46 character field name valid entries 60 value description 61 character result field rules for creating 60 character set support UCS2 level 1 4 character substitution warning 165 checking query results 8 choices selecting options 29 CL control language 5 creating database files 5 code Dec field E 121 J 121 L 121 O 121 T 121 V 121 Z 121 edit 129 L T or Z join te...

Page 277: ...reate Table 112 Delete Query DLTQRY 11 178 DLTQRY Delete Query 11 178 Override with Database File OVRDBF 187 OVRDBF Override with Database File 187 Query deleting 11 running 11 command continued Query 36 procedure equivalents 197 Run Query RUNQRY 11 171 RUNQRY Run Query 11 171 Start Query STRQRY 7 11 STRQRY Start Query 7 11 Work with Query WRKQRY 7 11 WRKQRY Work with Query 7 11 comparison 92 date...

Page 278: ...82 T 182 V 182 Z 182 time 66 timestamp 66 database file creating example 203 database file continued output maximum record length 30 output to differences between Query 36 and Query for iSeries 196 overriding 187 database relational definition 5 date arithmetic operation 70 converting numeric to date 71 decrementing 71 incrementing 71 numeric dates working with 71 comparison 96 constant record sel...

Page 279: ...attern special characters 100 DDS data description specifications introduction 5 Dec column data type E 45 182 G 45 J 45 182 Dec column continued data type continued L 182 O 45 182 T 182 V 182 Z 182 Dec field code E 121 J 121 L 121 O 121 T 121 V 121 Z 121 decimal data error migration from System 36 234 processing 238 point edited numeric fields 125 position fields in a report 120 for result field ...

Page 280: ...utput 148 Define Report Breaks 139 display continued Define Result Fields 57 Define Spooled Output 151 Define the Query 28 Describe Date Time Field Editing 128 Describe Numeric Field Editing 124 Display Date Format 69 Display File Selections 42 Display Join Tests 53 Display Report Layout 8 Exit This Query 167 169 Fields Missing from File Definition 54 Format Report Break 143 IDDU main system menu ...

Page 281: ...inition 211 code page 114 connecting record selection test 101 creating database file 203 creating query definition 206 215 defining result field 86 IDDU definition 199 example continued running query 206 selecting and sequencing field 88 selecting sort field 106 exercise Query 199 Exit This Query display 167 169 exiting Query for iSeries 7 167 expression character definition 58 considerations whe...

Page 282: ... 36 and Query for iSeries 195 secondary file sequence rule 49 selecting 47 selecting matched records using a primary file 48 file continued joining continued selecting unmatched primary file records 48 sequencing secondary files for a primary join 49 specifying join selections 42 linked differences between Query 36 and Query for iSeries 195 logical 5 55 multiple record formats 187 output to databa...

Page 283: ... time format 18 69 function key continued F21 to select all 30 F23 set collating sequence default 111 116 using to display a long comment 10 F4 using to obtain a list 9 F5 displaying report 169 using to display report 8 introduction to using 8 G GE greater than or equal comparison test 95 96 165 generic name 9 GG double byte G 9 GT greater than comparison test 95 96 165 H heading column in report ...

Page 284: ...cords 48 sequencing secondary files for a primary join 49 how to join 43 matched records join 43 matched records with primary file join 43 rules 44 secondary file sequence rule 49 types of join 43 unmatched records with primary file join 43 K key function 8 Print 9 keyed sequence access path definition 231 L L T or Z join test 45 labeled duration definition 73 labeled duration adding 235 subtracti...

Page 285: ...nt rules 77 syntax diagram 77 MONTH function argument rules 78 syntax diagram 78 moving through definition display 30 multilingual environment result field naming conventions 58 multiple Query for iSeries task 13 selecting files for a query 35 N name generic 9 numeric field 59 query definition 27 result field 58 special library 10 naming conventions result field multilingual environment 58 NE not ...

Page 286: ...r set 4 position to prompt 15 practice exercises 199 precision result fields 190 primary file definition 238 used in file join operations 43 primary record select omit criteria 43 Print key 9 printer defining printout cover page 152 selecting for query output 145 148 specifying page heading and footing 153 specifying spooled output override 150 printing differences between Query 36 and Query for i...

Page 287: ...and OR 101 record continued selection test continued DBCS LIKE 99 DBCS NLIKE not like 99 EQ equal 95 96 GE greater than or equal 95 96 GT greater than 95 96 IS is 95 ISNOT is not 95 LE less than or equal 95 96 LIKE like 95 98 LIST list 95 97 LT less than 95 96 NE not equal 95 96 NLIKE not like 95 98 NLIST not list 95 RANGE range 95 96 removing 103 unmatched 239 241 record format 187 definition 3 p...

Page 288: ...tion CCSID coded character set identifier 251 running query 8 example 206 Exit This Query display 169 interactively limiting 243 Query for iSeries menu 169 Query Utilities menu 11 RUNQRY Run Query command 171 using F5 169 with a different language 115 Work with Queries display 13 170 RUNQRY Run Query command 11 171 S saving query definition 167 SECOND function argument rules 78 syntax diagram 78 s...

Page 289: ...nce consideration 108 date time timestamp 108 example of how used 106 sort continued field continued null values 108 performance recommendations 236 priority number 105 selecting for query 105 236 specifying ascending or descending sequence 105 sequence differences between Query 36 and Query for iSeries 196 sorting DBCS character data 111 special library names 10 Specify Cover Page display 152 Spe...

Page 290: ...time field name 66 TIME function argument rules 79 syntax diagram 79 time value arithmetic 69 timestamp arithmetic operation addition rules 73 decrementing 73 incrementing 73 subtraction 70 73 comparison 96 constant record selection test 95 data type description 66 68 function CURRENT TIMESTAMP 80 sort considerations 108 value length 120 timestamp data Display Report Layout display 8 timestamp dur...

Page 291: ... Database Files display IDDU 203 Work with File Definitions display IDDU 202 Work with File Definitions Display IDDU 200 Work with Queries display getting to 7 introduction 12 listing names of queries 14 running query 170 Work with Query WRKQRY command 11 Work With Query WRKQRY command 7 working with Query 7 WRKQRY Work with Query command 7 11 Y YEAR function argument rules 80 syntax diagram 80 Z ...

Page 292: ...280 Query for iSeries Use V5R2 ...

Page 293: ......

Page 294: ... Printed in U S A SC41 5210 04 ...

Reviews:

No comments