I have developped a client/server application. the server side of this app. is a asp.net webservice that connect to db2 database via a COM build by ACCPAC (the accounting software).

My app. run perfectly untill , for some reason, a build up of dead connection occur.

Since i don't have a quick responce from ACCPAC, i'm wondering if there ia way that this problem occur because of setting in db2, like low mem or not enought delay timeout...?!?!

here some information of one of those dead connection(idle for 4 hour and counting some are there for days until i reboot...)

PLATEFORM IS WIN2K SERVER
USING : GET SNAPSHOT FOR APPLICATION AGENTID 103

Application handle = 103
Application status = UOW Waiting
Status change time =
Application code page = 1208
Application country/region code = 1
DUOW correlation token = *LOCAL.DB2.00F6C1180044
Application name = aspnet_wp.exe
Application ID = *LOCAL.DB2.00F6C1180044
Sequence number = 0002
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =

Connection request start timestamp = 10-21-2003 14:00:51.145036
Connect request completion timestamp = 10-21-2003 14:00:51.150908
Application idle time = Not Collected
Authorization ID = DB2ADMIN
Client login ID = ASPNET
...
...
...


If someone ave any idea !!!!

thanks

I would think there's a locking issue somewhere, not a connection problem. "UOW Waiting" means that the application waits on a resource. Also, the snapshot should list what exactly the UOW is waiting on but you omitted that part. Could you post the entire output? What's your LOCKTIMEOUT setting?

Thanks for the reply, here the complet output for this tread (still idle today(dev server)) . But i don't see lock problem here but maybe i don't see it.. im' pretty green in db2...

The other thing are that many info result in NOT COLLECTED... !!! how can i change that ?

db2 => GET SNAPSHOT FOR APPLICATION AGENTID 103

Application Snapshot

Application handle = 103
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1208
Application country/region code = 1
DUOW correlation token = *LOCAL.DB2.00F6C1180044
Application name = aspnet_wp.exe
Application ID = *LOCAL.DB2.00F6C1180044
Sequence number = 0002
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =

Connection request start timestamp = 10-21-2003 14:00:51.145036
Connect request completion timestamp = 10-21-2003 14:00:51.150908
Application idle time = Not Collected
Authorization ID = DB2ADMIN
Client login ID = ASPNET
Configuration NNAME of client =
Client database manager product ID = SQL08010
Process ID of client application = 1348
Platform of client application = NT
Communication protocol of client = Local Client

Inbound communication address = *LOCAL.DB2

Database name = ACCPACW
Database path = C:\DB2\NODE0000\SQL00004\
Client database alias = ACCPACW
Input database alias =
Last reset timestamp =
Snapshot timestamp = 10-22-2003 10:24:50.437199
The highest authority level granted =
Indirect SYSADM authority
Indirect CREATETAB authority
Indirect BINDADD authority
Indirect CONNECT authority
Indirect IMPLICIT_SCHEMA authority
Coordinating database partition number = 0
Current database partition number = 0
Coordinator agent process or thread ID = 1928
Agents stolen = 0
Agents waiting on locks = 0
Maximum associated agents = 1
Priority at which application agents work = 0
Priority type = Dynamic

Locks held by application = 0
Lock waits since connect = Not Collected
Time application waited on locks (ms) = Not Collected
Deadlocks detected = Not Collected
Lock escalations = 0
Exclusive lock escalations = 0
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = Not Collected

Total sorts = 0
Total sort time (ms) = Not Collected
Total sort overflows = 0

Data pages copied to extended storage = Not Collected
Index pages copied to extended storage = Not Collected
Data pages copied from extended storage = Not Collected
Index pages copied from extended storage = Not Collected
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool data writes = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index physical reads = Not Collected
Buffer pool index writes = Not Collected
Total buffer pool read time (ms) = Not Collected
Total buffer pool write time (ms) = Not Collected
Time waited for prefetch (ms) = Not Collected
Direct reads = Not Collected
Direct reads = Not Collected
Direct writes = Not Collected
Direct read requests = Not Collected
Direct write requests = Not Collected
Direct reads elapsed time (ms) = Not Collected
Direct write elapsed time (ms) = Not Collected

Number of SQL requests since last commit = 0
Commit statements = 1
Rollback statements = 0
Dynamic SQL statements attempted = 3
Static SQL statements attempted = 1
Failed statement operations = 0
Select SQL statements executed = 1
Update/Insert/Delete statements executed = 0
DDL statements executed = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 0
Rows inserted = 0
Rows updated = 0
Rows selected = 1
Rows read = 1
Rows written = 0

UOW log space used (Bytes) = Not Collected
Previous UOW completion timestamp = Not Collected
Elapsed time of last completed uow (sec.ms)= Not Collected
UOW start timestamp = Not Collected
UOW stop timestamp = Not Collected
UOW completion status = Not Collected

Open remote cursors = 0
Open remote cursors with blocking = 0
Rejected Block Remote Cursor requests = 0
Accepted Block Remote Cursor requests = 1
Open local cursors = 0
Open local cursors with blocking = 0
Total User CPU Time used by agent (s) = 0.000000
Total System CPU Time used by agent (s) = 0.203125
Host execution elapsed time = Not Collected

Package cache lookups = 2
Package cache inserts = 0
Application section lookups = 3
Application section inserts = 1
Catalog cache lookups = 4
Catalog cache inserts = 0
Catalog cache overflows = 0
Catalog cache high water mark = 0

Workspace Information

Shared high water mark = 0
Total shared overflows = 0
Total shared section inserts = 0
Total shared section lookups = 0
Private high water mark = 20568
Total private overflows = 0
Total private section inserts = 1
Total private section lookups = 2

Most recent operation = Static Commit
Most recent operation start timestamp = Not Collected
Most recent operation stop timestamp = Not Collected
Agents associated with the application = 1
Number of hash joins = 0
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0

Memory usage for application:
db2 =>

You should probably do this:

UPDATE MONITOR SWITCHES USING STATEMENT ON LOCK ON UOW ON

before taking the snapshot. It should show mutual waits between applications, if any.

That is not realy good i guest....see below...
can you help me solve that?
Do you thing that is the same problem that cause idle process...

db2 => UPDATE MONITOR SWITCHES USING UOW ON
DB20000I The UPDATE MONITOR SWITCHES command completed successfully.
db2 => UPDATE MONITOR SWITCHES USING LOCK ON
DB20000I The UPDATE MONITOR SWITCHES command completed successfully.
db2 =>

db2 => UPDATE MONITOR SWITCHES USING STATEMENT ON
SQL0973N Not enough storage is available in the "MON_HEAP_SZ" heap to process
the statement. SQLSTATE=57011
db2 =>

You need to increase dbm parameter - MON_HEAP_SZ.


I do not think you have the problem with locking. In case of locking issue the status of the connection is "Lock waiting", not "UOW waiting".

Is it possible the connection is lost somewhere is the code? I mean someone forgot to close it or something similar.

You can disconnect idle connections and check application logs (may be you would be able to get some information once you kill connection).

Yes, I agree with this correction. However, the fact that an application stays in "UOW Waiting" state for days may indicate that there may be a problem within that application and it never commits a UOW, which in turn may cause locking issues for other connections.

The application snapshot should show you the last statement executed by that connection. Knowing it you can look into your application to determine whether there's something with it after the point at which the above statement was executed.

thanks for the help,

i finnaly found that one line in my code , cause this problem...

This.oSession.ClearError();

Since i don't realy need to execute this line, i simply remove it...
but that odd because of the nature of this call...

but the fact is that without this line there is no build up of connection
and if i add it there is an emediate build up of dead connection...
i finnaly be able to test this the right way.

thanks for the help