You can create your own table/task in the sysadmin database to save SQL
traces greater than "X" number of seconds (in this case greater than 5
seconds) so you only get the slowest ones which would reduce the amount of
data you need to save.
In this case I am running the task every minute to check for new slow SQL's.
Create a separate dbspace to keep your trace data.
Create this table in the sysadmin database in the new dbspace.
You can adjust the size of the "sql_statement" column to match the size of
each trace buffer.
create raw table "informix".save_sqltrace
(
date_time datetime year to second,
sql_id int8,
sql_runtime float,
sql_sid int8,
sql_uid int8,
sql_finishtime integer,
sql_statement char(2000),
sql_database char(30)
) extent size 40000 next size 40000 lock mode row;
revoke all on "informix".save_sqltrace from "public" as "informix";
create index "informix".idx_savesql1 on "informix".save_sqltrace
(date_time) using btree ;
create index "informix".idx_savesql2 on "informix".save_sqltrace
(sql_runtime) using btree ;
create index "informix".idx_savesql3 on "informix".save_sqltrace
(sql_id) using btree ;
create index "informix".idx_savesql4 on "informix".save_sqltrace
(sql_finishtime) using btree ;
Run the following to prepopulate the table so the table will have data for
the select in the task to work.
insert into save_sqltrace select current, sql_id, sql_runtime, sql_sid,
sql_uid, sql_finishtime, sql_statement, sql_database from
sysmaster:syssqltrace where sql_runtime > 5
Load the new task into the ph_task table, change the datetime in "bold" to
be a couple minutes later than your current time.
task_sqltrace.unl
0|save_trace|Saves SQL Trace when run time greater than set
value.|TASK|0|||sysadmin|insert into save_sqltrace select current, sql_id,
sql_runtime, sql_sid, sql_uid, sql_finishtime, sql_statement, sql_database
from sysmaster:syssqltrace where sql_runtime > 5 and sql_finishtime >
(select max(sql_finishtime) from save_sqltrace)| 30 00:00:00|00:00:00|| 0
00:01:00|2015-09-02 15:00:00|0|0|t|t|t|t|t|t|t|400|PERFORMANCE|t|0|
Thanks, Jeff
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY
SORENSEN
Sent: Wednesday, September 02, 2015 1:16 PM
To: ids@iiug.org
Subject: RE: Turning on logging for DDL and DML [35718]
Thank you. I will give it a look. And thank you Art for your input.
Larry
> To: <mailto:ids@iiug.org> ids@iiug.org
> From: <mailto:George.Palmer@starwoodhotels.com>
George.Palmer@starwoodhotels.com
> Subject: RE: Turning on logging for DDL and DML [35717]
> Date: Wed, 2 Sep 2015 14:12:33 -0400
>
> SQLTRACE can be turned on for all sessions in an instantance, a
> singles DB , or a single user ( and other filters too ) I find it best
> to turn it on using the function "task" or "admin " from
inside
> the sysadmin DB ( the other whay involves bouncing the engine and an
onconfig
> parameter which will start a monitor job inside the sysadmin DB which
> can consume a lot of disk space )
>
> It is probably easiest to Google "Informix SQLRACE" and read about it
online.
>
> Be careful about the trace memory ( it comes from SHMVIRT and if you
> have limits on memory it can run them up high if you set the
> parameters high )
>
> George.
>
> -----Original Message-----
> From: <mailto:ids-bounces@iiug.org> ids-bounces@iiug.org [
<mailto:ids-bounces@iiug.org> mailto:ids-bounces@iiug.org] On Behalf Of
> LARRY SORENSEN
> Sent: Wednesday, September 02, 2015 10:57 AM
> To: <mailto:ids@iiug.org> ids@iiug.org
> Subject: RE: Turning on logging for DDL and DML [35713]
>
> I have seen SQLTRACE in a dbaccess session where I was running ad hoc
queries,
> but is it something that can be set up to collect data for outside
> applications as well?
>
> Larry
>
> > To: <mailto:ids@iiug.org> ids@iiug.org
> > From: <mailto:George.Palmer@starwoodhotels.com>
George.Palmer@starwoodhotels.com
> > Subject: RE: Turning on logging for DDL and DML [35712]
> > Date: Wed, 2 Sep 2015 12:10:38 -0400
> >
> > It might be possible to get what you want with SQLTRACE, It can be
> > setup dynamically, its included in the engine, has the rolling log
> > you are asking for, It can be a memory hog f you need to collect a
> > bunch of SQL .
> >
> > It might be worth a quick look.
> >
> > George.
> > -----Original Message-----
> > From: <mailto:ids-bounces@iiug.org> ids-bounces@iiug.org [
<mailto:ids-bounces@iiug.org> mailto:ids-bounces@iiug.org] On Behalf
> > Of LARRY SORENSEN
> > Sent: Wednesday, September 02, 2015 9:48 AM
> > To: <mailto:ids@iiug.org> ids@iiug.org
> > Subject: RE: Turning on logging for DDL and DML [35711]
> >
> > Thank you for the information.
> >
> > Larry
> >
> > > To: <mailto:ids@iiug.org> ids@iiug.org
> > > From: <mailto:paul@oninit.com> paul@oninit.com
> > > Subject: Re: Turning on logging for DDL and DML [35710]
> > > Date: Wed, 2 Sep 2015 11:44:55 -0400
> > >
> > > iwatch is free if you don't want support
> > >
> > > > Larry:
> > > >
> > > > There are two third party tools that you can use to capture 100%
> > > > of the SQL (DDL & DML) that is issued against a server including
> > > > information about the client's issuing the commands:
> > > >
> > > > SQL Power Tools - from SQL Power Tools, Inc, -
> > > > <http://www.sqlpower.com/> http://www.sqlpower.com/ - Can capture
100% of queries issued
> > > > over TCP/IP and load then into a repository (currently SQL
> > > > Server but they are working using an Informix
> > > > repository) from which their GUI viewer can display, manipulate,
> > > > and report. Display is not real time as the data is captured in
> > > > blocks of queries into flat files and bulk loaded into the
> > > > repository
> > periodically.
> > > > Uses a network sniffer utility on the server or a separate box
> > > > that has near zero impact on the server itself for capture.
> > > >
> > > > iWatch - from Exact-Solutions, Inc. -
<http://www.exact-solutions.com> www.exact-solutions.com -
> > > > Can capture 100% of queries issued over TCP/IP and load then
> > > > into a proprietary repository. Can display real time capture.
> > > > Uses a network sniffer utility on your server or a sniffer
> > > > appliance (for very high transaction rate
> > > > systems) that has very low impact on the server.
> > > >
> > > > iWatch captures and reports some more detail on query
> > > > performance metrics, has real-time capability, and a more
> > > > sophisticated GUI but is more expensive. Both will do the job for
you.
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel, President and Principal Consultant ASK Database
> > > > Management <http://www.askdbmgt.com> www.askdbmgt.com
> > > >
> > > > Blog: <http://informix-myview.blogspot.com/>
http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > opinions and do not reflect on the IIUG, nor any other
> > > > organization with which I am associated either explicitly,
> > > > implicitly, or by inference. 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 Wed, Sep 2, 2015 at 9:28 AM, LARRY SORENSEN
> > > > < <mailto:lsorensen25@msn.com> lsorensen25@msn.com>
> > > > wrote:
> > > >
> > > >> That helps a little. Is there any way to actually capture the
> > > >> DML that was run. It the example on the link, it had kind of a
> > > >> cryptic line representing an insert.
> > > >>
> > > >> Larry
> > > >>
> > > >> > To: <mailto:ids@iiug.org> ids@iiug.org
> > > >> > From: <mailto:khaled.bentebal@consult-ix.fr>
khaled.bentebal@consult-ix.fr
> > > >> > Subject: Re: Turning on logging for DDL and DML [35704]
> > > >> > Date: Tue, 1 Sep 2015 22:17:50 -0400
> > > >> >
> > > >> > Hi Larry,
> > > >> >
> > > >> > Are you talking about logging the different types de DDL
> > > >> > instructions
> > > >> (
> > > >> > such as CREATE, ALTER, etc)and the different types of DML
> > > >> > instructions (such as INSERT, SELECT, etc)?
> > > >> >
> > > >> > If so there is no Informix tool that allows you to do that
directly.
> > > >> > However, there is an AUDIT tool called *onaudit* that allows
> > > >> > to log
> > > >> all
> > > >> > kinds of operations (success or failure of the operation).
> > > >> > This tool also allows you to log operations such onmode, etc.
> > > >> > However, it is not based on the SQL operations but uses
> > > >> > special mnemonics instead to tell onaudit to log lower level
operations.
> > > >> >
> > > >> > Check this:
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > >
> >
> >
>
>
<http://www-01.ibm.com/support/knowledgecenter/#!/SSGU8G_12.1.0/com.ibm.sec.
doc/ids_au_001.htm>
http://www-01.ibm.com/support/knowledgecenter/#!/SSGU8G_12.1.0/com.ibm.sec.d
oc/ids_au_001.htm
> > > >> >
> > > >> >
> > > >> <
> > > >>
> > > >
> > >
> >
> >
>
>
<http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.se
c.doc/ids_au_001.htm>
http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.sec
.doc/ids_au_001.htm
> > > >> >
> > > >> >
> > > >> > RDRW: allows to log any reads of each row ==> so if you want
> > > >> > to log SELECTS, this allows you to log and view all of the
> > > >> > rows read thru the SELECT This fills up your logs very very
> > > >> > fast (since there a RDRW for each
> > > >> row)
> > > >> > and hirts your performance very highly depending what you are
> reading.
> > > >> > Since usually the SELECT represents 80% or more of the
> > > >> > activity, we advise not to log this operation
> > > >> > DLRW: allows you to log deletion of rows==> so a DELETE might
> > > >> > generate thousands of lines DLRW in the audit file generated
> > > >> > UPRW: allows you to log an update of a row
> > > >> > CRTB: logs the Creation of a table
> > > >> > CRDB: logs the Creation of a database
> > > >> > GRDB: logs a grant access to a database etc
> > > >> >
> > > >> > We usually use the mnemonics that we do not get that often in
> > > >> > order
> > > >> not
> > > >> > to jeopardize performance: OPDB open a database for example
> > > >> >
> > > >> > Here follows the onaudit mnemonics:
> > > >> >
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > >
> >
> >
>
>
<http://www-01.ibm.com/support/knowledgecenter/#!/SSGU8G_12.1.0/com.ibm.sec.
doc/ids_au_104.htm>
http://www-01.ibm.com/support/knowledgecenter/#!/SSGU8G_12.1.0/com.ibm.sec.d
oc/ids_au_104.htm
> > > >> >
> > > >> >
> > > >> <
> > > >>
> > > >
> > >
> >
> >
>
>
<http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.se
c.doc/ids_au_104.htm>
http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.sec
.doc/ids_au_104.htm
> > > >> >
> > > >> >
> > > >> > As far as the logging file, you tell the system where to log
> > > >> > the files
> > > >> > (directory) and the size of the audit files generated. That
> > > >> > way when a log file reaches the maximum size configured, the
> > > >> > system creates
> > > >> another
> > > >> > file for you in the same directory. That way you can purge
> > > >> > the old log files generated. DO not make the log files too
> > > >> > files since these files might fill up too fast and you will
> > > >> > have thousands
> of
> > log files.
> > > >> >
> > > >> > You can load the contents of the logs files generated into a
> > > >> > table and do stats on them afterwards.
> > > >> >
> > > >> > So the onaudit tool is very rich and simple to set up and use.
> > > >> > It
> > > >> exists
> > > >> > in the Informix engine since version 7 of IDS.
> > > >> >
> > > >> > I do not know if this is what you were looking for.
> > > >> >
> > > >> > Cordialement, Regards,
> > > >> >
> > > >> > Khaled Bentebal
> > > >> > Directeur Général - ConsultiX
> > > >> > Tél: 33 (0) 1 39 12 18 00
> > > >> > Fax: 33 (0) 1 39 12 18 18
> > > >> > Mobile: 33 (0) 6 07 78 41 97
> > > >> > Email: <mailto:khaled.bentebal@consult-ix.fr>
khaled.bentebal@consult-ix.fr Site Web:
> > > >> > <http://www.consult-ix.fr> www.consult-ix.fr
> > > >> >
> > > >> > Le 01/09/15 16:43, LARRY SORENSEN a écrit :
> > > >> > > IDS 11.70.FC7Solaris 10 Sparc Can someone send me a link on
> > > >> > > how to set up DDL and DML logging and
> > > >> how to
> > > >> > > monitor it? Is there a way to get it to overwrite the logs
> > > >> > > to limit
> > > >> the
> > > >> > size?
> > > >> > > Larry
> > > >> > >
> > > >> > >
> > > >> > >
> > > >> >
> > > >>
> > > >>
> > > >
> > >
> >
> >
>
>
****************************************************************************
***
> > > >> > > Forum Note: Use "Reply" to post a response in the
> > > >> > > discussion
forum.
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > >
> >
> >
>
>
****************************************************************************
***
> > > >> > Forum Note: Use "Reply" to post a response in the discussion
forum.
> > > >> >
> > > >>
> > > >>
> > > >>
> > > >>
> > > >
> > >
> >
> >
>
>
****************************************************************************
***
> > > >> Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >>
> > > >>
> > > >
> > > > --001a113ecb5aa0ccd2051ec574d5
> > > >
> > > >
> > > >
> > >
> >
> >
>
>
****************************************************************************
***
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > >
> > > --
> > > Paul Watson
> > > Tel: +1 913-674-0360
> > > Mob: +1 913-387-7529
> > > Web: <http://www.oninit.com> www.oninit.com
> > >
> > > Oninit® is a registered trademark of Oninit LLC
> > >
> > > Failure is not as frightening as regret.
> > > If you want to improve, be content to be thought foolish and stupid.
> > > What this country needs are more unemployed politicians
> > >
> > >
> > >
> >
> >
>
>
****************************************************************************
***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
>
>
****************************************************************************
***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> > This electronic message transmission contains information from the
> > Company that may be proprietary, confidential and/or privileged. The
> > information is intended only for the use of the individual(s) or
> > entity named above. If you are not the intended recipient, be aware
> > that any disclosure, copying or distribution or use of the contents
> > of this information is prohibited. If
> you
> > have received this electronic transmission in error, please notify
> > the
> sender
> > immediately by replying to the address listed in the "From:" field.
> >
> >
> >
>
>
****************************************************************************
***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> This electronic message transmission contains information from the
> Company that may be proprietary, confidential and/or privileged. The
> information is intended only for the use of the individual(s) or
> entity named above. If you are not the intended recipient, be aware
> that any disclosure, copying or distribution or use of the contents of
> this information is prohibited. If
you
> have received this electronic transmission in error, please notify the
sender
> immediately by replying to the address listed in the "From:" field.
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
