Mabeljovan,

Can anyone help me? I'm new to DB2.
How to solve this problem.

2007-09-19-18.43.33.891001 InstanceB2 Node:000
PID:400(db2syscs.exe) TID:4604 Appid:GA51401C.A809.015089100125
sort/list services sqlsOptimizeNumMergeRuns Probe:10 DatabasePCCSLG

ADM9000W Prefetching was disabled during sort merge; performance may be
suboptimal. If this message persists, consider increasing the buffer pool size
for temporary table space "TEMPSPACE1" (ID "1") or increase the value of the
SORTHEAP DB configuration parameter to reduce the extent of sort spilling.

as the message indicates
- get the value of SORTHEAP : db2 get db cfg for databasename | grep SORTHEAP and increase this value : db2 update db cfg for dbname using SORTHEAP newvalue
- check db2look and pick the name of the bufferpool used by this tempspace1 and increase the number of pages : db2 alter bufferpool ....

I'm still getting the problem and i have increase the SORT HEAP and BUFFPAGE

this is my cfg for my DB

Database Configuration for Database

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
Database collating sequence = BINARY
Alternate collating sequence (ALT_COLLATE) =

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
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

Backup pending = NO

Database is consistent = NO
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = YES

Log retain for recovery status = RECOVERY
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) = 600
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 501
Log buffer size (4KB) (LOGBUFSZ) = 99
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 88526
Buffer pool size (pages) (BUFFPAGE) = 300
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) = 50

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

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 1554
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 4000
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) = -1

Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 2
Number of I/O servers (NUM_IOSERVERS) = 33
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) = 1024
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 20
Changed path to log files (NEWLOGPATH) =
Path to log files = F:\DB2\NODE0000\SQL00
014\SQLOGDIR\
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0001324.LOG
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) = RECOVERY
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

First log archive method (LOGARCHMETH1) = LOGRETAIN
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 (ACCESS)
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

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

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

Hello Together,

i have the same problem. I have a Bufferpool of 1000 and SORTHEAP is 131072.

Give it any solution for this problem?

Thanks for your help!!!

In order to help you, I need the following information:

1. db2 "select * from syscat.bufferpools"

2. A list of any other databases on the same machine (same instance or a different instance) and the bufferpool info from 1 above if there are other databases.

3. The amount of phyiscal RAM on your DB2 server (total, used, free, cached), and a list of any other applications (web tier, app tier, etc) run on the same machine (preferably the server is for database only).

4. Output from db2level command.

1. Ibmdefaultbp
1 -
4000 4096 N
0 0 -

Objectpool
2 -
36000 32768 N
0 0 -

Objpartspool
3 -
4000 32768 N
0 0 -

Smspool
4 -
9500 4096 N
0 0 -

Partspool
5 -
2500 32768 N
0 0 -

Blobpool
6 -
9194 32768 N
0 0 -

Replicapool
7 -
9194 32768 N
0 0 -

Trackingpool
8 -
5000 4096 N
0 0 -

Validatepool
9 -
9000 32768 N
0 0 -

4. DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09052" wi
level identifier "03030107".
Informational tokens are "DB2 v9.5.201.346", "s080911", "WR21421", and Fix
"2a".
Product is installed at "F:\db2\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".

There seem to be several problems with your configuration.

1. It's not clear what bufferpool is assigned to TEMPSPACE1, but, assuming the default configuration, 15 MB is too small.
2. The total size of your bufferpools alone exceeds 2 GB. The default user memory limit on 32 bit Windows is 2 GB, so your server is likely to suffer from heavy swapping.
3. One large bufferpool is usually more efficient than 9 small bufferpools.

For Bufferpool for TEMPSPACE1 is IBMDEFAULTBP. The size is 4000 (4KB pages) at the moment. What is a good size for this bufferpool?
Sortheap size is 131072 (4KB pages).


This is a overview from our db2diag.log
This messages repeats all 10 minutes.


FUNCTION: DB2 UDB, sort/list services, sqlsOptimizeNumMergeRuns, probe:10
MESSAGE : ADM9000W Prefetching was disabled during sort merge; performance may
be suboptimal. If this message persists, consider increasing the
buffer pool size for temporary table space "TEMPSPACE1" (ID "1") or
increase the value of the SORTHEAP DB configuration parameter to
reduce the extent of sort spilling.

2009-09-09-15.10.01.734000+120 E1904H789 LEVEL: Warning
PID : 3552 TID : 3528 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : RMDB
APPHDL : 0-29548 APPID:
AUTHID : RMADMIN
EDUID : 3528 EDUNAME: db2agent (RMDB) 0
FUNCTION: DB2 UDB, sort/list services, sqlsOptimizeNumMergeRuns, probe:10
MESSAGE : ADM9000W Prefetching was disabled during sort merge; performance may
be suboptimal. If this message persists, consider increasing the
buffer pool size for temporary table space "TEMPSPACE1" (ID "1") or
increase the value of the SORTHEAP DB configuration parameter to
reduce the extent of sort spilling.

2009-09-09-15.15.55.875000+120 E2695H599 LEVEL: Warning
PID : 3552 TID : 1828 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : RMDB
APPHDL : 0-29554 APPID:
AUTHID : RMADMIN
EDUID : 1828 EDUNAME: db2agent (RMDB) 0
FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:2
MESSAGE : ADM5500W DB2 is performing lock escalation. The total number of
locks currently held is "47920", and the target number of locks to
hold is "23960".

2009-09-09-15.15.55.875000+120 E3296H544 LEVEL: Warning
PID : 3552 TID : 1828 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : RMDB
APPHDL : 0-29554 APPID:
AUTHID : RMADMIN
EDUID : 1828 EDUNAME: db2agent (RMDB) 0
FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:3
MESSAGE : ADM5502W The escalation of "47917" locks on table "RMADMIN
.RMOBJECTS" to lock intent "X" was successful.