Join Logical File In As400 Example
AS400 Join Logical File and SQL View tutorial. ExtJs 4 checkcolumn example with Model data mappin. A join logical file is a logical file that combines. Is that we can create join logical file. How to create join logical file in as400. An example of using a UNION VIEW in the same capacity as. Is that we can create join logical file. How to create join logical file in as400. An example of using a UNION VIEW in the same capacity as.
Hello, I am working on some self learning on our iSeries. Trying to increase my SQL knowledge as well as other things. We have some LF (logical files) on the system and I am interested in dissecting them if this is possible. It has always been my experience that taking something apart is one of the best ways to learn. Is there a way to see what makes up the logical file? Or to phrase it another way: can I see how a logical file was created?
Is that even possible or is it just a file that has mysterious attributes of which I cannot see?? LF (Logical Files) are created one of two ways: 1) Compiled from DDS Source via the CRTLF command. If you created them in house or own source code for you, you can just inspect the source code. You an find the location of the source code (at least where it was at compile time using the DSPOBJD command and specifying the.SERVICE attribute. There are also a number of free utilities that you can use to generate DDS source from an existing file: 2) Created using SQL DDL using the CREATE VIEW or CREATE INDEX command.
Use the DSPFD command to view the DDL, QSQGNDLL API to retrieve the DDL programatically, or download this utility: - Gary Patterson. To say what Gary said in layment terms - a logical file is a 'view' of a physical file.
The logical itself does not contain any data. Think of it as a pre-built filter or sort over an existing physical file. If you have a data need where you want all the records retrieved by Date instead of Account #, and sorted by Date descending, you would have a logical built over the physical with those attributes. As Gary said, the DSPFD command will tell you about what the logical is doing. It will show you the key fields, their sort, etc. Towards the bottom, it will also tell you the physical file that it is built on.
Hope that helps! Thank you both for the replies. Give me a day or two to get some time and I will poke it it a little using the information you supplied and will see what I can see.
Most of these logical files were created by a person who works for us an outside consultant. He is much better at SQL and other AS400 things than am I. He is willing to teach me but I get tired of always asking how he does something.
Would like to figure some of it out on my own by taking apart the objects to see how they are made. By the way.here is some information I found when doing a Google search on this subject.
Is this relevant and what does it mean? Patrick Madison. 'A simple logical file is a different view of the physical file. It is actually a list of pointers to the physical file. Most of the time, a logical file is nothing more than a way of accessing the physical file with different key fields. With the standard AS/400 supplied tools, it is hard to see the logical file. One way is to use the copy file CPYF to copy the logical file to a new physical file.
Then, look at the physical file. It will be in the same order as the logical file'. Yes, you can copy a logical to a physical and it allows you to view the data with the sort applied by the logical. You should be careful with this though, because copying a file with the same name into another library can cause problems if programs run using '.LIBL' (using the users library list). You will want to make sure you copy them into a junk library that isn't used for anything and delete the copied physical file from the junk library when you're finished looking at it. I think the key to what you're trying to understand is 'what makes a logical'.
Programmers like to use logicals, because it keeps them from always having to apply the same type of sorts to their result sets in their applications. If my users always want a list of books sorted by Author, then Title, the ISBN, but my original physical file is sorted by a meaningless key, a logical is the solution. Anytime I create a report or present a data set that has the requirement of being sorted by Author/Title/ISBN, I use the logical. Programmers can create many different logicals over the same physical to account for multiple output formats. I'm glad that Carol clarified the definition of a logical file. Let me add a bit: PHYSICAL FILE (SQL TABLE) A physical file is a data container. It contains row after row of data, typically stored in the order that it is written to the file (there are some file settings like REUSEDLT and commands like RGZPFM that can change this physical ordering on disk, though).
Physical Files And Logical Files
On DB2/400, a physical file can also contain one optional INDEX. We call physical files that also contain an index KEYED physical file, and PF's that don't contain an index NON-KEYED physical files. Conceptually, a physical file contains either two or three sections: a HEADER that contains file description information, a DATA SPACE that contains a numbered list of records (RRN - Relative Record Number - the physical row number of the record in the physical file), and an optional ACCESS PATH (index), that contains a sorted list of keys and the record number from the physical file: LOGICAL FILE (SQL INDEX or VIEW) Locical files don't contain data. Instead, they maintain a list of keys, plus the RRN of the actual data record in the physicalk file. A logical file (SQL INDEX or VIEW) is really nothing more than a data structure used to organized the information in one or more physical files. Logical files can SELECT or OMIT certain records from the physical file, present the physical records in a alternate SORT orders, JOIN one or more physical files together, provide alternate KEYS for random lookup or SEQUENTIAL-BY-KEY processing, FILTER out certain columns, and perform other transformations like generating CALCULATED FIELDS that do not exist in the underlying physical files.
Programs can open and manipulate logical files as if they were real data containers. The DBMS, under the covers, uses the LF access path to look up records in the underlying PF, and presents the PF records to the program in the order provided by the LF access path. Conceptually, a lgical file contains two sections: A HEADER that contains file description information, and an ACCESS PATH, that contains a list of keys and the associated PF RRN. ACCESS PATH MAINTENANCE The database management system automatically maintains the access path in each logical file. This typically happens syncronously (immediately) with each update to an underlying physical file.
USING LOGICAL FILES IN PROGRAMS In RPG. For example, when you opening, read, write, update, or delete records in a logical file, the DBMS actually presents and changes the data from the underlying physical file. The logical access path is just used to control the order and selection of information presented to the program. As a result, you can manipulate logical files AS IF they contain data, even though they really just provide a link to records stored in a physical file. Gary Patterson.
FYI, you can get the same 'sort' information from viewing the DSPFD command for the logical IF your programmer has used field names which make sense and are 'readable'. Because the field names are limited to 10 characters, that can be a little tricky. If you page down a couple of times on the DSPFD screen you'll see some sections that look like this: Key field.: ITMLCLBKNB Sequence.: Ascending Sign specified.: SIGNED Zone/digit specified.:.NONE Alternative collating sequence.: No In our logical, this tells me the file is sorted by the Item Local Book Number in ascending order. Does that help? OK, sounds great. Thank you both again for taking the time to reply in such detail.
Logical File In As400
A lot of this is over my head but, I can hopefully absorb some or all of it over a period of time. I did take a few moments to use the DSPFD command on one of the logical files on the 400. This pretty much shows me what I was trying to get to. It show me the SQL view create statement - the SQL that was used to create the LF. And that is exactly what I wanted. I need to get better at SQL and now I have the luxury of seeing how someone else did something, what tables they used, the fields and the syntax of the SQL. In other wodrs - perfect!
Logical Examples Definition
The other information you provided is very useful as well.