Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2009
    Posts
    42

    Unanswered: joining tables in bufferpool

    I have a question about how tables are joined in bufferpools


    Assume I have 2 tables

    table A - assigned to bufferpool A
    table B - assigned to bufferpool B


    I need to join 2 tables in my query. I understand DB2 has to read data in the bufferpool to join them. I think DB2 will place table A in bufferpool A and table B in bufferpool B. Is this correct?


    Which bufferpool will DB2 use to join them - bufferpool A or bufferpool B?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by db2zip
    I need to join 2 tables in my query. I understand DB2 has to read data in the bufferpool to join them. I think DB2 will place table A in bufferpool A and table B in bufferpool B. Is this correct?
    Yes.

    Quote Originally Posted by db2zip
    Which bufferpool will DB2 use to join them - bufferpool A or bufferpool B?
    Whichever one it determines is the best for the intermediate table--if it needs one. If it does not need one, then neither will be used, it will just retrieve the data from both bufferpools as it need it.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Bufferpools are not used to join tables. I understand join operations are performed in the agent private memory allocated from the application heap or sort heap, depending on the type of join. In a partitioned or intra-parallel environment it gets a bit more complicated.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Apr 2009
    Posts
    42
    Thank you. Where can I find information about where tables are joined? DB2 manuals explain how tables are joined but I could not find clear information about where they're joined. It's mentioned that sortheap is used for some join but not very clear. Can you point me to some documentations?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't think there is a single place where you can read the answer to your question, because it's a bit ambiguous. Can you please explain what you are trying to achieve by asking that question?
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If DB2 needs a temporay table to store results of a joined table, then it uses a system temporay tablespace, which it selects based on the page size needed. If there is more than one system temporary tablespace for the size needed, then it generally uses them in a round robin fashion.

    If a temporay table is not needed, then some other memory heaps are used. DB2 may start out without a temporary table for the join, but if the memory heaps overflow, then a temporary table may be created.
    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
    Apr 2009
    Posts
    42
    Thank you for feedback. The reason I asked is because I want to understand where tables are joined. v9 manual explains 3 basic join strategies:
    - nested loop
    - merge
    - hash


    For hash join, it says:

    First, the designated INNER table is scanned and the rows copied into memory buffers drawn from the sort heap specified by the sortheap database configuration parameter. The memory buffers are divided into sections based on a hash value that is computed on the columns of the join predicates. If the size of the INNER table exceeds the available sort heap space, buffers from selected sections are written to temporary tables.


    It uses sortheap and temporary tablespace if required.


    But what about other join methods. I can't find information in manuals.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Let me rephrase n_i's question: what exactly do you need the information for? For example, if it is for some sort of research project, the answer would have to be much more specific than what we can cover here. If it is just for curiosity, then the answers given before already cover the various cases - even if it wasn't broken down to the 3 different types of joins. In general, you don't care how exactly DB2 performs the joins and where it allocates the memory from. It could even be that no additional memory is needed at all if no temp table is used.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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