log files full autocommit

[pakku]

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

[Buck Nuggets]

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

[Mark Yudkin]

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

[pakku]

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!

[dotyet]

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

[pakku]

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 :-(

[dotyet]

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

[Mark Yudkin]

> 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 :-(

[Pakku]

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