Re[2]: OPNQRYF versus LF -- RPG400-L


     Actually, it's not necessary to use the SQL licensed product (SQL 
     pre-processor) to do embedded SQL in a HLL. Take a look at DB2 CLI. 
     There's a section on this (with examples) in the new redbook 
     (Sorcerer's Guide). 
     
     I plan to start using this soon. We don't have SQL licensed 
     product, but do use several in-house tools for SQL on the command 
     line. 
     
     eric.delong@pmsi-services.com


______________________________ Reply Separator _________________________________
Subject: RE: OPNQRYF versus LF 
Author:  <RPG400-L@midrange.com> at INET_WACO
Date:    3/24/00 8:28 AM


Of course, then again, you could learn SQL instead.  Lower 
learning curve and bigger payback.  The only problem is, if 
you want to use imbedded SQL, you have to be working for a 
company that plans to be in business in two years.  They should 
have SQL on their box.
     
     
     
     
     
DBale@TFSA.Textron.com on 03/23/2000 03:39:36 PM 
Please respond to RPG400-L@midrange.com@Internet 
To:        RPG400-L@midrange.com@Internet
cc:        Osvaldo.Rodrigues@brainag.com@Internet 
Fax to:        
Subject:        RE: OPNQRYF versus LF
     
Osvaldo,
     
Do yourself a huge favor and learn OPNQRYF.  Any AS/400 programmer worth his 
or her salt must know how to use this valuable command.
     
In your case, check out the following example code:
     
     PGM                                                     
     OVRDBF     FILE(YOURFILE) SHARE(*YES)                   
     OPNQRYF    FILE((YOURFILE)) OPTION(*ALL) +              
                 QRYSLT('&FLD2A *eq %values("50" "60" "70")')
     CALL       PGMA                                               
     CLOF       OPNID(YOURFILE)                              
     DLTOVR     FILE(YOURFILE)                               
     ENDPGM                                                  
     
Using this method, you are not using any temporary files; you are creating 
and using a temporary access path created by OPNQRYF and used in PGMA.  You 
define the YOURFILE file like normal in PGMA and remove all the selection 
code from it.  OPTION(*ALL) in the OPNQRYF command is the key to being able 
to update records in this file.
     
Using OPNQRYF (instead of program data selection) should win every 
performance contest.
     
You can find out more about the possibilities available by using OPNQRYF by 
reading the Data Management Guide and/or (if available for your version of 
OS/400) "DB2 UDB for AS/400 Database Programming" and look for the section 
titled "Using the Open Query File (OPNQRYF) command".
     
HTH,
- Dan Bale
     
-----Original Message-----
From: Osvaldo Rodrigues [mailto:Osvaldo.Rodrigues@brainag.com] 
Sent: Thursday, March 23, 2000 1:04 PM
To: RPG400-L@midrange.com
Subject: OPNQRYF versus LF
     
     
Hello!
I have a PGM that does a selection on a LF file by one or more fields. In 
that
selection we have a field that you can
select more than one value (allows a maximum of 5) for that field. Lets 
suppose
that it is 2Alfa and you had entered values '50','60' and '70' for a given 
selection.
     
At this moment I am reading each record of the query file and comparing the 
refered field to each of those values (I
inserted them in an array). I think this is a litle bit hassle, running all 
the
file and for each record seeking the array.
     
I know that if I use the OPNQRYF  it would help restricting the number of 
records. But since it creates the query file as a temporary file and I also 
need
to do some updates to the file I dont know how to do this.
     
One last question how can I measure in therms of perfomance and processing 
time
the 2 ways ?
     
I must confesse that I never used the OPNQRYF only read some stuff about it.
     
Any help would be great!
     
Thanks a lot.
     
Osvaldo Rodrigues
AS400 Programmer
     
BRAIN Portugal
A BRAIN International Company
     
BIW Iberia- Informatica Lda
Av. Joao Canavarro, 305   Phone:   +351 252 248-120 
Edificio Alameda   Fax:   +351 252 248-111
4480-668 Vila do Conde
Portugal
e-mail:            Osvaldo.Rodrigues@brainag.com 
Internet:          http://www.brainag.com
     
NOTICE    This message contains privileged and confidential information 
intended
only for the use of the address see named above. If you have received this 
message in error please notify BRAIN Portugal immediately.
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com. 
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com 
+---
     
     
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com. 
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com 
+---
     


+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---