SQL0954C: application heap shortage inserting rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

al - 11 Sep 2003 12:18 GMT

Hello,
I have got some probems with APPLHEAPSZ DB parameter on DB2 v8.1.x.

I try to make mass inserts into a table with a column specified as
"generated always as identity" and I receive :
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0954C
Not enough storage is available in the application heap to process
the statement. SQLSTATE=57011

The table (see below) must have (at least) a second column
(here 'VALUE'), different values (dynamic parameter) are used for
that column in the insert-statements
The problem does not occur when using a table without a column
specified as 'generated always as identity'.

Facts:
-java driver COM.ibm.db2.jdbc.app.DB2Driver
-> but this should not be relevant because the problem seems
not to be related to java / jdbc since it can also be
reproduced using the Command Line Interface (execute a
script containing the inserts).
-auto-commit mode not relevant(default:on)
-Under DB2 7.x, was ok
-a table without a generate identity column is working fine
-inserts where the value for field 'VALUE' is always the same
is working fine
-Under Windows or Linux the same problem arises
-I have got a test programm (java/jdbc) to reproduce the problem

Do somebody know something about that issue?

table with 'generated always as identity' column:
CREATE TABLE TEST_A
(
ID DECIMAL(31,0) generated always as identity,
VALUE VARCHAR(10)
)

table without 'generated always as identity' column:
CREATE TABLE TEST_B
(
ID DECIMAL(31,0),
VALUE VARCHAR(10)
)

DB2 Versions on which the problem could be reproduced:

db2level:

Windows:
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08010"
with level identifier "01010106".
Informational tokens are "DB2 v8.1.0.36", "s021023", "", and FixPak "0".
Product is installed at "d:\IBM\SQLLIB".

DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08013"
with level identifier "02040106".
Informational tokens are "DB2 v8.1.3.132", "s030728", "WR21324",
and FixPak "3".
Product is installed at "C:\PROGRA~1\SQLLIB".

Linux:
DB21085I Instance "db2inst2" uses "32" bits and DB2 code release "SQL08013"
with level identifier "02040106".
Informational tokens are "DB2 v8.1.0.24", "s030728", "MI00054",
and FixPak "3".
Product is installed at "/opt/IBM/db2/V8.1".

Reply to this Message

Robert - 12 Sep 2003 06:55 GMT

Did you try to increase the value of the applheapsz parameter on your database ?

Run this statement to get the current value of applheapsz:

db2 get database configuration for dbname

If the current value is 128, double it: 256 and run the following statement:

db2 update database configuration for dbname using applheapsz 256

Repeat until problem solved....

Reply to this Message

rf - 12 Sep 2003 12:34 GMT

> Did you try to increase the value of the applheapsz parameter on your database ?
>
[quoted text clipped - 7 lines]
>
> Repeat until problem solved....

Is not a satisfying solution because:

For the inserts we did, when we first encountered the problem,
we had to set the APPLHEAPSZ-Parameter to approximately 6000.
That means that about 24MB application heap is required
(6000 x 4KB). About 40'000 rows were inserted by tht task.
Every row is inserted with a single insert statement, the
connection used is autocommited (by the way: we have to do
it that way, using 'load' is not applicable).

We have other tasks that insert about 400'000
rows and that requires even (a lot) more application heap...

So you see, increasing the application heap is not really the
solution because it is growing and growing as long as inserts
are done on the connection the application heap is for...

The question is, why is that much memory consumed ? As mentioned,
for the same task, on DB2 7.2 a value of 64 for APPLHEAPSZ was
more than sufficient.
I know that very complex statements need a greater application heap
to be compiled but I don't see why the very simple inserts
use that much space and why the space is not freed when doing a
commit.

rf

Reply to this Message

Fan Ruo Xin - 13 Sep 2003 02:28 GMT

Hi, RF,
DB2 agent need more application heap space to figure out the next value which "generated always as
identity". Apparently something wrong here if which means so big applheapsz (6000 pages). I think (I am
not 100% sure) the application heap size also has relationship with the transaction size.

I suggest you contact IBM support.

Before you get response from them. You can do two things:
1. Add MORE commit among your insert stmts. Use more small transactions, other than big transactions.
2. If possible, use SEQUENCE replace the "enerated always as identity".
Suggest#2 is also the workaround for your current application.

Regards,
FRX

> > Did you try to increase the value of the applheapsz parameter on your database ?
> >
[quoted text clipped - 34 lines]
>
> rf

Reply to this Message

Vichai Kraisingkorn - 26 Sep 2003 12:48 GMT

> Hi, RF,
> DB2 agent need more application heap space to figure out the next value which "generated always as
[quoted text clipped - 49 lines]
> >
> > rf

I also has the same problem, in my case I noticed (from db2diag.log)
there was a lock escalation happen just after the Exception throws, so
I try to change to explicitly lock the table instead.

Reply to this Message

alederer - 26 Sep 2003 09:30 GMT

hallo!

do you have already a solution for the problem you described below?
my problem is, that i cannot change to sequence because the application
must run without change on db2 v6, v7 and v8!

thanks
andreas

> Hello,
> I have got some probems with APPLHEAPSZ DB parameter on DB2 v8.1.x.
[quoted text clipped - 63 lines]
> and FixPak "3".
> Product is installed at "/opt/IBM/db2/V8.1".

Reply to this Message

alederer - 26 Sep 2003 09:32 GMT

> hallo!
>
> do you have already a solution for the problem you described below?
> my problem is, that i cannot change to sequence because the application
> must run without change on db2 v6, v7 and v8!

and the problem occurse only on v8: the APPLHEAPSZ on the v7 machine is
512 and the application works without any problems.

andreas

Reply to this Message

rf - 30 Sep 2003 15:34 GMT

> Hello,
> I have got some probems with APPLHEAPSZ DB parameter on DB2 v8.1.x.
[quoted text clipped - 63 lines]
> and FixPak "3".
> Product is installed at "/opt/IBM/db2/V8.1".

Hello

Note:
Me (rf) and al started this thread, this is an informational reply
to our own posting for all that are interested into the status quo...

We contacted the IBM-Support, they could reproduce the problem and
confirmed that the behaviour stated is wrong (a bug).
We were told that
"APAR JR19060 addresses this problem and will be included in FP4".
So we will wait.

Thanks for all the help and suggestions made.

cheers,
rf

Reply to this Message