Results 1 to 2 of 2

Thread: Explain on CTE

  1. #1
    Join Date
    May 2005

    Unanswered: Explain on CTE

    Do we have any way to get an explain on a query with Common table expression?

    I can run db2exfmt, but that doesn't provide the subsection number.

    db2expln does provide that info, but doesn't seem to work for query with CTE. The -setup option helps for DGTT but doesn't seem to work for CTE.

    The query is spending most of the time in a particular subsection and the information about that would help in troubleshooting the delay.

    This is DB2 10.1.3 on Linux.


  2. #2
    Join Date
    Nov 2011
    hi, db2inst1.
    The CTE is not the same as dgtt to db2 optimizer.
    The optimizer always try to merge the cte to the main query instead of materialized the result of the cte。So sometimes we could not see the cte in the execution plan。
    And if you could,you can publish the execution plan ,maybe someone here can
    help you to determine the performance problem.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts