Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    1

    Unanswered: Using 32k and 16k TEMP tablespaces

    We have 16K page size in the EDW, but the new application that was migrated, is using and requires 32K.

    With two BP for each page size - 16K and 32K respectively, and two TEMP tablespaces, will DB2 always choose the respecitve one for the query depending on the tables/tablespaces/page size?

    What is the rule associated with the choise of the TEMP? What is the Best Practice?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    the choice of the tmp is based on the resultset of the query and the need for intermediate tables. there is no overhead for defining a tmp space 16 and 32k
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Ellen Reys View Post
    We have 16K page size in the EDW, but the new application that was migrated, is using and requires 32K.

    With two BP for each page size - 16K and 32K respectively, and two TEMP tablespaces, will DB2 always choose the respecitve one for the query depending on the tables/tablespaces/page size?

    What is the rule associated with the choise of the TEMP? What is the Best Practice?
    DB2 will choose a tempspace with sufficient size for the row (regardless of the page size of the tables involved). Here's an example:

    https://groups.google.com/forum/#!to...b2/KaIoHG-vuPg
    --
    Lennart

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by lelle12 View Post
    DB2 will choose a tempspace with sufficient size for the row (regardless of the page size of the tables involved). Here's an example:

    https://groups.google.com/forum/#!to...b2/KaIoHG-vuPg
    This is unfortunate if one wants to create a dedicated bufferpool for each system temporary tablespace. The above answer suggests that it not a good idea to do so, since it may be completed wasted memory (unless one is using STMM automatic bufferpool sizes).
    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
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    From v9.7 InfoCenter:

    "In general, when temporary table spaces of different page sizes exist, the optimizer will choose the temporary table space whose buffer pool can hold the most number of rows (in most cases that means the largest buffer pool). In such cases, it is often wise to assign an ample buffer pool to one of the temporary table spaces, and leave any others with a smaller buffer pool. Such a buffer pool assignment will help ensure efficient utilization of main memory."

    https://pic.dhe.ibm.com/infocenter/d...Fc0004975.html

Posting Permissions

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