|
Hi, an SQL index never can be specified in a SQL statement. But an or several indexes are used by the query optimizer to get access to the data. You have to check which index is used or needed by using STRDBG command and analyzing the joblog or iSeries Navigator Visual explain. If an index is needed the joblog or the index advisor will show it to you. A DDS described file is used like an SQL view. That means the query optimizer only uses the field selection, select/omit clauses and joins. In the next step all indexes and keyed logical files are analyzed and the optimal access path determined. If no index can be found or used, either a table scan is performed or an temporary index created. Birgitta -----Ursprüngliche Nachricht----- Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Chaudhary, Sachin (GE Equipment Services,Consultant) Gesendet: Mittwoch, 6. April 2005 01:04 An: RPG programming on the AS400 / iSeries Betreff: RE: SQL Wichtigkeit: Hoch 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. -- 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
