Logical File key not working

15 pts.
Tags:
AS/400
DDS
Logical Files
SQL
I am creating a new logical over a physical file. Very basic. Just one key field. It is a character file as the key. R RUB04000 PFILE(RUB0400F) K TRALER. After I create the logical file successfully and check the records, the records are NOT in the sequence as defined in the logical file DDS. I check in SQL (Select * from RUB0400L1) and also in Query/400. The logical is over the correct physical file. The sequence showing in the logical file records show as the keyed sequence of the physical file. I have NEVER seen this problem, I have checked everything. Any ideas would be greatly appreciated!
1

Answer Wiki

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

For WRKQRY, WRKQMQRY and SQL, using the default key defined in the DDS for a LF may not be the optimal situation when running a query. In your case if you are using just 1 file, it may make sense but you could be joining multiple files. Without a sort it will optimize the returned data. If no sort / order by was defined in the query or SQL the system decides the best sequence. Defining keys can be a big help with performance. We had one query that ran for 10-12 minutes. There was a join to a file with around 14 million records. We created a LF with the 6 keys matching our join and then used this file new LF and the query now takes about 15 seconds.

Discuss This Question: 6  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.
  • BigKat
    Unless you specify an ORDER BY clause, SQL is free to return the records in any sequence it wants to.
    9,420 pointsBadges:
    report
  • GregManzo
    What you are probably seeing is the records presented in arrival sequence of the physical, because you didn't specify an ORDER BY and that is the easiest way for SQL to return them. (computers are allowed to be lazy too...)
    Try using UPDDTA RUB0400L1 and just scrolling through the records one at a time, see if they are in the correct sequence that way? If so, then you now have confidence they will be in the correct sequence for your program.
    2,925 pointsBadges:
    report
  • Philcards
    Thank you. The UPDDTA does show the correct sequence. Does anyone know why the AS/400 query does not show the correct sequence? Query should use the key specified in the logical file. I still do not understand that AS/400 Query does not show the correct sequence.
    15 pointsBadges:
    report
  • ToddN2000
    It may be trying to optimize the query. Unless you specify a sort order for your query results it probably defaults to the PF key is one exists. If no key there then the next would be by RRN.
    128,395 pointsBadges:
    report
  • GregManzo
    If you don't specify an ORDER BY clause, then by definition you don't care what order the records are in so the query engine will go with whatever is easiest at the time. Sometimes it will be the primary key, sometimes it will be arrival sequence (RRN), and I dare say some other sequence is also possible depending on what other logicals & EVI's are created over the physical. But it will be whatever sequence is the *least* workload on the system overall.
    2,925 pointsBadges:
    report
  • Modiyooch
    I think your question is why doesn't Query/400 use my logical file key? Straight from the Query 400 manual:

    Note: When you select a logical file for use with Query or DB2 UDB for AS/400 programs without specifying any sort fields, unpredictable results may occur. For example, you may not receive the logical view of the physical file.

    *I always use query sort. The only time I rely on the LF definition is for select/omit considerations.
    850 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: