RE: sqlrpgle select statements

I thought about that but if I ran it through STRSQL, it acted as
expected. In any event, this was a program that was put in place 6 years
ago so other than remembering that it happened and how we fixed it, I
haven't soent any time trying to figure out why in a long, long time.

Matt 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Tony Carolla
Sent: Thursday, March 02, 2006 11:48 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: sqlrpgle select statements

Could it be that when you run the select statement in these different
environments (batch vs. interactive), you are using different QAQQINI
files?  I am not sure how the job picks which QAQQINI file for query
options, but I believe it is by *LIBL.

Just a thought.

On 3/1/06, Matt.Haas@xxxxxxxxxxx <Matt.Haas@xxxxxxxxxxx> wrote:
>
> Jon,
>
> I did that and if it was called in batch (like through Ops-Nav or from
a
> remote machine), it used the logical it was supposed to. If called
> interactively, it would do full table scans every time. Not cool on 60
> million+ row tables. The optimizer messages we got said to create an
> index exactly like the ones it wouldn't use. I spent lots of time
trying
> to make this work and until I changed the compile setting, it didn't.
My
> guess is that it had something to do with either the environment it
was
> running in (JD Edwards World) or with how the files are defined (files
> with CCSID 37 and data with CCSID 65535) but changing either of those
> wasn't an option.
>
> Matt
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jon Paris
> Sent: Wednesday, March 01, 2006 4:05 PM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: RE: sqlrpgle select statements
>
> >> I've run into cases where logicals that exactly matched the where
> and
> order by clauses were not used.
>
> Run the program in debug or simply key the select into Op-Nav and have
> it
> analyze the SQL.  Just because there is an apparent exact match in a
> logical
> does not necessarily mean that it will be the fastest access route.
The
> debug statements in the Job Log will show you _why_ the query
optimizer
> rejected the path in question.
>
>
> Jon Paris
> Partner400
>
> www.Partner400.com
> www.RPGWorld.com
> -----Original Message-----
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>


--
"Enter any 11-digit prime number to continue..."
"In Hebrew SQL, how do you use right() and left()?..." - Random Thought
"If all you have is a hammer, all your problems begin to look like
nails"