Q: How do I see what indexes are being used by my SQL statements?

-Carrie Campbell

A: You can see the access plan of an SQL query by starting debug (STR-DBG) before you issue the Start SQL (STRSQL) command. After entering interactive SQL, type in and execute your query as normal. When your query is finished, press the SYSREQ key and press Enter. Select option 3 (Display current job); the Display Job panel appears. From the Display Job panel, select option 10 (Display job log...); the Display Job Log panel appears. Press F10 to display detailed messages. You might have to scroll back a bit, but eventually you'll see the SQL statement preparation and execution job log entries.

The second level help text of these messages, accessed by putting the cursor on the message and pressing Help, is usually very specific about which access paths were used and the reasons for the optimizer's decisions. Making sure the SQL statement uses an access path instead of the arrival sequence (meaning the query optimizer reads the file one record at a time for the entire file) can make a tremendous difference in the speed of your SQL statement.

- Brian Singleton

Brian Singleton is former editor of Midrange Computing. He has worked in the IBM midrange arena for many years, performing every job from backup operator to programmer to systems analyst to technology analyst for major corporations and IBM Business Partners. He also has an extensive background in the PC world. Brian also developed a line of bestselling Midrange Computing training videos, authored the bestselling i5/OS and Microsoft Office Integration Handbook, and has spoken at many popular seminars and conferences.

---

MC Press books written by Brian Singleton available now on the MC Press Bookstore.

		i5/OS and Microsoft Office Integration Handbook Harness the power of Microsoft Office while exploiting the iSeries database. List Price $79.95 Now On Sale