IDS Forum

RE: Turning on logging for DDL and DML

Posted By: LARRY SORENSEN
Date: Wednesday, 2 September 2015, at 2:16 p.m.

In Response To: RE: Turning on logging for DDL and DML (Palmer, George)

Thank you. I will give it a look. And thank you Art for your input.

Larry

> To: ids@iiug.org
> From: 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: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY
> SORENSEN
> Sent: Wednesday, September 02, 2015 10:57 AM
> To: 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: 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.
>
> 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.
>

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.