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.
如何找出Informix数据库服务器上长时间运行的查询？我有一个耗尽CPU的查询，想知道查询是什么。
4 个解决方案
#1
5
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
如果查询当前正在运行，请观察onstat -g act -r 1输出并查找rstcb不为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.
在此示例中，第三行是当前正在运行的行。如果你有多行具有非零rstcb值，那么请注意寻找总是或几乎总是在那里的那一行。这很可能是您要寻找的会话。
c0000000d7afcf20 is the address that we're interested in for this example.
c0000000d7afcf20是我们对此示例感兴趣的地址。
Use onstat -u | grep c0000000d7afcf20 to find the session
使用onstat -u | grep c0000000d7afcf20找到会话
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.
这将为您提供会话ID，在我们的示例中为22887.使用onstat -g ses 22887列出有关该会话的信息。在我的例子中，它是一个系统会话，所以在onstat -g ses输出中没有什么可看的。
#2
1
That's because the suggested answer is for DB2, not Informix.
那是因为建议的答案是DB2，而不是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:
sysmaster数据库（Informix共享内存的虚拟关系数据库）可能包含您寻找的信息。这些页面可能会帮助您入门：
- http://docs.rinet.ru/InforSmes/ch22/ch22.htm
- http://docs.rinet.ru/InforSmes/ch22/ch22.htm
- http://www.informix.com.ua/articles/sysmast/sysmast.htm
- http://www.informix.com.ua/articles/sysmast/sysmast.htm
#3
1
Okay it took me a bit to work out how to connect to sysmaster. The JDBC connection string is:
好吧，我花了一些时间来研究如何连接到sysmaster。 JDBC连接字符串是：
jdbc:informix-sqli://dbserver.local:1526/sysmaster:INFORMIXSERVER=mydatabase
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
JDBC：INFORMIX-SQLI：//数据库：1541 / CRM：INFORMIXSERVER = crmlive
Then the sysmaster connection string is:
然后sysmaster连接字符串是：
jdbc:informix-sqli://database:1541/sysmaster:INFORMIXSERVER=crmlive
JDBC：INFORMIX-SQLI：//数据库：1541 / SYSMASTER：INFORMIXSERVER = crmlive
Also found this wiki page that contains a number of SQL queries for operating on the sysmaster tables.
还发现此Wiki页面包含许多用于在sysmaster表上操作的SQL查询。
#4
-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
信用：SQL查看长时间运行的查询
#1
5
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
如果查询当前正在运行，请观察onstat -g act -r 1输出并查找rstcb不为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.
在此示例中，第三行是当前正在运行的行。如果你有多行具有非零rstcb值，那么请注意寻找总是或几乎总是在那里的那一行。这很可能是您要寻找的会话。
c0000000d7afcf20 is the address that we're interested in for this example.
c0000000d7afcf20是我们对此示例感兴趣的地址。
Use onstat -u | grep c0000000d7afcf20 to find the session
使用onstat -u | grep c0000000d7afcf20找到会话
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.
这将为您提供会话ID，在我们的示例中为22887.使用onstat -g ses 22887列出有关该会话的信息。在我的例子中，它是一个系统会话，所以在onstat -g ses输出中没有什么可看的。
#2
1
That's because the suggested answer is for DB2, not Informix.
那是因为建议的答案是DB2，而不是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:
sysmaster数据库（Informix共享内存的虚拟关系数据库）可能包含您寻找的信息。这些页面可能会帮助您入门：
- http://docs.rinet.ru/InforSmes/ch22/ch22.htm
- http://docs.rinet.ru/InforSmes/ch22/ch22.htm
- http://www.informix.com.ua/articles/sysmast/sysmast.htm
- http://www.informix.com.ua/articles/sysmast/sysmast.htm
#3
1
Okay it took me a bit to work out how to connect to sysmaster. The JDBC connection string is:
好吧，我花了一些时间来研究如何连接到sysmaster。 JDBC连接字符串是：
jdbc:informix-sqli://dbserver.local:1526/sysmaster:INFORMIXSERVER=mydatabase
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
JDBC：INFORMIX-SQLI：//数据库：1541 / CRM：INFORMIXSERVER = crmlive
Then the sysmaster connection string is:
然后sysmaster连接字符串是：
jdbc:informix-sqli://database:1541/sysmaster:INFORMIXSERVER=crmlive
JDBC：INFORMIX-SQLI：//数据库：1541 / SYSMASTER：INFORMIXSERVER = crmlive
Also found this wiki page that contains a number of SQL queries for operating on the sysmaster tables.
还发现此Wiki页面包含许多用于在sysmaster表上操作的SQL查询。
#4
-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
信用：SQL查看长时间运行的查询