RE: Spooling all SQL Statements

In article <5ko05e$p9k@cssun.mathcs.emory.edu>, lperson@mcny.com (Lou Person) wrote: >We are sending sql statements to OWS from an >application. Is there anyway to "spool" all sql >statements which are sent to the server? Meaning, >can we store all SQL statements, which OWS >executed, to a table (or file) for subsequent review? > >Thanks, > >Lou Person You can consider using the onstat command to retrieve sql statements which are active. Of course, you would only get a snapshot. So you'd have to do it at intervals, which means that you could miss some sql stmts. However, if the idea is to identify stmts which are 'hoggers', this approach may be feasible. Also, you may find the output of this sampling approach easier to process, even though some accuracy may be lost. 'onstat -g sql session_id' will give you info. about the sql stmts of a session. You could use the output of 'onstat -g sql' (without session id) as the driver for 'onstat -g sql session_id'. On the other hand, if you are after the 'hoggers', you could use 'onstat -u' using the flags in that output as an indicator of active sessions (if the first flag of a session is not 'Y', there's a good chance that its a hogger), extract the session ids and pipe them to onstat -g sql. Here's a possible script : for SESSION in `onstat -u | \\ # skip informix sessions grep -v 'informix' | \\ # skip non-active sessions (18th character 'Y') grep -v '^.................Y' | \\ # skip header information (19th character to be -) grep '^..................-' | \\ # extract session id cut -b 26-34` do onstat -g sql $SESSION >> sqllogdone If you are want to track the session better, you could change the 'sql' flag of onstat -g to 'ses' - you will get the prize information of WHO is running the statement. However, the output will be substantially bigger. Bye, ----------------------- Rudy Fernandes GIC, Kuwait OL 7.20UC4, 4GL 6.04UC1 -----------------------