DB2i SQL select max(field) not using keyed access path

9000 pts.
Tags:
AS/400
AS/400 Access Paths
AS/400 DB2
SQL
I have a file that contains a timestamp field. This field is also the key field for the file.
I am running a select statement in a UDF for the max value of that field, but according to the Query Access Plan: arrival sequence access was used for the file because the left-most key of the access path did not match any fields specified for the selection criteria. Therefore, key row positioning could not be performed, making the cost to use this access path higher than the cost associated with the chosen access method.
SELECT MAX(ts_fld) FROM file
Any ideas why the query optimizer is reading every record instead of hitting the keyed access path for the max?


Software/Hardware used:
IBMi 7.2

Answer Wiki

Thanks. We'll let you know when a new response is added.
If there are no suitable index or the main table was created (DDS) and no key specified (A bad practice used many places) use that File.
If there is an Index (LF-Logical file in DDS) by that field (as first part of key), use that Logical File.
If there is an Index (in and SQL created table), do not worry–you have no further control.

Discuss This Question: 10  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.
  • carlosdl
    I don't know how that database works, but in other databases, such as Oracle, when you define a primary key for a table, a unique index is created (if one doesn't exist) for the key column(s).  This index is a separate database object.

    When one executes a query to get the max of the key column, the database gets it from the index, but it still performs a full scan on it to look for the max, but that scan is still usually cheaper than scanning the table.  In those cases, the execution plan shows that the scan was performed on the index, not on the table, though.

    This is most likely not very helpful.  I just wanted to share that other databases might as well perform full scans of an object (the index in this case) to get a max.
    84,355 pointsBadges:
    report
  • ToddN2000
    I have run into some weird things working with DB2 databases. In one case a large file 20 million records was 10-12 minutes to run. Not what we needed. We created a LF on the iSeries side and ran the SQL select over that and it dropped to about 15 seconds.. Just a thought if you created a LF over the time stamp on the iSeries side.
    89,385 pointsBadges:
    report
  • TheRealRaven
    Is there a SQL INDEX defined over ts_fld? Is the file a SQL table? Whatever type of file it is, please copy/paste the 'Access Path' section from DSPFD here.
    21,845 pointsBadges:
    report
  • TheRealRaven
    On DB2 for i 6.1, I create a SQL table, rows for each access path of all PFs in all libraries. Then I create a SQL index keyed by library name. When I query SELECT MAX( APLIB ) FROM ACCPTHS, it scans the index to find the value.

    So in order to answer, we need to know the related elements.
    21,845 pointsBadges:
    report
  • BigKat
    It is a DDS-created PF with a DDS-created LF with the ts_fld keyed, but it is not unique.
    9,000 pointsBadges:
    report
  • BigKat
    sorry for the late response, I expected to get email notice when this was responded to, and I didn't get one.?
    9,000 pointsBadges:
    report
  • ToddN2000
    Are you querying the PF or the LF in your select statement?
    89,385 pointsBadges:
    report
  • BigKat
    I have tried it both ways with the same results
    9,000 pointsBadges:
    report
  • ToddN2000
    Just a thought, is your LF defined with the timestamp field as the key in DESCENDING order?
    89,385 pointsBadges:
    report
  • BigKat
    no, it is in ascending order
    9,000 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: