[Info-ingres] Weird problem in Ingres 10

[Laframboise, André (BAC/LAC)]

I've rarely used the option to dump the audit data to a file. I find it more useful as text output to track down problem.
Years ago, I wrote a program that reads the text and re-created the SQL to rollback older changes to temp tables. Turned Deletes into Inserts, Inserts into deletes and for updates, it flipped the New row back to the Old one.

I still don't get why the journal files would contain aborted transaction. Seems like a waste of disk space and useless during a rollback.
Unless the Rollback actually reruns the all the aborted/rolled-back transaction ?

André Laframboise

Conseiller Principal Base de Données, Direction générale de l'Innovation et du Dirigeant principal de l'information
Bibliothèque et Archives Canada / Gouvernement du Canada
andre.laframboise at canada.ca / Tél. : 613-298-1346

Senior Database Advisor, Innovation and Chief Information Officer Branch
Library and Archives Canada / Government of Canada
andre.laframboise at canada.ca / Tel: 613-298-1346

-----Original Message-----
From: info-ingres-bounces at lists.planetingres.org <info-ingres-bounces at lists.planetingres.org> On Behalf Of Mark
Sent: Friday, May 18, 2018 6:55 AM
To: info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] Weird problem in Ingres 10

On Fri, 18 May 2018 09:11:26 +1000, "pwhite peercore"
<pwhite at peercore.com.au> wrote:

>This example thanks to Thien.
>
> 
>
>auditdb mydb  -b13-may-2018:07:00 -e14-may-2018:07:00 
>-table=gl_batch_detail -file
>
> 
>
>This will create a file ( ‘gl_batch_detail.trl’ ) that contains all the 
>transactions related to ‘gl_batch_detail’ table
>
>Then run this SQL
>
> 
>
>/*create audit structure table for output file from auditdb. Keep this 
>table handy for future use*/
>
>create table aud_structure
>
>       (datetemp ingresdate not null with default,
>
>       username char(32) not null with default,
>
>       operation char(8) not null with default,
>
>       tranid1 integer not null with default,
>
>       tranid2 integer not null with default,
>
>       table_id1 integer not null with default,
>
>       table_id2 integer not null with default) \g
>
> 
>
>create table gl_batch_detail_audit
>
>as select a.*, b.* from aud_structure a, gl_batch_detail b
>
>where 1 = 2 \g
>
> 
>
>copy table gl_batch_detail_audit () from 'gl_batch_detail.trl' \g
>
> 
>
>select top 20 * from gl_batch_detail_audit \g

In my case there are only two rows one with operation="begin" and one with operation="commit".  None of the other columns contain meaningful data [All string fields are '.' or NULL and numeric data is always 0 or NULL]

>From: info-ingres-bounces at lists.planetingres.org 
>[mailto:info-ingres-bounces at lists.planetingres.org] On Behalf Of Alex 
>Hanshaw
>Sent: Friday, 18 May 2018 2:14 AM
>To: Mark; info-ingres at lists.planetingres.org
>Subject: Re: [Info-ingres] Weird problem in Ingres 10
>
> 
>
>See:
>
> 
>
>http://docs.actian.com/ingres/11.0/index.html#page/DatabaseAdmin%2FHow_
>to_Load_an_Audit_Trail_as_a_Table.htm
>
> 
>
>The example is based on the columns in the example table. You’ll need to define the target table based on the audited table.
>
> 
>
>Alex

--
<insert witty sig here>
_______________________________________________
Info-ingres mailing list
Info-ingres at lists.planetingres.org
http://lists.planetingres.org/mailman/listinfo/info-ingres