IDS Forum

Re: Turning on logging for DDL and DML

Posted By: Art Kagel
Date: Wednesday, 2 September 2015, at 1:55 p.m.

In Response To: RE: Turning on logging for DDL and DML (LARRY SORENSEN)

Larry:

Yes, SQLTRACE captures all SQL into an in-memory ring buffer that you
size. But that is not persistent, the queries in memory are transient.
For example if you have an SQLTRACE buffer setup to hold 2000 queries and
you process 1million queries an hour then you would have to poll the
sysmaster:syssqltrace table every 7 seconds and dump the results to disk
(or set up a OAT sensor to do that for you) to not miss anything. If you
use OAT to poll the buffer it will write to a permanent table in sysadmin
which will severely impact server performance. Even an external process
writing to flat files would have to be set up carefully to minimize impact
on the server. That's not a practical solution in my mind when there are
reasonable alternatives (SQL Power Tools is about $5,000 per server) which
is why I did not bring it up.

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 11:56 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote:

> 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: ids@iiug.org
> > From: 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: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> LARRY
> > SORENSEN
> > Sent: Wednesday, September 02, 2015 9:48 AM
> > To: ids@iiug.org
> > Subject: RE: Turning on logging for DDL and DML [35711]
> >
> > Thank you for the information.
> >
> > Larry
> >
> > > To: ids@iiug.org
> > > From: 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/ - 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. - 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 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 9:28 AM, LARRY SORENSEN <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: ids@iiug.org
> > > >> > From: 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/#%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/#%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: khaled.bentebal@consult-ix.fr Site Web: 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: 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.
>
>

--001a113ecb5adb036c051ec75e6b

Messages In This Thread

• Turning on logging for DDL and DML
  LARRY SORENSEN -- Tuesday, 1 September 2015, at 10:43 a.m.
  • Re: Turning on logging for DDL and DML
    Khaled Bentebal -- Tuesday, 1 September 2015, at 10:17 p.m.
    • RE: Turning on logging for DDL and DML
      LARRY SORENSEN -- Wednesday, 2 September 2015, at 10:28 a.m.
      • Re: Turning on logging for DDL and DML
        Art Kagel -- Wednesday, 2 September 2015, at 11:38 a.m.
        • Re: Turning on logging for DDL and DML
          Paul Watson -- Wednesday, 2 September 2015, at 11:44 a.m.
          • RE: Turning on logging for DDL and DML
            LARRY SORENSEN -- Wednesday, 2 September 2015, at 11:48 a.m.
            • RE: Turning on logging for DDL and DML
              Palmer, George -- Wednesday, 2 September 2015, at 12:10 p.m.
              • RE: Turning on logging for DDL and DML
                LARRY SORENSEN -- Wednesday, 2 September 2015, at 12:56 p.m.
                • Re: Turning on logging for DDL and DML
                  Art Kagel -- Wednesday, 2 September 2015, at 1:55 p.m.
                • RE: Turning on logging for DDL and DML
                  Palmer, George -- Wednesday, 2 September 2015, at 2:12 p.m.
                  • RE: Turning on logging for DDL and DML
                    LARRY SORENSEN -- Wednesday, 2 September 2015, at 2:16 p.m.
                    • RE: Turning on logging for DDL and DML
                      Jeff Filippi -- Wednesday, 2 September 2015, at 2:51 p.m.
                      • Re: Turning on logging for DDL and DML
                        Art Kagel -- Wednesday, 2 September 2015, at 3:11 p.m.
                  • RE: Turning on logging for DDL and DML
                    Paul Watson -- Wednesday, 2 September 2015, at 2:23 p.m.
    • RE: Turning on logging for DDL and DML
      LARRY SORENSEN -- Wednesday, 2 September 2015, at 10:31 a.m.
      • Re: Turning on logging for DDL and DML
        Fernando Nunes -- Wednesday, 2 September 2015, at 10:54 a.m.
        • RE: Turning on logging for DDL and DML
          LARRY SORENSEN -- Wednesday, 2 September 2015, at 11:40 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.