How to check the LOB column I/O performance

Body

Have you ever been curious about the way to verify if a LOB column I/O with a table uses file system cache or NOT ?

There are common two perception points regarding LOB.

1. LOB uses file system cache. That means it does NOT utilise the buffer pool.

2. By setting with inline LOB, it uses buffer pool..

Above things may or may not be true.

For example, one of my friend had the following doubt.

"Does LOB I/O utilize O/S File system caching even though tablespace definition for LOB has 'NO FILESYSTEM CACHE'." ?
The reason behind this questions is tablespace is defined with 'NO FILESYSTEM CACHE" by default.

Regarding this, DB2 manual page says like below.

"If large object (LOB) data is inlined, then it is accessed as regular data and uses the I/O method (buffered or non-buffered) specified for
the table space FILE SYSTEM CACHING attribute.

If large object (LOB) data is not inlined, then the following statements apply:
For SMS table spaces, non-buffered I/O access is not requested for long
field (LF) data and large object (LOB) data even when the NO FILE
SYSTEM CACHING table space attribute is set. Buffering occurs in the
file system cache, subject to operating system configuration and
behavior, and potentially improves performance.

For DMS table spaces, DB2 does not distinguish between different data
types when performing I/O. Buffering of LF or LOB data does not occur
unless the table space is configured with FILE SYSTEM CACHING enabled.
If buffering of LF or LOB data in DMS tables spaces is wanted for
performance reasons, then you can place this data in a separate DMS
table space and explicitly enable FILE SYSTEM CACHING."

Therefore  we need to check the tablespace definition related to the LOB column and table. 
For example, if corresponding tablespace is DMS or defined as 'MANAGED BY AUTOMATIC STORAGE",
we should turn on 'FILE SYSTEM CACHING' to utilize O/S file system cache.

Then how to check the performance of LOB I/O. 
The answer is simple. Just monitor and calculate from the following value from DB2 tablespace snapshots taking delta value before and after the related SQL query.
LOB I/O is reflected by the Direct reads/writes. ( NOTE : Direct reads/writes includes other I/O behaviour as well such as backup. )

-  Direct reads
-  Direct writes
-  Direct reads elapsed time (ms)
-  Direct write elapsed time (ms)

Finally, let me share simple test result on my test AIX box.
( It's just for reference and NOT official result. The result can vary depending on system resource.)

-------------------------< Test Result > -------------------------

1. Created tablespaces 

...
CREATE LARGE TABLESPACE "TBS_LOB"
~MANAGED BY AUTOMATIC STORAGE
~NO FILE SYSTEM CACHING ~.
..

2. Create table

CREATE TABLE "LOBTEST"."ARTISTS"  (
                  "ARTNO" SMALLINT NOT NULL ,
                  "NAME" VARCHAR(50) ,
                  "CLASSIFICATION" CHAR(1) NOT NULL ,
                  "BIO" CLOB(102400) LOGGED NOT COMPACT ,
                  "PICTURE" BLOB(512000) LOGGED NOT COMPACT )
                 IN "TBS_TABLE" INDEX IN "TBS_INDEX" LONG IN "TBS_LOB" ;

3. Load table

load from lob_lnx of del
     lobs from /home2/db2ins54/LOB/lobs
     modified by lobsinfile
     method p(1,2,3,4,5)
     messages load_lob.txt
     insert
     into lobtest.artists
    (artno
        ,name
        ,classification
        ,bio
        ,picture);

<Test steps>

$ db2stop force;db2start;db2 activate db jsdb
$ db2 connect to jsdb
$ cat select.db2
reset monitor for DB JSDB    ;
select * from lobtest.artists;

1st
$ db2 -tvf select.db2
$ db2 get snapshot for tablespaces on jsdb |egrep 'Buffer pool data
logical reads|Direct reads'

2nd
$ db2 -tvf select.db2
$ db2 get snapshot for tablespaces on jsdb |egrep 'Buffer pool data
logical reads|Direct reads'

=> repeat 5 times
 

<Result>
NO FILE SYSTEM CACHE

- Not using File System CACHE.
  As seen below, it always takes the similar time. (0.04 ms per LOB I/O)
After first DB recycle

Attempt | Direct reads | Direct reads elapsed time(ms) | Direct reads time(ms) / read
1 888 36  0.0405
2 888 42  0.0473
3 888 41  0.0462
4 888 40  0.0450
5 888 40  0.0450

After 2nd DB2 recycle again

1 888 36  0.0405
2 888 37  0.0417
3 888 37  0.0417
4 888 40  0.0450
5 888 39  0.0439

FILE SYSTEM CACHING
( $ db2 alter tablespace TBS_LOB FILE SYSTEM CACHING )

- Using FILE SYSTEM CACHE.
   First I/O took 0.03 ms. ( The file is cached now. )
   And the following I/Os take very short time. (Almost 0.00 ms).
   

After first DB recycle

Attempt | Direct reads | Direct reads elapsed time(ms) | Direct reads time(ms) / read
time(ms) / read
1 888 30  0.0338
2 888 0  0.0000
3 888 0  0.0000
4 888 0  0.0000
5 888 1  0.0011

After 2nd DB2 recycle again

( Still fast as the file is on FILE SYSETM CACHE)
1 888 0  0.0000
2 888 0  0.0000
3 888 0  0.0000
4 888 0  0.0000
5 888 0  0.0000

-------------------------< End of the test Result > -------------------------