Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2009
    Posts
    21

    Question Unanswered: Slow first connection from .NET

    Hi all,

    I've Microsoft.NET web application using DB2 as a database engine. Application connects to the database using IBM.Data.DB2.dll runtime client version 9.5.3.2.

    My problem is the first DB2Connection.Open takes a long time (approx. 20 seconds ), other following calls of Open method are OK (probably by connection pooling). After some application idle time the scenario repeats.

    Web server and database server runs on the same machine (Intel Xeon dual core 2GHz, 3GB RAM, OS Win 2003 Server 64-bit, .NET 3.5, DB2 Client v 9.5.3.2, DB2 v9.5.0.808 (Express)).

    Does anybody have an idea what to check or set to avoid this "warming-up" delay?

    Thanks a lot!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    See if activating the database before the first connection makes any difference: "db2 activate db <db name>". Note that this is a CLP command, not an SQL statement, therefore you cannot issue it through the .Net driver.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2009
    Posts
    21
    Yes, using
    activate db
    makes it faster. Thank you, but what does it mean? Is there any way how to call this command before connecting to the database? Do you have an idea what should I do next?

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    it should only be executed once and will stay active
    see infocenter for details on activate
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Slight correction to what Guy said. You can run this command many times if you so desire, also it wont do you any good after the first time . The main point is that you SHOULD run this command right after you bring db2 up.

    It sets everything up and when your users get online they do not have to wait.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Apr 2009
    Posts
    21
    So you sugegst ro run this command after computer reboots? E.g. using HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Curr entVersion\Run registry entry? Or is there any other way?

    Why the database is not activated automatically by DB2?

    Thanks a lot.

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by frun
    So you sugegst ro run this command after computer reboots? E.g. using HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Curr entVersion\Run registry entry? Or is there any other way?

    Why the database is not activated automatically by DB2?

    Thanks a lot.
    Yes, activate the db right away.

    Ask IBM, they might be able to answer that There are reasons, but I haven't found them valid yet
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Dec 2008
    Posts
    76
    I don't work for IBM, but I imagine it is because an active database consumes resources that may not be necessary. The first call on a database puts it into active state, but you have the "warm up" delay.

    Do you want all your databases active at start up? Some do, some don't. IBM chose to implement the conservative (as to resources) approach.
    RD

  9. #9
    Join Date
    Apr 2009
    Posts
    21
    After some days experimenting it seems activating database solved my problem partially. When DB is activated (by activate command or by application) but not used some time (aprox. 30 min), first call after this "idle" time also takes aprox. 20 seconds.

    Does anybody know where the problem could be? I'm pretty sure it is not in the application logic.

    Thank you very much!

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Database memory (including bufferpool memory) is not allocated until the database is activated. A database is activated if there is at least one connection to the database (that is still active and not timed out), or if you explicitly activate the database with the activate command. It is done this way by design so that if you have a lot of databases on a server that are not being used they do not take large amounts of memory resources. It is usually not an issue on most applications that have a connection pool software on the client side that maintains at least one active connection.

    If you are using automatic memory and sharing the machine with your web server, I would change the bufferpool memory size from -2 to explicit values that ensures that it is using real memory that is available is not swapping virtual memory. The total memory for all bufferpools on all active databases should probably be set to about 500 MB on your particular machine for starters (bufferpools are allocated in pages depending on bufferpool page size, so you have to do some math).

    If you are using connection pooling on the client side, set the minimum number of connections to at least one (not zero) on at least one of your application servers.

    However, there may also be some issues with the DB2 server code you are using which is 9.5.0. I would upgrade that to the latest fixpack, which is 9.5.4 if you have DB2 Express, or if you have DB2 Express-C (the free one) then you can get 9.5.2 or 9.7.0. I would also use the same client release as the server release.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Do not forget to mention automatic maintenance in this context. If you configure your housekeeping tasks using automatic maintenance the database MUST be active, especially during the non-peek hours where you want to run the housekeeping tasks.

  12. #12
    Join Date
    Apr 2009
    Posts
    21

    Question

    Quote Originally Posted by Marcus_A
    ...If you are using automatic memory and sharing the machine with your web server, I would change the bufferpool memory size from -2 to explicit values that ensures that it is using real memory that is available is not swapping virtual memory.
    How to change the bufferpool value, please? What should be the name, how can i assign this buffer pool with the database?

    Thanks a lot.
    Last edited by frun; 07-08-09 at 10:36.

  13. #13
    Join Date
    Dec 2008
    Posts
    76
    ALTER BUFFERPOOL IBMDEFAULTBP IMMEDIATE SIZE <nnnn>
    RD

  14. #14
    Join Date
    Sep 2013
    Posts
    1
    Hi Frun,

    Did you able to resolve this issue? I have a exactly same issue, i have made my iis application pool ideal time out as 0, so application pool will remain active always also no recycling but issue is still persist for me.
    Not getting any clue on this. Please advise if you are able to resolve.

Posting Permissions

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