Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2008
    Posts
    7

    Unanswered: statement and plan "hash value"

    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.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.
    Last edited by db2girl; 12-28-10 at 12:09.

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
  •