Troubleshooting
Problem
Without an ORDER BY clause, the result set order is not guaranteed to be in arrival order.
Resolving The Problem
SQL select statements that do not contain an ORDER BY clause might not return the result set ordered by the arrival sequence. This behavior is allowed by the SQL specification. The specification states, "When ordering of a cursor is not defined by an ORDER BY clause, the relative position of two rows is implementation-dependent." Although the SQL specification does not guarantee any ordering for these types of statements, older operating system releases (pre-2002) returned result sets for simple queries in arrival sequence. Db2 for i makes more extensive use of the symmetric multiprocessing (SMP) feature (if installed). This behavior can result in some select statements (which have no ORDER BY clause) to return results in what appear to be a random order.
Applications that relied on an unspecified ordering might fail after upgrading to V5R2. The problem is often seen in applications that use SQL to return non-relational data. For example, some applications use data transfer or ODBC to return files containing spooled file data or source physical file members. The unordered data might corrupt the resulting file. These types of applications must be modified to specify an ordering of the result set.
Ordering by Arrival Sequence
Result sets can be ordered by arrival sequence (the order the data is stored in the file) by using the relative record number function (RRN). To modify a query so that it returns result sets ordered as they were in previous releases, add an order by rrn(fn) clause, where fn is the file name, for example:
Disabling the SMP feature might also change the ordering to the older behavior. The SMP feature can be controlled through the QQRYDEGREE system value, the CHGQRYA command, or a QAQQINI setting. Because the record set ordering is still undefined, this technique is not recommended. A future PTF or release upgrade could change the behavior.
Applications that relied on an unspecified ordering might fail after upgrading to V5R2. The problem is often seen in applications that use SQL to return non-relational data. For example, some applications use data transfer or ODBC to return files containing spooled file data or source physical file members. The unordered data might corrupt the resulting file. These types of applications must be modified to specify an ordering of the result set.
Ordering by Arrival Sequence
Result sets can be ordered by arrival sequence (the order the data is stored in the file) by using the relative record number function (RRN). To modify a query so that it returns result sets ordered as they were in previous releases, add an order by rrn(fn) clause, where fn is the file name, for example:
Select * from QIWS.QCUSTCDT order by rrn(qiws.qcustcdt). This technique can be used only with operating system V5R2 and later because the order by field is not in the result set.Disabling the SMP feature might also change the ordering to the older behavior. The SMP feature can be controlled through the QQRYDEGREE system value, the CHGQRYA command, or a QAQQINI setting. Because the record set ordering is still undefined, this technique is not recommended. A future PTF or release upgrade could change the behavior.
Related Information
[{"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Host Servers","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB68","label":"Power HW"}}]
Historical Number
28874910
Was this topic helpful?
Document Information
More support for:
IBM i
Software version:
Version Independent
Operating system(s):
IBM i
Document number:
640119
Modified date:
22 January 2025
UID
nas8N1016738
Manage My Notification Subscriptions