Below is a post I made a few months back. What is the DB in question. DB/2 for iSeries, or UDB for unix or Intel.
IBM's redbooks website has some very large publications you can reference just for JDE on AIX & iSeries.
-------------------From the archive--------------
Do you have a multi-processor box? Are you using the SMP option if you have it?
Several items for you. This is not as straight-forward as it seems sometimes. 400 index selection & performance is a weird-science kind of thing. It takes practice.
A tool that I have used w/ very good success is IBM's redbook titled:
Using AS/400 Database Monitor and Visual Explain To Identify and Tune SQL Queries. See URL
http://www.redbooks.ibm.com/abstracts/redp0502.html?Open URL LINK for IBM SQL Db Mon and Visual Explain
ALSO- Very imporatant...Review the QAQQINI settings via the following document from IBM
http://www-1.ibm.com/support/docview.wss?rs=0&q1=QAQQINI+index+messages&uid=nas19fbb11f1725466578625694c007511a1&loc=en_US&cs=iso-8859-1&cc=us&lang=en
also check your QAQQINI for the following:
MESSAGES_DEBUG : Specifies whether Query Optimizer debug messages that would normally be issued if the job was in debug are displayed to the job log
Valid values are:
*DEFAULT The default value is set to *NO.
*NO No debug messages are to be displayed.
*YES Issue all message that would be generated for STRDBG.
My 2cents
JJ
JDE used to offer a freebie called DBSUGG that would collect the index debug messages and suggest queries for you. I do not know if it still exists. It was unsupported but very easy to use.
