Coexistence, Oneworld indexes, should they be

Were a coexistence shop. Our coexistence install was done about 1 year ago.

When I look at some of our longer running UBE's (open files) I can see that there is no OneWorld index associated with the Physical file.

When I look in the business data library there are no OneWorld indexes for the Physical file in question, there are only World indexes.

When I look in PRISTDTA I can see all of the missing OneWorld indexes for the Physical files in my business data libraries.

My question is (and I hope you know) should these OneWorld indexes be in our Business data libraries? Shouldn't all of the OneWorld indexes be installed to the appropriate environmenst during the coexistence install?

PeopleSoft says they do not need to be, that the OneWorld UBE's will use a World index. I can see that sometimes a World index is used, but mostly not.

Thank-You,

XE, XU7, SP22_L1, OS/400 V5R2, A7.3. Cumm. 11

If you have a World index it will be used. The decision is made by Query Optimizer when the SQL statement is run. If you want to see why it's not picking up the World LF, and assuming you have Query Manger, you can see what the Query Optimizer is doing.

Here are the steps:

STRDBG (Start Debug)
go into Interactive SQL (STRSQL).
Run your Select statement from your UBE in question
exit from Interactive SQL,
stop debugging (ENDDBG).

Now, go to your job log (DSPJOBLOG) and look for "All access paths were considered for file XXXX". Do F1 on that line, which will take you to Addition Message. In there, you will see all the indexes and the reason why they weren't used and any suggestion for a new index....

Good luck,

Wes

Fortylove;
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/docvie...07511a1&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.