Re: Tool to Record Database changes (inserts, deletes, etc.)

<pookguy88@gmail.com> wrote in message news:1136849727.251928.189330@g43g2000cwa.googlegroups.com... > Is there a tool that somehow records what activity there was in the > database for a certain time period? > > I have a front-end program that has Informix on the backend and I want > to record what changes it did to the database when I do certain things. > So I need a program that I can start and stop recording when I do > things and/or record a pre-set time period of database activity. > > Thanks > Question --- would a log analysis utilty be a desirable thing to have? We have onlog, but it only dumps the logs - it does not really perform any analysis.

Madison Pruet wrote: > > <pookguy88@gmail.com> wrote in message > news:1136849727.251928.189330@g43g2000cwa.googlegroups.com... > > Is there a tool that somehow records what activity there was in the > > database for a certain time period? > > > > I have a front-end program that has Informix on the backend and I want > > to record what changes it did to the database when I do certain things. > > So I need a program that I can start and stop recording when I do > > things and/or record a pre-set time period of database activity. > > > > Thanks > > > > Question --- would a log analysis utilty be a desirable thing to have? We > have onlog, but it only dumps the logs - it does not really perform any > analysis. > > A support tool that could extract information for the onlog output would be useful. It would beat having to write and maintain AND port scripts Cheers Paul Paul Watson Oninit Ltd Tel : +44 1414161772 Cell : +44 7818 003457 Web : www.oninit.com

What kind of analysis are you thinking of?

Yes!!!!!! Something that would allow for undoing a transaction or finding out when a row was deleted, updated or table dropped. You can do this now but you really have to work at it. Also have the tool do the same type of analysis that onaudit does. This would be after the fact but the performance would be better. The alarm program, prior to backing of the log, could run a small report based on parameters passed and this could be saved or loaded to a table for auditing. The same information that onaudit is gathering would be good. Should we all enter a feature request to get the ball rolling? Thanks RDM

Paul, Do you have some script that already do some analysis on the log files? If so are they posted @ iiug.org or can we get a copy of them? Thanks RDM

I've looked into using the output of onlog in the past - one of the main issues is the UPDATE statement, which (in most circumstances) only logs the differences in the data. (Even down to just the couple of characters you change in the middle of a large string). (FWIW - Inserts and deletes are relatively easy...) I did mention this to some people at Informix to see if we could put some flag against the logging of UPDATE to always get the full before and after record ... On Thursday 12 January 2006 13:11, Roy Mercer wrote: > Paul, > > Do you have some script that already do some analysis on the log files? > If so are they posted @ iiug.org or can we get a copy of them? > > Thanks > > RDM > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list -- Mike Aubury

Roy Mercer wrote: > Paul, > > Do you have some script that already do some analysis on the log files? > If so are they posted @ iiug.org or can we get a copy of them? > > Thanks > > RDM > Written for specific customers and therefore belong to them and not me. Never got round to writing a generic one, probably no point now if IBM are going to do one Cheers Paul Paul Watson Oninit Ltd Tel : +44 1414161772 Cell : +44 7818 003457 Web : www.oninit.com

I have some Unix shell scripts that create the files needed to set up trigger-based logging of a specified table. I've been thinking about posting them on iiug.org. If the table of interest is "abc", there are two log tables: logabc and clogabc. logabc is designed to be used as a daily log. It has no user defined indexes, to minimize insert time. clogabc is a cumulative log, and can be indexed however you want. Typically, the contents of logabc are moved to clogabc each night. The two log tables have the same layout: log date as a DATE, log time as a DATETIME HOUR to FRACTION(3), activity type code (insert, update, delete), and the user's login-ID. Also, for each column "col" of the table being logged, the columns "o_col" and "n_col" are created in the log table to hold pre- and post-event images of the row. Both pre- and post- images are logged, as appropriate: just post- for inserts, just pre- for deletes, and both for updates. There is also a provision for saving "snapshot" records in the log. The scripts use awk/nawk to parse the output from dbschema for the production table to be logged. They generate SQL to create: the daily and cumulative log tables a default index on the cumulative log table the triggers to be placed on the production table There is also SQL to do the daily move and to take snapshots. There is also a script and template file that allows you to create a customized report program in Informix-4GL. The program generation script lets you specify things like which column(s) in the production table comprise the primary key, which columns the program should allow searches on, etc. The 4GL report program then also provides a number of options and search parameters. Strong points: It's easy to set up simple logging on a table and get meaningful reports. Weak points: I haven't tested all data types. There are no scripts to automate altering the log table schemas when the production table schema is altered. My design decisions and compromises may not be the ones that you would make. I don't have a README yet, but I can send you what I have if you want to look at it. HTH, Walt. "Roy Mercer" <roy.mercer@gmail.com> wrote in message news:1137071276.703741.82630@g44g2000cwa.googlegroups.com... > Yes!!!!!! > > Something that would allow for undoing a transaction or finding out > when a row was deleted, updated or table dropped. You can do this now > but you really have to work at it. Also have the tool do the same > type of analysis that onaudit does. This would be after the fact but > the performance would be better. The alarm program, prior to backing > of the log, could run a small report based on parameters passed and > this could be saved or loaded to a table for auditing. The same > information that onaudit is gathering would be good. > > Should we all enter a feature request to get the ball rolling? > > Thanks > > RDM >