Re: New Advised Index not used -- MIDRANGE-L

Hi, Kurt:

This sounds like a thread from a few years ago ... see:

http://archive.midrange.com/rpg400-l/201312/msg00085.html

and be sure to read through the entire thread.

I do not recall the reasons why this can happen, but sometimes the system cannot update the SQL access plan embedded in the program or SQL package. PRTSQLINF against the *PGM or *SRVPGM may reveal if it is an "old" access plan (e.g. no mention of that new index?). If that is true, you might be able to issue CRTSQLPKG against that program to recreate the SQL package and that might help. Otherwise, if that fails, you could always just recompile the program from source, e.g. with CRTSQLRPGI ... REPLACE(*YES) and that will "wipe the slate clean" and create a new *PGM with a brand-new "access plan" and SQL package.

HTH,

Mark S. Waterbury

> On 8/13/2015 3:56 PM, Kurt Anderson wrote:

Hi,

I've been making an effort to go through our advised indexes and add them to help our system out. However I don't understand why after putting the index out there it is not being used and the system continues to advise for that very index I just added.

In this particular case, the SQL is in an embedded SQL RPG program.
When I copy that SQL (an update) and run it in STRSQL with STRDBG on, I see that it uses the new index. (I thought it might be hanging onto a MTI, as was the case in a previous index implementation). However when the program runs, it's not using the new indexes (and as I said, is asking for them) - and I see the TimesAdv value go up in SysIxAdv.

A little background: we have clients and each client has their own library. 4 of our clients run this particular process. For one of them it did use the new index, for the others it didn't.
I recreated the module & service program that has the embedded SQL in it (which is used by all clients).

The last time I put an index in that wasn't getting used (due to a MTI being used), the issue got resolved by an IPL. But in that case I could see it using the MTI when I ran STRSQL with debug mode on.
Other notes: It's not a library list issue. These are actual indexes (vs being logical files). We're at v7.2.

Short of attempting an IPL, does anyone have any suggestions?

Thanks,

Kurt Anderson
Sr. Programmer/Analyst - Application Development, Service Delivery Platform
[cid:image002.jpg@01CD16F9.25D27630]
web: www.customcall.com<http://www.customcall.com/> | email: kanderson@xxxxxxxxxxxx<mailto:kanderson@xxxxxxxxxxxx>

A Division of Enghouse Systems Ltd.<http://www.enghouse.com/> (TSX:ESL)

Join us for the 2015 CustomCall Users Forum, Oct 7-9 in Madison, WI. For more information or to register - click here<http://events.r20.constantcontact.com/register/event?oeidk=a07eatvdpg47dd505f3&llr=pmm6kddab&showPage=true>.