Manuals / IBM / Computer Equipment / Server

IBM SC41-5210-04 manual ERserver

Models: SC41-5210-04

1 294

Download 294 pages 6.42 Kb

Page 3

ERserver

iSeries

Query for iSeries Use

Version 5

SC41-5210-04

Image 3

IBM SC41-5210-04 manual ERserver

Contents

ERserver Page ERserver Fifth Edition September Contents Defining result fields in Query for iSeries Selecting and sequencing fields in Query for iSeriesSelecting records in Query for iSeries Defining Query for iSeries report breaks Selecting sort fields in Query for iSeriesSelecting a collating sequence in Query for iSeries Specifying report column formatting in Query for iSeriesWorking with Query for iSeries query definitions Part 3. Advanced information about Query for iSeriesSpecifying Query for iSeries processing options Exiting and running a Query for iSeries queryAppendix B. Practice exercise for Query for iSeries query Viii Query for iSeries Use V5R2 Who should read the Query for iSeries Use book About Query for iSeries UseQuery for iSeries Use V5R2 Part 1. Introduction to Query for iSeries Query for iSeries Use V5R2 State What is Query for iSeries?Query for iSeries fundamentals Files, fields, and record formats in Query for iSeriesUCS2 level 1 character set support in Query for iSeries Double-byte character set Dbcs fields in Query for iSeriesQuery for iSeries definitions Libraries in Query for iSeriesMajor functions of Query for iSeries Query for iSeries Use V5R2 General operating information for Query for iSeries Using Query for iSeries function keys Using lists in Query for iSeries Printing what you see on your Query for iSeries displayDisplaying a Query for iSeries list Selecting items in a Query for iSeries list Special library names in Query for iSeriesSpecial Library Name Description Using Query for iSeries commands Using the Query Utilities menuQuery Choosing a single Query for iSeries task Using the Work with Queries displayQgpl Choosing multiple Query for iSeries tasksSpecifying a Query for iSeries query and its library =RunQuery for iSeries Use V5R2 Positioning a list of Query for iSeries queries Acctpayfeb AcctpaymarAcctrecfeb Qgpl Ccsid F12=CANCELDisplaying the format of constants in Query for iSeries QRY1Handling Query for iSeries messages and errors Query for iSeries profile informationChanging your Query for iSeries queries General operating information for Query for iSeries Query for iSeries Use V5R2 Creating a Query for iSeries query definition Selecting and sequencing fields in Query for iSeries Selecting sort fields in Query for iSeries Defining Query for iSeries report breaks Working with Query for iSeries query definitions Query for iSeries Use V5R2 Starting Query for iSeries query definition Creating a Query for iSeries query definitionDefine the Query Option Create Library Qgpl Selecting options for a Query for iSeries query definition Moving through the Query for iSeries definition displays Considerations for creating Query for iSeries queriesSelecting files, records, and fields for Query for iSeries Creating a Query for iSeries query definition Query for iSeries Use V5R2 Specifying file selections for a Query for iSeries query Specifying and selecting files for a Query for iSeries queryChoosing a file for a Query for iSeries query Choosing a library for a Query for iSeries queryFirst Exampfile Selecting multiple files for a Query for iSeries queryUsing file IDs for a Query for iSeries query Confirming your options for a Query for iSeries queryRemoving a option for a Query for iSeries query Custinv Selecting files on the Query for iSeries Select File displayCustinv Custrelmar CustCustinv Custrelfeb Custinv CustreljanSpecifying and selecting files for a Query for iSeries query EXAMPMBR1 EXAMPMBR2 EXAMPMBR3 EXAMPFMT1 EXAMPFMT2 EXAMPFMT3 Joining files in a Query for iSeries query EXAMPFILE1 Custinv FirstEXAMPFILE2 Custinv First Types of joins in a Query for iSeries query How to join files in a Query for iSeries queryRules for joining files in a Query for iSeries query NBR Name Phone AddrZIP NBR Specifying and selecting files for a Query for iSeries query Examples of joining files in a Query for iSeries query Specifying and selecting files for a Query for iSeries query Query for iSeries Use V5R2 Specifying and selecting files for a Query for iSeries query Correct method Incorrect method Query for iSeries Use V5R2 Displaying all join tests in a Query for iSeries query Handling missing fields in a Query for iSeries query Page Query for iSeries Use V5R2 Creating results fields in Query for iSeries Defining result fields in Query for iSeriesQuery for iSeries expressions Query for iSeries result field namePage Query for iSeries concatenation operation Query for iSeries character expressionsSubstr Query for iSeries function Query for iSeries character functionsValue Query for iSeries function Digits Query for iSeries functionVarchar Query for iSeries function Reschar Result Ccsid Description Character Vargraphic Query for iSeries functionDate, time, and timestamp expressions in Query for iSeries Query for iSeries time Query for iSeries dateFormat Name Abbreviation Time Format Example Query for iSeries timestampDisplaying constants format in Query for iSeries Subtracting dates in Query for iSeries Date arithmetic operation in Query for iSeriesSUBSTRCHARDATE,1,2’/’ Incrementing and decrementing dates in Query for iSeriesWorking with numeric dates in Query for iSeries SUBSTRCHARDATE,5,2’/’Yymmdd Mmddyy QtempDurations in Query for iSeries Time arithmetic operation in Query for iSeriesDate, time, and timestamp functions in Query for iSeries Char Query for iSeries functionTimestamp duration in Query for iSeries DAY Query for iSeries function Date Query for iSeries functionHour Query for iSeries function Days Query for iSeries functionMinute Query for iSeries function Microsecond Query for iSeries functionSecond Query for iSeries function Month Query for iSeries functionTimestamp Query for iSeries function Time Query for iSeries functionConverting date formats in Query for iSeries Year Query for iSeries functionMmddyy Yyddd Commands to run the query in example 4 are Odqysd NE Omcrmm EQ Omstat EQ ’50’ Odstat NE ’99’ Length and decimal positions in Query for iSeries Column headings in Query for iSeriesAdding or removing result fields in Query for iSeries Example of defining a result field in Query for iSeriesFirstname Selecting and sequencing fields in Query for iSeriesLetting Query for iSeries select and sequence fields Acctnumber Pastdue CompanyAcctnumber Pastdue Company Lastname Streetaddr City State Zipcode Telenumber Crlimit Company Baldue AcctnumberQuery for iSeries Use V5R2 Selecting records in Query for iSeries Letting Query for iSeries select recordsSelecting the records you want in Query for iSeries Comparison fields in Query for iSeries Comparison values in Query for iSeries Fields as values in Query for iSeries Character constants as values in Query for iSeriesNumeric constants as values in Query for iSeries Comparison tests in Query for iSeries Null values in Query for iSeriesTest Description Date, time, or timestamp comparisons in Query for iSeries Testing for equal EQ and not equal NE in Query for iSeriesTesting for values in a list List Nlist in Query for iSeries Lastname List Itemno List’ANNE EVA’ Lastname LikeExample 1 DBCSFLD1 Like ’s/oDD%%HH%%s/i’ Using more than one comparison test in Query for iSeries AND/OR Month List YearYear Baldue Year List Adding and removing comparisons in Query for iSeriesQuery for iSeries Use V5R2 105 Selecting sort fields in Query for iSeriesLastname Account number Additional sort considerations in Query for iSeries Sorted report looks like this Query for iSeries Use V5R2 Selecting a collating sequence in Query for iSeries Letting Query for iSeries select a collating sequencePurpose of a collating sequence in Query for iSeries How a collating sequence affects Query for iSeries Collating sequence and CCSIDs in Query for iSeriesSelecting a Query for iSeries collating sequence Query for iSeries Use V5R2 Defining your own collating sequence in Query for iSeries Selecting a translation table in Query for iSeries Selecting a system sort sequence in Query for iSeries F12=Cancel F13=Layout F18=Files F23=Save as default Column headings in Query for iSeries reports Specifying report column formatting in Query for iSeriesFormatting the columns of the Query for iSeriesquery report Column spacing in Query for iSeries reportsLength and decimal positions in Query for iSeries reports Lastname InitStarttime Omitting fields from a Query for iSeries report Defining numeric field editing in Query for iSeries reports Editing numeric fields in Query for iSeries reports=Edit word =Numeric editing choices=Date or time editing choice =Edit codeF10=Process/previous F12=Cancel F13=Layout Left negative sign in Query for iSeries reports Decimal point in Query for iSeries reportsThousands separator in Query for iSeries reports Show negative sign in Query for iSeries reportsRight negative sign in Query for iSeries reports Show currency symbol in Query for iSeries reportsLeft currency symbol in Query for iSeries reports Right currency symbol in Query for iSeries reportsSingle leading zero in Query for iSeries reports Print zero value in Query for iSeries reportsReplace leading zeros in Query for iSeries reports Replace with option in Query for iSeries reportsDate/time separator in Query for iSeries reports Edit code in Query for iSeries reports Specifying an edit code in Query for iSeries reportsInternal =Asterisk fill Specifying edit wordsOptional edit code modifier in Query for iSeries reports Edit word in Query for iSeries reportsF13=Layout F16=Remove edit F18=Files Edit word for summary total in Query for iSeries reports Query for iSeries Use V5R2 MAX Types of summary functions in Query for iSeries reports=Total Total=Count =Average=Minimum =MaximumSummarizing columns in Query for iSeries reports Name Total DUEFinal Totals Query for iSeries Use V5R2 Defining a Query for iSeries report break Defining Query for iSeries report breaksRegion DivisionRecords in file Field Skip to new page in Query for iSeries reports Suppress summaries in Query for iSeries reportsCompany Department Division Employee Region Break text in Query for iSeries reports =Printer =Database file 145=Summary only Form of output of Query for iSeries reportsLine wrapping in Query for iSeries reports =DetailWrapping width in Query for iSeries reports Record on one page in Query for iSeries reportsDisplaying wrapping widths in Query for iSeries reports Defining output to the printer in Query for iSeries reports IntegerDecimal Numeric Form width Printer device in Query for iSeries reportsForm size in Query for iSeries reports Form lengthPrint definition in Query for iSeries reports Start line in Query for iSeries reportsEnd line in Query for iSeries reports Line spacing in Query for iSeries reportsDefine Spooled Output Specify Cover Heading Footing F3=Exit F5=Report =Yes Qqryout FileLibcrtaut LIBCRTAUT, *CHANGE, *ALL EXCLUDE, *USE Query for iSeries Use V5R2 Page =Replace member =New file=Replace file =New memberExclude Authorization list nameLos Angeles Arcadia Using an output database file created by Query for iSeries Query for iSeries Use V5R2 Purpose of Query for iSeries processing options Specifying Query for iSeries processing optionsQuery for iSeries Use V5R2 Specifying Query for iSeries processing options Query for iSeries Use V5R2 Storing the Query for iSeries query definition Exiting and running a Query for iSeries queryEnding a Query for iSeries query definition Saving a Query for iSeries query definitionGiving authority to others to your Query for iSeries query Describing the Query for iSeries query definitionLibcrtaut Running a Query for iSeries queryQuery for iSeries Use V5R2 Running a Query for iSeries query using the Runqry command Query for iSeries Use V5R2 Working with Query for iSeries query definitions Changing a Query for iSeries query definitionChanges you can make to a Query for iSeries query Define the Query Prompt Changed Effect Considerations for changing Query for iSeries queries Acctrecfeb Inventfeb Mailaddr Copying a Query for iSeries query definitionDeleting a Query for iSeries query definition Renaming a Query for iSeries query definitionDisplaying a Query for iSeries query definition Printing a Query for iSeries query definition Information printed for a Query for iSeries query definition Breaklvl Working with Query for iSeries query definitions Query for iSeries Use V5R2 Part 3. Advanced information about Query for iSeries Query for iSeries Use V5R2 Files with different record formats in Query for iSeries File sharing considerations in Query for iSeriesOverriding database files in Query for iSeries Joining files in Query for iSeries Using *ALL in Query for iSeriesAddition and subtraction in Query for iSeries Multiplication in Query for iSeriesDivision in Query for iSeries PRN299B PRN299AChanging Decimal Precision Selecting records ignoring field case in Query for iSeriesQuery for iSeries Use V5R2 Part 4. Appendixes Query for iSeries Use V5R2 Operational Differences Conceptual DifferencesCalculation results Library defaultCharacter fields Sort sequence Result fieldsSystem/36 OS/400 System Query for iSeries Use V5R2 Iddu Appendix B. Practice exercise for Query for iSeries queryNameaddr SysdicAmount ADDRESS1ADDRESS2 ZIPADDRESS1 ADDRESS2 City State ZIP Amount USE, *EXCLUDE Query for iSeries query exercise Creating a database fileQuery for iSeries query exercise Entering data LIBCRTAUT, *ALL, *CHANGEWork with Data in a File EntryLastname Firstname ADDRESS1 ADDRESS2 City State ZIP Amount Lastname Firstname ADDRESS1 ADDRESS2 City State ZIP Amount =Yes, N=No Query for iSeries query exercise Creating a query KjoqryYourlib Nameaddrr BottomYourlib Ccsid CreateAPT C Change LIBCRTAUT, *ALL, *CHANGE EXCLUDE, *USESimpson Frank Daye BENQuery for iSeries query exercise Changing a query Change Press the Enter key. The Select Records display is shownPress F20 to display the right portion of the report ADDRESS2 City APTChange LIBCRTAUT, *CHANGE, *ALL EXCLUDE, *USE Cebqry Query for iSeries query exercise Creating an advanced query=== Wrkqry MainQname Qlidobjd QryfileOdlbnm Odobow Oddcen OdccenOdddat Odcdat Oddtim OdctimOddtim Display Time Odobnm Object Odobfr CLP Odobtp ListLibrary Object Attribute SizeOdobsz Object Size Odobtx Odlbnm Library Odobtp Object Type OdobatOdlbnm Library Odobtp Object Type Odobat Attribute Odobsz Object Size Odobtx Text Description Odlbnm Name Library Odobtp Object Type Odobat Attribute3 4 Break text Qname Name Object Qgpl File DspfQgpl File Tapf Qgpl Query for iSeries Use V5R2 Introduction to Query for iSeries query processing Keyed sequence access paths in Query for iSeriesAccess plans in Query for iSeries Select/omit access paths in Query for iSeriesUpdating access plans in Query for iSeries File definitions and data in Query for iSeriesFile definitions in Query for iSeries Defining queries for Query for iSeries File data considerations for Query for iSeriesDefine result fields in Query for iSeries Select and sequence fields in Query for iSeriesSelect records in Query for iSeries Select sort fields in Query for iSeries Select collating sequence in Query for iSeries Specify report summary functions in Query for iSeriesSelect output type and output form in Query for iSeries Using join operations in Query for iSeries Specify processing options in Query for iSeriesOption 1-Matched records Performance tips for join operations in Query for iSeries Option 2-Matched records with primary fileOption 3-Unmatched records with primary file Batch processing for Query for iSeries Miscellaneous tips and techniques for Query for iSeriesQuery for iSeries status messages Query for iSeries performance tuningQuery running. Building access path for file X in Y Query for iSeries migration considerations N to N-1Query running. Sorting copy of file *N in *N Query for iSeries debug mode messagesQuery running. Building access path from file X in Y Query running. Creating copy of file X in Y243 Query for iSeries Use V5R2 245 Query for iSeries Use V5R2 Ccsid marking in Query for iSeries CCSIDs and collating sequences in Query for iSeries Displaying a Query for iSeries query Query for iSeries language sequencesDefining a collating sequence in Query for iSeries Changing a Query for iSeries queryGetting a list of objects with text in Query for iSeries Defining a Query for iSeries querySelecting records at run time in Query for iSeries Running a default query in Query for iSeriesCcsid and result field expressions in Query for iSeries CCSIDs and Query for iSeries query definition itemsCcsid and file selections in Query for iSeries Ccsid and join tests in Query for iSeriesCcsid and summary functions in Query for iSeries Ccsid and result field column headings in Query for iSeriesCcsid and sort fields in Query for iSeries Ccsid and record selection tests in Query for iSeriesConsequence Ccsid compatibility considerations in Query for iSeriesCCSIDs Conversion Problems-Run-Time Consequences For displayed output, the run request continues, but all How CCSIDs Affect Query Use Query for iSeries Use V5R2 259 BibliographyQuery for iSeries Use V5R2 261 DB2 TrademarksDate Index Special CharactersCharacter substitution warning Command Currentdate ISO JIS Local Dec column data type Definition Display Example Field Function 189 List Heading Ccsid coded character set identifier 253 Defining Dbcs Like Report Sort Field Currenttime Currenttimezone ISO JIS Local Work with File Definitions display Iddu 202 Query for iSeries Use V5R2 Page SC41-5210-04