10 Impressive DB2 Explain Plan Validations You Need for SQL Performance

The best syntax for EXPLAIN PLAN

-EXPLAIN——————————————————> >–+-+-PLAN-+–+———————+–FOR–explainable-sql-statement-+-><    | ‘-ALL–‘ ‘-SET QUERYNO=integer-‘ |       +-STMTCACHE–+-ALL——————————–+—————–+       | +-STMTID–+-id-host-variable-+——-+ |       | | ‘-integer-constant-‘ | |       | ‘-STMTTOKEN–+-token-host-variable-+-‘ |       | ‘-string-constant—–‘ |       ‘-PACKAGE–| package-scope-specification |————————–‘

What happens when you issue EXPLAIN PLAN?

  • Specifies that access path information is captured for the SQL statement. Under this option, DB2 uses the access path selection process to generate the EXPLAIN records for the statement.
  • One row is inserted into the PLAN_TABLE for each step used in executing explainable-sql-statement. The steps for enforcing referential constraints are not included.
  • If a statement table exists, one row that provides a cost estimate of processing the explainable statement is inserted into the statement table. If the explainable statement is a SELECT FROM data-change-statement, two rows are inserted into the statement table.
  • If a function table exists, one row is inserted into the function table for each user-defined function that is referred to by the explainable statement.
  • If additional EXPLAIN tables exist, rows are also inserted into those tables.
How to EXPLAIN for Embed SQL
How to EXPLAIN for Embed SQL

Explanation for Set Query No?

SET QUERYNO = integer ==>Associates integer with explainable-sql-statement. The column QUERYNO is given the value integer in every row inserted into the plan table, statement table, or function table by the EXPLAIN statement. If QUERYNO is not specified, DB2 itself assigns a number. For an embedded EXPLAIN statement, the number is the statement number that was assigned by the precompiler and placed in the DBRM.

What are the detailed validations will happen from PLAN_TABLE?

Issue the following SQL statement:
SELECT * FROM user-ID.PLAN_TABLE
WHERE APPLNAME = ‘application-name’
ORDER BY Start of changeEXPLAIN_TIMEEnd of change, QUERYNO1, QBLOCKNO, PLANNO, MIXOPSEQ;

Refer for detailed PLAN_TABLE decriptions. Click here.

How to check DB2 SQL Queries taking longer time in Production?

Reference click here

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.