Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: idle or dead connection

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: idle or dead connection

    Originally posted by xeaon
    USING : GET SNAPSHOT FOR APPLICATION AGENTID 103

    Application handle = 103
    Application status = UOW Waiting

    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?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2003
    Posts
    4

    Re: idle or dead connection

    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 =>

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: idle or dead connection

    Originally posted by xeaon
    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 ?

    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.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Oct 2003
    Posts
    4

    Re: idle or dead connection

    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 =>

  6. #6
    Join Date
    Dec 2002
    Posts
    134

    Re: idle or dead connection

    Originally posted by xeaon
    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
    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).

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: idle or dead connection

    Originally posted by chuzhoi

    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".

    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.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Oct 2003
    Posts
    4

    thanks for the help

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •