Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: how to calculate DB memory?

    (DB2 v8.2 ESE on AIX v5.3) trying to calculate how much memory DB takes. According to documentation: "If this amount [DATABASE_MEMORY] is less than the amount calculated from the 1 individual parameters (for example, locklist, utility heap, 1 bufferpools, and so on), the larger amount will be used. "
    What exactly are the individual db cfg params to be used for memory calculation? Here is an exerpts from our db cfg:

    Database heap (4KB) (DBHEAP) = 3000
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(37676)
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4) 40x4=160
    Log buffer size (4KB) (LOGBUFSZ) = 8
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
    Buffer pool size (pages) (BUFFPAGE) = 20000
    Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
    Max storage for lock list (4KB) (LOCKLIST) = 100

    Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000
    Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES) 20000
    Sort list heap (4KB) (SORTHEAP) = 256
    SQL statement heap (4KB) (STMTHEAP) = 4096
    Default application heap (4KB) (APPLHEAPSZ) = 512
    Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8) 40x8=320
    Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    please take a look at the following article:
    The DB2 UDB memory model

    Search for "formula"

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by db2girl
    please take a look at the following article:
    The DB2 UDB memory model

    Search for "formula"
    This is awesome. Spasibo, Bella.

    Why the formula does not include SORTHEAP and BUFFPAGE sizes?

    The article does not mention size of hidden bufferpools but I did a search on hidden bufferpools and found that each of them is of 16 page size.
    Last edited by MarkhamDBA; 05-22-09 at 11:19.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by MarkhamDBA
    This is awesome. Spasibo, Bella.

    Why the formula does not include SORTHEAP and BUFFPAGE sizes?

    The article does not mention size of hidden bufferpools but I did a search on hidden bufferpools and found that each of them is of 16 page size.
    BUFFPAGE is obsolete (will be removed in a future release) and should not be used. It is only active if a bufferpool size is set to -1 (select * from syscat.bufferpools).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Marcus_A
    BUFFPAGE is obsolete (will be removed in a future release) and should not be used. It is only active if a bufferpool size is set to -1 (select * from syscat.bufferpools).
    In what DB2 version is BUFFPAGE obsolete, Marcus? I am doing calculations for DB2 v8.2. Thanks
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    When intra_parallel or concentrator is off, sorts are private. Private sorts are allocated out of agent private memory ("ulimit -d" setting) so sortheap is not included into the calculation.

    For shared sorts, you need to use the second formula which also counts sheapthres_shr.

    Please check the following URL regarding buffpage:
    DB2 Universal Database

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by MarkhamDBA
    In what DB2 version is BUFFPAGE obsolete, Marcus? I am doing calculations for DB2 v8.2. Thanks
    Although BUFFPAGE is still working in the latest release (9.5) IBM has indicated that it is depricated and will be removed in a future release (no mention of when that will be), so do not use it. It is only applicable to bufferpools where the size is set to -1 in syscat.bufferpools.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Marcus_A
    Although BUFFPAGE is still working in the latest release (9.5) IBM has indicated that it is depricated and will be removed in a future release (no mention of when that will be), so do not use it. It is only applicable to bufferpools where the size is set to -1 in syscat.bufferpools.
    one of our production databases has only one bufferpool IBMDEFAULTBP and it has NPAGES=-1. Does it mean that I should add (BUFFPAGE) = 100000 to database shared memory calculation? Thanks Marcus
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by MarkhamDBA
    one of our production databases has only one bufferpool IBMDEFAULTBP and it has NPAGES=-1. Does it mean that I should add (BUFFPAGE) = 100000 to database shared memory calculation? Thanks Marcus
    You can run db2mtrk (see Command Reference for options) to see the actual bufferpool memory being used.

    But yes, if your bufferpool size is -1, you are using the value in the BUFFPAGE setting. I would recommend that you alter the bufferpool and set the size explicitly, and set BUFFPAGE back to 1000.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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