Find long running query on Informix?

Question

How can you find out what are the long running queries are on Informix database server? I have a query that is using up the CPU and want to find out what the query is.

Answer 1

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

Answer 2

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

The server is Informix Dynamic Server 10.00 UC7

Answer 3

That's because the suggested answer is for DB2, not Informix.

The sysmaster database (a virtual relational database of Informix shared memory) will probably contain the information you seek. These pages might help you get started:

• http://docs.rinet.ru/InforSmes/ch22/ch22.htm
• http://www.informix.com.ua/articles/sysmast/sysmast.htm

Answer 4

If the query is currently running watch the onstat -g act -r 1 output and look for items with an rstcb that is not 0

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

In this example the third row is what is currently running. If you have multiple rows with non-zero rstcb values then watch for a bit looking for the one that is always or almost always there. That is most likely the session that your looking for.

c0000000d7afcf20 is the address that we're interested in for this example.

Use onstat -u | grep c0000000d7afcf20 to find the session

c0000000d7afcf20 Y--P--- 22887    informix -        c0000000d5b0abd0 0    5     14060    3811

This gives you the session id which in our example is 22887. Use onstat -g ses 22887 to list info about that session. In my example it's a system session so there's nothing to see in the onstat -g ses output.

Answer 5

Okay it took me a bit to work out how to connect to sysmaster. The JDBC connection string is:

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

Where the port number is the same as when you are connecting to the actual database. That is if your connection string is:

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

Then the sysmaster connection string is:

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

Also found this wiki page that contains a number of SQL queries for operating on the sysmaster tables.