If the results of an SQL query are unexpected, collect
a copy of the SQL statement, the related DDL and catalog statistics,
and the EXPLAIN output to diagnose the problem.
Before you begin
To collect data to diagnose incorrect SQL results, you
need the appropriate Db2 administrative
authority.
Procedure
To collect data for incorrect output from an SQL statement:
- Obtain a copy of the SQL statement to investigate.
- Write a detailed description of the SQL statement results.
Describe whether the results returned contain unexpected rows, too
few rows, or too many rows. Also, describe any recent changes to the Db2 subsystem such as Db2 migrations, maintenance, application code
updates, or access path changes.
- Generate an EXPLAIN report of the query.
For
example, use the following EXPLAIN statement. Replace
query-number with
the row identifier in the PLAN_TABLE and
problem-SQL-statement with
the SQL statement.
EXPLAIN PLAN SET QUERYNO = query-number FOR
{problem-SQL-statement}
SELECT *
FROM PLAN_TABLE
WHERE QUERYNO = query-number
ORDER BY TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;
Collect service SQL documentation that describes the environment for the SQL statement, as
described in Collecting service SQL documentation.
