CODEWITHSUNDEEP
databaseinformix
grom
grom

Reputation: 16116

Find long running query on Informix?

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.

Upvotes: 3

Views: 9545

Answers (4)

grom
grom

Reputation: 16116

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.

Upvotes: 1

DL Redden
DL Redden

Reputation: 608

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.

Upvotes: 7

RET
RET

Reputation: 9188

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:

Upvotes: 1

Jason Navarrete
Jason Navarrete

Reputation: 7521

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

Upvotes: -1

Related Questions