Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    40

    Unanswered: Bufferpool Usage.

    Hello All,
    I have a question regarding tablespaces and bufferpool usage.
    Let's say there is tableA residing in TablespaceA which is pagesize 32K.
    There is another Table tableB residing in TablespaceB which is of pagesize 4K.

    Now If write a query to join both these tables which bufferpool will DB2 use?

    Thanks
    Rajesh.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 will not necessarily use any bufferpool for the result-set unless it has to spool it to temporary table first before returning the result to the SQL client. For example, if an ORDER BY is used, DB2 "may" have to write the result-set to the temporary table, but even this might be avoided if there is enough sort heap memory available (the amount of sort heap memory is determined by two of the db cfg parms, or by STMM if you are using that). Likewise, a nested loop join may not need any temporary table, while a very large merge-scan join might need one. If a temporary table is needed by DB2, then one of the system temporary tablespaces will be used that matches the size of the resultset, along with the appropriate bufferpool assigned to such a tablespace.
    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
    May 2010
    Posts
    88
    That was a nice question by rajesh.

    @Marcus,
    What you explained is true if we perform operations that will bring temporary tables into the picture. But what if its a simple join operation which does not need temporary table spaces?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2cap View Post
    That was a nice question by rajesh.

    @Marcus,
    What you explained is true if we perform operations that will bring temporary tables into the picture. But what if its a simple join operation which does not need temporary table spaces?
    I thought I explained that if no temporary table needs to be created by DB2, then there is no seperate bufferpool for the result-set, other than the bufferpools for the two tables being joined. Thre are obviously other memory heaps that DB2 uses to cache the result-set, but not 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
    Oct 2007
    Posts
    246
    i believe this is wht marcus wanted to say. When there is simple join were every thing happens in bufferpool itself for 32k tablespace and 4k tablespace all works done in there own bufferpools IF the memory is not enought then it goes to temp tablespace and as marcus said then one of the system temporary tablespaces will be used that matches the size of the resultset, along with the appropriate bufferpool assigned to such a tablespace. Please correct me.

    regds
    Paul

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Mathew_paul View Post
    i believe this is wht marcus wanted to say. When there is simple join were every thing happens in bufferpool itself for 32k tablespace and 4k tablespace all works done in there own bufferpools IF the memory is not enought then it goes to temp tablespace and as marcus said then one of the system temporary tablespaces will be used that matches the size of the resultset, along with the appropriate bufferpool assigned to such a tablespace. Please correct me.

    regds
    Paul
    It is not just a question of whether there is memory in bufferpools for the tables being joined. It depends on whether DB2 needs to materialize the entire result-set before it can return the first row back to the SQL client. For example, if the SQL uses an ORDER BY, and DB2 cannot (or decides not to) process the results in the order requested by using an ordered index, DB2 will need to spool the entire result set somewhere before the first row can be returned. In the case of a sort, DB2 will try and use sort heap memory before spooling the result-set to a temporary table (which has its own bufferpool), but if the sort heap memory is not sufficient than a temporary table will be created in the system temporary tablespace and the bufferpool for the system temporary tablespace will be utilized.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Rajesh1203 View Post

    Now If write a query to join both these tables which bufferpool will DB2 use?
    None, one or the other, or both, depending on the query and its access plan.

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Where does db2 allocate memory from to do the join? I think hash join will use sortheap. What about nested loop and merge?

Posting Permissions

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