Tracing sql statements
fpr...@my-dejanews.com
issued by a user. Does anyone have a suggestion or know of a way to run a
complete trace on a specific user?
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Simpson, Don
You can use the Auditing feature, onaudit. This audits just about anything
you want, by specific users as well. Only, it doesn't show you the actuall
SQL statement executed, just the table Id and row Id effected, and whether
it was an update, delete, etc.. You can also use the sysmater database and
select from syssqlcurses table. ie.
select scs_sqlstatement
from syssqlcurses, sysuserthreads
where scs_sessionid = us_sid
and us_name = "username"
However, this may not pick up every query, even if ran continuously, because
it takes a bit to pull the data from the system; some queries will have
already come and gone.
I too would like to know if there really is a way to capture every sql
statement of a user.
Don.
Art S. Kagel
>
> I've used the "onstat -g sql xxx' statement to view the last sql statement
> issued by a user. Does anyone have a suggestion or know of a way to run a
> complete trace on a specific user?
dbaccess sysmaster -
> select * from syssqexplain where sqx_sessionid = xxxx;
This view is inconsistent as to what queries it tracks and what it does
not. Also it does almost exactly what onstat -g sql does so if you
have a very busy multiprocessor system and onstat -g sql hangs or runs
forever this query will do the same (a word to the wise). If onstat -g
sql works well on your system then this will also.
Art S. Kagel
Willem Roos
Simpson, Don wrote:
>
> I too would like to know if there really is a way to capture every sql
> statement of a user.
I believe that's not an option. I'm writting a quick and dirty proggy
that goes "select distinct sqx_sqlstatement from syssqexplain" every
configurable interval and sticks the results in a different
database.table. I hope to have caught all queries after some time. It's
nothing fantastic but it helps - if you want it, mail me.
--
/* ----------------------------------------------------- *
* Willem Roos wr...@shoprite.co.za *
* ro...@mweb.co.za *
* 0(+27)21 980 4941 *
* 0(+27)21 919 0198 *
* ----------------------------------------------------- */
Jonathan Leffler
> I too would like to know if there really is a way to capture
> every sql statement of a user.
I don't recall which version numbers are under discussion. I
have a surrogate for sqlexec or sqlturbo which can be used in
version 5.x and earlier systems to monitor the communications
between program and database. Actually, I have a couple of them,
but that's another story.
For ClientSDK programs, there's the sqliprint program which can
be used on the log files generated when you set some environment
variable -- my memory fails me at home, late at night, when I'm
typing this. If you're using earlier versions of ESQL/C, then you
may be able to get hold of sqliprint after all, but probably not
and you are stuck.
--
Jonathan Leffler (jlef...@informix.com, jlef...@earthlink.net)
Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN
#include <disclaimer.h>
FProse
Thanks,
Fred