Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    21

    Unanswered: DB2 Configuration

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

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    with 31bit addressing the limit is 2G
    if above, you need 64bit addressing
    you can check in sql reference or infocenter for limits
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    can also use /3gb switch to bypass 2 gb restriction
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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 ??
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    This is a Windows startup option switch, requesting to extend user memory area from the standard 2 GB to 3 GB.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by bga

    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.
    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.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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