| |
| |
| Introduction | |
| |
| |
| Overview | |
| |
| |
| How Opnqryf Works | |
| |
| |
| Open Data Paths and Pointers | |
| |
| |
| Comparing Opnqryf to Logical Files | |
| |
| |
| Coding Opnqryf | |
| |
| |
| CL Requirements | |
| |
| |
| High-Level Language Requirements | |
| |
| |
| Summary | |
| |
| |
| The Keyfld Parameter | |
| |
| |
| Valid Values | |
| |
| |
| Variable Key Field Name | |
| |
| |
| Processing | |
| |
| |
| Accessing an Opnqryf File Randomly | |
| |
| |
| Special Sorting Problems | |
| |
| |
| Qualified Field Names | |
| |
| |
| Performance Considerations | |
| |
| |
| Restrictions | |
| |
| |
| Summary | |
| |
| |
| Record Selection | |
| |
| |
| The Basics | |
| |
| |
| Using Literals | |
| |
| |
| Operators | |
| |
| |
| Relational Operators | |
| |
| |
| Logical Operators | |
| |
| |
| Arithmetic Operators | |
| |
| |
| String Operators | |
| |
| |
| Built-In Functions | |
| |
| |
| Commonly Used Functions | |
| |
| |
| %Substring | |
| |
| |
| %Xlate | |
| |
| |
| %Max and %Min | |
| |
| |
| Special Functions and Equal Conditions | |
| |
| |
| %Range | |
| |
| |
| %Values | |
| |
| |
| %Wldcrd | |
| |
| |
| Special Functions for Null-Capable Fields | |
| |
| |
| %Null | |
| |
| |
| %Nonnull | |
| |
| |
| Syntax Considerations | |
| |
| |
| Priority of Operators | |
| |
| |
| Using CL Variables | |
| |
| |
| Using a Qryslt Variable | |
| |
| |
| Modifying a Static Qryslt Variable | |
| |
| |
| Record Selection in HLL Programs | |
| |
| |
| Performance Considerations | |
| |
| |
| Summary | |
| |
| |
| Assigning Values to Fields | |
| |
| |
| Syntax | |
| |
| |
| The Format Parameter | |
| |
| |
| The Mapfld Parameter | |
| |
| |
| Mapfld Techniques | |
| |
| |
| Substituting a Value | |
| |
| |
| Virtual Fields | |
| |
| |
| Date Arithmetic with Numeric and Character Fields | |
| |
| |
| Making One File Look Like Another File | |
| |
| |
| Preventing Division by Zero | |
| |
| |
| Variable Control Breaks | |
| |
| |
| Basic CL Logic | |
| |
| |
| Additional Basic CL Logic | |
| |
| |
| Logical Functions | |
| |
| |
| Other Functions | |
| |
| |
| Summary | |
| |
| |
| Joining Files | |
| |
| |
| Overview | |
| |
| |
| Join Parameters | |
| |
| |
| The File Parameter | |
| |
| |
| The Format Parameter | |
| |
| |
| The Jfld Parameter | |
| |
| |
| The Jorder Parameter | |
| |
| |
| Resolving Ambiguity of Field Names | |
| |
| |
| Join Types | |
| |
| |
| The Inner Join | |
| |
| |
| The Left Outer Join | |
| |
| |
| The Exception Join | |
| |
| |
| Variations | |
| |
| |
| Joining on More Than One Field | |
| |
| |
| Joining a File to Itself | |
| |
| |
| Unequal Joins | |
| |
| |
| Joining on Fields of Different Types and Formats | |
| |
| |
| Joining Through an Intermediary File | |
| |
| |
| Another Way to Do Date Arithmetic | |
| |
| |
| The Cartesian Product | |
| |
| |
| Using a Cartesian Product to Generate Percentage of Total | |
| |
| |
| Joining In the Qryslt Parameter | |
| |
| |
| Why Join In the Qryslt Parameter? | |
| |
| |
| Joining More than Two Files | |
| |
| |
| Performance Considerations | |
| |
| |
| Inner Joins Versus Left Outer Joins | |
| |
| |
| Access Paths | |
| |
| |
| Duplicate Test Conditions | |
| |
| |
| Key Fields from More Than One File | |
| |
| |
| Summary | |
| |
| |
| Date and Time Data Types | |
| |
| |
| Literals | |
| |
| |
| Date, Time, and Time-Stamp Functions | |
| |
| |
| Constant Functions | |
| |
| |
| Extraction Functions | |
| |
| |
| Conversion Functions | |
| |
| |
| Duration Functions | |
| |
| |
| Date, Time, and Time-Stamp Arithmetic | |
| |
| |
| Summary Processing | |
| |
| |
| Unique Key Processing | |
| |
| |
| Group Processing | |
| |
| |
| Grouping by Common Field Values | |
| |
| |
| Selecting Groups | |
| |
| |
| Grpslt Versus Qryslt | |
| |
| |
| Grouping into One Record | |
| |
| |
| Random Access | |
| |
| |
| Summary | |
| |
| |
| Reusing an Open Data Path | |
| |
| |
| Shared Open Data Paths | |
| |
| |
| The Posdbf Command | |
| |
| |
| Resetting the File Pointer from a High-Level Language Program | |
| |
| |
| The Optimize Parameter | |
| |
| |
| Summary | |
| |
| |
| The Cpyfrmqryf Command | |
| |
| |
| Debugging | |
| |
| |
| Copying to a File | |
| |
| |
| Using Cpyfrmqryf in Recap Jobs | |
| |
| |
| Using Cpyfrmqryf for Random Access Over Group Queries | |
| |
| |
| Summary | |
| |
| |
| Using Opnqryf in Interactive Programs | |
| |
| |
| The Optimize Parameter | |
| |
| |
| The Seqonly Parameter | |
| |
| |
| Mechanics | |
| |
| |
| Access Paths | |
| |
| |
| Summary | |
| |
| |
| Opnqryf in the Integrated Language Environment | |
| |
| |
| Activation Groups | |
| |
| |
| Scope | |
| |
| |
| Conversion from OPM to ILE | |
| |
| |
| Another Example | |
| |
| |
| Using Opnqryf in CL Programs | |
| |
| |
| Using Two CL Programs | |
| |
| |
| Using One CL Program | |
| |
| |
| Summary | |
| |
| |
| Opnqryf and the Year 2000 Issue | |
| |
| |
| Sorting Records | |
| |
| |
| Sequencing Technique 1: Adjust the Year | |
| |
| |
| Sequencing Technique 2: Generate a Century Digit | |
| |
| |
| Sequencing Technique 3: Joining | |
| |
| |
| Record Selection | |
| |
| |
| Selection Technique 1: Convert Dates | |
| |
| |
| Selection Technique 2: Alter the Boolean Logic | |
| |
| |
| Other Parameters of the Opnqryf Command | |
| |
| |
| Option (Open Option) | |
| |
| |
| Alwcpydta (Allow Copy Data) | |
| |
| |
| Igndecerr (Ignore Decimal Errors) | |
| |
| |
| Opnid (Open ID) | |
| |
| |
| Commit (Commitment Control) | |
| |
| |
| Type (Open Type) | |
| |
| |
| Dupkeychk (Check for Duplicate Keys) | |
| |
| |
| Strtseq (Sort Sequence) | |
| |
| |
| Langid (Language Identifier) | |
| |
| |
| Optallap (Optimize All Access Paths) | |
| |
| |
| Comparing Opnqryf to Fmtdta Data | |
| |
| |
| Contrasting Opnqryf and Fmtdta | |
| |
| |
| Advantages of Opnqryf | |
| |
| |
| How Not to Convert Fmtdta to Opnqryf | |
| |
| |
| Summary | |
| |
| |
| Performance Considerations | |
| |
| |
| Data Retrieval Methods | |
| |
| |
| Dynamic Processing | |
| |
| |
| Key Selection | |
| |
| |
| Key Positioning | |
| |
| |
| Index-from-Index | |
| |
| |
| Index-from-Data | |
| |
| |
| Sort Routine | |
| |
| |
| The Job Log | |
| |
| |
| Improving Performance | |
| |
| |
| Additional Considerations | |
| |
| |
| Common Runtime Errors | |
| |
| |
| Errors Caused by Invalid Parameter Values (CPF9899) | |
| |
| |
| Missing Operand in the Expression in the Qryslt Parameter | |
| |
| |
| Missing Operator in the Expression in the Qryslt Parameter | |
| |
| |
| Key Field xxxxx/yyyyy Not Valid for Record Format | |
| |
| |
| Key Field xxxxx on the Format Parameter Not Found | |
| |
| |
| Key Field xxxxx/yyyyy Not Allowed in the Record Format | |
| |
| |
| Field xxxxx Found in More Than One Record Format | |
| |
| |
| Example | |
| |
| |
| Character "x" Following String "xx" "Not Valid | |
| |
| |
| Example | |
| |
| |
| Other Runtime Errors | |
| |
| |
| Cpf4174 Opnid (xxxxx) for File yyyyy Already Exists | |
| |
| |
| Logic Errors | |
| |
| |
| Many Records | |
| |
| |
| No Records Selected | |
| |
| |
| Query Results May Differ From Intended Value of and1 | |
| |
| |
| The Report Has the Wrong Sequence | |
| |
| |
| Specifying Variable-Control Breaks | |
| |
| |
| Built-In Functions | |
| |
| |
| Single-Record Functions | |
| |
| |
| Other Numeric Functions | |
| |
| |
| Functions Allowed Only in Qryslt and Grpslt Parameters | |
| |
| |
| Grouping Functions | |
| |
| |
| Generating a Date-Conversion File | |
| |
| |
| Optimization Messages | |
| |
| |
| CPI4321 (Access Path Built for File and4) | |
| |
| |
| CPI4322 (Access Path Built from Keyed File and1) | |
| |
| |
| CPI4325 (Temporary Result File Built for Query) | |
| |
| |
| CPI4326 (File and1 Processed in Join Position and2) | |
| |
| |
| CPI4327 (File and1 Processed in Join Position and1) | |
| |
| |
| CPI4328 (Access Path %4 Used By Query) | |
| |
| |
| CPI4329 (Arrival Sequence Access Used for File and1) | |
| |
| |
| CPI432A (Query Optimizer Timed Out) | |
| |
| |
| CPI432C (All Access Paths Considered for File and1) | |
| |
| |
| CPI432D (Additional Access Path Reason Codes Used) | |
| |
| |
| Index | |