Turning on logging for DDL and DML
Larry asked how to log DDL and DML activity on IDS 11.70 (Solaris) and control log file size. Replies explained that Informix's built-in onaudit facility can record operations via mnemonics (CRTB, CRDB, INRW, UPRW, DLRW, RDRW, etc.), with configurable audit directory and file size so old files roll over and can be purged, but row-level auditing is set per table/user and performance-heavy (especially RDRW for selects). Crucially, onaudit does not capture the actual SQL text. For full SQL capture, posters suggested IBM Guardium DAM, SQL Power Tools, or Exact Solutions' iWatch (noted as free without support). Larry's goal was reverse-engineering a legacy app; no single definitive choice is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
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/i
ds_au_001.htm
<http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.sec.do
c/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/i
ds_au_104.htm
<http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.sec.do
c/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.
>
>
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/i
ds_au_001.htm
>
>
<http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.sec.do
c/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/i
ds_au_104.htm
>
>
<http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.sec.do
c/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.
>
Also, in the example, it showed turning on audit individually for each table.
Is there a way to turn auditing on for the database to capture all the DML and
DDL for the day?
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/i
ds_au_001.htm
>
>
<http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.sec.do
c/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/i
ds_au_104.htm
>
>
<http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.sec.do
c/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.
>
No without additional products.
IBM Guardium DAM (Database Activity Monitoring) can be used to capture
everything that is sent to the database.
But usually this generates more information than what anyone is willing to
process/analyze.
Can you tell us what's your driver for this request? It may be easier to
advise if we understand what are you trying to solve.
Informix audit is free and easy to setup, but will not show you the SQL.
It can be activated/configured for specifi operations and users. For the 4
mnemonics that refer to row operations (RDRW, UPRW, DLRW, INRW
-respectively read a row, update a row, delete a row and insert a row) you
can specify which tables are subject to audit (a property of the create
table/alter table statements).
You cannot specify that audit for these operations is to be used for a
user, table operations. You must specify one or more of these operations
for one or more users and then all the tables subjected to auditing will
appear whenever that user(s) does that operation(s) on any of those tables.
Naturally, for DDL operations, in most case it's more simple to infer the
SQL than for DML. But even so, if you see a create tabel you won't get the
columns and column definition etc.
Guardium on the other hand can capture everything, but costs money (I think
there's now cheaper offers than in the past).
Regards.
On Wed, Sep 2, 2015 at 3:31 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> Also, in the example, it showed turning on audit individually for each
> table.
> Is there a way to turn auditing on for the database to capture all the DML
> and
> DDL for the day?
>
> 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/i
ds_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/i
ds_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.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001a113ff69c1fa345051ec4d67f
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/i
ds_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/i
ds_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
We have an old application that they are trying to reengineer, and they were
hoping to make it a little easier by capturing the DML. The company that they
had previously contracted to maintain the application left.
Larry
> To: ids@iiug.org
> From: domusonline@gmail.com
> Subject: Re: Turning on logging for DDL and DML [35707]
> Date: Wed, 2 Sep 2015 10:54:06 -0400
>
> No without additional products.
> IBM Guardium DAM (Database Activity Monitoring) can be used to capture
> everything that is sent to the database.
> But usually this generates more information than what anyone is willing to
> process/analyze.
>
> Can you tell us what's your driver for this request? It may be easier to
> advise if we understand what are you trying to solve.
> Informix audit is free and easy to setup, but will not show you the SQL.
>
> It can be activated/configured for specifi operations and users. For the 4
> mnemonics that refer to row operations (RDRW, UPRW, DLRW, INRW
> -respectively read a row, update a row, delete a row and insert a row) you
> can specify which tables are subject to audit (a property of the create
> table/alter table statements).
> You cannot specify that audit for these operations is to be used for a
> user, table operations. You must specify one or more of these operations
> for one or more users and then all the tables subjected to auditing will
> appear whenever that user(s) does that operation(s) on any of those tables.
>
> Naturally, for DDL operations, in most case it's more simple to infer the
> SQL than for DML. But even so, if you see a create tabel you won't get the
> columns and column definition etc.
> Guardium on the other hand can capture everything, but costs money (I think
> there's now cheaper offers than in the past).
>
> Regards.
>
> On Wed, Sep 2, 2015 at 3:31 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > Also, in the example, it showed turning on audit individually for each
> > table.
> > Is there a way to turn auditing on for the database to capture all the DML
> > and
> > DDL for the day?
> >
> > 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/i
ds_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/i
ds_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.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --001a113ff69c1fa345051ec4d67f
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
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/i
ds_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/i
ds_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
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/i
ds_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/i
ds_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 f
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/i
ds_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/i
ds_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
> >> > >
> >> > >
> >> > >
> >> >
> >>
> >>
> >
>
******************************************************************************
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/i
ds_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/i
ds_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
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/i
ds_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
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/i
ds_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/i
ds_au_104.htm
> > >> >
> > >> >
> > >> <
> > >>
> > >
> >
>
>
http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.sec.doc
/ids_au_104.htm
> > >> >
> > >> >
> > >> > As
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/i
ds_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
try this - just comment out the stuff you don't need
select sysadmin:task('set sql tracing off') as sql from
sysmaster:sysdual
union all select sysadmin:task("set sql user tracing off") from
sysmaster:sysdual -- USER mode
union all select sysadmin:task("set sql user tracing clear") from
sysmaster:sysdual -- USER mode
union all select sysadmin:task('set sql tracing user clear') from
sysmaster:sysdual
union all select sysadmin:task('set sql tracing database clear') from
sysmaster:sysdual
union all select sysadmin:task('set sql tracing session', 'clear') from
sysmaster:sysdual
union all select sysadmin:task('set sql tracing info') from
sysmaster:sysdual
union all select sysadmin:task('set sql tracing database list') from
sysmaster:sysdual
union all select sysadmin:task('set sql tracing user list') from
sysmaster:sysdual
union all select sysadmin:task('set sql tracing session list') from
sysmaster:sysdual
union all select sysadmin:task('set sql tracing database add','testdb')
from sysmaster:sysdual
union all select sysadmin:task('set sql tracing user add','oninit') from
sysmaster:sysdual
union all select sysadmin:task('set sql tracing user add','informix')
from sysmaster:sysdual
union all select sysadmin:task('set sql tracing session','on', sid) from
sysmaster:syssessions where username = 'localuser' and (sid in (0) or pid
in (0))
union all select sysadmin:task('set sql user tracing on ', sid) from
sysmaster:syssessions where username = 'localuser' and (sid in (0) or pid
in (0))
union all select sysadmin:task('set sql user tracing on ', 354851) from
sysmaster:sysdual
union all select sysadmin:task('set sql tracing
on',150000,'4000b','high','user') from sysmaster:sysdual
;
> 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 ope
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 fromsysmaster: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
> > > > system
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@@NL@