RE: RE: How to clear transaction log

The suggestion struck me as odd. If there is an active rollback, you'd
expect a SQL1035N database in use message for an offline backup, but how
would an online backup clear the log?

1. An offline backup requires an exclusive connection.
2. A rollback by definition maintains a connection until after it completes.
3. A rollback, like a backup, is a non-interruptible operation.
Put these 3 together and you conclude you have to let DB2 take its course.

You may find crash recovery is quicker than a long rollback with lots of
concurrent connections, but that's extreme. What is DEFINITELY guaranteed to
save you a lot of rollback time is the max_log db cfg parameter. This sets
the maximum percentage of the log that any one transaction can grab. So in a
live transactional system with lots of users, you can set it very low - say
1%, in a development environment maybe 25-50%, and in a batch production
environment the full 100%. I really recommend this; there's so many better
things to do with our time than having to explain transaction logging to end
users who insist on asking how long they will have to wait before they can
issue update queries.

In reply to 'commiting' every n records, you can only really do this in
serial programming (i.e. with cursors), and there are restart implications.
The SAVECOUNT in a LOAD, COMMITCOUNT in an IMPORT and SAVEPOINTs in stored
procedures or atomic compound SQL statements may be useful adjuncts.

Regards
Alex Levy
Sustainable Software Ltd.

-----Original Message-----
From: paul
[mailto:db2udbdba-ezmlmshield-x86701503.[Email address protected]
Sent: 30 March 2006 14:05
To: LazyDBA Discussion
Subject: Fwd: RE: How to clear transaction log

First time I have heard this one. Can anyone confirm that a backup will
indeed take transactions out of rollback and or be quicker than just
allowign DB2 to "do it's thing?"

Since I see this so much in my current job, I want to expand upon this
thread and hopefully save a few other DBA's answering this question a many
times. The transaction log fills up because one or a series of transactions
has too many uncommitted transactions. An easy way to see what transaction
it is would be to do repeated db2 list transactions show detail. Look for
one or a series of transactions that are either in rollback or executing
status. Now for the fun part, even if you db2 force application xxxx you
will have to let the database rollback. Many times the rollback takes quite
a while depending upon how large your transaction log is.

If anyone has a tool or technique for issuing a "commit" after say 10,000
updates, inserts, deletes I would love to see it (borrow it, plagerize it).

PL

> To: "LazyDBA Discussion" <[Email address protected]
> From: "Doug Kostelnik " <db2udbdba-ezmlmshield-x36288881.[Email address
protected]
> Date: Thu, 30 Mar 2006 06:43:31 -0500
> Subject: RE: How to clear transaction log
>
>
> Run a database backup.
>
> Douglas Kostelnik
> Senior Database Administrator/Architect
> [Email address protected]
> AAA Auto Club South
> (813)-289-1342
>
> -----Original Message-----
> From: Sakib R Saikia
> [mailto:db2udbdba-ezmlmshield-x45692765.[Email address protected]
>
> Sent: Thursday, March 30, 2006 2:09 AM
> To: LazyDBA Discussion
> Subject: How to clear transaction log
>
> I am unable to perform any operations, and I get the message that
>
> transaction log is full whenever I try to perform an delete/insert. Can
>
> you please suggest how to clear the transaction log.
>
> Thanks & Regards
>
> Sakib R Saikia
>
> Application Development and Web Services
> IBM India
>
>
> DLF Silokhera, Sector 30, NH 8, Gurgaon-122001 Email:
>
> sakib.r.[Email address protected]
>
> ______________________________________________________________________
> Camp: C/o BTVL, Desk No. B-58, A-236, Okhla Industrial Area 1, Ph: +91
> 11
>
> 41706000 X 3916
>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
> <html>
> <align=justify> 
> _____________________________________________________________ 
> The information transmitted is intended only for the person(s) or entity
to which it is addressed and may contain confidential and or privileged
material and should be treated as a confidential AAA Auto Club South
communication. If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited. If you are not the intended recipient,
please contact the sender and delete the material from any
computer.
> </html>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html

---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

-----------------------------------------------
Scanned by 186k-NetCleanse, spam and virus protection

DB2 & UDB email list listserv db2-l LazyDBA home page