Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23

    Unanswered: SQL with the same execution plan

    Hello,

    How can I find in DB2 LUW 9.5 which SQLs have the same execution plan? I doesn't found admin view (in sysibmadm) that contains unique identity of the sql in cache (hash, id or other identity) and execution plan identity. Is it possible to find identity for the sql and plan and then select in some admin views sqls by plan identity. I can give a simple example:

    SLQ with ID=200 has plan with ID=20
    SLQ with ID=323 has plan with ID=67
    SLQ with ID=400 has plan with ID=20

    I know that my "bad" SQL has identity 200 and plan with identity 20. I would like to find other SQLs that have the plan with identity 20. In this example it is SQL wth identity 400.

    Is it possible?

    In Oracle I use V$SQL and V$SQL_PLAN. In DB2 I use now sysibmadm.snapdyn_sql but it has no sql indentity.

    Thanks,
    Sergey

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm not sure what you're after, but try "db2pd -dynamic" - look for statement anchor ID (AnchID).

  3. #3
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    Quote Originally Posted by n_i View Post
    I'm not sure what you're after, but try "db2pd -dynamic" - look for statement anchor ID (AnchID).
    Thank you,

    I have found SQL identity in db2pd report but I can't find the plans.

    Is there any admin view that produces the same output as db2pd? The problem is - the view can be selected and the db2pd report is only the plain text.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by shubin_du View Post

    Is there any admin view that produces the same output as db2pd?
    Not that I know of.

  5. #5
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    thank you n_i. the absence of information is information as well.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Check http://www.dbforums.com/db2/1663130-...ash-value.html

    Probably not exactly what you're looking... and not available in v9.5.

  7. #7
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    Quote Originally Posted by BELLO4KA View Post
    Check http://www.dbforums.com/db2/1663130-...ash-value.html

    Probably not exactly what you're looking... and not available in v9.5.
    thank you белочка. there was the same question i have made in my post )) it is interesting that 9.7 has the unique id for sql. i try to find the solution in 9.7 although we use now 9.5.

Posting Permissions

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