As/400 logical file select statement

Field-level entries optional. Key field —level entries optional. Entries in join logical file:. Select and Omit field level entries.

There must one primary file and can have more than I secondary files. The format for this keyword is:. Inner join means the matching records in between the joining file will be selected. Left outer join all the records from primary file and matching records from the secondary file will be selected. A physical file can be joined to itself to read records that are formed by combining two or more records from the PF itself.

Non join logical file. Join logical file. We can able to insert or delete or update records using non-logical file. Insertion, updating or deletion of records is not possible in join logical files. DFU can be used to display non-join logical file. DFU is not available. Only one record format can be specified.

Commitment control is used. Commitment control cannot be used. Physical file. Logical file. Occupies the portion of memory. Does not occupy any memory space. Does not contain any data. A physical file contains one record format. A logical file can contain up to 32 record formats.

Can be exist even without LF. The LF can be deleted without deleting the PF. Is it possible to create "logical files" with sql statements?

If yes I would appreciate a sample statement. Improve this question. Aba Dov Aba Dov 4 4 gold badges 12 12 silver badges 20 20 bronze badges. Tracy Probst Hi, My original purpose was to create a materialized query containing a select on multiple tables for performance.

I think you may be a bit stuck on the nomenclature here. The "as" now called IBM i operating system describes files in one of two basic ways. Either a physical or a logical file. An SQL Table is like a physical. Indexes and Views are like logicals. Ref: wiki. Lynette Duffy Thanks for the Link. Add a comment. Active Oldest Votes. Improve this answer. Tracy Probst Tracy Probst 1, 11 11 silver badges 13 13 bronze badges. Thanks Tracy. Your answer is valuable. However I am looking for a solution that does not use "green screens" — Aba Dov.

It's just helpful because you can prompt. The commands outlined above will work just fine via ODBC. The optimizer will then use that index first, pick up the pointers to the id and slam over to the second index. Fast, fast. It's like connecting the dots : — Lynette Duffy. JMilnes JMilnes 11 2 2 bronze badges.

Confusion Confusion The problem with views is that when you need to fetch data from them, their "build" query is executed. Logical files are similar to materialized view, are updated automatically but instead of saving the data they have "pointers" or indexes of the table rows they are based on. Aba Dov : It's true that opening a view can take longer than opening a native LF about. The crossover can be after reading maybe 75K rows or so. So the number of rows to process can be a factor, especially if tables get large.

For the largest tables, the view can easily outperform a LF. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name.