Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Join Date
    Sep 2009
    Posts
    66

    Unanswered: database_memory automatic

    Can we set database_memory to AUTOMATIC on DB2 9.5.5 for Linux?

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368

  3. #3
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    Yes , but it is worth analysing what this means in a multithreaded environment. and How this relates to instance_memory .

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    My preference on Linux is to use COMPUTED and to hard code the following STMM configurations (example settings given below):

    Self tuning memory (SELF_TUNING_MEM) = ON
    Size of database shared memory (4KB) (DATABASE_MEMORY) = COMPUTED
    Database memory threshold (DB_MEM_THRESH) = 10
    Max storage for lock list (4KB) (LOCKLIST) = 8192
    Percent. of lock lists per application (MAXLOCKS) = 30
    Package cache size (4KB) (PCKCACHESZ) = 8192
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 50000
    Sort list heap (4KB) (SORTHEAP) = 10000


    I also recommend that Bufferpools be hard-coded and not set to -2. Setting bufferpools to about 50% of total system memory is a good starting point (but not larger than the tables and indexes using a given bufferpool). Also, you will need to take into consideration all bufferpools for all databases on the server when using the 50% number.

    Note that even though STMM is ON, if the above parameter are hard-coded (as they would be in 8.2) then it shows as "Inactive" when you do "get db cfg show details" (you must be connected to the database to run this command).

    To tune the above parms do a "snapshot for database":

    1. If the package cache is frequently showing overflow, then increase package cache setting. If it never shows overflow, then you can lower it.
    2. If sorts are overflowing frequently, then increase the 2 sort parms proportionately. If not overflow, you can lower it.
    3. Check you bufferpool hit ratios for data and indexes, and adjust the bufferpool sizes (if you have more memory) to keep it as high as possible.
    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
    Sep 2009
    Posts
    66
    Quote Originally Posted by Jack Vamvas View Post
    Yes , but it is worth analysing what this means in a multithreaded environment. and How this relates to instance_memory .
    Jack, Can you explain what this means in a multithreaded environment and how this relates to instance_memory?

  6. #6
    Join Date
    Sep 2009
    Posts
    66
    Marcus,

    What is the difference between COMPUTED and AUTOMATIC for DATABASE_MEMORY on DB2 Linux? Why do you set SELF_TUNING_MEM to ON if not using STMM?

  7. #7
    Join Date
    Sep 2009
    Posts
    66
    What do you recommend for INSTANCE_MEMORY? COMPUTED or AUTOMATIC?

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't set STMM to ON, it is ON by default. But it is Inactive if the parameters it controls (see config above) are all set to specific values.

    AUTOMATIC means that STMM can control the database memory when the other parms increase or decrease according to STMM. COMPUTED means that DB2 simply adds up the various components that make up the database memory that you hard-coded (although it can increase somewhat beyond that if needed).

    If you have database memory set to AUTOMATIC, but STMM is OFF or Inactive, then it automatically changes to COMPUTED at db activation time.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by L_DBA_L View Post
    What do you recommend for INSTANCE_MEMORY? COMPUTED or AUTOMATIC?
    For Instance Memory on Linux, I set it to a hard-code value if there is more than one instance. This is maximum amount of memory that can be used, and DB2 does not allocate it unless needed by the all the other memory configs defined in dbm and db.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Sep 2009
    Posts
    66
    Why do we need STMM if we're not going to use it?

    What is the best way to see how much DATABASE_MEMORY and INSTANCE_MEMORY DB2 is using?

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by L_DBA_L View Post
    Why do we need STMM if we're not going to use it?
    IBM needs it to help convince management that they should purchase DB2 because it automatically configures itself without having to have any of those pesky (and expensive) DBA's. Unfortunately, IMO it does not work well (at least so far).

    Quote Originally Posted by L_DBA_L View Post
    What is the best way to see how much DATABASE_MEMORY and INSTANCE_MEMORY DB2 is using?
    There are (at least) two ways:

    • db2mtrk
    • db2pd

    Check the Command Reference manual for the different options of the above commands.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Sep 2009
    Posts
    66
    Quote Originally Posted by Marcus_A View Post
    IBM needs it to help convince management that they should purchase DB2 because it automatically configures itself without having to have any of those pesky (and expensive) DBA's. Unfortunately, IMO it does not work well (at least so far).

    Do you think this is helping IBM? Do companies make their decision about which DBMS to purchase based on STMM? I don't want to be replaced by STMM Do you know if other DBMS have STMM?

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by L_DBA_L View Post
    Do you think this is helping IBM? Do companies make their decision about which DBMS to purchase based on STMM? I don't want to be replaced by STMM Do you know if other DBMS have STMM?
    There have been many studies and marketing claims as to which database has the lowest cost of ownership. IBM has undertaken significant efforts to automate DB2 to lower the need for DBA's (or have them concentrate on more important things). Unfortunately two things have happened:

    • Automation features like STMM don't always work well, sometimes causing serious problems that require many more hours of DBA work than would be needed to just manually tune the database.
    • DB2 is adding other features at an amazing rate to be competitive with the features of other databases, and this has made DB2 significantly more complex than before, so the overall need for qualified DBA's is even higher than before.

    So I don't think you need to worry about your DBA job going away. The biggest worry is being able to keep your database up and running with these "so-called" improvements that have implemented by IBM.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Sep 2009
    Posts
    66
    Excellent. Thank you.

  15. #15
    Join Date
    Apr 2009
    Posts
    42
    Marcus_A, hello,can it somehow draw into conclusion that most of the time db2 is running slow because database memory is set to auto? Or at least it is a good starting point to look at?

Posting Permissions

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