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!

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.

Yes, using 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?

it should only be executed once and will stay active
see infocenter for details on activate

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.

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

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.

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!

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.

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.

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.

ALTER BUFFERPOOL IBMDEFAULTBP IMMEDIATE SIZE <nnnn>