How will you discover do you know the lengthy running queries take presctiption Informix database server? I've got a query that's depleting the CPU and wish to discover exactly what the totally.
When the totally presently running watch the onstat -g act -r 1 output and search for products by having an rstcb that's not
Running threads: tid tcb rstcb prty status vp-class name 106 c0000000d4860950 0 2 running 107soc soctcppoll 107 c0000000d4881950 0 2 running 108soc soctcppoll 564457 c0000000d7f28250 c0000000d7afcf20 2 running 1cpu CDRD_10
Within this example the 3rd row is exactly what is presently running. For those who have multiple rows with non-zero rstcb values then watch out for a little searching for the main one that's always or more often than not there. That's possibly the session that the searching for.
c0000000d7afcf20 may be the address that we are thinking about let's imagine.
Use onstat -u grep c0000000d7afcf20 to obtain the session
c0000000d7afcf20 Y--P--- 22887 informix - c0000000d5b0abd0 0 5 14060 3811
This provides the session id which within our example is 22887. Use onstat -g ses 22887 to list out information on that session. During my example it is a system session so there is nothing to determine within the onstat -g ses output.
Like the recommended response is for DB2, not Informix.
The sysmaster database (an online relational database of Informix shared memory) will most likely retain the information you seek. These pages might help you to get began:
Okay it required me a little to sort out how you can connect with sysmaster. The JDBC connection string is:
In which the port number is equivalent to when you're hooking up towards the actual database. That's in case your connection string is:
Then your sysmaster connection string is:
Also found this wiki page that consists of numerous SQL queries for operating around the sysmaster tables.
SELECT ELAPSED_TIME_MIN,SUBSTR(AUTHID,1,10) AS AUTH_ID, AGENT_ID, APPL_STATUS,SUBSTR(STMT_TEXT,1,20) AS SQL_TEXT FROM SYSIBMADM.LONG_RUNNING_SQL WHERE ELAPSED_TIME_MIN > 0 ORDER BY ELAPSED_TIME_MIN DESC
Credit: SQL to View Long Running Queries