By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 IT Pros & Developers. It's quick & easy.

log files full autocommit

P: n/a
We are being faced with a situation where our Windows Server based UDB
(V7) crashes every so often due to log files getting too full.
We are seeing lots of messages in the logs about full log files and
escalating locks.
We think both the escalating locks and the full log files are caused by
queries that bring back too much data without enough commits.

ON another forum I heard a reference to some kind of COMMIT parameter
in Control Center which defaults to a million or something like that
and can be changed but I have had no luck finding this.
Any help would be appreciated.

THanks

Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
for full log files do one of the following:
- increase log file size
- commit more frequently - this would affect your inserts, updates or
imports

for lock escalation:
- commit more frequently also
- possibly increase lock list size, maybe adjust maxlocks

not familiar with a control center configuration for commiting, command
center does allow you to turn autocommit on/off. But most folks don't
run production queries via command center.

If you can't adjust commit interval consider adjusting the size of the
transaction in other ways. More info about your problem would be
helpful.

Also, this online manual is really good:
http://publib.boulder.ibm.com/infoce...lp/conhow2.htm
buck

Nov 12 '05 #2

P: n/a
Using CS instead of RR will lower the number of locks on SELECTS. I suspect
that one or more of the following are the case:
1) updates, inserts and deletes aren't being committed,
2) locklist is tiny,
3) log file allocations are far too small.

Post the locklist and log file parameters

Why would you want to select millions of rows using Control Centre? It will
take DAYS to display the results and you'll never look at them anyway.
Outside of Control Centre, Control Centre's options are irrelevant.

<pa***@soccermail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
We are being faced with a situation where our Windows Server based UDB
(V7) crashes every so often due to log files getting too full.
We are seeing lots of messages in the logs about full log files and
escalating locks.
We think both the escalating locks and the full log files are caused by
queries that bring back too much data without enough commits.

ON another forum I heard a reference to some kind of COMMIT parameter
in Control Center which defaults to a million or something like that
and can be changed but I have had no luck finding this.
Any help would be appreciated.

THanks

Nov 12 '05 #3

P: n/a
Firstly our UDB is used only for testing applications before they are
ported to MVS (OS/390, Z/OS whatever...)

I asked the DBA (who is really a mainframe DB2 muddling along as a
Windows UDB DB2 as well) the questions about locklist and logfile
parms and I was told that they are moving the UDB database to a new
server and they hope that will fix the problem.
He told me that he had no time to find the answers.

I can find those numbers for my personal instance of UDB on my
workstation by using "db2set -lr" but that doesn't work when I am
connected to the Server.

A lot of the querying is done adhoc using COmmand Center and though it
is not going to return millions of rows obvioulsy it is returning
enough to cause log file full issues.

I can encourage my fellow developers to add "WITH UR" at the end of
their SELECT statements and I trust that will reduce the locks
acquired.

Thanks y'all!

Nov 12 '05 #4

P: n/a
locklist size and number/size of logfiles are not visible by db2set
-lr, they are accessible by database configuration. Something similar
is needed:

db2 "get db cfg for <your_db_name>" | grep "log files"

db2 "get db cfg for <your_db_name>" | grep "lock"

the greps are optional, I used them just to view the relevant text.

Also, in my opinion, *just* moving to a newer server won't solve the
problem, you may still need to tune your db for appropriate usage.
regards,
dotyet

Nov 12 '05 #5

P: n/a
Thanks.
As it turns out I got the values from the Control Center itself, I
should have tried that out first.
FWIW here are the values
Log File SIze 250
Number of Primary Log files 3
Number of Secondary Log Files 2
LockList 1084

I agree that moving to the server won't fix the problem, but as I said
that is the stance the dba is taking :-(

Nov 12 '05 #6

P: n/a
Your "Log File Size (LOGFILSIZ) parameter value is too low. Even the
default value (when the database is installed) is 1000 4 K pages. Did
someone on purpose reduced this value?

As far as "Primary" and "Secondary" log files are concerned, they are
at there defaults (3 and 2). Increasing them will depend on few other
factors like your servers filesystem size, any other I/O specific tasks
that happen on this machine, the type of transactions that are
happening on the server, etc etc. I suggest you have a look at the UDB
Documentation at the following link, and then move forward.

http://publib.boulder.ibm.com/infoce...help/index.jsp
regards,
dotyet

Nov 12 '05 #7

P: n/a
> Log File Size 250
LockList 1084 No wonder you have problems. Review my previous response and address points
2 and 3.

<pa***@soccermail.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com... Thanks.
As it turns out I got the values from the Control Center itself, I
should have tried that out first.
FWIW here are the values
Log File SIze 250
Number of Primary Log files 3
Number of Secondary Log Files 2
LockList 1084

I agree that moving to the server won't fix the problem, but as I said
that is the stance the dba is taking :-(

Nov 12 '05 #8

P: n/a
Mark and dotyet,
I will pass on your tips to the DBA though affairs have come to a
pretty pass when the developer is having to tell the DBA how to
configure the database!
THanks for taking the trouble to help me

Regards

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.