Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Posts
    6

    Unanswered: question on database_memory=automatic

    I undersstand in db2 v8 when database_memory is set to AUTOMATIC(default), DB2 internally calculates how much memory is required for the shared memory contributors: bufferpool,locklist,package cache, catalog cache dbheap etc.

    What happens upon db activation if the database_memory is set to AUTOMATIC and these shared memory contributors are assigned to fixed values? For example, bufferpool1 = 500M, bufferpool2 =300M, locklist = 1000, package cache =50M (this is just hypothetical values)

    1. Will the database memory ignore the assigned values and used the internally calculated values or
    2. will the database memory be the sum of all the assigned values for these memory contributors?

    I am confused by the phrase "DB2 internally calculates how much memory is required"
    What factors is this internal calculation based on?
    Any ideas will be greatly appreciated.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Basically, the answer closest to the truth is number, 2, the database memory be the sum of all the assigned values for these memory contributors. For example, the database memory does not override the bufferpool sizes you have set up.

    If the database memory specified is less than the amount calculated from the individual parameters (for example, locklist, utility heap, bufferpools, etc), the larger amount will be used.

    However, in V9, the capability does exist to let DB2 choose all the memory allocations dyanamically (including 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

  3. #3
    Join Date
    Oct 2007
    Posts
    6
    Marcus, i thought your explanation pertains to the situation where you have database_memory set to a fixed value. In this case DB2 compares the assigned value with the value for the sum of all the shared memory contributers and uses whichever is greater.

    Are you saying even if the database_memory is set to AUTOMATIC, DB2 nontheless will sum up the values assigned to the shared memory contributers and use that?

    What is the meaning of "db2 will internally calculate the memory requirements of the database if database_memory is set to AUTOMATIC". -- db2 docs?
    I am still not convinced.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If it is set to "automatic" (highly recommended) then DB2 "internally" adds up all the components and uses that number. Some of the shared memory allocations are not hard-fixed, so setting database memory to a specific number will limit how much more than the specified values that DB2 will use, but this can be very dangerous IMO unless you know exactly what you are doing.

    Just set it to automatic and use db2mtrk to see how much instance (-i) and database (-d) memory is being used (if you are concerned). Obviously, the amount of memory being used should be less than the amount of real memory available (before DB2 is started). You do not want to be using virtual memory with DB2. However, some OS's will use any free memory for disk cache and give it back if another resource asks for it, so sometimes it is hard to tell how much memory is actually available.
    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
    Oct 2007
    Posts
    6
    with db_memory set as AUTOMATIC, what happens if I try to increase my bufferpool size and the requested
    size is greater than the reserved 10% overflow.?

    Will bufferpool increase request fail or DB2 will grab it from the OS?

    If the request should succeed, does the additional bufferpool become readily available?

    Thanks

Posting Permissions

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