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.

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.

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.

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.

None, one or the other, or both, depending on the query and its access plan.

Where does db2 allocate memory from to do the join? I think hash join will use sortheap. What about nested loop and merge?