IDS 11.5.FC3 : Tracking new sql sessions

Our business requirement is not only to track current sql session BUT all new sessions coming in from a particular user. Does IDS supports / will support this in the current release? We are currently using Informix Version : 11.5.FC3 Steps I followed: 1. Changed onconfig and bounced engine : SQLTRACE level=LOW,ntraces=5000,size=160000,mode=User 2. dbaccess sysdmin : select task("set sql user tracing on", sid) FROM sysmaster:syssessions WHERE username not in ("root","informix"); The query returns: (expression) SQL user tracing on for sid(34). (expression) SQL user tracing on for sid(33). Note : These are CURRENTLY connected session with username not in root and informix. 3. Kicked off DW jobs and watched new SQL session connected to database: onstat -gr sql : IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 00:30:55 --8207528 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 60 UPDATE (all) unigo NL Not Wait 0 0 9.22 Off 34 SELECT unigo NL Wait 5 0 0 9.22 Off 33 - unigo NL Wait 120 0 0 9.22 Off 32 - unigo NL Not Wait 0 0 9.22 Off 29 - sysadmin CR Not Wait 0 0 9.24 Off 26 - sysmaster DR Not Wait 0 0 9.24 Off 25 sysadmin DR Wait 5 0 0 - Off 24 sysadmin DR Wait 5 0 0 - Off 22 sysadmin DR Wait 5 0 0 - Off In this case session id 60 is a new session with non root / non informix user. 4. dbaccess sysmaster select sql_sid from syssqltrace; Only tracked session id 34 / 33 and not session 60. We really need a solution to get moving and migrate from this legacy DW system that IBM dropped support on. Tracking SQL session is the only way to go. Let me know. Thank you

Could you use the sysdbopen procedure that can be created for any/all databases which gets executed when a session opens/connects to the specific database? Then inside the sysdbopen procedure execute the command to turn on the sql trace you are trying to do. (More info about them in chapter 6 of the guide to sql syntax) Jacques Renaut IBM/Informix APD

Perhaps you should do some reading around the informix trusted facility, or perhaps the stored procedure sysdbopen -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Ashutosh Khunte Sent: 21 April 2009 06:16 PM To: ids@iiug.org Subject: IDS 11.5.FC3 : Tracking new sql sessions [15568] Our business requirement is not only to track current sql session BUT all new sessions coming in from a particular user. Does IDS supports / will support this in the current release? We are currently using Informix Version : 11.5.FC3 Steps I followed: 1. Changed onconfig and bounced engine : SQLTRACE level=LOW,ntraces=5000,size=160000,mode=User 2. dbaccess sysdmin : select task("set sql user tracing on", sid) FROM sysmaster:syssessions WHERE username not in ("root","informix"); The query returns: (expression) SQL user tracing on for sid(34). (expression) SQL user tracing on for sid(33). Note : These are CURRENTLY connected session with username not in root and informix. 3. Kicked off DW jobs and watched new SQL session connected to database: onstat -gr sql : IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 00:30:55 --8207528 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 60 UPDATE (all) unigo NL Not Wait 0 0 9.22 Off 34 SELECT unigo NL Wait 5 0 0 9.22 Off 33 - unigo NL Wait 120 0 0 9.22 Off 32 - unigo NL Not Wait 0 0 9.22 Off 29 - sysadmin CR Not Wait 0 0 9.24 Off 26 - sysmaster DR Not Wait 0 0 9.24 Off 25 sysadmin DR Wait 5 0 0 - Off 24 sysadmin DR Wait 5 0 0 - Off 22 sysadmin DR Wait 5 0 0 - Off In this case session id 60 is a new session with non root / non informix user. 4. dbaccess sysmaster select sql_sid from syssqltrace; Only tracked session id 34 / 33 and not session 60. We really need a solution to get moving and migrate from this legacy DW system that IBM dropped support on. Tracking SQL session is the only way to go. Let me know. Thank you **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ================== Please read our Email Disclaimer : http://www.thefuelgroup.com/disclaimer.html

Hi, First, we need to be clear about what you mean by "tracking" sessions. From your posting, I think you mean you wish to record all the SQL statements from any sessions opened by a specific userid. Is that correct? If so, then you need to be sure you've got the latest manuals. In IDS 11.50xC3, additional commands were added to control tracing, one of which allows tracing to be set on for any session for a specific userid. That will accomplish what you want. Example: execute function task ("set sql tracing user add", "sam"); will cause all session for userid sam, both existing and future, to be traced, assuming tracing is on in the first place. See chapter 6 of the Guide to SQL: Syntax manual. Things are significantly different in the latest release compared to the first releases. Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "Ashutosh Khunte" <akhunte@yahoo.com> To: ids@iiug.org Date: 04/21/09 12:24 PM Subject: IDS 11.5.FC3 : Tracking new sql sessions [15568] Sent by: ids-bounces@iiug.org Our business requirement is not only to track current sql session BUT all new sessions coming in from a particular user. Does IDS supports / will support this in the current release? We are currently using Informix Version : 11.5.FC3 Steps I followed: 1. Changed onconfig and bounced engine : SQLTRACE level=LOW,ntraces=5000,size=160000,mode=User 2. dbaccess sysdmin : select task("set sql user tracing on", sid) FROM sysmaster:syssessions WHERE username not in ("root","informix"); The query returns: (expression) SQL user tracing on for sid(34). (expression) SQL user tracing on for sid(33). Note : These are CURRENTLY connected session with username not in root and informix. 3. Kicked off DW jobs and watched new SQL session connected to database: onstat -gr sql : IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 00:30:55 -- 8207528 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 60 UPDATE (all) unigo NL Not Wait 0 0 9.22 Off 34 SELECT unigo NL Wait 5 0 0 9.22 Off 33 - unigo NL Wait 120 0 0 9.22 Off 32 - unigo NL Not Wait 0 0 9.22 Off 29 - sysadmin CR Not Wait 0 0 9.24 Off 26 - sysmaster DR Not Wait 0 0 9.24 Off 25 sysadmin DR Wait 5 0 0 - Off 24 sysadmin DR Wait 5 0 0 - Off 22 sysadmin DR Wait 5 0 0 - Off In this case session id 60 is a new session with non root / non informix user. 4. dbaccess sysmaster select sql_sid from syssqltrace; Only tracked session id 34 / 33 and not session 60. We really need a solution to get moving and migrate from this legacy DW system that IBM dropped support on. Tracking SQL session is the only way to go. Let me know. Thank you ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Dick, This works perfrect. Looks like there are lots of changes in xc3 release. Thanks for your help. --- On Tue, 4/21/09, Richard Snoke <dsnoke@us.ibm.com> wrote: From: Richard Snoke <dsnoke@us.ibm.com> Subject: Re: IDS 11.5.FC3 : Tracking new sql sessions [15572] To: ids@iiug.org Date: Tuesday, April 21, 2009, 10:41 AM Hi, First, we need to be clear about what you mean by "tracking" sessions. >From your posting, I think you mean you wish to record all the SQL statements from any sessions opened by a specific userid. Is that correct? If so, then you need to be sure you've got the latest manuals. In IDS 11.50xC3, additional commands were added to control tracing, one of which allows tracing to be set on for any session for a specific userid. That will accomplish what you want. Example: execute function task ("set sql tracing user add", "sam"); will cause all session for userid sam, both existing and future, to be traced, assuming tracing is on in the first place. See chapter 6 of the Guide to SQL: Syntax manual. Things are significantly different in the latest release compared to the first releases. Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "Ashutosh Khunte" <akhunte@yahoo.com> To: ids@iiug.org Date: 04/21/09 12:24 PM Subject: IDS 11.5.FC3 : Tracking new sql sessions [15568] Sent by: ids-bounces@iiug.org Our business requirement is not only to track current sql session BUT all new sessions coming in from a particular user. Does IDS supports / will support this in the current release? We are currently using Informix Version : 11.5.FC3 Steps I followed: 1. Changed onconfig and bounced engine : SQLTRACE level=LOW,ntraces=5000,size=160000,mode=User 2. dbaccess sysdmin : select task("set sql user tracing on", sid) FROM sysmaster:syssessions WHERE username not in ("root","informix"); The query returns: (expression) SQL user tracing on for sid(34). (expression) SQL user tracing on for sid(33). Note : These are CURRENTLY connected session with username not in root and informix. 3. Kicked off DW jobs and watched new SQL session connected to database: onstat -gr sql : IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 00:30:55 -- 8207528 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 60 UPDATE (all) unigo NL Not Wait 0 0 9.22 Off 34 SELECT unigo NL Wait 5 0 0 9.22 Off 33 - unigo NL Wait 120 0 0 9.22 Off 32 - unigo NL Not Wait 0 0 9.22 Off 29 - sysadmin CR Not Wait 0 0 9.24 Off 26 - sysmaster DR Not Wait 0 0 9.24 Off 25 sysadmin DR Wait 5 0 0 - Off 24 sysadmin DR Wait 5 0 0 - Off 22 sysadmin DR Wait 5 0 0 - Off In this case session id 60 is a new session with non root / non informix user. 4. dbaccess sysmaster select sql_sid from syssqltrace; Only tracked session id 34 / 33 and not session 60. We really need a solution to get moving and migrate from this legacy DW system that IBM dropped support on. Tracking SQL session is the only way to go. Let me know. Thank you ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.