How to see queries executing on Informix DB

Question

I would like to know if there is a way to see queries executing on Informix.
If I use onstat -g sql it just shows me the SQL statement type (if it is insert or select). But I want to see the complete query text like select * from table.
It is possible to do that on SQL Server with SQL Server Profiler.

Answer 1

The onstat -g sql and -g ses will give to you the current and last statement parsed at the session and the complete statement + host variables (to current statement).
If you have quickly statements running, for sure you will miss a lot of them.

Here is an example:

$ onstat -g sql 170

IBM Informix Dynamic Server Version 11.70.FC6 -- On-Line -- Up 13:39:50 -- 529332 Kbytes


Sess       SQL            Current            Iso Lock       SQL  ISAM F.E.
Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain
170        SELECT         sysmaster          CR  Not Wait   0    0    9.24  Off

Current statement name : slctcur

Current SQL statement (3) :
  select t.tabname, c.colno, c.colname   from systables t, syscolumns c
    where t.tabid = c.tabid order by 1,2

Last parsed SQL statement :
  select t.tabname, c.colno, c.colname   from systables t, syscolumns c
    where t.tabid = c.tabid order by 1,2

If you want to get all history of statements executed the best way available is using the 'SQL TRACE' resource. Which is available only after version 11.

This trace enable you save all statement executed at the instance/database/user/session into a rotatory buffer (when they fill, start to override it self).

You can watch this trace using the command onstat -g his or querying the table sysmaster:syssqltrace.

Here is a IBM manual reference for SQL TRACE

Just sharing, this is my "swissknife" to enable and trace someone... where I just change some parameters, comment/discomment what I need and execute with informix. After that I start to monitor the buffer with onstat/selects...

select sysadmin:task('set sql tracing off') as sql       from sysmaster:sysdual

union all select sysadmin:task("set sql user tracing off")         from sysmaster:sysdual -- USER mode
union all select sysadmin:task("set sql user tracing clear")       from sysmaster:sysdual -- USER mode
union all select sysadmin:task('set sql tracing user clear')       from sysmaster:sysdual
union all select sysadmin:task('set sql tracing database clear')   from sysmaster:sysdual
union all select sysadmin:task('set sql tracing session', 'clear') from sysmaster:sysdual

union all select sysadmin:task('set sql tracing info')             from sysmaster:sysdual
union all select sysadmin:task('set sql tracing database list')    from sysmaster:sysdual
union all select sysadmin:task('set sql tracing user list')        from sysmaster:sysdual
union all select sysadmin:task('set sql tracing session list')     from sysmaster:sysdual

--union all select sysadmin:task('set sql tracing database add','testdb')    from sysmaster:sysdual
union all select sysadmin:task('set sql tracing user add','aviana')  from sysmaster:sysdual
--union all select sysadmin:task('set sql tracing session','on', sid) from sysmaster:syssessions where username = 'cinacio' and (sid in (0) or pid in (0))
--union all select sysadmin:task('set sql user tracing on ', sid) from sysmaster:syssessions where username = 'cinacio'  and (sid in (0) or pid in (0))
--union all select sysadmin:task('set sql user tracing on ', 354851) from sysmaster:sysdual
union all select sysadmin:task('set sql tracing on',150000,'4000b','high','user')  from sysmaster:sysdual
;

---

There are others options which are improvisation for such thing :

• Active at the informix client the $SQLIDEBUG + sqliprint command.
  (undocummented resource) Which is originally used to debug and investigate problems of communication between client/server. You can get more information at this IBM article
• Active at the windows ODBC configuration the sql trace.
  Which is a standard feature from any ODBC driver (if you use it).
  You able to active this at your ODBC properties.
• Active the set explain (informix specific statement).
  This command says to server save at a file all sql statements executed at the session, plus informations of their optimization , etc.
  Can be enabled by the own session (executing the set explain on; statement) or activated with the command onmode -Y <options>.
  As reference, check the IBM manuals: explain , onmode

---

Other options

Quoting Art from this IIUG thread : http://www.iiug.org/forums/ids/index.cgi/read/35708

SQL Power Tools - from SQL Power Tools, Inc, - http://www.sqlpower.com/ - Can capture 100% of queries issued over TCP/IP and load then into a repository (currently SQL Server but they are working using an Informix repository) from which their GUI viewer can display, manipulate, and report. Display is not real time as the data is captured in blocks of queries into flat files and bulk loaded into the repository periodically. Uses a network sniffer utility on the server or a separate box that has near zero impact on the server itself for capture.

iWatch - from Exact-Solutions, Inc. - www.exact-solutions.com - Can capture 100% of queries issued over TCP/IP and load then into a proprietary repository. Can display real time capture. Uses a network sniffer utility on your server or a sniffer appliance (for very high transaction rate systems) that has very low impact on the server.