Hi folks, since updating to 10.1 and being able to take advantage of the new memory restriction of 4gb rather than 2, I have been having a difficult time finding the right setup without experiencing errors in the stmm.log regarding unable to find donors.

Firstly I am running a personal database of some 36gb in size, with 4 main tablespaces being results, indexes, rawdata and static data. These all share a single bufferpool called 'DATAPOOL'.

My database is NOT OLTP, it is used for datamining, storing and processing association rules, sequential pattern rules etc. Rules are received with bodies of elements and a rule head. The bodies are stored then broken into individual elements and stored again. This way I am able to use relational division queries to the rawdata table and get the dates on which the rules occur. The datamining engine only returns counts ie: confidence support etc, no dates. So it is up to me to find the actual dates the each rule occurs on in the past, then be able to check when active and store future dates as they occur.

Now in order to get dates etc, the relational division query must gather a significant number of dates that each of the individual elements has occurred on, then using 'having count(*) = the number of elements in the rule body' it is able to find the specific dates they all happened together.

My problem is finding the right balance of bufferpool versus database (sortetc) memory. I have thought about using stmm but it reacts too slowly to the processing as the database is only active for processing at the end of the day, with no activity at other times. Leaving the database active, stmm reduces all the appropriate values over time so come processing there is only a minimal setup and performance is terrible.

Given 4gb of available memory, I started very basically going for somethinglike 2gb for bufferpools and 2gb for the rest, but no matter how I try to set it up, it always gives me these stmm.log donor errors.

System has two (2) physical discs that I split the 4 tablespace containers over, logs and results on 1, indexes, rawdata and static on the other. System has 8gb of real memory, running 64-bit win 7 pro sp1 and db2 express-c luw 10.1 64-bit.
I use oorexx 4.1 32-bit to run the whole thing executing sql stored procedures etc. As I am the only one connecting to the database, everything usually happens sequentially in that my rexx programs run one at a time with a single connection only. There are only very rare occasions where there may bemore than 1 connection active at a time and that would be for minimal queries.

Anyone that could help with some suggestions as to where best to start at least would be most welcome. I realize specifics are not practical but some suggestions and how tos would be gratefully appreciated.

Relevant config params as follows:

Datapool bufferpool = 375000 4kb pages, with 5000 block pages 24 extents.
All others are set to automatic sizing by stmm.

Database Manager Configuration

CPU speed (millisec/instruction) (CPUSPEED) = 1.180861e-007
Diagnostic error capture level (DIAGLEVEL) = 2
Notify Level (NOTIFYLEVEL) = 2
Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(66)
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(1048576)
Instance memory for restart light (%) (RSTRT_LIGHT_MEM) = 10
Agent stack size (AGENT_STACK_SZ) = 16
Sort heap threshold (4KB) (SHEAPTHRES) = 0
Directory cache support (DIR_CACHE) = YES
Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10
Priority of agents (AGENTPRI) = SYSTEM
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)

Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = YES

No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(1024)
No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(512)
db2start/db2stop timeout (min) (START_STOP_TIME) = 10

C:\SQ4BLANK>db2 get db cfg for dbname

Database Configuration for Database dbname
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = ANY
Number of frequent values retained (NUM_FREQVALUES) = 50
Number of quantiles retained (NUM_QUANTILES) = 50

Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(904928)
Database memory threshold (DB_MEM_THRESH) = 15
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(12320)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98)
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(4550)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 200000
Sort list heap (4KB) (SORTHEAP) = 20000

Database heap (4KB) (DBHEAP) = AUTOMATIC(4778)
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 3072
Log buffer size (4KB) (LOGBUFSZ) = 4024
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 65000
Buffer pool size (pages) (BUFFPAGE) = 5000
SQL statement heap (4KB) (STMTHEAP) = 20400
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(1024)
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(10016)
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4096)

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

Changed pages threshold (CHNGPGS_THRESH) = 33
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 6
Number of I/O servers (NUM_IOSERVERS) = 18
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC

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

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

Log file size (4KB) (LOGFILSIZ) = 15000
Number of primary log files (LOGPRIMARY) = 25
Number of secondary log files (LOGSECOND) = 200
Changed path to log files (NEWLOGPATH) =
Percent log file reclaimed before soft chckpt (SOFTMAX) = 175

I have also never been able to get the total memory used (from process explorer) above something like 3gb max, why can I not get to use the entire 4gb as advertized ?

This is the error I get, not all the time but fairly frequently.
*** stmmCostBenefitRecord ***
Type: BUFFER POOL ( IBMDEFAULTBP )
PageSize: 4096
Saved Misses: 0
Benefit:
-> Simulation size: 1024
-> Total seconds saved: 0 (+ 0 ns)
-> Normalized seconds/page: 0
Cost:
-> Simulation size: 1024
-> Total seconds saved: 0 (+ 0 ns)
-> Normalized seconds/page: 0
Current Size: 1003
Minimum Size: 1000
Potential Increase Amount: 501
Potential Increase Amount From OS: 501
Potential Decrease Amount: 0
Pages Available For OS: 0
Interval Time: 182.532

2012-05-15-18.35.58.554000-240 I134099F970 LEVEL: Event
PID : 6884 TID : 6044 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : dbname
APPHDL : 0-95 APPID: *LOCAL.DB2.120515222645
AUTHID : DB2ADMIN HOSTNAME: Tim-PC
EDUID : 6044 EDUNAME: db2stmm (dbname)
FUNCTION: DB2 UDB, Self tuning memory manager, stmmGetDBMemDataAutomatic, probe:3905
DATA #1 : String, 477 bytes
Configured memory on the system: 2066944
Memory currently available on the system: 947968
Configured memory on the partition: 1048576
Memory currently available on the partition: 92144
Set's configured size: 903568
Overflow left: 145264
Uncommited size: 351344
Target consumer size: 628797
Current consumer size: 758304
Max growth: 0
Current overflow percent: 0.200000
Target overflow percent: 0.200000
Average benefit: 0
Global benefit: Instance: DB2 DB: dbnameBenefit: 0


2012-05-15-18.35.58.554000-240 I135071F530 LEVEL: Event
PID : 6884 TID : 6044 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : dbname
APPHDL : 0-95 APPID: *LOCAL.DB2.120515222645
AUTHID : DB2ADMIN HOSTNAME: Tim-PC
EDUID : 6044 EDUNAME: db2stmm (dbname)
FUNCTION: DB2 UDB, Self tuning memory manager, stmmEnforceMinSizeConstraints, probe:2364
MESSAGE : Unable to find donor to satisfy minSize constraint

On 2012-05-16 00:53, Fin wrote:
Isn't there some kind of mem limit for windows somewhere around 3GB? As
you can tell I don't know any details (since I don't use windows
myself), so perhaps this has nothing at all to do with it.



/Lennart

Not for 64-bit. That only applies to the 32-bit version of windows.

No other general guidelines to help where to start ?

Anyone ?

On Wed, 16 May 2012 17:01:37 -0700, Fin wrote:

I would post the question the DB2 Express-C Forum on the IBM website.