Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2010
    Posts
    10

    Unanswered: db2 9.7.0 (Linux RH 5.4 32 bits) memory issue

    Hi

    We have installed DB2 9.7.0 on Linux RedHat 5.4 (32 bits) and there are several worrying messages in the db2diag.log file. Here are some extracts :

    - Some "no available buffer pool pages" :

    2010-03-24-09.37.43.867087+060 I15502996G1938 LEVEL: Severe
    PID : 3582 TID : 430959504 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : PRDATABASE
    APPHDL : 0-9744 APPID: 10.131.197.34.50863.10032406542
    AUTHID : DB2INST1
    EDUID : 1310 EDUNAME: db2agent (PRDATABASE)
    FUNCTION: DB2 UDB, buffer pool services, sqlbDMScheckObjAlloc, probe:4640
    MESSAGE : ZRC=0x8502002C=-2063466452=SQLB_BPFULL
    "no available buffer pool pages"
    DATA #1 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 80 bytes
    Obj: {pool:2;obj:10;type:0} Parent={2;10}
    lifeLSN: 000000000346661B
    tid: 0 0 0
    extentAnchor: 864
    initEmpPages: 0
    poolPage0: 0
    poolflags: 3122
    objectState: 27
    lastSMP: 0
    pageSize: 4096
    extentSize: 32
    bufferPoolID: 4096
    partialHash: 655362
    bufferPool: 0x813e2280
    pdef: 0x81f61430
    DATA #2 : Fix control block, PD_TYPE_SQLB_FIX_CB, 144 bytes
    accessMethod: SQLB_POOL_RELATIVE
    fixMode: 2 SQLBOLD/SQLBOLDS
    buffptr: 0x00000000
    bpdPtr: 0x00000000
    dmDebugHdl: 0
    objectPageNum: 0
    empDiskPageNum: 864
    unfixFlags: 0
    dirtyState: SQLBCLEAN
    fixInfoFlags:
    regEDUid: 0
    Pagekey: {pool:2;obj:10;type:64} PPNum:0
    Obj: {pool:2;obj:10;type:64} Parent={2;10}
    lifeLSN: 000000000346661B
    tid: 0 0 0
    extentAnchor: 864
    initEmpPages: 0
    poolPage0: 0
    poolflags: 3122
    objectState: 27
    lastSMP: 0
    pageSize: 4096
    extentSize: 32
    bufferPoolID: 4096
    partialHash: 1074397186
    bufferPool: 0x813e2280
    pdef: 0x81f61430
    glob: 0x68951f50
    DATA #3 : unsigned integer, 4 bytes
    5

    2010-03-24-09.37.43.872345+060 E15504935G545 LEVEL: Error
    PID : 3582 TID : 2941250448 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : PRDATABASE
    APPHDL : 0-9746 APPID: 10.131.197.34.55471.10032406551
    AUTHID : DB2INST1
    EDUID : 1374 EDUNAME: db2agent (PRDATABASE)
    FUNCTION: DB2 UDB, Common Trace API, sqlbFreeUpSlot, probe:122
    MESSAGE : ADM6019E All pages in buffer pool "IBMSYSTEMBP4K" (ID "4096") are in
    use. Refer to the documentation for SQLCODE -1218.

    2010-03-24-09.37.43.872904+060 I15505481G520 LEVEL: Severe
    PID : 3582 TID : 2941250448 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : PRDATABASE
    APPHDL : 0-9746 APPID: 10.131.197.34.55471.10032406551
    AUTHID : DB2INST1
    EDUID : 1374 EDUNAME: db2agent (PRDATABASE)
    FUNCTION: DB2 UDB, Common Trace API, sqlbFreeUpSlot, probe:0
    DATA #1 : String, 92 bytes
    Could not fix page for objID=10, tbspaceID=2, objType=64, parentObjID=10 parentTbspaceID=2.

    2010-03-24-09.37.43.873054+060 I15506002G472 LEVEL: Severe
    PID : 3582 TID : 2941250448 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : PRDATABASE
    APPHDL : 0-9746 APPID: 10.131.197.34.55471.10032406551
    AUTHID : DB2INST1
    EDUID : 1374 EDUNAME: db2agent (PRDATABASE)
    FUNCTION: DB2 UDB, Common Trace API, sqlbFreeUpSlot, probe:0
    DATA #1 : String, 44 bytes
    Current size of bufferpool #4096: 16 slots.

    2010-03-24-09.37.43.873235+060 I15506475G498 LEVEL: Error
    PID : 3582 TID : 2941250448 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : PRDATABASE
    APPHDL : 0-9746 APPID: 10.131.197.34.55471.10032406551
    AUTHID : DB2INST1
    EDUID : 1374 EDUNAME: db2agent (PRDATABASE)
    FUNCTION: DB2 UDB, buffer pool services, sqlbFixDataEMP, probe:815
    MESSAGE : ZRC=0x8502002C=-2063466452=SQLB_BPFULL
    "no available buffer pool pages"

    2010-03-24-09.37.43.873400+060 I15506974G507 LEVEL: Error
    PID : 3582 TID : 2941250448 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : PRDATABASE
    APPHDL : 0-9746 APPID: 10.131.197.34.55471.10032406551
    AUTHID : DB2INST1
    EDUID : 1374 EDUNAME: db2agent (PRDATABASE)
    FUNCTION: DB2 UDB, buffer pool services, sqlbFixDataEMP, probe:815
    DATA #1 : String, 73 bytes
    Obj={pool:2;obj:10;type:0} State=x27 Parent={2;10}, EM=864, PP0=0 Page=0


    2010-03-24-09.37.43.873750+060 I15507482G908 LEVEL: Error
    PID : 3582 TID : 2941250448 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : PRDATABASE
    APPHDL : 0-9746 APPID: 10.131.197.34.55471.10032406551
    AUTHID : DB2INST1
    EDUID : 1374 EDUNAME: db2agent (PRDATABASE)
    FUNCTION: DB2 UDB, buffer pool services, sqlbFixDataEMP, probe:0
    DATA #1 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 80 bytes
    Obj: {pool:2;obj:10;type:0} Parent={2;10}
    lifeLSN: 000000000346661B
    tid: 0 0 0
    extentAnchor: 864
    initEmpPages: 0
    poolPage0: 0
    poolflags: 3122
    objectState: 27
    lastSMP: 0
    pageSize: 4096
    extentSize: 32
    bufferPoolID: 4096
    partialHash: 655362
    bufferPool: 0x813e2280
    pdef: 0x81f61430

    2010-03-24-09.37.43.873916+060 I15508391G698 LEVEL: Severe
    PID : 3582 TID : 2941250448 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : PRDATABASE
    APPHDL : 0-9746 APPID: 10.131.197.34.55471.10032406551
    AUTHID : DB2INST1
    EDUID : 1374 EDUNAME: db2agent (PRDATABASE)
    FUNCTION: DB2 UDB, buffer pool services, sqlbIsExtentAllocated, probe:4415
    MESSAGE : ZRC=0x8502002C=-2063466452=SQLB_BPFULL
    "no available buffer pool pages"
    DATA #1 : Pointer, 4 bytes
    0xa6feed18
    DATA #2 : unsigned integer, 4 bytes
    68
    DATA #3 : unsigned integer, 4 bytes
    5
    DATA #4 : Pointer, 4 bytes
    0xaf4f72fc
    DATA #5 : Pointer, 4 bytes
    0xaae22f50



    Also other messages like this :

    2010-03-24-10.03.35.326664+060 E15513668G1544 LEVEL: Error (OS)
    PID : 3582 TID : 2932861840 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : PRDATABASE
    APPHDL : 0-10102 APPID: 10.131.197.34.52423.10032408213
    AUTHID : DB2INST1
    EDUID : 1371 EDUNAME: db2agent (PRDATABASE)
    FUNCTION: DB2 UDB, SQO Memory Management, sqloLogMemoryCondition, probe:100
    CALLED : OS, -, mmap OSERR: ENOMEM (12)
    MESSAGE : Private memory and/or virtual address space exhausted, or data ulimit
    exceeded
    DATA #1 : Soft data resource limit, PD_TYPE_RLIM_DATA_CUR, 4 bytes
    4294967295
    DATA #2 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 4 bytes
    260636672
    DATA #3 : Current set size, PD_TYPE_SET_SIZE, 4 bytes
    35651584
    CALLSTCK:
    [0] 0xB5D6EA82 sqloLogMemoryCondition. + 0x1CC
    [1] 0xB68EF437 sqlogmblkEx + 0x7AB
    [2] 0xB4902954 _ZN15sqm_dlconn_list17gen_dlconn_eventsEPK8sqeAgen tP8SQLP_LRB + 0x2CC
    [3] 0xB4BCA191 _ZN15sqm_dlconn_list17gen_dlconn_eventsEP9sqeBsuEd uP8SQLP_LRB + 0x11
    [4] 0xB4BC9478 _Z8sqlplnfdP9sqeBsuEduP14SQLP_LOCK_INFOP8SQLP_LCBP 9SQLP_LHSHP8SQLP_LRBjijiP8SQLP_AWBi + 0x19B0
    [5] 0xB68FE8B6 _Z7sqlplrqP9sqeBsuEduP14SQLP_LOCK_INFO + 0xB62
    [6] 0xB4798DB0 _Z12sqldReadNormP13SQLD_DFM_WORKi + 0xC94
    [7] 0xB68B2B15 /home/db2inst1/sqllib/lib32/libdb2e.so.1 + 0x32CCB15
    [8] 0xB68B1D25 _Z7sqldfrdP13SQLD_DFM_WORK + 0x5BD
    [9] 0xB68A9600 _Z12sqldRowFetchP8sqeAgentP8SQLD_CCBjjPP10SQLD_VAL UEP8SQLZ_RIDjP12SQLD_ID_LISTP9SQLP_LSN8 + 0xC9A

    2010-03-24-10.03.35.343282+060 E15515213G728 LEVEL: Warning
    PID : 3582 TID : 2932861840 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : PRDATABASE
    APPHDL : 0-10102 APPID: 10.131.197.34.52423.10032408213
    AUTHID : DB2INST1
    EDUID : 1371 EDUNAME: db2agent (PRDATABASE)
    FUNCTION: DB2 UDB, SQO Memory Management, sqloMemLogPoolConditions, probe:30
    DATA #1 : <preformatted>
    Out of memory failure for Persistent Private Heap.
    Requested block size : 260624640 bytes.
    Physical heap size : 17170432 bytes.
    Configured heap size : 2147483648 bytes.
    Unreserved memory used by heap : 0 bytes.
    Unreserved memory left in set : 0 bytes.

    The db2 configuration is attached to this post (conf.txt)

    Since my db2 knowledge is actually very poor, any help would be much appreciated.


    Stephane
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are running a 32bit instance, so you are limited to 4GB total for the OS, DB2, and all other apps running on the server. You seem to be having problems loading up the bufferpools due to memory constraints. You gave tons of information, but did not give us the size of your bufferpools. Do this: turn off automatic on all of the different caches including the bufferpools if you have them set as such. Set them to values that will total to about 2 GB. Then see what you get.

    Andy

  3. #3
    Join Date
    Mar 2010
    Posts
    10
    Hi Andy
    I think I gave the size of the bufferpool in the previous attached file. Here are some informations in this post.
    The system has 4GB of RAM, only running DB2 on Linux (32), without any other applications (even not an X server)

    The other issue is that we don't have a direct access to this production system, and all changes in the settings have to be planified, so we can not test anything without being sure that there will be an significant improvement.

    Is the bufferpool size not included in db2 bet db cfg (see below) ?

    Thanks !
    Stephane

    ------------------------------------------------------------
    db2 get db cfg for PRDATABASE
    ------------------------------------------------------------

    Database Configuration for Database PRDATABASE

    Database configuration release level = 0x0d00
    Database release level = 0x0d00

    Database territory = us
    Database code page = 1208
    Database code set = UTF-8
    Database country/region code = 1
    Database collating sequence = IDENTITY
    Alternate collating sequence (ALT_COLLATE) =
    Number compatibility = OFF
    Varchar2 compatibility = OFF
    Date compatibility = OFF
    Database page size = 4096

    Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

    Statement concentrator (STMT_CONC) = OFF

    Discovery support for this database (DISCOVER_DB) = ENABLE

    Restrict access = NO
    Default query optimization class (DFT_QUERYOPT) = 5
    Degree of parallelism (DFT_DEGREE) = 1
    Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
    Default refresh age (DFT_REFRESH_AGE) = 0
    Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
    Number of frequent values retained (NUM_FREQVALUES) = 10
    Number of quantiles retained (NUM_QUANTILES) = 20

    Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN

    Backup pending = NO

    All committed transactions have been written to disk = NO
    Rollforward pending = NO
    Restore pending = NO

    Multi-page file allocation enabled = YES

    Log retain for recovery status = NO
    User exit for logging status = YES

    Self tuning memory (SELF_TUNING_MEM) = ON
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(259968)
    Database memory threshold (DB_MEM_THRESH) = 10
    Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(69088)
    Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(94)
    Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(88363)
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(26190)
    Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(5238)

    Database heap (4KB) (DBHEAP) = AUTOMATIC(2336)
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300
    Log buffer size (4KB) (LOGBUFSZ) = 256
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 19346
    Buffer pool size (pages) (BUFFPAGE) = 1000
    SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(2048)
    Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(256)
    Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(10000)
    Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384)

    Interval for checking deadlock (ms) (DLCHKTIME) = 10000
    Lock timeout (sec) (LOCKTIMEOUT) = 30

    Changed pages threshold (CHNGPGS_THRESH) = 80
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(1)
    Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC(3)
    Index sort flag (INDEXSORT) = YES
    Sequential detect flag (SEQDETECT) = YES
    Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC

    Track modified pages (TRACKMOD) = ON

    Default number of containers = 1
    Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

    Max number of active applications (MAXAPPLS) = AUTOMATIC(111)
    Average number of active applications (AVG_APPLS) = AUTOMATIC(1)
    Max DB files open per application (MAXFILOP) = 30720

    Log file size (4KB) (LOGFILSIZ) = 1024
    Number of primary log files (LOGPRIMARY) = 13
    Number of secondary log files (LOGSECOND) = 4
    Changed path to log files (NEWLOGPATH) =
    Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file = S0033568.LOG
    Block log on disk full (BLK_LOG_DSK_FUL) = NO
    Block non logged operations (BLOCKNONLOGGED) = NO
    Percent max primary log space by transaction (MAX_LOG) = 0
    Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

    Group commit count (MINCOMMIT) = 1
    Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
    Log retain for recovery enabled (LOGRETAIN) = OFF
    User exit for logging enabled (USEREXIT) = OFF

    HADR database role = STANDARD
    HADR local host name (HADR_LOCAL_HOST) =
    HADR local service name (HADR_LOCAL_SVC) =
    HADR remote host name (HADR_REMOTE_HOST) =
    HADR remote service name (HADR_REMOTE_SVC) =
    HADR instance name of remote server (HADR_REMOTE_INST) =
    HADR timeout value (HADR_TIMEOUT) = 120
    HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
    HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0

    First log archive method (LOGARCHMETH1) = DISK:/var/DB2backup/archivelogs/
    Options for logarchmeth1 (LOGARCHOPT1) =
    Second log archive method (LOGARCHMETH2) = OFF
    Options for logarchmeth2 (LOGARCHOPT2) =
    Failover log archive path (FAILARCHPATH) =
    Number of log archive retries on error (NUMARCHRETRY) = 5
    Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
    Vendor options (VENDOROPT) =

    Auto restart enabled (AUTORESTART) = ON
    Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
    Log pages during index build (LOGINDEXBUILD) = OFF
    Default number of loadrec sessions (DFT_LOADREC_SES) = 1
    Number of database backups to retain (NUM_DB_BACKUPS) = 12
    Recovery history retention (days) (REC_HIS_RETENTN) = 366
    Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF

    TSM management class (TSM_MGMTCLASS) =
    TSM node name (TSM_NODENAME) =
    TSM owner (TSM_OWNER) =
    TSM password (TSM_PASSWORD) =

    Automatic maintenance (AUTO_MAINT) = ON
    Automatic database backup (AUTO_DB_BACKUP) = OFF
    Automatic table maintenance (AUTO_TBL_MAINT) = ON
    Automatic runstats (AUTO_RUNSTATS) = ON
    Automatic statement statistics (AUTO_STMT_STATS) = ON
    Automatic statistics profiling (AUTO_STATS_PROF) = OFF
    Automatic profile updates (AUTO_PROF_UPD) = OFF
    Automatic reorganization (AUTO_REORG) = OFF

    Auto-Revalidation (AUTO_REVAL) = DEFERRED
    Currently Committed (CUR_COMMIT) = ON
    CHAR output with DECIMAL input (DEC_TO_CHAR_FMT) = NEW
    Enable XML Character operations (ENABLE_XMLCHAR) = YES
    WLM Collection Interval (minutes) (WLM_COLLECT_INT) = 0
    Monitor Collect Settings
    Request metrics (MON_REQ_METRICS) = BASE
    Activity metrics (MON_ACT_METRICS) = BASE
    Object metrics (MON_OBJ_METRICS) = BASE
    Unit of work events (MON_UOW_DATA) = NONE
    Lock timeout events (MON_LOCKTIMEOUT) = NONE
    Deadlock events (MON_DEADLOCK) = WITHOUT_HIST
    Lock wait events (MON_LOCKWAIT) = NONE
    Lock wait event threshold (MON_LW_THRESH) = 5000000

    SMTP Server (SMTP_SERVER) =

    ------------------------------------------------------------
    db2level
    ------------------------------------------------------------
    DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL09070"
    with level identifier "08010107".
    Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXIA3297", and Fix Pack
    "0".
    Product is installed at "/opt/ibm/db2/V9.7".

  4. #4
    Join Date
    Mar 2010
    Posts
    10
    Also, some more usefull informations :

    ------------------------------------------------------------
    ipcs -l
    ------------------------------------------------------------

    ------ Shared Memory Limits --------
    max number of segments = 4096
    max seg size (kbytes) = 4194303
    max total shared memory (kbytes) = 1073741824
    min seg size (bytes) = 1

    ------ Semaphore Limits --------
    max number of arrays = 1024
    max semaphores per array = 250
    max semaphores system wide = 32000
    max ops per semop call = 32
    semaphore max value = 32767

    ------ Messages: Limits --------
    max queues system wide = 1024
    max size of message (bytes) = 65536
    default max size of queue (bytes) = 65536



    ------------------------------------------------------------
    ulimit -a
    ------------------------------------------------------------
    core file size (blocks, -c) 0
    data seg size (kbytes, -d) unlimited
    scheduling priority (-e) 0
    file size (blocks, -f) unlimited
    pending signals (-i) 147456
    max locked memory (kbytes, -l) 32
    max memory size (kbytes, -m) unlimited
    open files (-n) 1024
    pipe size (512 bytes, -p) 8
    POSIX message queues (bytes, -q) 819200
    real-time priority (-r) 0
    stack size (kbytes, -s) 10240
    cpu time (seconds, -t) unlimited
    max user processes (-u) unlimited
    virtual memory (kbytes, -v) unlimited
    file locks (-x) unlimited

    ------------------------------------------------------------
    db2mtrk -i -d
    ------------------------------------------------------------
    Tracking Memory on: 2010/03/25 at 14:08:49

    Memory for instance

    other fcmbp monh
    10.9M 768.0K 576.0K

    Memory for database: WPRCSDB

    utilh pckcacheh other catcacheh bph (7) bph (6)
    64.0K 3.3M 128.0K 704.0K 8.1M 8.1M

    bph (5) bph (4) bph (3) bph (2) bph (1) bph (S32K)
    8.2M 16.0M 12.2M 6.3M 370.8M 832.0K

    bph (S16K) bph (S8K) bph (S4K) shsorth lockh dbh
    576.0K 448.0K 384.0K 256.0K 50.4M 22.6M

    apph (13992)apph (11557)apph (6114) apph (6112) apph (6087) apph (5684)
    64.0K 64.0K 64.0K 64.0K 64.0K 64.0K

    apph (5683) apph (5682) apph (5681) apph (5674) apph (5667) apph (5666)
    64.0K 64.0K 64.0K 64.0K 64.0K 64.0K

    apph (5665) apph (5664) appshrh
    64.0K 64.0K 960.0K

    Memory for database: PRDATABASE

    utilh pckcacheh other catcacheh bph (S32K) bph (S16K)
    64.0K 23.8M 128.0K 896.0K 832.0K 576.0K

    bph (S8K) bph (S4K) shsorth lockh dbh apph (15746)
    448.0K 384.0K 768.0K 280.4M 21.6M 128.0K

    apph (10005)apph (6141) apph (6140) apph (6139) apph (6138) appshrh
    128.0K 64.0K 64.0K 64.0K 64.0K 1.6M



    ------------------------------------------------------------
    db2pd -d PRDATABASE -mempools
    ------------------------------------------------------------

    Database Partition 0 -- Database PRDATABASE -- Active -- Up 1 days 22:08:08

    Memory Pools:
    Address MemSet PoolName Id Overhead LogSz LogUpBnd LogHWM PhySz PhyUpBnd PhyHWM Bnd BlkCnt CfgParm
    0x6F6B5CC0 PRDATABASE utilh 5 0 2424 79298560 39475232 65536 79298560 39780352 Ovf 12 UTIL_HEAP_SZ
    0x6F6B5B58 PRDATABASE pckcacheh 7 226432 24375811 Unlimited 24465698 24969216 Unlimited 24969216 Ovf 6996 PCKCACHESZ
    0x6F6B5AA4 PRDATABASE xmlcacheh 93 50944 80008 20971520 80008 131072 20971520 131072 Ovf 1 n/a
    0x6F6B59F0 PRDATABASE catcacheh 8 0 893739 Unlimited 893739 917504 Unlimited 917504 Ovf 190 CATALOGCACHE_SZ
    0x6F6B5888 PRDATABASE bph 16 32 782656 Unlimited 782656 851968 Unlimited 851968 Ovf 5 n/a
    0x6F6B5720 PRDATABASE bph 16 32 520512 Unlimited 520512 589824 Unlimited 589824 Ovf 3 n/a
    0x6F6B55B8 PRDATABASE bph 16 32 389440 Unlimited 389440 458752 Unlimited 458752 Ovf 2 n/a
    0x6F6B5450 PRDATABASE bph 16 32 323904 Unlimited 323904 393216 Unlimited 393216 Ovf 2 n/a
    0x6F6B539C PRDATABASE shsorth 18 0 9736 107282432 10563368 786432 107282432 14024704 Ovf 19 SHEAPTHRES_SHR
    0x6F6B52E8 PRDATABASE lockh 4 32 294039552 294125568 294039552 294060032 294125568 294060032 Ovf 1 LOCKLIST
    0x6F6B5234 PRDATABASE dbh 2 407328 21736681 39321600 21932976 22675456 39321600 22740992 Ovf 2417 DBHEAP
    0xA6F55888 AppCtl apph 1 0 27001 1048576 162581 131072 1048576 262144 Phy 101 APPLHEAPSZ
    0xA6F557D4 AppCtl apph 1 0 26117 1048576 80857 131072 1048576 131072 Phy 75 APPLHEAPSZ
    0xA6F5566C AppCtl apph 1 0 8111 1048576 8111 65536 1048576 65536 Phy 14 APPLHEAPSZ
    0xA6F555B8 AppCtl apph 1 0 8173 1048576 8173 65536 1048576 65536 Phy 18 APPLHEAPSZ
    0xA6F55504 AppCtl apph 1 0 8173 1048576 8173 65536 1048576 65536 Phy 18 APPLHEAPSZ
    0xA6F55450 AppCtl apph 1 0 8173 1048576 9585 65536 1048576 65536 Phy 18 APPLHEAPSZ
    0xA6F55234 AppCtl appshrh 20 41760 1219940 20480000 1486464 1638400 20512768 1966080 Phy 635 application shared

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, the bufferpool sizes are not given in the CFG listings. In DB2 there are 4 bufferpools that are automatically created (1 of each pagesize) that are real small so that you have bufferpools that are active. I think these are what is showing in db2pd. Run the following select statement: "select bpname,npages,pagesize from syscat.bufferpools". This will show the size of the bufferpools that you have defined.

    Andy

  6. #6
    Join Date
    Mar 2010
    Posts
    10
    here is the output of the command :

    BPNAME NPAGES PAGESIZE
    -------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
    IBMDEFAULTBP -2 4096

    1 record(s) selected.

    Stephane

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I believe -2 for npages is "Automatic". Alter the bufferpool to set it to a specific value:

    ALTER BUFFERPOOL IBMDEFAULTBP IMMEDIATE SIZE 2000

    Then see if you problem goes away.

    Andy

  8. #8
    Join Date
    Mar 2010
    Posts
    10
    Andy
    I made a quick search on IBM web site, and they always come (in the db2 tuning stuff) with a NPAGES of 20000. Since our system is only dedicated to DB2, isn't it preferable to set a higher value directly (20000 instead of 2000) ?

    Stephane

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by steph66 View Post
    Andy
    I made a quick search on IBM web site, and they always come (in the db2 tuning stuff) with a NPAGES of 20000. Since our system is only dedicated to DB2, isn't it preferable to set a higher value directly (20000 instead of 2000) ?

    Stephane
    That is correct, but right now you are having memory problems. So start at a low value and move it up in increments. Since it is a dedicated DB2 server, make sure that your tablespaces are not using file system caching. This will help maximize the available memory to DB2.

    Andy

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    It is clear that you have memory issues and your IBMDEFAULTBP has not started, possibly because DB2 has less memory than it had before shutdown.

    I suggest that you make sure DB2 has atleast 2GB of memory and also set a limit on the INSTANCE_ MEMORY to this level.

    if you have memory issues when activating the database, use DB2_OVERRIDE_BPF to override the current stmm value, define the bp size to a reasonable level and then restart the database after switching off DB2_OVERRIDE_BPF.

    Creating two instances - one for each db and/or upgrading to 64 bits might help. Of course, you have to make sure there is sufficient memory available to DB2.

    You may also consider turning off STMM for bufferpools.

    HTH
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Mar 2010
    Posts
    10
    thank you all for your useful answers, we made the modifications and will post the results by the beginning of next week
    Cheers
    Stephane

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would definitely install FP1 as soon as you can.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Another point is - as you are using STMM make sure you get your indexing right .... If not, STMM tries to allocate more memory for bufferpools, sortheap etc to accomodate the higher volume of data this is being processed .. On low memory servers or 32-bit platforms this causes more issues.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  14. #14
    Join Date
    Mar 2010
    Posts
    10
    thanks for all of your valuable informations, we made the following changes :

    db2 update dbm cfg using INSTANCE_MEMORY 524288
    db2 alter bufferpool IBMDEFAULTBP SIZE 5000

    and we have also index some more tables and the performance issues seems to be solved.

    The next step is indeed to install 64 bits code, together with FP1

    Stephane

Posting Permissions

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