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:

jdbc:informix-sqli://dbserver.local:1526/sysmaster:INFORMIXSERVER=mydatabase

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:

jdbc:informix-sqli://database:1541/crm:INFORMIXSERVER=crmlive

Then your sysmaster connection string is:

jdbc:informix-sqli://database:1541/sysmaster:INFORMIXSERVER=crmlive

Also found this wiki page that consists of numerous SQL queries for operating around the sysmaster tables.

@Jason: I am getting table not in database. Using SQuirreL with JDBC driver from here.

The server is Informix Dynamic Server 10.00 UC7

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