SQL FETCH order problem due to incorrect access path choice - IBM AS400

This is a discussion on SQL FETCH order problem due to incorrect access path choice - IBM AS400 ; I'm having a problem with some embedded SQL whose behaviour has changed since the program was originally written. I'm trying to SELECT all records where a Field A has a particular value, then read those records sequentially. From STRSQL, everything ...

+ Reply to Thread
Results 1 to 7 of 7

Thread: SQL FETCH order problem due to incorrect access path choice

  1. SQL FETCH order problem due to incorrect access path choice

    I'm having a problem with some embedded SQL whose behaviour has
    changed since the program was originally written. I'm trying to SELECT
    all records where a Field A has a particular value, then read those
    records sequentially. From STRSQL, everything works as expected. The
    program used to function as expected, but now does not.

    From debugging, I see the system is using an access path of a DDS
    logical file that is keyed on Field A and Field B (no select / omit
    criteria). This causes a problem because the program then receives
    matching records sorted by Field B, not matching records in sequential
    order.

    I'm not sure whether this query is being handled by CQE or SQE (even
    though I'm using STRDBG, I can't see any messages in the Joblog to
    indicate which is used).
    IGNORE_DERIVED_INDEX is currently set to *NO by default (we are on
    v5r3), I tried changing this to *YES and running the program again,
    but (as expected) this made no difference (the logical file access
    path was still used).

    How do I get the system to return me the records in the original order?

  2. Re: SQL FETCH order problem due to incorrect access path choice

    At first glance it seemed adding a simple ORDER BY RRN(Field A) would
    work, but supposedly this is not reliable when REUSEDLT is set to
    *YES.

    Further investigation reveals that the underlying physical is itself
    keyed on Field A. When the program is running, the optimizer
    determines that using the logical file is quicker; under STRSQL the
    optimizer decides that the using the physical file is quicker (which I
    guess means that one route is going via SQE and the other via CQE,
    though I still don't know which is which - my guess is STRSQL uses
    SQE).

  3. Re: SQL FETCH order problem due to incorrect access path choice

    >When the program is running, the optimizer
    > determines that using the logical file is quicker; under STRSQL the
    > optimizer decides that the using the physical file is quicker (which I
    > guess means that one route is going via SQE and the other via CQE,
    > though I still don't know which is which - my guess is STRSQL uses
    > SQE).


    Both embedded SQL and STRSQL will use SQE if its available. The
    difference is that STRSQL is optimized to get the first record as fast
    as possible, whereas embedded SQL (or batch) is optimized to get the
    entire result set as fast as possible. Sometimes, this results in
    different access paths being used.

    The IGNORE_DERIVED_INDEX set to *NO means that if ANY logical file
    over this PF has select/omit (or any any derived keys), then CQE is
    forced. If this isn't the case, it may not make any difference.

    Note also that the access path selection may change over time due to
    differences such as how many records in the file, estimated returned
    records, and amount of memory available. If you need a specific
    sequence, you need to specify in ORDER BY clause.





  4. Re: SQL FETCH order problem due to incorrect access path choice

    Hi,

    >>Further investigation reveals that the underlying physical is itself
    >>keyed on Field A. When the program is running, the optimizer
    >>determines that using the logical file is quicker; under STRSQL the
    >>optimizer decides that the using the physical file is quicker (which I
    >>guess means that one route is going via SQE and the other via CQE,
    >>though I still don't know which is which - my guess is STRSQL uses
    >>SQE).


    This has nothing to do with CQE or SQE, but with different
    optimization goals.

    All dynamic SQL interfaces such as STRSQL are optimized to return the
    first block of results as fast as possible (Optimization goal =
    *FIRSTIO) per default . Static interfaces (for example static embedded
    SQL) on the other hand are optimized to return the complete result set
    as fast as possible (optimization goal = *ALLIO).

    To affect the optimization goal just add OPTIMIZE FOR x ROWS at the
    end of the select statement. If x is a rather small integer value,
    optimization goal *FIRSTIO is used. If x is a large integer value or
    ALL, optimization goal *ALLIO is used.

    Birgitta

  5. Re: SQL FETCH order problem due to incorrect access path choice

    Thanks Graybeard - I've gone with ORDER BY RRN for now, because I've
    realised that this file will never be changed to REUSEDLT *YES.

  6. Re: SQL FETCH order problem due to incorrect access path choice

    Thansk Birgitta - I've gone with ORDER BY RRN for now, but the
    OPTIMIZE FOR approach sounds like it might be useful in future.

  7. Re: SQL FETCH order problem due to incorrect access path choice -actually, for no ORDER BY requested

    Let me reiterate the important part. Beyond that OPTIMIZE FOR a
    number of rows can _influence_ the optimization to *possibly* yield the
    same order as seen in another interface [or even same interface, at a
    prior invocation] for the same request, the *only* way to ensure any
    particular order is by *explicitly* stating _ORDER BY_ for the SELECT.
    SQL is set-based, and order has no implicit meaning in a set; i.e. if
    the result set returned is required by the requester to have some
    specific ordering, then ordering must be explicit in the request, else
    the ordering is left to the implementation details [which may not remain
    the same even with both the same requested /influences/ and /statement/
    from one invocation to the next].

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    walker.l2 wrote:
    > I've gone with ORDER BY RRN for now, but the
    > OPTIMIZE FOR approach sounds like it might be useful in future.


+ Reply to Thread