Need a Help, Access login and sql traces

Hello under IDS 9.40 FC9, i want to log access to ids, and trace the sql statements is there a way to do this with IDs commands only ? or IDS options ? by the way, i will migrate to ids 11 but in the summer, how to do the same things on ids 11 please ? Thanks in advance

For logging access you could use the auditing facility. I don't know any way to log the SQLs without external tools: - Brute force tcpdump - Something like IBM Guardium (not sure if it works with 9.x) - Some external party tool for replication... not sure about this one, but it's possible that some may capture the SQLs For version 11, you could use the sysdbopen() and sysdbclose() procedure to capture login information. As for logging the SQL you could try something with SQLTRACE, but there is no guarantee that you'll be able to capture and store all of them (it works by using a circular buffer, so the buffer may be re-used before you're able to save it... Regards. On Mon, Jan 9, 2012 at 3:31 PM, SMITH JOHN <daylight@webmails.com> wrote: > Hello > > under IDS 9.40 FC9, i want to log access to ids, and trace the sql > statements > > is there a way to do this with IDs commands only ? or IDS options ? > > by the way, i will migrate to ids 11 but in the summer, how to do the same > things on ids 11 please ? > > Thanks in advance > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00235429e164d1b84c04b61a8ec8

I am using Informix 11.50, but the system table should also be available in your version. I created a shell script and scheduled it every 10 minutes through the cron, it writes the output to a log which I monitor. . setdb.sh # Set environment to intended database echo `date` >>$PROD/log/whoson.$TDAY dbaccess sysmaster <<!>>$PROD/log/whoson.$TDAY select sid, connected, uid, pid, username[1,8], hostname[1,35], tty[1,13],sqs_statement from syssessions, syssqlstat where sqs_sessionid = sid order by 2; You can then use the onstat -g ses or onstat -g sql to gather additional information on a particular sql that was written to the log.

thanks i also found a way to do it, with server studio, but i still need to log sessions, the successfuls and the unsuccessful ones. your script and also server studio logs only the successfuls sessions. i understrand that there is a way to logs unsuccessful sessions with informix auditing, can someone tell me more ?

If by "unsuccessful sessions" you are referring to failed attempts to connect to Informix, you can check the online.log for these errors. The following statement can be used and redirected to an output file for logging. cat online.log|grep password