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?

Yes.

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

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.

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?

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?

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.

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.

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.