Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    36

    Question Unanswered: How to fetch PREFECTHSIZE AUTOMATIC value from the database?

    In DB2 UDB V8.2 the PREFETCHSIZE clause of the CREATE TABLESPACE command accepts the AUTOMATIC value which is calculated using the formula:

    Prefetch size = (number of containers) * (number of physical disks per container) * (extent size)

    The value in the SYSIBM.SYSTABLESPACES shows a value of -1 for the PREFECHSIZE. However on executing the command LIST TABLESPACES SHOW DETAIL the correct value of the PREFETCHSIZE is displayed.

    Can anyone tell me as to how and from where does DB2 get the correct value?

    Regards,
    ritz

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    db2 get db cfg for db-name

    Look at the dft_prefetch_sz

    Once it has been created, the tablespace prefetch size does not change when you change the db cfg parm.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2005
    Posts
    36
    Hi,

    Consider the following scenario for some tablespace TBS_TEST:

    EXTENTSIZE = 16
    PREFETCHSIZE = AUTOMATIC (DB cfg parmater DFT_PREFECTH_SIZE = AUTOMATIC)

    Now when I query the catalog tables for the PREFETCHSIZE I get a value of -1.

    On using the LIST TABLESPACES SHOW DETAIL command the value for PREFETCHSIZE is shown as 16, considering the no. of containers and the physical disks per container to be 1.

    I wanted to know that how does DB2 get the correct value for the PREFETCHSIZE using the LIST TABLESPACES command, whereas the catalog tables show -1 in them.

    Hope this explains the problem. Let me know if other details are needed.

    Regards,
    ritz.
    Last edited by ritz; 01-06-05 at 05:15.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Likely, db2 is storing the information in the SQLSPCS file (file in which the tablespace information for list tablespaces is obtained)

    Cheers
    Sathyaram

    Quote Originally Posted by ritz
    Hi,

    Consider the following scenario for some tablespace TBS_TEST:

    EXTENTSIZE = 16
    PREFETCHSIZE = AUTOMATIC (DB cfg parmater DFT_PREFECTH_SIZE = AUTOMATIC)

    Now when I query the catalog tables for the PREFETCHSIZE I get a value of -1.

    On using the LIST TABLESPACES SHOW DETAIL command the value for PREFETCHSIZE is shown as 16, considering the no. of containers and the physical disks per container to be 1.

    I wanted to know that how does DB2 get the correct value for the PREFETCHSIZE using the LIST TABLESPACES command, whereas the catalog tables show -1 in them.

    Hope this explains the problem. Let me know if other details are needed.

    Regards,
    ritz.
    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
  •