Tablespace Prefetchsize

kumaran rajendran

Tablespace Prefetchsize

Hi All,

Just wanted to share issue face in db2 V 9.7 fix pack 4, SAP PI system. 

Query on table with size of 2 T of LOB object . The query status always show UOW executing, but the Application is hung nothing processing, other queries in DB is working, even same query from db2 client to server is working when we run db2 command line. 


IBM informed its hitting APAR when tablespace with LOB object  reaches size more than 2 T  and gave a work around to disable DB2_PARALLEL I/O for the tablespace which didnt work and last option they informed to reduce the tablespace prefetchsize from 10 to 2 which worked. 


Was looking any similar issues faced and how significant role of tablespace perfetchsize matters. 


Another thing was noticed even though NUM_I/O was set to automatic it was not using more than 5 perfetch agents