Viewing Running and Last Parsed SQL Statements

IDS: 11.50.FC5 OS: AIX 6.1.3.0 I have a user that wishes to track/trace their SQL connections and statements. They were able to perform onstat -g sql or onstat -g ses in the past, but now they are not able to run onstat commands from their application server. What table and database that would contain current and previously executed SQL, if any? Will it show the last parsed SQL, if the connection remains, but not executing? Should I give them access to Informix or some other onstat access? $onstat -g sql 3053 IBM Informix Dynamic Server Version 11.50.FC5 -- On-Line -- Up 8 days 18:24:07 -- 306352 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 3053 - vrel_sys CR Not Wait 0 0 9.24 Off Last parsed SQL statement : select tabname , tabid , owner from informix . systables wheretabname != 'ANSI' and tabtype != 'P' order by tabname Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings - BU: I & T Group 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com <mailto:2244650553@messaging.sprintpcs.com> Page via Skytel: 2244650553@sprint.skytel.com <mailto:2244650553@sprint.skytel.com> Informix or MySQL Primary: 9110210@skytel.com <mailto:9110210@skytel.com> Informix or MySQL Secondary: 7276872@skytel.com <mailto:7276872@skytel.com> " Yes we can make a Change! " " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! " " Lets not forget - GO Pistons and Red Wings! " GSU *******************************************************************

Hi Ernest, A more useful approach might be to use the SQL tracing facility (SQLTRACE and related parameters, tables, etc.) You can configure that to capture SQL for many subsets of users, including just a single session. That way you'd have a more complete and permanent record of what went on. There are some limitations, one being that you have to set the rules for what to capture before the user begins the work you're trying to trace. However, that's not too tricky in many cases. Cheers, Dick Snoke IBM ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "Knox, Ernest" <Ernest.Knox@searshc.com> To: ids@iiug.org Date: 03/10/11 11:15 AM Subject: Viewing Running and Last Parsed SQL Statements [23025] Sent by: ids-bounces@iiug.org IDS: 11.50.FC5 OS: AIX 6.1.3.0 I have a user that wishes to track/trace their SQL connections and statements. They were able to perform onstat -g sql or onstat -g ses in the past, but now they are not able to run onstat commands from their application server. What table and database that would contain current and previously executed SQL, if any? Will it show the last parsed SQL, if the connection remains, but not executing? Should I give them access to Informix or some other onstat access? $onstat -g sql 3053 IBM Informix Dynamic Server Version 11.50.FC5 -- On-Line -- Up 8 days 18:24:07 -- 306352 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 3053 - vrel_sys CR Not Wait 0 0 9.24 Off Last parsed SQL statement : select tabname , tabid , owner from informix . systables wheretabname != 'ANSI' and tabtype != 'P' order by tabname Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings - BU: I & T Group 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com <mailto:2244650553@messaging.sprintpcs.com> Page via Skytel: 2244650553@sprint.skytel.com <mailto:2244650553@sprint.skytel.com> Informix or MySQL Primary: 9110210@skytel.com <mailto:9110210@skytel.com> Informix or MySQL Secondary: 7276872@skytel.com <mailto:7276872@skytel.com> " Yes we can make a Change! " " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! " " Lets not forget - GO Pistons and Red Wings! " GSU ******************************************************************* ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

We're going to test the use of this onconfig parameter for 11.50: # UNSECURE_ONSTAT - Controls whether non-DBSA users are # allowed to run all onstat commands. # Acceptable values are: # 1 Enabled # 0 Disabled (Default) Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings - BU: I & T Group 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com Page via Skytel: 2244650553@sprint.skytel.com Informix or MySQL Primary: 9110210@skytel.com Informix or MySQL Secondary: 7276872@skytel.com =20 " Yes we can make a Change! " " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! " " Lets not forget - GO Pistons and Red Wings! " GSU ******************************************************************* -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Richard Snoke Sent: Thursday, March 10, 2011 12:51 PM To: ids@iiug.org Subject: Re: Viewing Running and Last Parsed SQL Statements [23027] Hi Ernest,=20 A more useful approach might be to use the SQL tracing facility (SQLTRACE=20 and related parameters, tables, etc.) You can configure that to capture=20 SQL for many subsets of users, including just a single session. That way you'd have a more complete and permanent record of what went on. There=20 are some limitations, one being that you have to set the rules for what to=20 capture before the user begins the work you're trying to trace. However, that's not too tricky in many cases.=20 Cheers,=20 Dick Snoke=20 IBM ChannelWorks=20 dsnoke@us.ibm.com=20 (404) 487-1595=20 From: "Knox, Ernest" <Ernest.Knox@searshc.com>=20 To: ids@iiug.org=20 Date: 03/10/11 11:15 AM=20 Subject: Viewing Running and Last Parsed SQL Statements [23025]=20 Sent by: ids-bounces@iiug.org=20 IDS: 11.50.FC5=20 OS: AIX 6.1.3.0=20 I have a user that wishes to track/trace their SQL connections and=20 statements. They were able to perform onstat -g sql or onstat -g ses in=20 the past, but now they are not able to run onstat commands from their=20 application server.=20 What table and database that would contain current and previously=20 executed SQL, if any?=20 Will it show the last parsed SQL, if the connection remains, but not=20 executing?=20 Should I give them access to Informix or some other onstat access?=20 $onstat -g sql 3053=20 IBM Informix Dynamic Server Version 11.50.FC5 -- On-Line -- Up 8=20 days 18:24:07 -- 306352 Kbytes=20 Sess SQL Current Iso Lock SQL ISAM=20 F.E.=20 Id Stmt type Database Lvl Mode ERR ERR=20 Vers Explain=20 3053 - vrel_sys CR Not Wait 0 0=20 9.24 Off=20 Last parsed SQL statement :=20 select tabname , tabid , owner from informix . systables where=20tabname !=3D=20 'ANSI' and tabtype !=3D 'P' order by tabname=20 Thanks,=20 *******************************************************************=20 Ernie Knox=20 IT Database Administrator Specialist=20 Sears Holdings - BU: I & T Group=20 3333 Beverly Rd., B4-266A=20 Hoffman Estates, IL. 60179=20 Office: (847) 286-5735=20 Email: Ernest.Knox@searshc.com=20 Blackberry: 2244650553@messaging.sprintpcs.com=20 <mailto:2244650553@messaging.sprintpcs.com>=20 Page via Skytel: 2244650553@sprint.skytel.com=20 <mailto:2244650553@sprint.skytel.com>=20 Informix or MySQL Primary: 9110210@skytel.com=20 <mailto:9110210@skytel.com>=20 Informix or MySQL Secondary: 7276872@skytel.com=20 <mailto:7276872@skytel.com>=20 " Yes we can make a Change! "=20 " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! "=20 " Lets not forget - GO Pistons and Red Wings! "=20 GSU=20 *******************************************************************=20 ************************************************************************ *******=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20 ************************************************************************ *******=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20 This message, including any attachments, is the property of Sears Holdings = Corporation and/or one of its subsidiaries. It is confidential and may cont= ain proprietary or legally privileged information. If you are not the inten= ded recipient, please delete it without reading the contents. Thank you.