Querying Session Control Block from user-defined function or SPL?

Folks: I'd like to log some data about the current client session, and there doesn't seem to be any direct way to do this using Informix-provided native functions or variables (for example, something equivalent to HOST_NAME, HOST_ID and SYSTEM_USER in MSSQL). I'm assuming this would be done by using DBINFO('sessionid') to get the Session Control Block ID and then doing some trolling around in the SCB to get the data I want to log, but a quick search doesn't find any examples of accessing the SCB via UDF/SPL code. I'm loathe to drop to the OS to do something like SYSTEM('onstat -g ses #####') and then parse the results. This is intended for use in a logging trigger and that would absolutely kill performance. Has anybody done this and can anybody provide examples? Thanks for any help! -- John Hardin KA7OHZ http://www.impsec.org/~jhardin/ jhardin@impsec.org FALaholic #11174 pgpk -a jhardin@impsec.org key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79 ----------------------------------------------------------------------- USMC Rules of Gunfighting #6: If you can choose what to bring to a gunfight, bring a long gun and a friend with a long gun. ----------------------------------------------------------------------- 5 days until The 272nd anniversary of John Peter Zenger's acquittal

On Mon, 2007-07-30 at 17:50 +0000, John Hardin wrote: > Folks: > > I'd like to log some data about the current client session, and there > doesn't seem to be any direct way to do this using Informix-provided native > functions or variables (for example, something equivalent to HOST_NAME, > HOST_ID and SYSTEM_USER in MSSQL). > > I'm assuming this would be done by using DBINFO('sessionid') to get the > Session Control Block ID and then doing some trolling around in the SCB to > get the data I want to log, but a quick search doesn't find any examples of > accessing the SCB via UDF/SPL code. Try "select * from sysmaster:syssessions where sid = dbinfo('sessionid')" and go from there. HTH, -- Carsten Haese http://informixdb.sourceforge.net

"Carsten Haese" <carsten@uniqsys.com> wrote: > On Mon, 2007-07-30 at 17:50 +0000, John Hardin wrote: >> >> I'd like to log some data about the current client session, and there >> doesn't seem to be any direct way to do this using Informix-provided >> native >> functions or variables (for example, something equivalent to HOST_NAME, >> HOST_ID and SYSTEM_USER in MSSQL). >> >> I'm assuming this would be done by using DBINFO('sessionid') to get the >> Session Control Block ID and then doing some trolling around in the SCB >> to >> get the data I want to log, but a quick search doesn't find any examples >> of >> accessing the SCB via UDF/SPL code. > > Try "select * from sysmaster:syssessions where sid = > dbinfo('sessionid')" and go from there. Perfect! Thanks! Here's what I came up with: SELECT TRIM(username) || ' @ ' || TRIM(hostname) || ' (' || TRIM(tty) || ')' FROM sysmaster:syssessions WHERE sid = DBINFO('sessionid') -- John Hardin KA7OHZ Senior Applications Developer, RetailCRM Development web: http://www.epicor.com voice: (425) 672-1304 fax: (425) 672-0192 email: <jhardin@epicor.com> EPICOR|CRS Retail Solutions Division 3400 188th Street SW, Suite 185 Lynnwood, WA 98037 USA Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA ------------------------------------------------------------------------ The first time I saw a bagpipe, I thought the player was torturing an octopus. I was amazed they could scream so loudly. ------------------------------------------------------------------------