Re: Using an SQL index over DDS file?

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Loyd,

How many other indexes and or logical files exist over the physical?

It's possible the optimizer is timing out before having a chance to
see the new index.  But I'd expect you too need _lots_ of indexes for
that to be the case.  If it is the case, you should be able to see a
message to that from the optimizer in the joblog if you run the query
after a STRDBG.

Charles

On Mon, Jul 26, 2010 at 11:10 AM, Loyd Goodbar <loyd@xxxxxxxxxxxxxx> wrote:
> I created an index in our test environment to support a SELECT DISTINCT
> query. The file has 4 milion rows in test, and 8 million rows in production.
> The file is DDS-defined.
>
> The index is in test, but Visual Explain says a table scan will be
> performed, with the reason "No indexes exist". The query asks for the same
> columns as the index, and in the same order. DSPDBR reports the index is
> associated with the base file. I'm using the iNav "Run SQL Scripts".
>
> Here is the index and query:
> create index prod_mod.iml10026 on prod_mod.imp1001 (i1azcd asc, i1date asc,
> i1time asc, i1badt asc);
> select distinct i1azcd as location, i1date as observationdate, i1time as
> observationtime, i1badt as costdate from prod_mod.imp1001;
>
> The query will be utilized in embedded SQL in an RPGLE program.
>
> Is there anything I can do to influeunce how the query performs, to say "use
> this index"? This would be similar to SQL Server "hints". Is this a CQE vs.
> SQE issue? Are there any gotchas between running the query in Run SQL
> Scripts versus an embedded SQLRPGLE, other than naming convention and
> date/time formats?
> All is on V5R4.
>
> Thanks,
> Loyd
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.