How to Get "SQL" statement in running session

Hello ? How are you ? How to Get "SQL" statement in running session without "SQL TRACE ON" ? Our customer want to get "SQL" history to get Top N SQL . They are developing to gather Informix DBMS log and OS log's history these day. They use Informix 11.5 version. In case of Korea , we have no reference that use "SQL TRACE ON". This customer maintain over 3000 session everyday. Could you guide about sysmaster query or onstat commend to get SQL statement in real time ?

At running time you can get the statement from sysmaster:sysconblock table or the view sysmaster:syssqlcurses. The table sysmaster:syssqlstat also provides the SQL statement but it stores it on a CHAR(200), the other ones stores it on a CHAR(32000). onstat -g ses <SESSION_ID> http://www-01.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.adref.doc/i ds_adr_0574.htm onstat -g sql <SESSION_ID> http://www-01.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.adref.doc/i ds_adr_0579.htm And for prepared statements: onstat -g stm http://www-01.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.adref.doc/i ds_adr_0583.htm By default, only the DBSA can view these commands information. However, when the UNSECURE_ONSTAT configuration parameter is set to 1, all users can view this information. Cheers. On Thu, 20 Aug 2015 at 07:55 SUN-MI MIN <mhera76@naver.com> wrote: > Hello ? > > How are you ? > > How to Get "SQL" statement in running session without "SQL TRACE ON" ? > Our customer want to get "SQL" history to get Top N SQL . > They are developing to gather Informix DBMS log and OS log's history these > day. > > They use Informix 11.5 version. > > In case of Korea , we have no reference that use "SQL TRACE ON". > This customer maintain over 3000 session everyday. > > Could you guide about sysmaster query or onstat commend to get SQL > statement > in real time ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c27f4061c4e0051db9dc24

Sun-Min: onstat -g ses 0 >somefile.out -or- onstat -g sql 0 >somefile.out This will return all of the running sessions including the SQL but only for sessions that still exist at that moment and only the current and previous SQL statement for each session. If a session has many prepared statements or even many open cursors you will not see every SQL. If you need to capture every SQL there are two third party products that can do that for you without turning SQLTrace on: iWatch from Exact-Solutions (www.exact-solutions.com) and SQL PowerTools from SQL Power Tools from SQL Power Tools (www.sqlpower.com) AGS's Server Studio with Sentinel also has an SQL Capture feature that can capture a large percentage of SQL but it polls the equivalent sysmaster tables to the onstat commands above so it will miss a lot of queries. Their next version will do better but it will be working with turning SQLTrace on to do that. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 20, 2015 at 2:54 AM, SUN-MI MIN <mhera76@naver.com> wrote: > Hello ? > > How are you ? > > How to Get "SQL" statement in running session without "SQL TRACE ON" ? > Our customer want to get "SQL" history to get Top N SQL . > They are developing to gather Informix DBMS log and OS log's history these > day. > > They use Informix 11.5 version. > > In case of Korea , we have no reference that use "SQL TRACE ON". > This customer maintain over 3000 session everyday. > > Could you guide about sysmaster query or onstat commend to get SQL > statement > in real time ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013c6614cb32f3051dbcc41b

Hi, I did a webcast on using SQL Trace a while back that is available on our web site at http://advancedatatools.com/Informix/Webcasts.html or on YouTube at http://youtu.be/Yf01OJP94NM You can get current info from sysmaster.syssqexplain but that does not have history. One of my sysmaster scripts to figure out which is the current most expensive SQL statements is below Regards - Lester ----------------------------------------------------------------------------- -- Module: @(#)syssqexplain.sql 1.0 Date: 2015/03/20 -- Author: Lester Knutsen Email: lester@advancedatatools.com -- Advanced DataTools Corporation -- Description: -- Tested with Informix 11.70 and Informix 12.10 ----------------------------------------------------------------------------- database sysmaster; select sqx_estcost, sqx_sqlstatement from syssqexplain into temp A; select sqx_sqlstatement sqlstatement, sum(sqx_estcost) sum_estcost, count(*) count_executions from A group by 1 order by 2 desc; On 8/20/15 2:54 AM, SUN-MI MIN wrote: > Hello ? > > How are you ? > > How to Get "SQL" statement in running session without "SQL TRACE ON" ? > Our customer want to get "SQL" history to get Top N SQL . > They are developing to gather Informix DBMS log and OS log's history these > day. > > They use Informix 11.5 version. > > In case of Korea , we have no reference that use "SQL TRACE ON". > This customer maintain over 3000 session everyday. > > Could you guide about sysmaster query or onstat commend to get SQL statement > in real time ? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- ______________________________________________________________________ Lester Knutsen lester@advancedatatools.com Advanced DataTools Corporation Voice: 703-256-0267 x102 Visit our Web page: http://www.advancedatatools.com ______________________________________________________________________