Hi.

I am looking to query our of the db2 internal system tables the unique identifier of a SQL statement as well as the unique identifier of the execution plan. I need to see those for an active query.

In Oracle - each sql statement has a hash_value which is a unique identifier (column hash_value in v$sql)
in addition, each execution plan has a unique identifier called plan_hash_value (column plan_hash_value in v$sql)

I am looking for the equivalent in DB2. is there anything like this (I read something about sql token but unable to find the right snapshot view or table function to find it)

please advice.

I'm not sure if this is what you're looking for... but you can try it to see if the following gives you what you need. This needs to be done on v9.7. Example:


$ db2 "select count(*) from sysibm.systables"

1
-----------
417

1 record(s) selected.


$ db2 "select executable_id, varchar(stmt_text,100) from table (mon_get_pkg_cache_stmt(NULL,NULL,NULL,-1)) as t"

EXECUTABLE_ID 2
------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
x'010000000000000001000000000000000000000002002010 1228105531202390' select count(*) from sysibm.systables
x'010000000000000002000000000000000000000002002010 1228105607118283' select executable_id, varchar(stmt_text,100) from table (mon_get_pkg_cache_stmt(NULL,NULL,NULL,-1))
SQL0445W Value "select executable_id, varchar(stmt_text,100) from table (mon"
has been truncated. SQLSTATE=01004


2 record(s) selected with 1 warning messages printed.


$ db2 "call explain_from_section (x'01000000000000000100000000000000000000000200201 01228105531202390','M',NULL,0,'TEST',?,?,?,?,?)"

Value of output parameters
--------------------------
Parameter Name : EXPLAIN_SCHEMA
Parameter Value : TEST

Parameter Name : EXPLAIN_REQUESTER
Parameter Value : TEST

Parameter Name : EXPLAIN_TIME
Parameter Value : 2010-12-28-10.56.24.789244

Parameter Name : SOURCE_NAME
Parameter Value : SQLC2H20

Parameter Name : SOURCE_SCHEMA
Parameter Value : NULLID

Parameter Name : SOURCE_VERSION
Parameter Value :

Return Status = 0



$ db2exfmt -d <db name> -w 2010-12-28-10.56.24.789244 -n SQLC2H20 -s NULLID -# 0 -o explain.out
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Output is in explain.out.
Executing Connect Reset -- Connect Reset was Successful.