Difference between logical join file vs. view

25 pts.
Tags:
DB2/400
Logical Files
What is the difference between a Logical Join file and a view in DB2/400?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi RBauer925

Check this post it explain your answer
Thanks

Discuss This Question: 16  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • philpl1jb
    Join logical includes join view capabilities plus the related indexes.
    54,090 pointsBadges:
    report
  • TheRealRaven
    There are many differences even though SQL VIEWs are generally implemented as LFs. What specific problem are you having?
    34,430 pointsBadges:
    report
  • TheRealRaven
    Since some of the info in the link in the 'Answer' above is inaccurate, and even incorrect, and since it doesn't address JOINs at all, the 'Answer' might be disregarded.

    @philpl1jb: A JOIN LF need not be indexed. Of course, it can be indexed.
    34,430 pointsBadges:
    report
  • philpl1jb
    Opinion: Avoid both in programming. You'll write a little more code and have a little more programmatic control.
    54,090 pointsBadges:
    report
  • TheRealRaven
    Nowadays I'd agree with avoiding creating JOIN LFs directly, though only as a guideline. They should be used only with significant justification, and I can't think of any actual examples.

    But I strongly disagree with avoiding SQL VIEWs, even JOIN VIEWs. By pushing as much DB processing as possible down to the integrated/internal database level, all related efficiency goes up. Fewer transitions in and out of lower-level code to program code. Essentially no loss of flexibility when join-logic is actually needed. Multiple types of joins create the flexibility.

    If it's actually database logic, it should be defined as part of the database. It's external to program logic and so can be modified/maintained outside of program code. Separate expertise can be applied and can focus only on the relevant parts. A DBA can do the DB stuff, and a business programmer can do the program logic.

    More can be said, but this wouldn't be the proper thread for it.
    34,430 pointsBadges:
    report
  • RBauer925
    TheRealRaven - I'm not having a problem.  I'm just trying to understand the difference between the two.  Now I understand that a view is mainly for inquiry, whereas a Logical file can be used for updating the data.  I'm just comparing fields from 2 files and generating a report from the results. 
    25 pointsBadges:
    report
  • TheRealRaven
    You can also update through a VIEW, so it's not clear what's been learned. There's an example of a problem with such a question. Since it doesn't address an actual problem, there's no strong control over what forms answers will take.
    34,430 pointsBadges:
    report
  • TheRealRaven
    BTW, since the OP asked about "Logical Join file", they are also not updatable except under the same conditions as a JOIN VIEW, and the enhancement that allowed JOIN LFs to be updated was introduced primarily due to SQL being improved and it can only be implemented with SQL.

    It was practically merely a side effect that it benefited native JOIN LFs because of how VIEWs are implemented.
    34,430 pointsBadges:
    report
  • RBauer925
    TheRealRaven - I can tell from your responses, that, in your opinion, you are right, and everyone else is wrong.  Also, you are not very well like at work.
    25 pointsBadges:
    report
  • TheRealRaven
    @RBauer925: See INSTEAD OF Triggers - All Views are Updatable! from IBM, 2002, for example. I don't doubt that others remember what that brought to DB2 on i, including to JOIN LFs. From other things I've seen from him, I'm almost certain philpl1jb will recognize the background there. That article is a few years old, so it's not news.

    But that's not a "difference"; it's actually a similarity. Almost no differences have been mentioned (excepting philpl1jb's initial comment). Perhaps the single biggest advantage (difference) of VIEWs is missing. The ability to create VIEWs over other VIEWs (and then again) gives big capabilities to encapsulate a lot of logic in them. A set of well thought out VIEWs, using CASE structures, various predicates, sub-SELECTs, SQL functions, any of them at any logic depth of VIEWs, can make a powerful application database. When remote ODBC/JDBC, etc., is considered, having a robust database definition makes huge differences to client apps.

    Or see The power of user-defined table functions. While not about VIEWs explicitly, think about exposing some through a VIEW. Then try working out how it might be done with any native LF. Simply completing such an exercise would teach much about differences.

    Or see Native Regular Expressions In DB2 For i 7.1 And 7.2. If pre-7.1 is required, see Bringing the Power of Regular Expression Matching to SQL. As earlier, not explicitly about VIEWs but accessible. But native LFs?

    Much of the power of VIEWs comes from the rich set of SQL functions. Listing them side by side with those for native LFs is part of describing differences, and they haven't really been mentioned. Should they be? If not, then what exactly is the question?

    Now we've finally started an overview of differences, but really only started. Giving decent discussion is close to providing a training course in both VIEWs and native LFs. But it's not really appropriate for a problem solving forum. (See the site FAQ and read the 'Try to offer as much detail as possible about your situation' subtopic for an idea about expected questions. Also, note that there is an IT Discussions area separate from Questions, accessible from tabs at the top.)
    34,430 pointsBadges:
    report
  • TheRealRaven
    Odd... I entered the above a couple hours ago and got an error message back and no indication of successful posting. So I just entered a very different post, similar content/concepts, and the above post appeared instead. If the second one shows up, there's not much I can do about it other a general apology for a duplicate post.
    34,430 pointsBadges:
    report
  • philpl1jb
    System holds entries with hyperlinks for approval.
    54,090 pointsBadges:
    report
  • philpl1jb

    and Join Logicals can only be indexed on the primary

    (or did I make that up, too)

    54,090 pointsBadges:
    report
  • philpl1jb
    That would be fields from the primary file.
    54,090 pointsBadges:
    report
  • philpl1jb
    And Join Logicals can only be Keyed on fields in the primary.
    54,090 pointsBadges:
    report
  • TheRealRaven
    Yep, JOIN LFs require key fields from the first PF listed. I suppose JOIN VIEWs have a similar restriction since CREATE INDEX works over TABLEs rather than VIEWs. I don't know how much of an impact comes from creating INDEXes over all TABLEs in a JOIN VIEW and then querying with an ORDER BY that references multiple base TABLEs.

    Only way I know of to have an index that references multiple files is with the old OPNQRYF. But because that's created at run-time anyway, it doesn't seem much different from an ORDER BY over split key columns..
    34,430 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: