https://www.google.se/search?q=opnqryf+vs+logical+file&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-GB:official&client=firefox-a

My humble opinion is LF if usage is high and/or ongoing, and the selection criteria is fairly simple.. Otherwise, Opnqryf (if you can figure out the weird syntax).

 

Somewhat incomplete answers.

The main deciding factor FOR a logical file is maintenance of the access path - unique key needs to be enforced, a key is used for interactive retrieval, you want to access the file immediately.  But having too many immediate access paths creates an overhead - try creating a physical file, add about 30 logical access paths to it, then copy 1million records to the file and you'll immediately see the problem.  Every time you write a record (or change a key field) the access paths need to be updated - the more access paths, then bigger the overhead.

Outside of the immediate access need, there are actually 3 options.  Probably the simplest is to change the MAINT on a logical file to either (*DLY or *REBLD), delay prevents the access path from being rebuilt every time you write or update a record, so the access path maintenance overhead is significantly lowered.  However, when the file is opened, if there are new records on the file (since last open) then the access path will be rebuilt (if nothing has changed on the file then no rebuild is needed), causing a delay in reading the file - this is fine for batch jobs, not so much for interactive jobs or processes called from web pages.  While the access path is open it is treated as an Immediate rebuild, until closed.  Delay also uses up the same disk space as an immedidate maintained access path.  The rebuild option destroys the access path when the file is not in use, this means that there is always an access rebuild every time the access path is opened, this version saves on disk space, but is probably less significant than it used to be, considering the storage abilities of the system today.  The other access options are OPNQRYF and SQL - and to be honest there really is no need to resort to OPNQRYF, it is part of IBM AS/400 legacy (not being improved/maintained) and SQL is far improved over it.

Hi all, i am starting on AS400 platform so this was one of my questions. All i had in my mind earlier was that in case if the requirement is a long term or will be used very frequently then we'll go for a logical file becuase the logical file object is created in the system; but if the requirement is dynamic and is not used quiet often then we go for OPNQRYF because the scope is within the program. Please correct me if i am wrong. Also there are other factors like performance issues and speed. i guess i missed this accesspath concept.

Sorry, Neil, but SQL is still a chargeable item; not everyone has it.

For the original question, a logical file is a prebuilt access path. OPNQRYF runs a query. If you'd like to phrase it in SQL terms, an LF is a combination of an INDEX and a VIEW; whereas OPNQRYF is the SELECT statement. They're related, but it's not one or the other.

OPNQRYF? Like left hand indicators, it's a very old tool, not used much anymore. I'd go with a LF with the correct key structure with possibly SELECT/OMIT and/or embedded SQL in RPG. But that's just me.

Chris

OpnQryF itself is very powerful, although a bit bulky and i am not impressed with its syntax. i find it good for batch programs, generally running overnight.

i generally use LF view with a proper select/omit. this can also be bulky, but the syntax is very easily understood as long as we are not talking multiple format file de!--script--ions.

my preference is SQL and of that, SEQUEL. i prefer SQL because it can be embedded, but i really, really like the fact that SEQUEL SQL can be placed in the CL, if people still use CL, that is. :)

-sarge

In legacy code the decision was often made on when the program was written, and then later on, many programmers simply used OPNQRYF instead of logicals because it was "easier".

Today OPNQRYF has been deprecated by embedded SQL and on v7.1 the upcoming RUNSQL command. Granted you have to pay money for stuff, but if you're looking for a recommendation on whether to use a dynamic database access language/method vs a Logical View then the answer is this:

If you need to access the file using native RPG IV I/O opcodes such as SETLL and CHAIN, then a Logical File is best.

If you need to access the file using embedded SQL or simply need the data sequenced and read "sequentially" in RPG, then a dynamic database language like SQL or OPNQRYF is best.

Having said that, SQL can also be used to create an SQL INDEX or SQL VIEW. On IBM i, these SQL objects are "logical files". So you could use SQL's data definition language to create logical views. I'm sure its not perfect yet so a simple DDS-based Logical is often a good choice too.

But for the record, OPNQRYF is almost reaching the level of "format data' (ie., S/36 SORT) in its reputation in software development. in other words, the only reason it is still being added to code today is because the Programmer hasn't taken the time to learn how to "do it in SQL" yet. Or the other "management made me do" reason.

You said that SQL is a chargeable item. Well SQL is built into the operating system so to speak. The SQL development kit is a "chargeable" item (STRSQL), but one can run all the SQL statements they want from inav.

True, inav is an alternative to STRSQL (which is part of the chargeable ST1). But inav does not give you precompilers for embedded SQL programming.

what it really boils down to is: is the company willing to pay for a supported product that will open other, better (in many cases) avenues of approach to generating solutions.

embedded SQL, or for that matter, SQL is like any other language in that it is the right tool at the right time....in other words, when you need it and it can be convenient.

there is a small learning curve with SQL because of all the things it can do. i used SQL a little bit, since JDE did not, but when i bought the book SQL/400, it opened up a whole new world where i could define tables, functions, etc.

-sarge

Just a short question:

Obvious SQL is the way to go. But I do seem to have understood that you can actually create and compile SQL RPGLE programs without having the actual SQL license. This is something I cannot check as all my customers are doing the smart thing and have a paid version of SQL installed. Can anyone verify this dilemma?

Paulster, You can run programs with embedded SQL on any machine. The run-time is part of the OS. But the precompilers are part of the ST1 licensed program product. That's where the "Dev Kit" part of the product name comes in to play.