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.
