DB2 transaction problem
in [DB2]
|
From: Oleg Kozlovski on 3 Dec 2009 04:45 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!
From: Frederik Engelen on 3 Dec 2009 05:16 On Dec 3, 10:45 am, Oleg Kozlovski <oleg...(a)googlemail.com> 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. 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! 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
From: Ian on 3 Dec 2009 18:48 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.
From: dunleav1 on 7 Dec 2009 13:40 On Dec 3, 4:45 am, Oleg Kozlovski <oleg...(a)googlemail.com> 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. 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! Can you use the new truncate table command?
From: Oleg Kozlovski on 9 Dec 2009 05:33 On Dec 7, 10:40 am, dunleav1 <jmdunle...(a)comcast.net> wrote: > On Dec 3, 4:45 am, Oleg Kozlovski <oleg...(a)googlemail.com> 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. 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! > > Can you use the new truncate table command? 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!
|
Pages: 1 Prev: Need advice to optimize this query Next: How to maintain a standby database using log shipping |