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

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.

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.

Likely, db2 is storing the information in the SQLSPCS file (file in which the tablespace information for list tablespaces is obtained)

Cheers
Sathyaram