Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: memory problem when connecting to DB

    One of the tables is growing fast so we need to move it to TS with 16K pagesize. So I created
    new BP and new TS with pagesize 16K.

    db cfg (DBHEAP) = AUTOMATIC(5500)

    Existing BPs before creating new BP:

    BPNAME BUFFERPOOLID DBPGNAME NPAGES PAGESIZE ESTORE NUMBLOCKPAGES BLOCKSIZE NGNAME
    IBMDEFAULTBP 1 - 2000000 4096 N 0 0 -
    BP_SYSCATSPC 2 - 20000 4096 N 0 0 -
    BP_TBS_PROD 3 - 40000 4096 N 0 0 -

    I created new BP and TS with pagesize 16K:

    db2 -v "create bufferpool BP_TBS16K size 1000 pagesize 16K"

    BP_TBS16K 4 - 1000 16384 N 0 0 -

    db2 -v “create tablespace TBS16K pagesize 16 K managed by system using (‘/database/db2v9i1/NODE0000/SQL00002/tbs16K1’,'/database/db2v9i1/NODE0000/SQL00002/tbs16K2','/database/db2v9i1/NODE0000/SQL00002/tbs16K3','/database/db2v9i1/NODE0000/SQL00002/tbs16K4') EXTENTSIZE 64 PREFETCHSIZE 128 bufferpool BP_TBS16K“

    At this point everythins is fine - I can connect to DB. When I load the table with 66432894 recs I get error when trying to connect to the DB:

    db2 connect to AAAAAAA
    SQL1478W The defined buffer pools could not be started. Instead, one small
    buffer pool for each page size supported by DB2 has been started.
    SQLSTATE=01626
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Check if there is enough free memory to allocate the db shared memory for this db. Also, check what database_memory/instance_memory is set to. If not automatic, try changing it to automatic and restart database/instance. If still the same errors, please check db2diag.log for any memory related errors. Also, you can try reducing your large bp

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    you are right, Bella. I checked db2diag report but did not see this at that point:

    009-07-17-14.01.54.307779-240 I27047707A1004 LEVEL: Error
    PID : 160360 TID : 9087 PROC : db2sysc 0
    INSTANCE: db2v9i1 NODE : 000 DB : SMCIRRIF
    APPHDL : 0-33646 APPID: *LOCAL.db2v9i1.090717180154
    AUTHID : DBA
    EDUID : 9087 EDUNAME: db2agent (SMCIRRIF) 0
    FUNCTION: DB2 UDB, SQO Memory Management, SqloMemController::registerConsumer, probe:1000
    MESSAGE : ZRC=0x8B0F0000=-1961951232=SQLO_NOMEM
    "No Memory Available (reason code is id of requested heap)"
    DIA8300C A memory heap error has occurred.
    DATA #1 : String, 60 bytes
    Insufficient instance_memory to allocate new memory consumer
    DATA #2 : String, 11 bytes
    DB-SMCIRRIF
    DATA #3 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 8 bytes
    11932008448
    DATA #4 : Current instance_memory consumption in bytes, PD_TYPE_MEM_INSTANCE_CUR, 8 bytes
    4111728640
    DATA #5 : Maximum allowed instance_memory in bytes, PD_TYPE_MEM_INSTANCE_MAX, 8 bytes
    14030888960


    Another thing is I did not restart DB after creating a new BP and TS.

    thanks so muck, Bella
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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