Hi all, I've got a tricky DB2 / perl DBD::DB2 problem.
We have a large web site using mod_perl and DBI with DB2 running on RS6000's. The web application is written in oo-perl and uses XML-based templates, and this setup requires highly dynamic SQL. Over the last two years we have experienced memory leak in the web server, but when we upgraded to UDB 7.2 this problem became much larger, and now parts of the system gets out-of-memory errors. I finally tracked the memory leak down to the DB2 client. In our system, every SQL query was prepared every time, and even though the perl statement handle was freed, every prepare looses up to a couple of K of memory. I tried to work around this problem by implementing a statement cache, so repeated statements would not be prepared more than once. Unfortunately, this only moved the problem to the size of the application heap. About 40 queries filled up the default heap of 128. As we have hundreds of different queries, and about 140 active agents, I am not sure if it is wise to extend it. Questions: 1. Should we set the application heap to about 1024? (This would require about 600MB RAM, and may not even be enough) 2. Is there a way of getting perl DBD::DB2 to free all memory associated with prepared statements? 3. Is there a way of reducing the need for the application heap for storing prepared statements? 4. Other ideas? Thanks! Rune Nilsen rune at adastra.no
