 |
IDS Forum
RE: Turning on logging for DDL and DML
Posted By: Paul Watson
Date: Wednesday, 2 September 2015, at 2:23 p.m.
In Response To: RE: Turning on logging for DDL and DML (Palmer, George)
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 operations but uses special
>> > >> > mnemonics instead to tell onaudit to log lower level operations.
>> > >> >
>> > >> > Check this:
>> > >> >
>> > >> >
>> > >>
>> > >>
>> > >
>> >
>>
>>
>
> http://www-01.ibm.com/support/knowledgecenter/#!/SSGU8G_12.1.0/com.ibm.sec.doc/ids_au_001.htm
>> > >> >
>> > >> >
>> > >> <
>> > >>
>> > >
>> >
>>
>>
>
> http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.sec.doc/ids_au_001.htm
>> > >> >
>> > >> >
>> > >> > RDRW: allows to log any reads of each row ==> so if you want to
>> > >> > log SELECTS, this allows you to log and view all of the rows
>> > >> > read thru the SELECT This fills up your logs very very fast
>> > >> > (since there a RDRW for each
>> > >> row)
>> > >> > and hirts your performance very highly depending what you are
> reading.
>> > >> > Since usually the SELECT represents 80% or more of the
>> > >> > activity, we advise not to log this operation
>> > >> > DLRW: allows you to log deletion of rows==> so a DELETE might
>> > >> > generate thousands of lines DLRW in the audit file generated
>> > >> > UPRW: allows you to log an update of a row
>> > >> > CRTB: logs the Creation of a table
>> > >> > CRDB: logs the Creation of a database
>> > >> > GRDB: logs a grant access to a database etc
>> > >> >
>> > >> > We usually use the mnemonics that we do not get that often in
>> > >> > order
>> > >> not
>> > >> > to jeopardize performance: OPDB open a database for example
>> > >> >
>> > >> > Here follows the onaudit mnemonics:
>> > >> >
>> > >> >
>> > >> >
>> > >>
>> > >>
>> > >
>> >
>>
>>
>
> http://www-01.ibm.com/support/knowledgecenter/#!/SSGU8G_12.1.0/com.ibm.sec.doc/ids_au_104.htm
>> > >> >
>> > >> >
>> > >> <
>> > >>
>> > >
>> >
>>
>>
>
> http://www-01.ibm.com/support/knowledgecenter/#%21/SSGU8G_12.1.0/com.ibm.sec.doc/ids_au_104.htm
>> > >> >
>> > >> >
>> > >> > As far as the logging file, you tell the system where to log
>> > >> > the files
>> > >> > (directory) and the size of the audit files generated. That way
>> > >> > when a log file reaches the maximum size configured, the system
>> > >> > creates
>> > >> another
>> > >> > file for you in the same directory. That way you can purge the
>> > >> > old log files generated. DO not make the log files too files
>> > >> > since these files might fill up too fast and you will have
>> > >> > thousands
> of
>> log files.
>> > >> >
>> > >> > You can load the contents of the logs files generated into a
>> > >> > table and do stats on them afterwards.
>> > >> >
>> > >> > So the onaudit tool is very rich and simple to set up and use.
>> > >> > It
>> > >> exists
>> > >> > in the Informix engine since version 7 of IDS.
>> > >> >
>> > >> > I do not know if this is what you were looking for.
>> > >> >
>> > >> > Cordialement, Regards,
>> > >> >
>> > >> > Khaled Bentebal
>> > >> > Directeur Général - ConsultiX
>> > >> > Tél: 33 (0) 1 39 12 18 00
>> > >> > Fax: 33 (0) 1 39 12 18 18
>> > >> > Mobile: 33 (0) 6 07 78 41 97
>> > >> > Email: khaled.bentebal@consult-ix.fr Site Web:
>> > >> > www.consult-ix.fr
>> > >> >
>> > >> > Le 01/09/15 16:43, LARRY SORENSEN a écrit :
>> > >> > > IDS 11.70.FC7Solaris 10 Sparc Can someone send me a link on
>> > >> > > how to set up DDL and DML logging and
>> > >> how to
>> > >> > > monitor it? Is there a way to get it to overwrite the logs to
>> > >> > > limit
>> > >> the
>> > >> > size?
>> > >> > > Larry
>> > >> > >
>> > >> > >
>> > >> > >
>> > >> >
>> > >>
>> > >>
>> > >
>> >
>>
>>
>
> *******************************************************************************
>> > >> > > Forum Note: Use "Reply" to post a response in the discussion
>> forum.
>> > >> > >
>> > >> > >
>> > >> >
>> > >> >
>> > >> >
>> > >>
>> > >>
>> > >
>> >
>>
>>
>
> *******************************************************************************
>> > >> > Forum Note: Use "Reply" to post a response in the discussion
>> forum.
>> > >> >
>> > >>
>> > >>
>> > >>
>> > >>
>> > >
>> >
>>
>>
>
> *******************************************************************************
>> > >> Forum Note: Use "Reply" to post a response in the discussion forum.
>> > >>
>> > >>
>> > >
>> > > --001a113ecb5aa0ccd2051ec574d5
>> > >
>> > >
>> > >
>> >
>>
>>
>
> *******************************************************************************
>> > > Forum Note: Use "Reply" to post a response in the discussion forum.
>> > >
>> >
>> > --
>> > Paul Watson
>> > Tel: +1 913-674-0360
>> > Mob: +1 913-387-7529
>> > Web: www.oninit.com
>> >
>> > Oninit® is a registered trademark of Oninit LLC
>> >
>> > Failure is not as frightening as regret.
>> > If you want to improve, be content to be thought foolish and stupid.
>> > What this country needs are more unemployed politicians
>> >
>> >
>> >
>>
>>
>
> *******************************************************************************
>> > Forum Note: Use "Reply" to post a response in the discussion forum.
>> >
>>
>>
>>
>
> *******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>> This electronic message transmission contains information from the
>> Company that may be proprietary, confidential and/or privileged. The
>> information is intended only for the use of the individual(s) or
>> entity named above. If you are not the intended recipient, be aware
>> that any disclosure, copying or distribution or use of the contents of
>> this information is prohibited. If
> you
>> have received this electronic transmission in error, please notify the
> sender
>> immediately by replying to the address listed in the "From:" field.
>>
>>
>>
>
> *******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> This electronic message transmission contains information from the Company
> that may be proprietary, confidential and/or privileged. The information
> is
> intended only for the use of the individual(s) or entity named above. If
> you
> are not the intended recipient, be aware that any disclosure, copying or
> distribution or use of the contents of this information is prohibited. If
> you
> have received this electronic transmission in error, please notify the
> sender
> immediately by replying to the address listed in the "From:" field.
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--
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
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
