In DB2, the file system caching is turned on by default for SYSTEM or USER TEMPORARY table spaces but turned off other DMS.
If you have LOBS (CLOB, BLOB or XML) in a table, try to follow these guidelines:
- Put LOBS in a separate table space. Use ADMIN_MOVE_TABLE to do this change online (no outage).
- Create LOBS table space with FILE SYSTEM CACHING turned on –> Advantage is : LOBS are not in bufferpool so that they can be cached at the OS level.
- Try to do LOBS inline as much as possible. Create table in page size of 32K and max out LOBS inline to fill the whole page so that inlined LOBS are in buffer pool.
Your performance mileage may vary depending upon how LOBS are used.