AW: SQL

Hi guys,

here are some basics:
1. DDS described logical file:
   - Field selection 
   - Record selection by adding Select/Omit clauses
   - Sort sequence by adding key fields
   - Join between several physical files
   - Access path maintenance *IMMED or *REBLD
     (*IMMED if an unique key is specified on the physical file)

2. SQL defined index
   - contains only key fields
   - Access path maintenance always *IMMED

3. SQL defined view
   - everything that is possible with a SQL select statement
     with the exception of the ORDER BY clause.
   - It is possible to create a view over a view.
   - Access path maintenance alywas *REBLD

SQL view AND index can be used in the F-Specifiations like any
DDS described logical file. But an SQL view will never have a
specified sequence.

Within a SQL statement, only physical files, DDS described logical
files, SQL views or physical files can be specified, but never an index.
Specifying a logical file in a SQL statement will not force the 
query optimizer to use the access path specified in the logical file.
Specifying a logical file in a SQL statement will cause the query
dispatcher to reroute the query to the classical query engine (CQE),
so it cannot take advantage of the new SQE (SQL query engine).

Indexes are used by the query optimizer to determine the optimal
access path and to create the access plan. 
(If no SQL index is defined, the query optimizer will
consider the keys defined in logical files for searching the 
optimal index).

An SQL index has a page size of 64K, while a logical file only have a
page size between 8 and 32K (if you have some old keyed logical files
created before release V4R1M0 the page size even may be 4K).
An DDS described logical file can share access path with an other
logical file, that have the same or more key fields. It also can share
access path with SQL indexes.

An SQL index will only share access path with an other SQL index, if
it contains the same key fields in the same order.

Birgitta

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von rob@xxxxxxxxx
Gesendet: Freitag, 1. April 2005 00:12
An: RPG programming on the AS400 / iSeries
Betreff: 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.