DB2 – Not enough storage in the database heap error when trying to create database

db2

So like the title says I'm trying to create a database using this script

db2 -v -t "CREATE DATABASE %DBNAME% ON '%DBDRIVE%'
USING CODESET UTF-8 TERRITORY US COLLATE
USING SYSTEM CATALOG TABLESPACE MANAGED BY SYSTEM
USING ('%DBDRIVE%%DBDIR%\%DBNAME%\sys') TEMPORARY TABLESPACE MANAGED BY SYSTEM
USING ('%DBDRIVE%%DBDIR%\%DBNAME%\systmp') USER TABLESPACE MANAGED BY SYSTEM
USING ('%DBDRIVE%%DBDIR%\%DBNAME%\usr')";

but I get an error that says:

SQL0956C Not enough storage is available in the database heap to process the statement. SQLSTATE=57011

I've looked online about this error and it says to use this command to increase the database heap

db2 UPDATE DB CFG FOR <db-name>
  USING DBHEAP <value>

I've set every database's dbheap value to automatic. I've also checked the values in the dbm such as: database monitor heap size (4KB) -> AUTOMATIC(66) and Size of instance shared memory (4KB) -> AUTOMATIC(1000000). So everything is set to automatic but there is still "not enough memory". also I looked in the db2diag.log file and this seems to be the main error:

2014-02-12-09.09.38.562000-480 E36299119H2188       LEVEL: Error (OS)
PID     : 2540                 TID : 7140           PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000           DB   : COM1_513
APPHDL  : 0-11886              APPID: *LOCAL.DB2.140212164245
AUTHID  : ADMINISTRATOR        HOSTNAME: CM-RM-WIN04
EDUID   : 7140                 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, SQO Memory Management, sqloLogMemoryCondition, probe:100
CALLED  : OS, -, VirtualAlloc
OSERR   : 8 "Not enough storage is available to process this command."
MESSAGE : Private memory and/or virtual address space exhausted
DATA #1 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 4 bytes
1048576
DATA #2 : Current set size, PD_TYPE_SET_SIZE, 4 bytes
65536

This seems to be saying there is not enough memory on the machine, which is weird because when I see the PF Usage in Windows Task Manager only about 3 / 12 GB of RAM is in use…

I am using DB2 v10.1 and Windows 2003 Server 32 bit

Also I believe this problem is due to the memory management of the databases I've made on this machine. Not positive though.

Best Answer

On a 32-bit Windoes system each process, including the DB2 instance, is limited to 2 GB of memory space (3 GB at most, with the appropriate startup switch), regardless of the amount of physical memory available. Your instance configuration requests 4 GB (Size of instance shared memory (4KB) -> AUTOMATIC(1000000)), which cannot be satisfied.

Try changing the INSTANCE_MEMORY parameter to a smaller value, e.g. 50000 pages.

By the way, with DB2 10.1 you should be using automatic storage instead of specifying tablespace containers explicitly.

Related Question