I am writing a front-end to a Perl-based web-service running against a DB2 v. 8.1 database, and every once in a while I get the following error:
DBD:B2::db prepare failed: [IBM][CLI Driver][DB2/NT] SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011
The server is running Win2k and Apache2/mod_perl 1.99.
After approx. 40 calls to the database I get the error. The SQL thats being prepared or executed is always varying, so its not a specific SQL statement that crashes the database - it seems to be just a certain quantity.
Ive tried increasing the heap size, but this only delays the problem. Im new to DB2 and Id rather try to fix the problem (if possible) than delay it.
I should mention that I had no problems when I was running Apache 1.3, so maybe the multi-threads is whats doing it?
"The required heap size depends on the size and complexity of SQL executed in a single connection. The error most likely means that an SQL statement is
holding too many open cursors, doing too much sorting etc.etc. The easiest
way to solve the problem is to increase your appheapsz, it is not 'delaying'
the problem since the size specified is per connection. You should find that
once your application heap size is sufficient (the size probably relates to
your most complex query). You can hit the database with many connections and never see this error again."
<http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&threadm=3D1A4269.C0807F3F%40gds.ca&rnum =12&prev=/groups%3Fq%3D%2522SQL0954C%2B%2BNot%2Benough%2Bsto rage%2Bis%2Bavailable%2Bin%2Bthe%2Bapplication%2Bh eap%2Bto%2Bprocess%2Bthe%2Bstatement.%2B%2BSQLSTAT E%253D57011%2522%2Bperl%2Bdbi%26start%3D10%26hl%3D en%26lr%3D%26ie%3DUTF-8%26c2coff%3D1%26selm%3D3D1A4269.C0807F3F%2540gds. ca%26rnum%3D12>
Increasing the heap size and enabling mod_perl for the web service location (doh!) has seemingly fixed the problem.