trigger on SMI table syssqltrace
Hi,
Could it effect the performance if we increase the "ntraces" value or set the
"level" to high for SQLTRACE where default is:
SQLTRACE level=low,ntraces=1000,size=2,mode=global
We created a trigger (on each insert on syssqltrce) as
create trigger log_sql_statements insert on syssqltrace referencing new as newfor each row
when ((new.sql_runtime > 1))
(
insert into "db_monitoring":sql_statement_log(sql_sorttotal, sql_totaltime,
sql_runtime, sql_maxtime, sql_sqlmemory, sql_statement)
values(new.sql_sorttotal, new.sql_totaltime,new.sql_runtime, new.sql_maxtime,
new.sql_sqlmemory, new.sql_statement )
)
trigger created successfully and can be seen through dbaccess (but cannot be
get through dbschema as it says syssqltrace is psedo table), but it does not
working. Why is it so?
regards,
Kamran
Yes it will affect performance depending on how aggressive you make it.
Triggers on pseudo-tables will not fire. That's documented somewhere.
My dbschema replacement utility, myschema, will output a schema for
pseudo-tables, FWIW. Myschema is contained in the package utils2_ak which
you can download from the Oninit website (www.oninit.com/utils) or the IIUG
Software Repository (www.iiug.org/software).
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Tue, Jul 28, 2009 at 11:46 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote:
> Hi,
> Could it effect the performance if we increase the "ntraces" value or set
> the
> "level" to high for SQLTRACE where default is:
> SQLTRACE level=low,ntraces=1000,size=2,mode=global
> We created a trigger (on each insert on syssqltrce) as
>
> create trigger log_sql_statements insert on syssqltrace referencing new as> new
> for each row
> when ((new.sql_runtime > 1))
> (
> insert into "db_monitoring":sql_statement_log(sql_sorttotal, sql_totaltime,
> sql_runtime, sql_maxtime, sql_sqlmemory, sql_statement)
> values(new.sql_sorttotal, new.sql_totaltime,new.sql_runtime,
> new.sql_maxtime,
> new.sql_sqlmemory, new.sql_statement )
> )
>
> trigger created successfully and can be seen through dbaccess (but cannot
> be
> get through dbschema as it says syssqltrace is psedo table), but it does
> not
> working. Why is it so?
>
> regards,
> Kamran
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5bd8c9c21bd046fc7eab4
SMI tables (tables in the symaster tables) are not
real tables but views over in memory data. Triggers
only work when the data is really inserted, this data is
not inserted and hence the trigger will never fire.
The newer versions of OAT have the ability to capture
the SQL trace data. It uses an database scheduler job
to capture the data an regular intervals and purge
data the is to old.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 07/28/2009 08:46:18 AM:
> [image removed]
>
> trigger on SMI table syssqltrace [16522]
>
> KAMRAN HAQ
>
> to:
>
> ids
>
> 07/28/2009 08:47 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hi,
> Could it effect the performance if we increase the "ntraces" value or set
the
> "level" to high for SQLTRACE where default is:
> SQLTRACE level=low,ntraces=1000,size=2,mode=global
> We created a trigger (on each insert on syssqltrce) as
>
> create trigger log_sql_statements insert on syssqltrace referencing> new as new
> for each row
> when ((new.sql_runtime > 1))
> (
> insert into "db_monitoring":sql_statement_log(sql_sorttotal,
sql_totaltime,
> sql_runtime, sql_maxtime, sql_sqlmemory, sql_statement)
> values(new.sql_sorttotal, new.sql_totaltime,new.sql_runtime,
new.sql_maxtime,
> new.sql_sqlmemory, new.sql_statement )
> )
>
> trigger created successfully and can be seen through dbaccess (but cannot
be
> get through dbschema as it says syssqltrace is psedo table), but it does
not
> working. Why is it so?
>
> regards,
> Kamran
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>