Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007

    Unanswered: Deciding tablespace page size

    dear all,

    i need advise from you all..

    while create a table, how do we decide whether we can place the table in 4k or 8k or 32k tablespace.

    is there any calculation involved to decide.

    pls advise.



  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    You determine the maximum possible width of a row in the table, add some more bytes to accommodate for future addition of more columns, and then you pick the tablespace size that can contain such rows. The alternative is that you just let DB2 decide which tablespace to pick. Have a look at the documentation for the CREATE TABLE statement, where the algorithm for choosing a tablespace is explained.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Unless you row size is the limiting factor, you would normally use smaller page sizes for OLTP, and larger page sizes for data warehouse applications.

    There are two considerations:

    1. Memory usage (bufferpools) - If you just need to access one row (or a few rows) for a query that will be retrieved via an index, the entire page must be placed in the bufferpool memory. The smaller the page size, the less memory that is used. This is not a factor if the entire database can fit into your bufferpools and stay resident in memory, otherwise, if you need to use memory to retreive a page, DB2 must kick some other pages out of memory to make room for the new page.

    2. Speed of table scans - When you have tables that require a table scan to return the result of a query or to process an update, etc, then DB2 can usually process a table scan faster with a larger tablespace page size. Make sure the prefetch size is set appropriately.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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