Hi everyone,

What is the maximum memory of DB2 UDB 8.1.6 can address? While running DB2 configuration advisor, in the server part it shows that the physical memory is only 2GB while our current RAM is 16GB, can anybody guide us on how to maximize the memory available. I have attached the DB2 configuration recommendations and the current configuration of our system.

We are using PeopleSoft Enterprise 8.11 SP1 / DB2 UDB 8.1.6 / Webshpere 5 / Windows 2003 / HP Server with 3 physical processor / 16GB RAM / and with 100 active users

Thank you in advance.

Benjamin Agupitan

================================================== ===
Current and Recommended Values for Database Manager Configuration
================================================== ===
Database server = DB2/NT 8.1.6

Description Parameter Current Value Recommended Value
-------------------------------------------------------------------------------------------------
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 1024 168
Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 17693 17693
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 930 931
Changed pages threshold (CHNGPGS_THRESH) = 60 60
Database heap (4KB) (DBHEAP) = 1000 1000
Degree of parallelism (DFT_DEGREE) = 1 1
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32 32
Default query optimization class (DFT_QUERYOPT) = 5 5
Max storage for lock list (4KB) (LOCKLIST) = 3072 3072
Log buffer size (4KB) (LOGBUFSZ) = 512 132
Log file size (4KB) (LOGFILSIZ) = 2048 1024
Number of primary log files (LOGPRIMARY) = 3 3
Number of secondary log files (LOGSECOND) = 0 0
Max number of active applications (MAXAPPLS) = 100 40
Percent. of lock lists per application (MAXLOCKS) = 60 60
Group commit count (MINCOMMIT) = 2 1
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 2 2
Number of I/O servers (NUM_IOSERVERS) = 12 20
Package cache size (4KB) (PCKCACHESZ) = 2000 859
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100 120
Sort list heap (4KB) (SORTHEAP) = 2032 2032
SQL statement heap (4KB) (STMTHEAP) = 8000 8000
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 10000 133653
Number of FCM buffers = 8192 4096
Enable intra-partition parallelism = 1 1
Maximum query degree of parallelism = 6 5
Maximum number of agents = 1250 401
Agent pool size = -1 50
Initial number of agents in pool = 4 0
Private memory threshold in 4KB pages = 20000 32767
Sort heap threshold = 66508 40652
IBMDEFAULTBP Bufferpool size = 2428 4812
PSBP32K Bufferpool size = 10000 14493
PSBP4K Bufferpool size = 72000 96626
PSBPIDX Bufferpool size = 72000 96626
TEMP32BP Bufferpool size = 7200 9660
TEMP4BP Bufferpool size = 7200 9660

================================================== =======
Database Configuration for Database
================================================== =======
Database server = DB2/NT 8.1.6

Database configuration release level = 0x0a00
Database release level = 0x0a00
Database territory = US
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
Discovery support for this database (DISCOVER_DB) = ENABLE
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
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
Backup pending = NO
Database is consistent = NO
Rollforward pending = NO
Restore pending = NO
Multi-page file allocation enabled = NO
Log retain for recovery status = NO
User exit for logging status = NO
Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0
Database heap (4KB) (DBHEAP) = 1000
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 930
Log buffer size (4KB) (LOGBUFSZ) = 512
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 10000
Buffer pool size (pages) (BUFFPAGE) = 8000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 3072
Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 17693
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 80
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 1024
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 2032
SQL statement heap (4KB) (STMTHEAP) = 8000
Default application heap (4KB) (APPLHEAPSZ) = 6000
Package cache size (4KB) (PCKCACHESZ) = 2000
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384
Interval for checking deadlock (ms) (DLCHKTIME) = 1000
Percent. of lock lists per application (MAXLOCKS) = 60
Lock timeout (sec) (LOCKTIMEOUT) = 60
Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 2
Number of I/O servers (NUM_IOSERVERS) = 20
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = NO
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32
Track modified pages (TRACKMOD) = OFF
Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = 100
Average number of active applications (AVG_APPLS) = 50
Max DB files open per application (MAXFILOP) = 2000
Log file size (4KB) (LOGFILSIZ) = 2048
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 0
Changed path to log files (NEWLOGPATH) =
Path to log files =
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
Group commit count (MINCOMMIT) = 2
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
Auto restart enabled (AUTORESTART) = ON
Index re-creation time (INDEXREC) = SYSTEM (RESTART)
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
TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =

with 31bit addressing the limit is 2G
if above, you need 64bit addressing
you can check in sql reference or infocenter for limits

can also use /3gb switch to bypass 2 gb restriction

i don't see what this means
can also use /3gb switch to bypass 2 gb restriction
i had in mind that switches have something todo with network equipment ??

This is a Windows startup option switch, requesting to extend user memory area from the standard 2 GB to 3 GB.

On a 32-bit Windows system any user (that is, non-kernel) program, including the DB2 service, is limited to 2 GB of RAM (3 GB if the /3GB switch is used at startup, as mentioned earlier).

To utilize the remaining memory on your server, apart from migrating to the 64-bit version of Windows, you could define extended memory bufferpools. There is a Redbook about tuning DB2 on Windows; go to www.redbooks.ibm.com and use the search there.