IBM Support

IBM i - DB2_NUMBER_ROWS information

Troubleshooting


Problem

I'm using the GET DIAGNOSTICS statement to obtain information about the previous SQL statement that was executed with my IBM i application and I'm noticing that the DB2_NUMBER_ROWS value being returned isn't always very accurate.  In some situations it is off by a large amount.

Cause

On the IBM i, DB2_NUMBER_ROWS uses an internal algorithm to determine the value to return; it's not always possible at open time to know how many rows will actually return.

Resolving The Problem

If the IBM i Query Optimizer uses a temporary result for the processing of the SQL statement then the DB2_NUMBER_ROWS is more likely to be accurate.  Here are some examples of why a temporary result may be needed.

1 - The query contains grouping fields (GROUP BY) from more than one file, or contains grouping fields from a secondary file of a join query that cannot be reordered.

2 - The query contains ordering fields (ORDER BY) from more than one file, or contains ordering fields from a secondary file of a join query that cannot be reordered.

3 - The grouping and ordering fields are not compatible.

4 - DISTINCT was specified for the query.

5 - Set operator (UNION, EXCEPT, or INTERSECT) was specified for the query.

6 - The query had to be implemented using a sort. More than 120 key fields specified for ordering.

7 - The query optimizer chose to use a sort rather than an access path to order the results of the query.

8 - Perform specified record selection to minimize I/O wait time.

9 - The query optimizer chose to use a hashing algorithm rather than an access path to perform the grouping for the query.

10 - The query contains a join condition that requires a temporary file.

11 - The query optimizer creates a run-time temporary file in order to implement certain correlated group by queries.

12 - The query contains grouping fields (GROUP BY, MIN/MAX, COUNT, etc.) and there is a read trigger on one or more of the underlying physical files in the query.

13 - The query involves a static cursor or the SQL FETCH FIRST clause.

Document Location

Worldwide

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"DB","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Document Information

Modified date:
18 December 2019

UID

ibm10874408

Page Feedback