Re: E: Logical File or OPNQRYF or anyother way ?

Hi, Dave

I don't understand why you say use the term "legacy AS/400 flat-file 
architecture". I think there a misunderstanding here. A flat file, as I 
understand it, is a file which has records in which there are no fields 
(columns) defined. (And in some systems I suppose they might not even have the 
same record length.) That is what was used with early forms of RPG - and you 
COULD do it now - with I-specifications & O-specifications, where you define 
the layout of the data internally to the program. But that was not how it 
worked and works with what are called externally-defined files. Yes, there WAS 
a flat-file architecture in the System 36 - and I'm probably even a little 
wrong there. But there have been the equivalent of tables and indexes and all 
you mention since the '80s on the System 38. The 400 uses the same system and 
database architecture as the 38. The last time I actually USED what i think you 
would call a flat file in RPG was 15 years ago, in school, learning RPG II.

There is essentially no difference between the database on the i5 and that on 
the 38 - except for some things like triggers and referential integrity that 
were added more recently, at the time this "no-name" relational database got 
the same name as its IBM brethren. But the lack of those items did not make the 
database into flat-files.

There are charts that describe how SQL objects are implemented on the 400 - 
tables are physical files, albeit with some different attributes. Indexes and 
views are both logical files, inheriting all the general attributes thereof and 
adding the attributes that distinguish them. (This, BTW, is the beauty of the 
object-based architecture of this system.) A collection is nothing more than a 
library. Old fogies know of rows as records and columns as fields.

So there have ALWAYS been tables and appropriate indexes & views. We've even 
had keyed physical files all along, which are sort of like having a primary 
key, except they did not need to be unique, which disqualifies them for that 
role.

Should we use Static SQL? Maybe, although native programs called as stored 
procedures often provide better performance.

Now, I CAN create what looks like a flat file - CRTPF SOMELIB/SOMEFILE 
RCDLEN(132) will do it. But this is usually a very special case. I use that 
kind of thing for the output of a CPYSPLF command.Usually a file is created 
either using DDS or an SQL CREATE statement. 

Just for fun, create a DDS source member with a variety of data types - A25, 
S5,0, say. Create the PF from it. Then use an equivalent CREATE TABLE (CHAR(25) 
NOT NULL WITH DEFAULT,DECIMAL(5,0) NOT NULL WITH DEFAULT). Then execute DSPFD 
and DSPFFD against the 2 objects - they are very close - a couple little 
differences in attributes, but both can be accessed equally well using SQL or 
native I/O.

Create a view - it is a logical file that is essentially a container for the 
SELECT statement that will be run dynamically when you access that view. And 
the view can be used in an RPG just like a good old logical file. Create an 
index with SQL and an equivalent LF with DDS - again, DSPFD will show that 
there is an access path in both, and they will look almost identical.

I guess you pushed my OK button - sorry to wax wordy - I'm sure I misunderstand 
what you mean by a flat file, so please describe what that is from your 
experience.

Thanks
Vern

-------------- Original message -------------- 

> Charles, 
> 
> You wrote that which is quoted below and let me say BRAVO to someone 
> out of the very few on the iSeries that seems to understand the benefits 
> and ins-and-outs of SQL, logical files, etc. 
> 
> Let me also offer something in cursory form... if the developer has a 
> choice, use the full benefits of DB2/400 and instead of creating the 
> legacy AS/400 flat file architecture, create DB2 tables with appropriate 
> indexes, views, etc., and use Static SQL in a compiled program, not a 
> query language nor command nor anything that creates Dynamic SQL. If 
> you can, use stored procedures, especially if your application is 
> web-based and accessing the DB2 tables. 
> 
> Take care, 
> Dave 
> 
> "While others have pointed out that embedded SQL is an option (and one 
> I and others prefer over OPNQRYF), let me point a reason not to use a 
> logical. 
> 
> Each set of sort/select criteria needs its own LF. 
> 
> In contrast, SQL (and OPNQRYF?) can make use of composite keys when 
> they run. 
> 
> For example, lets say you have three fields: key1, key2, key3. To 
> provide for all possible sorts, you'd need 9 separate logicals with the 
> overhead or performance penalties associated with maintaining the access 
> paths. On the other hand, you could simply use 3 indexes, one over each 
> key. As needed the DB2 query engine make composite keys from the 
> separate indexes. 
> 
> Does this make sense? 
> 
> Additionally, one plus for embedded SQL is it's higher performance vs. 
> native RPG I/O. _IF_ you use it properly. This means, only select the 
> fields of interest to you and fetch more than one row at a time. 
> 
> 
> HTH, 
> 
> Charles Wilt 
> iSeries Systems Administrator / Developer 
> Mitsubishi Electric Automotive America 
> ph: 513-573-4343 
> fax: 513-398-1121" 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>