Tweet
This has baffled me for a while. I will run an SQL statement using the DB Monitor and see what indexes are suggested. I will create that index by clicking on CREATE and rerun the SQL statement and get the same suggestion. Upon further investigation I see that the engine created a temporary index. I look at DB Relations and the index is there. This doesn't happen all the time, just often enough to be annoying. TIA Bill Barnes
-
-
Monitor and Suggested Indexes
It may be possible that the advised index and the created temp index are used for different purposes. If U look into the job log to identify messages CPI4321, “Access path built for file &1.” and CPI432F, “Access path suggestion for file &1.”. In the detailed portion of these messages, there will be an indicated reason code that let U know why index was built or suggested. To verify if your created index is used or not, look into the detail of the message CPI432C, “All access paths were considered for file &1”. To do this, I suggest U run your SQL statement from "Run SQL Script" session (invoked from iSeries Navigator) with its option of "Include Debug Messages in Job Log" enabled. Then, use the option Visual Explain --> Explain to invoke Visual Explain session for your statement. In the VE session, U will see a "Message" tab of Query Engine feedback messages of that particular statement that U can browse for the details. These articles may be useful for you: http://www.ibm.com/developerworks/sy...5os/index.html JR. HOWARD F. ARNER "Optimizing the Optimizer" 7/1/99 12:01am
Comment