Dear all,

we are wondering about the following issue:

DB2 DPF 9.5 ESE SP3 on AIX
20 GB Mem
12 CPU
STMM activated.

We have massive Waits and asynchr I/O so we gave more MEM and decreased filesystemcaching with the aim to provide a maximum of memory for bufferpools. -> RESTART -> but even after waiting for some days we still have 8gb free mem.

MB Used 11861.3MB
MB Free 8618.6MB

Why doesn't the db use the mem?

DB2TOP shows:
Database PDEDW BufferPool 65.32% 4.2G 4.2G 0.39% 1.0T 54

Any ideas?

Thanks

Mala

Please post your dbm & db cfs's and the result of "db2mtrk -i -d"

Hi,

thanks for your reply. Maybe I found the reason:

STMM is a very useful tool, but automatic doesn't replace intelligence. When using STMM in a DPF environment you have to be careful how you use or you run in a szenario like we did. First you need to know how it works in a dpf environment. When all Partitions are set to STMM ON, by default it takes the first data partition, optimizes it and sends the configs (parallel) to the other partitions.


db2 => CALL SYSPROC.ADMIN_CMD( 'get stmm tuning dbpartitionnum' )


Result set 1
--------------

USER_PREFERRED_NUMBER CURRENT_NUMBER
--------------------- --------------
1 1

1 record(s) selected.

Return Status = 0


If the chosen tuning partition is not representative (as was with us), you run into performance issues. We have the first partition populated with normal tables, while 4 other partitions host the distributed tables.

So we need to selectivly run stmm on those groups of partitions that are representative for its peer group.

Ill do that over the weekend ..

Here is the output of - I am not familiiar with it, could you explain what you see?

db2mtrk -i -d

Tracking Memory on: 2009/09/04 at 11:28:14

Memory for instance

other monh fcmbp
131.0M 320.0K 79.1M

Memory for database: PDEDW

utilh pckcacheh other catcacheh bph (5) bph (4)
64.0K 11.4M 192.0K 2.2M 31.8M 16.2M

bph (3) bph (2) bph (1) bph (S32K) bph (S16K) bph (S8K)
8.2M 631.5M 31.8M 832.0K 576.0K 448.0K

bph (S4K) shsorth lockh dbh apph (11057)apph (10776)
384.0K 384.0K 124.9M 35.3M 64.0K 64.0K

apph (10775)apph (5399) apph (4601) apph (723) apph (100) apph (65587)
64.0K 64.0K 64.0K 192.0K 384.0K 64.0K

apph (55) apph (54) apph (53) appshrh
64.0K 64.0K 64.0K 2.0M



Many regards
Mala

In your 1st post you claim to have "stmm activated". Please read this How to setup the Self Tuning Memory Manager (STMM) | db2ude because I'm not sure if STMM "works as designed" in you config.
I asked for the DBM config because it is essential that you have a 64bit version of DB2 installed to be able to comsume the 20 Gb available.
With the results of you db2mtrk I am not suprised that your database does not perform too wel, you bufferpools are extreme small.... I've got bigger on my laptop...

Bottomline: set your bufferpools to "AUTOMATIC" and tail you db2diag.log and notice the bufferpools getting extended. If you see that you will notice that DB2 will perform better. Allow DB2-STMM a few hours to settle in.

Thx for your answer,

yes its 64 bit. We just changed the "to tune DB" to the right partition.

Regarding the bufferpools - they sum to 4.2 GB:

(DB2top)
Database PDEDW BufferPool 63.94% 4.2G 4.2G 0.39% 1.0T 54

After we switched the "Partition to be optimised" and some waiting, the Bufferpools haven't been updated so far. the Nfy file show index rebuilts but no change in BP. We'll wait for some more minutes.

BP Settings:

db2 "SELECT BPNAME, NPAGES, PAGESIZE FROM SYSCAT.BUFFERPOOLS"

BPNAME NPAGES PAGESIZE
-------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
IBMDEFAULTBP -2 32768
BP_4K -2 4096
BP_8K -2 8192
BP_16K -2 16384
BP_32K -2 32768

BUT:

*** stmmCostBenefitRecord ***
Type: BUFFER POOL ( BP_16K )
PageSize: 16384
Saved Misses: 11
Benefit:
-> Simulation size: 1024
-> Total seconds saved: 0 (+ 13129182 ns)
-> Normalized seconds/page: 1.06769E-07
Cost:
-> Simulation size: 1024
-> Total seconds saved: 0 (+ 13129182 ns)
-> Normalized seconds/page: 1.06769E-07
Current Size: 1004
Minimum Size: 1000
Potential Increase Amount: 502
Potential Increase Amount From OS: 502
Potential Decrease Amount: 0
Pages Available For OS: 0
Interval Time: 30.0215

-> STMM.log shows that it wants to increase the BP - but somehow it doesn't..

EDIT: Pls see post below: BP have been increased by STMM



see DB CFG/ DBM CFG as attached

piedw_PDEDW_DBM_CFG.txt
db CFG: shows the config -> they differ between the partitions only in the SELF_TUNING_MEM, LOG_FILESIZE,LOGPATH, LOGPRIMARY
db2 get db.txt: show actual values of DB CFG (db2 "SELECT NAME, DEFERRED_VALUE, DBPARTITIONNUM FROM SYSIBMADM.DBCFG order by name,DBPARTITIONNUM" | tr -s " " )


There is an more in depth explanation that I prefer to the one u mentioned:

ftp://ftp.software.ibm.com/software/...apers/stmm.pdf


Many regards
Mala

After waiting some hours, BP16k increased from 6000 pages to 47000 pages using about 1GB BP mem.. :-) hopefully this will increase further over time..

Many regards

Mala

PS If you have any suggestions regarding the cfgs please tell me..

EDIT:

Checking everything there is something strange.

We set:

[pdb_f204:/pdb_f204/db2/dump/piedw] >db2 "SELECT NAME, DEFERRED_VALUE, DBPARTITIONNUM FROM SYSIBMADM.DBCFG order by DBPARTITIONNUM,name" | tr -s " " | grep -i self
self_tuning_mem OFF 0
self_tuning_mem OFF 1
self_tuning_mem ON 2
self_tuning_mem ON 3
self_tuning_mem ON 4
self_tuning_mem ON 5


because DBPNo 0 (Coord) and 1 is a non-representative Partition.

We set:
db2 => CALL SYSPROC.ADMIN_CMD( 'get stmm tuning dbpartitionnum' )


Result set 1
--------------

USER_PREFERRED_NUMBER CURRENT_NUMBER
--------------------- --------------
2 2

1 record(s) selected.

Return Status = 0


But I find (hours after the CFG-Change):

2009-09-04-20.26.02.401215+120 I248742833A501 LEVEL: Info
PID : 2146392 TID : 33488 PROC : db2sysc 1
INSTANCE: piedw NODE : 001 DB : PDEDW
APPHDL : 2-2166 APPID: *N2.DB2.090904110026
AUTHID : PIEDW
EDUID : 33488 EDUNAME: db2agntp (PDEDW) 1
FUNCTION: DB2 UDB, buffer pool services, sqlbAlterBufferPoolAct, probe:90
MESSAGE : Altering bufferpool "BP_16K" From: "5082" <automatic> To: "7623"
<automatic>

2009-09-04-20.26.02.421245+120 I248743335A501 LEVEL: Info
PID : 1785982 TID : 37940 PROC : db2sysc 0
INSTANCE: piedw NODE : 000 DB : PDEDW
APPHDL : 2-2166 APPID: *N2.DB2.090904110026
AUTHID : PIEDW
EDUID : 37940 EDUNAME: db2agntp (PDEDW) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbAlterBufferPoolAct, probe:90
MESSAGE : Altering bufferpool "BP_16K" From: "5082" <automatic> To: "7623"
<automatic>

So STMM changed the config to the Partitions I wanted fixed..

Is setting SELF_TUNING_MEM to off not sufficiant? I thought it overruled the other automatics like bufferpools...

Thanks alot
Mala