Wednesday, April 1, 2015

find the complete sql of a really long sql and explain it

I had a system engineer responisble for an application contact me about a problematic sql statement,
he wanted me to have a look an explain it,
in his management software the statement was truncated.

I noted the tablenames mentioned in the truncated statement and decided that i would have a look on the DB2 level,

I thought of the table function mon_get_pkg_cache_stmt,
I ran a query filtering on the tablenames ,

db2 "select STMT_TEXT from table(mon_get_pkg_cache_stmt('D',null,null,-2)) where STMT_TEXT like '%TABLE1%TABLE2%TABLE3%'"

the output came back with the correct statement , but including this warning:
SQL0445W:the warning in knowledge center 

I tried to query the sysibmadm.mon_current_sql administrative view, to check whether I could see the complete sql statement,
but the value for the stmt_text was truncated as well.

Next I had a look in db2top,
and after some fiddling I used the following steps to find the complete sql statement ( 41kb long )

  1. 1.       Start db2top
  2. 2.       Type V ( upper case letter v )
  3. 3.       Enter the default schema => the query was using not fully qualified tablenames
  4. 4.       Locate the agent_id for the connection executing the sql ( this was identified with the earlier mon_current_sql view )
  5. 5.       Type lower case a
  6. 6.       Enter the agent_id
  7. 7.       You will now see more information on this connection…
  8. 8.       Type Upper case L, this will zoom in on the sql being executed and allow some extra commands
  9. 9.       Type lower case e to launch a dynamic explain of the query
  10. 1.    A file is opened  in vi containing the query, the explain plan and more information on the execution plan and indexes being used
  11.       You can save this file be typing :w <full path for new file>/filename

     from the file generated above I had both the explain information and the complete sql,
      and this allowed me to communicate the full sql to the system engineer, adn look into the issue myself .

No comments:

Post a Comment