Professional Web Applications Themes

SQL0954C: application heap shortage inserting rows - IBM DB2

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 ...

  1. #1

    Default SQL0954C: application heap shortage inserting rows

    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".
    al Guest

  2. #2

    Default Re: SQL0954C: application heap shortage inserting rows

    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....
    Robert Guest

  3. #3

    Default Re: SQL0954C: application heap shortage inserting rows

    [email]rob4frhotmail.com[/email] (Robert) wrote in message news:<d53bbf72.0309112155.77e07aa7posting.google. com>...
    > 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....
    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
    rf Guest

  4. #4

    Default Re: SQL0954C: application heap shortage inserting rows

    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

    rf wrote:
    > [email]rob4frhotmail.com[/email] (Robert) wrote in message news:<d53bbf72.0309112155.77e07aa7posting.google. com>...
    > > 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....
    >
    > 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
    Fan Ruo Xin Guest

  5. #5

    Default Re: SQL0954C: application heap shortage inserting rows

    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

    al wrote:
     

    alederer Guest

  6. #6

    Default Re: SQL0954C: application heap shortage inserting rows

    alederer wrote:
     

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

    andreas

    alederer Guest

  7. #7

    Default Re: SQL0954C: application heap shortage inserting rows

    Fan Ruo Xin <net> wrote in message news:<net>... 
    > >
    > > 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[/ref][/ref]

    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.
    Vichai Guest

  8. #8

    Default Re: SQL0954C: application heap shortage inserting rows

    ch (al) wrote in message news:<google.com>... 


    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
    rf Guest

Similar Threads

  1. inserting into multiple rows with one call
    By Andy Smith in forum ASP Database
    Replies: 7
    Last Post: October 3rd, 05:27 PM
  2. Inserting Multiple Rows
    By Pappy17 in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 3rd, 06:24 PM
  3. Inserting rows into a DB
    By Kenny M. in forum ASP.NET Web Services
    Replies: 0
    Last Post: June 8th, 01:29 PM
  4. Replies: 2
    Last Post: July 8th, 12:10 PM
  5. Inserting into a set of rows in an SPL
    By Richard Harnden in forum Informix
    Replies: 0
    Last Post: June 26th, 09:55 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139