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.

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.

Outcome of PLAN_TABLE

Issue the following SQL statement:

SELECT * FROM user-ID.PLAN_TABLE
WHERE APPLNAME = 'application-name'
ORDER BY Start of change EXPLAIN_TIME End of change, QUERYNO1, QBLOCKNO, PLANNO, MIXOPSEQ;

Refer for detailed PLAN_TABLE descriptions. Click here.

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

Reference click here

Related