IDS Forum

Re: Turning on logging for DDL and DML

Posted By: Khaled Bentebal
Date: Tuesday, 1 September 2015, at 10:17 p.m.

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

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.
>
>

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.