Search code examples
sqlcursordb2cobol

Is there a default sort order for cursors in COBOL reading from DB2?

I am trying to understand how the following COBOL cursor works:

T43624     EXEC SQL
T43624         DECLARE X_CURSOR CURSOR FOR
T43624             SELECT
T43624                    A
T43624                   ,B
T43624                   ,C
T43624                   ,D
T43624                   ,E
T43624                   ,F
T43624             FROM
T43624                    X
T43624             WHERE
T43624                    L        = :PP-L
T43624               AND  M <= :PP-M
T43624               AND  N  = :PP-N
T43624               AND  O        = :PP-O
T43624               AND  P       = :PP-P
T43624               AND  Q        = :PP-Q
T43624     END-EXEC.

Given that there is no ORDER BY clause, in what order will the rows be returned? Could a default have been set somewhere?

Solution

  • There is no default sort order for results returned from a DB/2 select statement. If you need, or expect, data to be returned in some order then the ordering must be specified using an ORDER BY clause on the SQL predicate.

    You may find that results appear to be ordered but that ordering is just an artifact of the access paths used by DB/2 to resolve the perdicate. Simple queries requiring only stage 1 processing are often resolved using an index and these are typically ordered because the undelying index follows that order. This is totally unreliable and may change due to a rebind causing a different access path to be used or when the underlying index is in need of being rebuilt (after many insertions/deletions, lack of free space etc).

    Queries that require stage 2 processing tend to come out ordred, but this too is just an artifact of query resolution and should never be relied upon.

    COBOL does not excercise any inherent control over DB/2 operations other that what may be achieved using SQL alone.