 |
IDS Forum
Re: Turning on logging for DDL and DML
Posted By: Art Kagel
Date: Wednesday, 2 September 2015, at 3:11 p.m.
In Response To: RE: Turning on logging for DDL and DML (Jeff Filippi)
Jeff:
My personal "Best Practice" is to never write performance tracking data to
the server that I am trying to track! I would write that data to a
separate server either directly or by unloading the data to a file and then
loading the file up on the "repository" system. That is why I prefer tools
like Server Studio/Sentinel, SQL Power Tools, and iWatch/iReplay that use
an independent repository to OAT which writes its tracking data to the
servers it is monitoring.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: 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 1:51 PM, Jeff Filippi <iiug@itdataconsulting.com>
wrote:
> 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.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e013c6614bd0788051ec86de1
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
