serious memory problem

[xixi]

i have a very serious memory problem, we have db2 udb v8.1 load on a
HP titanium machine with 4 G memory, it is 64bit machine, currently on
DB2 instance , i have three databases, but only one is being used, on
the same machine, i have an application server running connect to this
DB2 server, every day there are around 12 persons has connection
through the app server to db server, the database has 9 G data/index,
problem is in the morning the memory used is better than in the
afternoon, in the afternoon, the memory used by db2syscs.exe used over
2g memory, and the VM size used by the process is over 4 g, also i
found there are 4 of db2fmt64.exe process running there, don't
understand why. i don't know how to control the memory not increasing
like this, i have tried to lower down one of the bufferpool size to
250000 pages, so if the bufferpool size is being used by all the
connection, shouldn't be the memory used limited to some point? one
thing i noticed is that i used control center change the bufferpool
size IBMfefaultfb from 437471 to 250000, but the get db cfg still
gives me 437471, so which value DB2 is using? i am confusing

i have get the db cfg for reference
Database Configuration for Database nji

Database configuration release level = 0x0a00
Database release level = 0x0a00

Database territory = US
Database code page = 1252
Database code set = IBM-1252
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) = 7200
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 820
Log buffer size (4KB) (LOGBUFSZ) = 65
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 437471
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) = 1000

Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 11592
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) =
(SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 192
SQL statement heap (4KB) (STMTHEAP) = 4096
Default application heap (4KB) (APPLHEAPSZ) = 60000
Package cache size (4KB) (PCKCACHESZ) = 859
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
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) = 1
Number of I/O servers (NUM_IOSERVERS) = 6
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
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) = 40
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64

Log file size (4KB) (LOGFILSIZ) = 20480
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 0
Changed path to log files (NEWLOGPATH) =
Path to log files =
D:\DB2\NODE0000\SQL00002\SQLOGDIR\
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) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 120
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
(ACCESS)
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) =
db2 =>

[Larry]

What's your fixpack level? When you say only one db is "being used" what
does that mean? Have you checked with support for any applicable fixes?

Larry

xixi wrote:

i have a very serious memory problem, we have db2 udb v8.1 load on a
HP titanium machine with 4 G memory, it is 64bit machine, currently on
DB2 instance , i have three databases, but only one is being used, on
the same machine, i have an application server running connect to this
DB2 server, every day there are around 12 persons has connection
through the app server to db server, the database has 9 G data/index,
problem is in the morning the memory used is better than in the
afternoon, in the afternoon, the memory used by db2syscs.exe used over
2g memory, and the VM size used by the process is over 4 g, also i
found there are 4 of db2fmt64.exe process running there, don't
understand why. i don't know how to control the memory not increasing
like this, i have tried to lower down one of the bufferpool size to
250000 pages, so if the bufferpool size is being used by all the
connection, shouldn't be the memory used limited to some point? one
thing i noticed is that i used control center change the bufferpool
size IBMfefaultfb from 437471 to 250000, but the get db cfg still
gives me 437471, so which value DB2 is using? i am confusing

i have get the db cfg for reference
Database Configuration for Database nji

Database configuration release level = 0x0a00
Database release level = 0x0a00

Database territory = US
Database code page = 1252
Database code set = IBM-1252
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) = 7200
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 820
Log buffer size (4KB) (LOGBUFSZ) = 65
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 437471
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) = 1000

Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 11592
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) =
(SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 192
SQL statement heap (4KB) (STMTHEAP) = 4096
Default application heap (4KB) (APPLHEAPSZ) = 60000
Package cache size (4KB) (PCKCACHESZ) = 859
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
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) = 1
Number of I/O servers (NUM_IOSERVERS) = 6
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
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) = 40
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64

Log file size (4KB) (LOGFILSIZ) = 20480
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 0
Changed path to log files (NEWLOGPATH) =
Path to log files =
D:\DB2\NODE0000\SQL00002\SQLOGDIR\
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) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 120
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
(ACCESS)
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) =
db2 =>

[Larry]

What's your fixpack level? When you say only one db is "being used" what
does that mean? Have you checked with support for any applicable fixes?

Larry

xixi wrote:

i have a very serious memory problem, we have db2 udb v8.1 load on a
HP titanium machine with 4 G memory, it is 64bit machine, currently on
DB2 instance , i have three databases, but only one is being used, on
the same machine, i have an application server running connect to this
DB2 server, every day there are around 12 persons has connection
through the app server to db server, the database has 9 G data/index,
problem is in the morning the memory used is better than in the
afternoon, in the afternoon, the memory used by db2syscs.exe used over
2g memory, and the VM size used by the process is over 4 g, also i
found there are 4 of db2fmt64.exe process running there, don't
understand why. i don't know how to control the memory not increasing
like this, i have tried to lower down one of the bufferpool size to
250000 pages, so if the bufferpool size is being used by all the
connection, shouldn't be the memory used limited to some point? one
thing i noticed is that i used control center change the bufferpool
size IBMfefaultfb from 437471 to 250000, but the get db cfg still
gives me 437471, so which value DB2 is using? i am confusing

i have get the db cfg for reference
Database Configuration for Database nji

Database configuration release level = 0x0a00
Database release level = 0x0a00

Database territory = US
Database code page = 1252
Database code set = IBM-1252
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) = 7200
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 820
Log buffer size (4KB) (LOGBUFSZ) = 65
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 437471
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) = 1000

Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 11592
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) =
(SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 192
SQL statement heap (4KB) (STMTHEAP) = 4096
Default application heap (4KB) (APPLHEAPSZ) = 60000
Package cache size (4KB) (PCKCACHESZ) = 859
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
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) = 1
Number of I/O servers (NUM_IOSERVERS) = 6
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
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) = 40
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64

Log file size (4KB) (LOGFILSIZ) = 20480
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 0
Changed path to log files (NEWLOGPATH) =
Path to log files =
D:\DB2\NODE0000\SQL00002\SQLOGDIR\
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) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 120
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
(ACCESS)
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) =
db2 =>

[xixi]

fix pack 3, only one db used means i have DB2 instance, under this
instance we have three database, but one is being used by application.
fix pack 5 seems have problem when i load on my machine since the
error in db2diag.log like this
2004-05-03-10.34.38.296000 Instance:DB2 Node:000
PID:704(db2dasstm.exe) TID:836 Appid:none
oper system services sqloSSemClose Probe:20

Unexpected system error 0x6 has occurred.
This has been mapped to ZRC 0x83000006.

PID:704 TID:836 Node:000 Title: SYSTEM ERROR DESCRIPTION
The handle is invalid.

so i don't want to rush to put the fixpack5.

[xixi]

fix pack 3, only one db used means i have DB2 instance, under this
instance we have three database, but one is being used by application.
fix pack 5 seems have problem when i load on my machine since the
error in db2diag.log like this
2004-05-03-10.34.38.296000 Instance:DB2 Node:000
PID:704(db2dasstm.exe) TID:836 Appid:none
oper system services sqloSSemClose Probe:20

Unexpected system error 0x6 has occurred.
This has been mapped to ZRC 0x83000006.

PID:704 TID:836 Node:000 Title: SYSTEM ERROR DESCRIPTION
The handle is invalid.

so i don't want to rush to put the fixpack5.