Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: how to solve "not enough storage" issue?

    Hi all,
    which dbm or db parameters should I set to insert 2G BLOB data into a column using a stored procedure?

    If I create a table like this:

    create table MYTAB (myid integer, mydata blob(2G) )

    then I create a proc like this:

    create procedure updateData
    (in id integer,
    in data blob(2g))
    language sql
    begin
    update MYTAB
    set mydata= data
    where myid = id;
    end

    If I call the stored procedure (with a blob parameter that is greater than 1MB), I get the error

    SQL0930N There is not enough storage available to process the statement. SQLSTATE=57011

    I tried setting DBHEAP to 200000 but didn't solve, could you please suggest?

    Thanks,
    Robert

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Code:
    SQL0930N  There is not enough storage available to process the
          statement.
    
    Explanation:
    
    A request was made to the database that required another memory page but
    no more pages are available to the database manager.
    
    The statement cannot be processed.
    
    User response:
    
    Possible solutions include:
    *  Verify that your system has sufficient real and virtual memory.
    *  Remove background processes.
    *  If the error occurs during DUOW resynchronization, reduce the
       resync_interval database manager configuration parameter value.
    *  If the error occurs on a statement which referenced a routine (UDF,
       stored procedure or method), it may be that the storage required to
       contain the arguments and return values was more than the memory
       available in the system. This might occur if the routine is defined
       with BLOB, CLOB, DBCLOB parameters or return values, having a large
       size (2GB for example).
    
    If the possible solutions listed above do not solve the problem, it may
    be necessary to consider changing the definition of the routine.
    
     sqlcode: -930
    
     sqlstate: 57011
    What is your DB2 version and OS? How much memory on the system?

    Andy

  3. #3
    Join Date
    Mar 2012
    Posts
    120
    Hi Andy,
    I also found the info you pasted, and I believe I'm in the last case of possible solutions, but when it says
    "it may be that the storage required to
    contain the arguments and return values was more than the memory
    available in the system"
    I don't understand what I have to do to fix the issue.

    I'm running DB2 ESE 9.7.4 (32 bit) on Windows 7 64 bit with 6 Gb RAM.

    Other instance memory settings:
    INSTANCE_MEMORY = AUTOMATIC(1000000)

    Other database memory settings:
    DATABASE_MEMORY = AUTOMATIC(340960)
    APPL_MEMORY = AUTOMATIC(10000)

    I have just one bufferpool, the default IBMDEFAULTBP with 32K page size, and I set its size with
    ALTER BUFFERPOOL IBMDEFAULTBP SIZE 20480 AUTOMATIC

    From Task Manager I see db2syscs.exe holds almost 200 MB.

    Hope this info helps.
    Thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check db2diag.log - it may contain a hint to what memory pool is being exhausted. You may need to adjust APPL_MEMORY, STMTHEAP, or APPLHEAPSZ. Even if set to automatic, they may not be adjusted fast enough for the application to complete successfully.

    Consider also installing the 64-bit version of DB2, otherwise you won't be able to fully utilize all memory available to the OS.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I thought you could only install 64 bit DB2 on a 64 bit OS and a 32 bit DB2 on a 32 bit OS. (at least for V9.7). If DB2 is indeed 32 bit, then you only have about 1.7 GB of usable memory which is why you are getting the error.

    Andy

  6. #6
    Join Date
    Mar 2012
    Posts
    120
    Ok I see DB2 64 bit performs better, but we should also support 32 bit version.
    I confirm that Db2 is 32 bit and the OS is 64 bit, and it installed successfully.

    What still sounds strange to me is that if I call the procedure with a BLOB value for example of size 850K (everything less than 1 MB actually) the execution is successful.

    So the threshold in this case seems to be 1MB.... and I don't understand why it works in the example above

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You have to understand a little about how programs work. When you call a function in a program, the arguments to that function have to be stored somewhere so the function can work with them. How big they are defines how much storage (memory) is needed.

    Calling stored procedures work the same way. When you call one, the parameters that you pass need to be stored in memory so the SP can work with them. When they are small (< 1MB) then the amount of storage need is also small and you server has an ample amount. When they are bigger, you server does not have unused storage big enough to hold the parameters, so you get the error.

    Andy

  8. #8
    Join Date
    Mar 2012
    Posts
    120
    Well I think I understand this little about how program works, and it's for this reason that it sounds strange to me that a parameter of 1,01 MB causes an error, when you said I should have 1.7 GB of usable memory, and the system is far to be fully loaded....

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    As n_i mentioned, check db2diag.log for errors. It should contain information about how much memory it's trying to allocate and where it's trying to allocate it from.

  10. #10
    Join Date
    Nov 2011
    Posts
    334
    Check db2diag.log or take a trace(db2trc) to see which memory consumer got the error....
    It is common that db2 runs out of address space in 32bit instance.

  11. #11
    Join Date
    Mar 2012
    Posts
    120
    I checked db2diag.log and this should be the error:

    2012-03-21-13.48.26.054000+060 E490614H747 LEVEL: Warning
    PID : 7972 TID : 7140 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : MYDB
    APPHDL : 0-31 APPID: *LOCAL.DB2.120321124526
    AUTHID : DB2ADMIN
    EDUID : 7140 EDUNAME: db2agent (MYDB) 0
    FUNCTION: DB2 UDB, SQO Memory Management, sqloMemLogPoolConditions, probe:30
    DATA #1 : <preformatted>
    Out of memory failure for User Data Private Heap on node 0.
    Requested block size : 2147485432 bytes.
    Physical heap size : 262144 bytes.
    Configured heap size : 2147483648 bytes.
    Unreserved memory used by heap : 0 bytes.
    Unreserved memory left in set : 0 bytes.

    Could you please help me find some useful information?
    Thanks

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    db2 is trying to allocate 2147485432 out of 2147483648 for private memory. 2147483648 = 2GB (2GB is the architectural limit for a 32-bit instance on Windows). See The DB2 UDB memory model

    Don't know for sure, but I think this memory allocation (2147485432 bytes) counts towards instance_memory limit even if the actual BLOB is much smaller (allocation doesn't mean used). Clearly, this won't work for a 32-bit instance. Try defining your BLOB column smaller or 64-bit instance.

  13. #13
    Join Date
    Mar 2012
    Posts
    120
    Thanks db2girl for the link and your hint.

    I tried defining the column and procedure parameter as BLOB (25M) and it works, but then I'll have to better tune this size since I may have larger data to handle.

    For a reason that I'll try to better understand in the documentation DB2 tries to allocate 2GB even if the actual parameter is 1,01MB... but doesn't do so when the parameter is less then 1MB.

    At least, this is what I understand from my tests.

    Robert

  14. #14
    Join Date
    Nov 2009
    Posts
    10
    We had similar issues, keep in mind

    ... having multiple procedures parametrized for "small, medium, large" blobs per table. Your inserts will become faster using customized INSERT procedures by size.

    Good luck.

Posting Permissions

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