Collecting data for incorrect output from an SQL statement

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:

  1. Obtain a copy of the SQL statement to investigate.
  2. 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.
  3. 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;
    
  4. Start of change Collect service SQL documentation that describes the environment for the SQL statement, as described in Collecting service SQL documentation. End of change