Explain Part 2 – Command Line Explain Plans Using db2exfmt

You may also like...

25 Responses

  1. Paul Bird says:

    Hi Ember,

    For parameter markers and temporary table scenarios (e.g. DGTTs and CGTTs), you can consider using the new explain from section functionality introduced in DB2 9.7… this also introduces the ability to get section actuals in the explain information.

    I hope you get a chance to cover this in a future post! πŸ™‚

    Thanks for the good reading ,


  2. Nice example Ember.

    When I read you query terms like “NLJOIN” and “I’d code a join” flashed thru my head. Later on you see that the optimizer had the same thoughts and re-wrote it te be a join.

    Good reading, keep it up!

  3. Tracy says:

    Ember, I love you! I am a relatively new DB2 LUW DBA and you are making me a better DBA every week. Your blog is fantastic and more helpful than I can explain. I have gained a tremendous amount of value from so many of your posts. You should take great pride and satisfaction knowing how your hard work is improving the DB2 community. Keep up the good work and thank you for making me better and more satisfied with my job.

  4. Murali Chava says:

    Ember this is one of the awesome st blogs in db2, u experience reflects in the way that you the things .. Thanks for helping around world..

  5. Kaushik says:

    Thank you so much. Was so addicted to control center. But this worked just fine.

  6. Rucha says:

    Hi..Loved ur article..really helpful.
    I tried generating explain plan..i got error SQL0035N the file “db2exfmt.msg” cannot be opened.
    Can you please help me this issue.

  7. Tu says:

    It’s realy useful to me :). thanks somuch

  8. Aravind says:

    Hi very nice . But my requirement to run many select statement need to explain in single execution. Please any one help on this

  9. Joe Hayes says:

    Hi, Ember πŸ™‚
    I’m sort of an accidental DB2 DBA, and I’ve learned so much from what you’re doing, here. Much obliged.
    Quick question: It seems like the parallelism value under the DatabaseContext is always set to “none” in db2exfmt. Do I have to capture an actual plan to get the parallelism? Or am I misinterpreting something, perhaps?

    Thanks again. πŸ™‚

    • Ember Crooks says:

      Do you have parallelism enabled via intra_parallel and possibly setting the dft degree? Are they queries with larger result sets that would benefit from parallelism?

      Research settings and drawbacks before enabling, if it’s not enabled.

  10. Santhosh says:

    Hi Ember,

    Thanks for the blog.

    I have one query.

    Do we have optimized way to perform self join?

    • Ember Crooks says:

      “It Depends”. I like to do self-joins as common table expressions. I’m not sure as to the performance comparisons of other methods.

  11. Raj says:

    Hi Ember,

    We are executing an SQL statement with db2advis but it is returning the error shown below.

    Explain tables not set up properly for schema

    The insert into the ADVISE_INSTANCE table has failed.
    0 solutions were evaluated by the advisor
    exiting with error code [-219]

    I had rerun the explain.ddl but i got same error. Please suggest me on this.
    Thank you.

  12. Birendra Kumar says:

    Hi Ember,
    Thanks for support. i am fresher DB2 DBA. After reading explain.Please How can analyze problem and give their solution.?
    Please help me

  1. June 18, 2013

    […] Please start with the first two articles in this series: Explain Part 1 – Explain and the DB2 Optimizer Explain Part 2 – Command Line Explain Plans Using db2exfmt […]

Leave a Reply

Your email address will not be published. Required fields are marked *