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