Results 1 to 9 of 9

Thread: Problem

  1. #1
    Join Date
    Jun 2007
    Posts
    27

    Unanswered: Problem

    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.

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    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 ....
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jun 2007
    Posts
    27
    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

  4. #4
    Join Date
    Sep 2009
    Posts
    14
    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!!!

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Sep 2009
    Posts
    14
    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 -
    Last edited by m_isterco; 09-04-09 at 09:50.

  7. #7
    Join Date
    Sep 2009
    Posts
    14
    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".

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Sep 2009
    Posts
    14
    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.
    Last edited by m_isterco; 09-09-09 at 10:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •