How to View Queries Executed in an Informix DB Database

Question

I would like to know if there is a way to see queries running on Informix.
If I use onstat -g sql, it just shows me the type of SQL statement (if it is inserted or selected). But I want to see the full text of the request, for example select * from table.
This can be done on SQL Server using the SQL Server Profiler.

Answer 1

onstat -g sqland -g sesprovide you with the current and last statements analyzed in the session, and the full statement + host variables (in the current statement).
If you have fast operators, you will probably skip 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

, , SQL TRACE. 11.

, / //​​, ( , ).

onstat -g his sysmaster:syssqltrace.

IBM SQL TRACE

, "swissknife", -... , / , , informix. 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
;

---

, :

• informix $SQLIDEBUG + sqliprint.
  ( ) /. IBM.
• ODBC Windows. ODBC ( ).
  ODBC.
• set explain ( ).
  sql-, , ..
  ( set explain on;) onmode -Y <options>.
  IBM: explain, onmode

---

IIUG: http://www.iiug.org/forums/ids/index.cgi/read/35708

SQL Power Tools - SQL Power Tools, Inc, - http://www.sqlpower.com/ - 100% , TCP/IP, ( SQL Server, Informix ), GUI , . , . .

iWatch - Exact-Solutions, Inc. - www.exact-solutions.com - 100% , TCP/IP, . . ( ), .