sql trace not capturing all activity

Hi, I hope someone can help. I'm trying to trace all activity on one specific database, using commands select sysadmin:task('set sql tracing database add','trace_this_db') from sysmaster:sysdual; and select sysadmin:task('set sql tracing on',150000,'4000b','high','global') from sysmaster:sysdual; and select sysadmin:task('set sql tracing session','on', sid) from sysmaster:syssessions ; It works - but there is some activity that is not being captured, I'm pretty sure our application vendor is running commands as user informix. Is there something special about that user that prevents the commands from being logged? Thanks

Matt - There is always a possibility that some sqls won't get captured. It depends on the turnover in the sysmaster:syssqltraces table. You're capturing all users (since the mode is "global") - if you just want informix, set the mode to "User" and for user "informix" only. That will reduce the captures and make it less likely the turnover will lose sqls. It also depends on how often you are running the "save sql trace" task, which moves rows in sysmaster:syssqltraces to sysadmin:mon_syssqltrace. The command I use is: database sysadmin; execute function task("set sql tracing on", $NTRACES, $BUFFER_SIZE, "$MODE","global"); Typically, if you disable the "Job Cleanup" task, sysadmin:mon_syssqltrace will stay persistent and have the rows that it moved from sysmaster:syssqltrace. Question for everyone though: I have disabled the "Job Cleanup" task, but mon_syssqltrace IS getting purged. I have no idea why? Trying to tune our month-end jobs and am definitely losing rows there as the row count keeps going down (and then back up of course). Hopefully John Miller will chime in here. This is definitely his area. Thanks - Mark Scranton The Mark Scranton Group www.markscranton.com

Hi, The 'Job Results Cleanup' task cleans only the result from the jobs in ph_bg_jobs_results. For Sensors that have a result table normally you will want to look for the column tk_delete on the ph_task table. Best regards On Mon, 29 Jun 2015 at 15:52 MARK SCRANTON <mark@markscranton.com> wrote: > Matt - > > There is always a possibility that some sqls won't get captured. It > depends on > the turnover in the sysmaster:syssqltraces table. You're capturing all > users > (since the mode is "global") - if you just want informix, set the mode to > "User" and for user "informix" only. That will reduce the captures and > make it > less likely the turnover will lose sqls. > > It also depends on how often you are running the "save sql trace" task, > which > moves rows in sysmaster:syssqltraces to sysadmin:mon_syssqltrace. > > The command I use is: > database sysadmin; > execute function task("set sql tracing on", $NTRACES, $BUFFER_SIZE, > "$MODE",> "global"); > > Typically, if you disable the "Job Cleanup" task, sysadmin:mon_syssqltrace > will stay persistent and have the rows that it moved from > sysmaster:syssqltrace. > > Question for everyone though: I have disabled the "Job Cleanup" task, but > mon_syssqltrace IS getting purged. I have no idea why? Trying to tune our > month-end jobs and am definitely losing rows there as the row count keeps > going down (and then back up of course). > > Hopefully John Miller will chime in here. This is definitely his area. > > Thanks - > Mark Scranton > The Mark Scranton Group > www.markscranton.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec53f3985a525050519a99006

Thanks Ricardo ... that was definitely why rows are getting purged. I assume if I set tk_delete to 00:00 (since it's an interval data type), the purge won't occur at all? Thanks - Mark

When I want to manage the clean up of a result table "outside" I set 'tk_delete' to NULL, there is no way of doing that on the OAT page. I believe that if you set it to 0 00:00:00 it will clean all the results and then run, then you will only have the last set of data. Best, On Mon, 29 Jun 2015 at 16:25 MARK SCRANTON <mark@markscranton.com> wrote: > Thanks Ricardo ... that was definitely why rows are getting purged. I > assume > if I set tk_delete to 00:00 (since it's an interval data type), the purge > won't occur at all? > > Thanks - > Mark > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c23d040bc4fa0519aa2c10