Thanks for your advice, all of which I would highly agree with. This
was just a simple case of a user wanting to do something he thought
would be easy without having a DBA butt in with arcane suggestions about
"units of work" and loading and unloading strategies.
Conceptually what the guy wants to do IS easy, something on the order of
"get rid of all the information in these tables older than yesterday".
The problem is most of the data in the database is older than yesterday
and it involves deleting millions and millions of rows.
He just couldn't understand why I wouldn't just turn off logging and
make things easy for him. Somehow the suggestion to use the ALTER TABLE
tablename ACTIVATE NOT LOGGED INITIALLY statement, which would in fact
turn off logging for the duration of his unit of work, didn't seem
appease him.
Sometimes, I get irritated with users who have no interest in the
technology and just want me to "make things better". But on the other
hand, he kind of has a point. We market these relational databases as
being easy to use and easy to understand and then we put all kinds of
restrictions and special qualifications on their use.
The upshot was, I increased the size of LOGFILSZ to 4096 and he agreed
to break up his work into smaller bites and between the two of us the
log full messages went away.
Ken Cofield
Group Health Cooperative
Database Consultant, ISD - EDM
(206) 448 - 5787
cofield.[Email address protected]
>CONFIDENTIALITY NOTICE:
>This message and any attached files might contain confidential
information protected by federal and state law. The information is
intended only for the use of the individual(s) or entities originally
named as addressees. The improper disclosure of such information may be
subject to civil or criminal penalties. If this message reached you in
error, please contact the sender and destroy this message. Disclosing,
copying, forwarding, or distributing the information by unauthorized
individuals or entities is strictly prohibited by law.
>
-----Original Message-----
From: Abel Mike J
[mailto:db2udbdba-ezmlmshield-x90177367.[Email address protected]
Sent: Friday, September 30, 2005 3:30 PM
To: LazyDBA Discussion
Subject: RE: ADM1823E The active log is full
I think more basic question is, why is your transaction requiring so
much logging? Do you have a runaway program that is looping doing
updates? Consider your UOW - are you committing at the appropriate
points? Increasing the log size may only be a bandage to an underlying
problem.
If the transaction is huge, perhaps consider changing your logging
strategy from circular to linear - you'll never have a 'full' log then.
Worst that would happen is you run out of phycical disk space. If you do
implement linear logging, you'll need to periodically archive off old
logs or you will run out of disc space. This is where the userexit may
come in handy.
-----Original Message-----
From: Cofield Kenneth [mailto:db2udbdba-ezmlmshield-x13539480.[Email
address protected]
Sent: Thursday, September 29, 2005 3:28 PM
To: LazyDBA Discussion
Subject: ADM1823E The active log is full
Gurus:
DB2 UDB V8.2, Solaris SunOS 5.9 . Actually the full message is:
ADM1823E The active log is full and is held by application handle
"321". Terminate this application by COMMIT, ROLLBACK or FORCE
APPLICATION.
Followed by:
ZRC=0x85100009=-2062548983=SQLP_NOSPACE
"Log File has reached its saturation point"
DIA8309C Log file was full.
User is trying to do a massive number of deletes, but not emptying the
table. Doesn't seem interested in or doesn't know how to code ALTER
TABLE tablename ACTIVATE NOT LOGGED INITIALLY.
It's a training system which is treated like production. We have
increased the size of the LOGFILSZ to 2048, increased LOGPRIMARY to 15
and increased LOGSECOND to 100, and he still gets the message. Will
changing BLK_LOG_DSK_FUL to yes or increasing MAX_LOG from zero to some
higher value help?
Ken Cofield
Group Health Cooperative
Database Consultant, ISD - EDM
>CONFIDENTIALITY NOTICE:
>This message and any attached files might contain confidential
information protected by federal and state law. The information is
intended only for the use of the individual(s) or entities originally
named as addressees. The improper disclosure of such information may be
subject to civil or criminal penalties. If this message reached you in
error, please contact the sender and destroy this message. Disclosing,
copying, forwarding, or distributing the information by unauthorized
individuals or entities is strictly prohibited by law.
>
---------------------------------------------------------------------
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
DB2 & UDB email list listserv db2-l LazyDBA home page