Statement text in LONG_RUNNING_SQL

db2cap · #1 (**permalink**) 06-30-11, 06:41

Hi,
DB2 Version: 9.1
OS: AIX 5.3

I am trying to find the longest running query in my database using SYSIBMADM.LONG_RUNNING_SQL view but it is not displaying the statement text.

$ db2 "SELECT SUBSTR(STMT_TEXT, 1, 10) AS STMT_TEXT, AGENT_ID, APPL_STATUS FROM SYSIBMADM.LONG_RUNNING_SQL"

STMT_TEXT AGENT_ID APPL_STATUS
---------- -------------------- ----------------------
- 1388 UOWEXEC
- 926 UOWWAIT

I have also tried by enabling the monitor switch for statement but it did not help

nvk@vhv · #2 (**permalink**) 06-30-11, 09:01

If the switch was not activated before, you won't get the data afterwards...
In Addition the monitor switch is not enough. You need to activate DFT_MON_STMT.

Quote:

All snapshot monitoring administrative views and associated table functions use a separate instance connection, which is different from the connection the current session uses. Therefore, an implicit instance attachment can be established, and only default database manager monitor switches are effective. Ineffective monitor switches include any that are turned on or off dynamically from the current session or application.

http://publib.boulder.ibm.com/infoce.../r0006117.html

Internet Services

Web Development

Digital Marketing

Consumer Tech