More about SQL and Logical Files
April 16, 2008 Ted Holt
|
Last week I advised you to avoid referencing logical files in SQL queries. This week I have a little more information for you regarding SQL and its love-hate relationship with logical files. This week’s tip: In your QAQQINI file, set IGNORE_DERIVED_INDEX to *YES. So what does that mean? First, QAQQINI is a file that you can use to control query processing. If you’re not familiar with it, follow the links at the end of this article to learn about it. At this point, I assume you know what QAQQINI is and that you have duplicated it from QSYS to QUSRSYS using the Create Duplicate Object (CRTDUPOBJ) command. Use your favorite database editor to add or update a record in QAQQINI. You can also use SQL statements like the following ones:
UPDATE QUSRSYS/QAQQINI
SET QQVAL='*YES'
WHERE QQPARM='IGNORE_DERIVED_INDEX'
INSERT INTO QUSRSYS/QAQQINI
VALUES('IGNORE_DERIVED_INDEX','*YES',NULL)
To understand what this setting does for you, let me remind you that the database engine has two query optimizers, the Classic Query Engine (CQE) and the SQL Query Engine (SQE). When you reference a physical file that has a DDS-defined logical file with select/omit criteria, SQE gives up and lets CQE take over optimization. However, if IGNORE_DERIVED_INDEX is set to *YES, SQE will ignore the unusable logical file and optimize the query. As of V6R1, the default setting is *YES. If you’re not using a QAQQINI file for your queries, V6R1 will assume you want to ignore the invalid logical files. For earlier releases, the default is *NO and you’ll have to use QAQQINI to turn on this setting. If you like this performance tip and its predecessor, you may be interested in a class that IBM puts on. You can find more information at http://www-03.ibm.com/systems/i/software/db2/db2performance.html. RELATED STORIES SQL Doesn’t Like Logical Files Adjust Default Query Optimizer Settings with QAQQINI Controlling Queries Dynamically with the Query Options File QAQQINI
|
