Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

DB2 transaction problem

7 views
Skip to first unread message

Oleg Kozlovski

unread,
Dec 3, 2009, 4:45:13 AM12/3/09
to
Hello all!

we're deleting huge amount of records from a table. We had a perl
script to do in loop by 100, 500, 1000 etc records in a loop but it
always failed due to lock contentions. Now we tried to do this:
1. set autocommit off
2. lock table in share mode
3. delete rows
4. commit
this lead us to the "maximum number of agents exceeded" situation...

db2diag:
FUNCTION: DB2 UDB, common communication, sqlcctcpconnmgr_child, probe:
125
MESSAGE : ADM7009E An error was encountered in the "TCPIP" protocol
support.
A possible cause is that the maximum number of agents has
been
exceeded.

any connection attempts ended with:

SQL0901N The SQL statement failed because of a non-severe system
error.
Subsequent SQL statements can be processed. (Reason "164".)
SQLSTATE=58004

Anybody knows why did it happen and how to resolve?
thanks!

Frederik Engelen

unread,
Dec 3, 2009, 5:16:34 AM12/3/09
to

Google Serge Rielau's SQL On Fire presentation. That has a chapter on
performing mass deletes that will propably help you out.

--
Frederik Engelen
N-Tier System Engineer
RealDolmen

Ian

unread,
Dec 3, 2009, 6:48:10 PM12/3/09
to
On 12/3/09 2:45 AM, Oleg Kozlovski wrote:
> Hello all!
>
> we're deleting huge amount of records from a table. We had a perl
> script to do in loop by 100, 500, 1000 etc records in a loop but it
> always failed due to lock contentions.

One application shouldn't get lock contention with itself. Are you
running multiple copies of this perl script at the same time? Or
do you have other applications running accessing the same data?

Now we tried to do this:
> 1. set autocommit off
> 2. lock table in share mode
> 3. delete rows
> 4. commit
> this lead us to the "maximum number of agents exceeded" situation...

This sounds like a bug in your application -- like it keeps making
more and more connections to the database until it exhausts the
configured maximum number of connections.


dunleav1

unread,
Dec 7, 2009, 1:40:07 PM12/7/09
to

Can you use the new truncate table command?

Oleg Kozlovski

unread,
Dec 9, 2009, 5:33:14 AM12/9/09
to

Problem resolved - the app was issuing the delete w/o commit in the
loop. Hence the maximum number of agents exceeded... Thank you all for
your responses!

0 new messages