Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Posts
    33

    Unanswered: advice on bufferpool

    hi,

    i need advise from db2 experts on bufferpool.

    scenario is given below.

    i have a (32k) tablespace and bufferpool for the same 600mb and this tablespace contains around 120 gb of datas.

    tablespace 32k bufferpool 32k total size of data (tables)
    20000 120 gb

    How much i can increase the bufferpool size in order to support the 120gb of data and performance?

    pls advice


    regards

    Sanjai

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you have a 64 instance, you can allocate as much real unused memory as your server currently has. Real means physical memory, and not virtual. Unused means no other process on that server is using it (it is free memory). Just increase the bufferpool size (number of pages).

    If you have a 32 bit instance, you are limited to about 1 GB - 2GB for bufferpools depending on the operating system.
    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
    Jan 2007
    Posts
    33
    Thanks Mr. Marcus, For DWH environment, if i alter the buffer pool as block based bufferpool will the performance improve. kindly advice

    regards

    sanjai

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Block based might help if you have a lot of table scans.

    Since it is very unlikely that you can fit all the data in bufferpools and keep it there with a large data warehouse, you have to figure out which data should be (such as indexes and highly used small tables) put in a seperate bufferpool that has about the same amount of memory as there is data assigned to that bufferpool. This will enable your bufferpool hit ratio to be high for at least some important objects (indexes and small tables), even if the larger tables will have a lower bufferpool hit ratio.

    In order to assign indexes to a seperate bufferpool than the table data, it must be in a DMS tablespace, and you must specify a seperate tablespace for indexes when the table is created.
    Last edited by Marcus_A; 07-11-08 at 23:45.
    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
  •