Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    67

    Unanswered: 8k page size tablespace without 8k page size temp tablespace

    Hi All,
    our database has one 8k page size user tablespace but ex-DBA has not created 8k page size temporary tablespace.

    my question is if tables into 8k tablespace need to be sorted or ... and use temp tablespace, are they use 4k default temp tablespace ? what db2 does in this case ?

    Thank you in advance for your help

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Tables don't usually get sorted, but you might need a matching (8K page size) system temporary tablespace for a reorg, as an example. What can be sorted is a query result set, so there must be a temp tablespace with a page size to accommodate the widest result set (including intermediate results).

    If you have a temp tablespace with a _larger_ page size, it will be used.

    In other words, you don't _have_ to have a matching temp tablespace, especially if you have one with a larger page size, but if you don't, at some point you _might need_ one.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2010
    Posts
    67
    Thank you n_i ,

    in other word db2 use 4k temp tablespace if could not find 8k temp tablespace.

    is there any performance benefit if we create 8k temp tablespace ?

    one question:

    we are using db2 v7 and npage in syscat.bufferpool for 8k bufferpool shows me -1 (as know in db2 v7 we do not have automatic bufferpools) what does it mean? and how can I find the size of 8k bufferpool.

    Thank you in advance for your help.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mars View Post
    in other word db2 use 4k temp tablespace if could not find 8k temp tablespace.
    Notice how I said "If you have a temp tablespace with a _larger_ page size, it will be used"? It was for a reason.

    Quote Originally Posted by mars View Post
    is there any performance benefit if we create 8k temp tablespace ?
    There may be, or there may be not.

    Quote Originally Posted by mars View Post

    we are using db2 v7 and npage in syscat.bufferpool for 8k bufferpool shows me -1 what does it mean?
    If I remember correctly, the BUFFPAGE database configuration parameter will determine the size in this case. You may want to RTFM.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    * TALK * TALK * TALK *
    Why? For each pagesize you use you should have user & work tempspace. Even if you do not have 32k tablespaces you should have 32k tempspaces. A join can create the need for db2 for such an area.
    The topic starter is just lucky that an error has not yet occurred.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by dr_te_z View Post
    * TALK * TALK * TALK *
    Why?
    That sounds more unfriendly than I meant. My appologies
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

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