RE: Problem with opening a file using embedded SQL.

Randy,

Have you tried building a separate index over the order number?  I'm
making the presumption that the physical file is indexed.  I don't
believe the optimizer will use the key on a physical file.  Run the
program in debug and look at the job log.  There should be a message
regarding the indexes considered and why each was not used.  There is
some other good information there also.

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kesterson, Randy
Sent: Tuesday, August 22, 2006 9:28 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Problem with opening a file using embedded SQL.

I'm having a problem with SQLRPGLE that I don't understand. 
I'm trying to do a very simple open of my main Order file to
fill a subfile display.  I prepare a dynamic selection field
which can get complicated later in the program, however, at
the start it is very simple.  At the first pass DYN_SEL
equals:
  Select * from "ORDER" order by ORODR#
The name of the file is ORDER and the field name ORODR# is
the Order Number which is the only key field to the physical
file. I've tried the select with and without the quotation marks.

When I get to the OPEN C1 statement, the program takes
forever to open the file.  The fetches and re-preparations
later in the program happen at lightning speed.  When I
display the Job right after the OPEN C1 I see the ORDER file
has an I/O Count of 13,705.  I believe what it's doing is
rebuilding the index for the file each time.  Why wouldn't it
just use the already existing index?

Here's a sample of the exec sql's:

C
c/exec sql
C+ PREPARE S1 FROM :DYN_SEL
c/end-exec
C
c/exec sql
C+ DECLARE C1 CURSOR FOR S1
c/end-exec
C
c/exec sql
C+ OPEN C1
c/end-exec





______________________________________________________________
__________
______________________
Randy Kesterson, Information Services

Market Transport Services  - a UTi Worldwide Company| 110 N
Marine Drive
| Portland, OR 97217 | USA
rpkesterson@xxxxxxxxxxxxxxxxxxx
<mailto:rpkesterson@xxxxxxxxxxxxxxxxxxx>
| D +1 503.978.4353 | O +1 503.283.2405 x353 | F +1 503.978.4392

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.