Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003

    Question Unanswered: EXTENT and PREFETCH

    Hello gurus' Looking forward to increase my knowledge on the db2 ...

    1. what is an EXTENT ?
    2. how much EXTENT size is requrd for a table and view to create ?
    3. wht is prefetch ?
    4. how prefetch plays a important role for improving DB2 preformance ?
    5. how to use prefetch ?

    hopefully I will get better theoritical and technical knowledge from you all.

    best regards


  2. #2
    Join Date
    Aug 2001


    A recent article on High Performance talks about all performance topics ... You can find it at :

    'Copy-pasted' from the article

    Extent size
    The Extent Size specifies the number of PAGESIZE pages that will be written to a container before skipping to the next container and is defined at table space creation time (and cannot be easily modified after). Smaller tables are handled more efficiently with smaller extents.

    Rule of thumb is based on the average size of a table in the table space:

    Less than 25 MB, use an Extent Size of 8
    Between 25 and 250 MB, use an Extent Size of 16
    Between 250 MB and 2 GB, use an Extent Size of 32
    Greater than 2 GB, use an Extent Size of 64
    Use larger values for OLAP databases and tables that are mostly scanned (query only) or have a very high growth rate.

    If the table space resides on a disk array, set the extent size to the stripe size (that is, data written to one disk of the array).

    Prefetch size
    Prefetch size can be changed easily using ALTER TABLESPACE. The optimal setting seems to be: Prefetch Size = (# Containers of the table space on different physical disks) * Extent Size

    If the table space resides on a disk array, set it as: PREFETCH SIZE = EXTENT SIZE * (# of non-parity disks in array).

    DB2 v8 Documentation:

    Concepts ==> Administration ==> Database design ==> Physical ==> Table Space Design
    Reference ==> SQL ==> SQL Statements ==> CREATE TABLESPACE
    Reference ==> SQL ==> SQL Statements ==> ALTER TABLESPACE

    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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