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

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.

Not that I know of.

thank you n_i. the absence of information is information as well.

Check statement and plan "hash value"

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.