Check query plan

Diagnosing the problem

• Oracle:
  • You can view the query plan on Oracle based installations directly from within the DB Admin console. Paste the query into the SQL Command window and click Explain plan.
    Note: Do not click Run Query, especially if you are investigating an update, insert, or delete statement.
• DB2:
  • There exist various options to generate query plans with DB2, but the preferred option is to use the db2exfmt utility, as the generated output will contain most details.
  • Perform the following steps to generate the query plan:
    1. Log into DB2 server as instance owner.
    2. Ensure that tables exist or just recreate them. Details can be found in DB2 infocenter. For example:
       db2 connect to <database name>
db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C',CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))"

or

db2 connect to <database name>
cd ~/sqllib/misc
db2 -tvf EXPLAIN.DDL
    3. Save the problematic SQL statement in a file, for example, sql1.sql. Statements should be terminated by ;. Either use fully qualified table names (including the schema name) or pre-pend the SQL statement with a set current schema statement, for example:
       set current schema nes1011b;
select * from (SELECT itm_id, itm_primary_key pk , '' d
FROM tctg_itm_item
WHERE itm_id IN (
    select pk_row.ita_item_id
      from tctg_ita_item_attributes pk_row
     where pk_row.ita_node_id = 1657
       and pk_row.ita_value_string like '%335245'  ESCAPE '\'
       and pk_row.ita_next_version_id >= 999999999
       and pk_row.ita_version_id <= 999999999
       and pk_row.ita_catalog_id = 1602
fetch first 500 rows only ) AND itm_container_id = 1602
AND itm_version_id <= 999999999
AND itm_next_version_id >= 999999999
ORDER BY UPPER(pk)  ) AS OM98 fetch first 10 rows only;
    4. Use the db2exfmt command to generate a query plan:
       db2 set current explain mode explain
db2 -tvf  sql1.sql                                                  
db2exfmt -d <dbname> -# 0 -w -1 -g TIC -n % -s % -o sql1_exfmt.
db2 set current explain mode no
       The generated sql1_exfmt file contains a graphical query plan with statistical information for each data retrieval step.

       As an alternative, use the db2support feature to collect optimizer related diagnostic information.

    5. Run the following command as DB2 instance owner:
       db2support . -d <dbname> -sf search.sql -cl 1
       It will generate a db2support file with all of the required information, also showing the db2exfmt output. More details can be found in DB2 technote: Collecting Data for DB2 Compiler Issues.