• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: How To Override Query Options

    May 21, 2018 Ted Holt

    QAQQINI is a physical file with which you can control certain behaviors of the DB2 for i query engine. For a list of the query options that you can change, visit the IBM Knowledge Center. Normally you don’t have to use it, as the engineers of IBM have done a great job designing an optimizer that does a great job.

    At times you may want to change a query attribute for a certain job. There is no need to build many QAQQINI files to handle all possibilities. Instead, you can temporarily override query attributes within a job. DB2 for i provides the OVERRIDE_QAQQINI stored procedure for this purpose.

    I can best illustrate with an example. Here’s some code that Scott Forstie of IBM shared with me. Imagine this as part of in an SQL PL stored procedure.

     call qsys2.override_qaqqini(1, '', '');
     call qsys2.override_qaqqini(2,
        'PREVENT_ADDITIONAL_CONFLICTING_LOCKS', '*YES');
     LOCK TABLE TOYSTORE.SALES IN EXCLUSIVE MODE ALLOW READ;
    ( . . . do something with the SALES table here . . . )
    
    call qsys2.override_qaqqini(3, '', '');
    -- Deallocate the lock
    call qsys2.qcmdexc('DLCOBJ OBJ((TOYSTORE/SALES *FILE *EXCLRD *FIRST))');
    

    What does this mean? OVERRIDE_QAQQINI has three parameters. The first tells which action to take.

    • Use option 1 to create a temporary override file in QTEMP. This is obviously the first step.
    • Use option 2 to specify a value for one of the query options. This example has only one call with option 2, but there’s no reason you can’t have more.
    • Use option 3 to delete the temporary file.

    The default value of PREVENT_ADDITIONAL_CONFLICTING_LOCKS is *NO, which means that the system may allow other jobs access to a file in spite of the LOCK TABLE command. In this example, overriding restricts the access that other jobs have to the SALES table.

    In many shops, it is sufficient to create QAQQINI with appropriate values in library QUSRSYS. Thanks to OVERRIDE_QAQQINI, that one file does it all.

    RELATED STORIES

    QAQQINI Query Options

    OVERRIDE_QAQQINI procedure

    Fair lock option – PREVENT_ADDITIONAL_CONFLICTING_LOCKS

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: DB2 for i, FHG, Four Hundred Guru, IBM i, QAQQINI

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Big Blue Gives IBM i Shops A Special 30th Birthday Bash Box Past And Future Collide At PowerUp 18

    Leave a Reply Cancel reply

TFH Volume: 28 Issue: 37

This Issue Sponsored By

  • Fresche Solutions
  • UCG TECHNOLOGIES
  • Software Concepts
  • MiTEC 2018
  • LUG

Table of Contents

  • Syncsort Acquires Townsend’s IBM i Encryption Software
  • Past And Future Collide At PowerUp 18
  • Guru: How To Override Query Options
  • Big Blue Gives IBM i Shops A Special 30th Birthday Bash Box
  • Inside IBM’s SAP HANA On Power Playbook

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Security Still Top Concern, IBM i Marketplace Study Says
  • Bob Langieri Shares IBM i Career Trends Outlook for 2023
  • Kisco Brings Native SMS Messaging to IBM i
  • Four Hundred Monitor, February 1
  • 2023 IBM i Predictions, Part 4
  • Power Systems Did Indeed Grow Revenues Last Year
  • The IBM Power Trap: Three Mistakes That Leave You Stuck
  • Big Blue Decrees Its 2023 IBM Champions
  • As I See It: The Good, the Bad, And The Mistaken
  • IBM i PTF Guide, Volume 25, Number 5

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.