RE: Embedded SQL Logical file use

Could you post some of the SQL code?  I would really help to see what
the statement looks like and how it's used in the RPG.


> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kyle Collie
> Sent: Thursday, March 30, 2006 8:27 AM
> To: RPG programming on the AS400 / iSeries
> Subject: Re: Embedded SQL Logical file use
>
> Yea, I failed to mention that I was posting for a colleague
> and I just pasted his text into the post. He is using
> SQLRPGLE. This is static SQL.
>
> I have suggested that he try adding the select/omit logic
> from his logical to the where clause, thinking that this
> might help give the optimizer a better clue as to what he is
> trying to do.
>
> He has written it in record level access and it runs in about
> the same total time. The 25% time lost while the optimizer
> churns would be a big boost if we could recover it.
>
> Does the access plan not get stored with the *pgm object?
>
> KC
>
> On 3/30/06, Michael_Schutte@xxxxxxxxxxxx
> <Michael_Schutte@xxxxxxxxxxxx> wrote:
> > Kyle, You mentioned that you are using SQLRPG, can I assume
> that you
> > meant SQLRPGLE?  If not, try converting the code to
> SQLRPGLE.  I have
> > a program that I prepare a statement and depending on the user
> > selection, I pick which file that needs to be used in the
> SQL statement.
> >
> > For example, if the user chooses to search the employee
> master by last
> > name, I chose a logical file that has the last name as the
> first part
> > of the key.  If user chooses company and department, then I
> choose a
> > logical file that has company and department first.  My processing
> > time in this program went from 5 minutes to 10 seconds.
> >
> > Something in the WHERE statement is causing SQL to search
> for a better
> > file.
> >
> > You could always not write it in SQL... Then you will not have to
> > worry about it.
> >
> > Michael Schutte
> > Work 614-492-7419
> > email  michael_schutte@xxxxxxxxxxxx
> >
> >
> >
> >              "Kyle Collie"
> >              <kcollie@xxxxxxxx
> >              m>                                            
>             To
> >              Sent by:                  "RPG List"
> <rpg400-l@xxxxxxxxxxxx>
> >              rpg400-l-bounces@                             
>             cc
> >              midrange.com
> >                                                            
>        Subject
> >                                        Embedded SQL Logical file use
> >              03/29/2006 06:41
> >              PM
> >
> >
> >              Please respond to
> >               RPG programming
> >               on the AS400 /
> >                   iSeries
> >              <rpg400-l@midrang
> >                   e.com>
> >
> >
> >
> >
> >
> >
> > We have an SQLRPG module that is opening a cursor based on a S/O
> > logical. Everytime the program is run the query optimizer
> is checking
> > for a better file to use and is eventually just coming back
> and using
> > the initial S/O logical that we specified in the select
> statement. Is
> > there a way to force the logical and prevent that
> processing time that
> > is wasted when it tries to find a better index?
> >
> > The time it takes up is about 1 minute 20 seconds.
> >
> > Thanks in advance,
> >
> > --
> > 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.
> >
> >
>
> --
> 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.
>
>
>

Privileged and Confidential.  This e-mail, and any attachments there to, is 
intended only for use by the addressee(s) named herein and may contain 
privileged or confidential information.  If you have received this e-mail in 
error, please notify me immediately by a return e-mail and delete this e-mail.  
You are hereby notified that any dissemination, distribution or copying of this 
e-mail and/or any attachments thereto, is strictly prohibited.