RE: SQL -- RPG400-L

Hey Rob

I ran a query on an INDEX and it gives an error!
IMPILN00L1 in ILMOLIB not table, view, or physical file.
It took around 1 hour for index to be built and now it is not accepted!!
Don't know why only Physical Files/Logical Files(not useful in SQL as it starts 
building its own index at run time) AND Views are accepted by SQL Optimizer???

Thanks,
Sachin

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of rob@xxxxxxxxx
Sent: Thursday, March 31, 2005 4:12 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL


Before starting your query, start debugging.  For example:
STRDBG UPDPROD(*YES)
STRSQL
Then run your query.  When you exit the STRSQL do
DSPJOBLOG
You may see messages in there like "Dude, I might run faster if you create 
an index on file IMPILN00 in library ILMOLIB over the 
ILNSOLDTO field."

So, if you want to speed things up, you do a
CREATE INDEX ILMOLIB/IMPILN00L1
ON ILMOLIB/IMPILN00
(ILNSOLDTO ASC)

Now, there is a difference between Index, View and Logical File.
An index only creates a sort order.  You do not more than one physical 
file.  You do not select which fields to appear.  You do not select which 
records to appear.
A View can be created over one or more files.  You can select which fields 
to appear.  You can select which records to appear.  You CANNOT set a sort 
order.
A logical file can combine both of these functions.  There are some things 
a logical cannot do.  Like BLOB fields, UDF's and a host of other things.
I forget which, but only one of the index or view family can be used on a 
F spec in RPG, if you want to use traditional I/O versus imbedded SQL on 
occasion.  Basically that is the ONLY advantage of a logical file.  Well, 
I suppose if you want to get into the debate on whether it's better to 
store your source in QDDSSRC or QSQLSESS you can go there, but that's more 
a matter of style.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Douglas W. Palme" <dns@xxxxxxxxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
03/31/2005 04:49 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Re: SQL






That helped, thanks, another question kinda related, I noticed that when 
the 
query starts it displays the following: "Query Running.  Building Access 
Path for file IMPILN00 in ILMOLIB"

Can someone explain what that means building access path?


On Thu, 31 Mar 2005 13:43:20 -0800, Tony Carolla wrote
> Try this:
> 
> select ilnsoldto, sum(ilneprice) from impiln00 group By ilnsoldto
> 
> On Thu, 31 Mar 2005 13:40:20 -0800, Douglas W. Palme
> <dpalme@xxxxxxxxxxxxxxxxxxxxx> wrote:
> > As a follow up to the questions I had the other day, I've ordered the
> > appropriate license key for the SQL and have started working on 
learning
> > embedded sql to use with my rpg.
> > 
> > Needless to say, it'll delay some reports a bit but I think in the 
long 
run
> > it'll help.
> > 
> > One question I do have, I am running the sql interactive query and I 
am 
just
> > playing with it a bit, but have run into a problem that maybe one of 
you 
can
> > help me out with; considering the slightly different syntax between 
sql 
for
> > the iSeries and what I am used to.
> > 
> > I have a PF that cotnains among other fields, ilneprice (which 
contains
> > extended price amount), and a ilnsoldto field which is a reference 
field 
for
> > the customer number.
> > 
> > When I run 'select ilnsoldto' by itself it runs fine, when I run sum
> > (ilneprice) by itself its fine but when I run the following command it 

tells
> > me the ilnsoldto field is incorrect:
> > 
> > 
> > 
> > Suggestions? Comments?
> > 
> > --
> > 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..."
> -- 
> 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.


--
Open WebMail Project (http://openwebmail.org)

-- 
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.