LOB data is not cached in buffer pools. When a LOB is retrieved from the database, the data is obtained directly from the storage device. DB2 refers this operation as direct read. On the other hand when a LOB is written to the database, DB2 performs a direct write operation.

Select the three actions on this page in order to illustrate direct read and write activities happening with LOB data.

Collect direct read and write statistics

The first step collects direct read and write statistics before LOB data is retrieved.

SELECT DIRECT_READS, DIRECT_WRITES
  FROM SYSIBMADM.SNAPDB

SYSIBMADM.SNAPDB is an adminstrative view to retrieve snapshot information of the database. There are a number of different snapshot monitor SQL administrative views available. Each returning monitor data about a specific area of the database system.

The administrative view used above contains database level information and counters for the currently connected database.

Retrieve LOB data

The second step executes a SQL query to retrieve LOB data in the VIEW_TEXT and COLUMN_TEXT columns.

SELECT VIEW_TEXT, COLUMN_TEXT
  FROM LOBDATA

Compare direct read and write statistics

The third step compares the direct read and write statistics before and after the execution of the query. As expected, here is no direct write activities for LOB retrieval. Please note that there is a 104 direct reads overhead generated by the Technology Explorer. If the same SQL statements run on the DB2 command, you will not see the overhead.