Re: [Info-Ingres] transaction logging on update

Paul, Marty,

The journaling state will make no difference to an update statement, as
all the updated rows need to be logged so they can be rolled back if the
transaction is aborted.

John

-----Original Message-----
From: info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx
[mailto:info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx] On Behalf Of
Martin Bowes
Sent: Monday, 22 December 2008 10:24 PM
To: Ingres and related product discussion forum
Cc: paul.caffrey@xxxxxxxxx
Subject: Re: [Info-Ingres] transaction logging on update

Hi Paul,

Presumably you want to do this because the update logging is blowing
your log file apart.

You have a few options:
1. Set nojournaling on tablename; update tablename...; set journaling on
tablename.
This should improve the logging situation, but at the cost of
removing journaling from the table until the next checkpoint runs. You
should follow the update with a checkpoint.

2. set session with on_logfull=commit;
Everything gets logged, but when the transaction log file fills
up it autocommits what it has and then continues with the rest of the
update statement.

I find this to be the best option.

3. set nologging.
This is extremely dangerous. One error of any type and you are
up for a database recovery. If you absolutely must use it, take a backup
of the database first and then run the update using an exclusive
database lock. Once completed, take another backup of the database.

Martin Bowes

-----Original Message-----
From: info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx
[mailto:info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx] On Behalf Of
paul.caffrey@xxxxxxxxx
Sent: 22 December 2008 11:08
To: info-ingres@xxxxxxxxxxxxxxxxxxxxxxxxx
Subject: [Info-Ingres] transaction logging on update

Hi

I know I can do a "copy.in" to a structured table which has no secondary
indexes and it is not logged.

I tried the same approach for an update but cannot seem to get it to
work.

I have
set autocommit on;
set nojournaling;
update ...

but even without secondary indexes it is being logged.

Is it possible to do updates without logging?

Regards
_______________________________________________
Info-Ingres mailing list
Info-Ingres@xxxxxxxxxxxxxxxxxxxxxxxxx
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres

_______________________________________________
Info-Ingres mailing list
Info-Ingres@xxxxxxxxxxxxxxxxxxxxxxxxx
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres

.

Relevant Pages

  • Re: [Info-Ingres] transaction logging on update
    ... The difference between journaling and logging always ... [Info-Ingres] transaction logging on update ... The journaling state will make no difference to an update statement, ...
    (comp.databases.ingres)
  • Re: Disabled/turn off Transcation logging while performing BCP opratio
    ... You can't completely turn off transaction logging but you can change your ... ALTER DATABASE SET RECOVERY BULK_LOGGED ... > I am doing BCP in particular database.I need to improve perfommace of BCP ...
    (microsoft.public.sqlserver.server)
  • RE: Tables of type raw
    ... Subject: Tables of type raw ... move your table to this database ... However, the current database uses transaction logging, and the ...
    (comp.databases.informix)
  • Re: ISA Connection to SQL Logging DB Drops
    ... troubleshooting logging issues: ... Starting Saturday the connection between ISA and the SQL DB ... > I check the SQL Database to make sure the USERID is good and that the ISA ... I then go over to the ISA storage server console and the the ...
    (microsoft.public.isa)
  • Re: redirecting stdout/err to mysql table
    ... connection to the database *every* time you log. ... if you have many programs logging to the same ... to the same MySQL database on a separate machine, ...
    (comp.lang.python)