Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2

    Answered: Get explain plan for sql statement hash value

    Hi guys,

    I'm looking in the dynamic statements in db2top, I wanted to know the explain for one of the statements (and the full statement).
    The only thing I have to go by is the sql statement hash value.

    How can I go from this point to generate an explain plan ?

    this is an example of what I see

    Code:
    SQL_Statement              Sql                                  Num       Exec        Avg        Cpu        Avg         Rows         Rows         Data    Data        Index   Index         Temp    Temp
      HashValue                  Statement (30 first char.)     Execution       Time   ExecTime       Time    CpuTime         read      written      l_reads    Hit%      l_reads    Hit%      l_reads    Hit%
      -------------------------- ------------------------------ --------- ---------- ---------- ---------- ---------- ------------ ------------ ------------ ------- ------------ ------- ------------ -------
      00000011478829371231693902 select  BR_TRANSACTION.ID as t       936 110.931956   0.118517  31.051534   0.033174   52,871,415            0   52,947,475  99.98%       17,795 100.00%            0   0.00%

  2. Best Answer
    Posted by tafster


  3. #2
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  4. #3
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    great thanks

    and how about historical data?
    I dont see the same queries I saw yesterday.

  5. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Use this for statements released from the package cache.
    CREATE EVENT MONITOR (package cache) statement

    Use this for examining the current contents of the package cache.
    MON_GET_PKG_CACHE_STMT table function - Get SQL statement activity metrics in the package cache
    Regards,
    Mark.

  6. #5
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    thanks a lot guys.

    I marked @tafster as best answer since it answered my original question.

Posting Permissions

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