Storing text files in DB2 column

[MattyB]

Hello all,

What is the best approach to store small text files (< 50K) in a DB2 column? VARCHAR for BIT data, CLOB, ...?

Thanks,

-Matt

[Mark A]

Post by MattyB
Hello all,
What is the best approach to store small text files (< 50K) in a DB2
column? VARCHAR for BIT data, CLOB, ...?
Thanks,
-Matt

If you can limit it to slightly less than 32K then a VARCHAR would be the
best in terms of performance in most cases as opposed to a CLOB. That is
because a CLOB do not make use of DB2 bufferpools and uses synchronous I/O
to disk for reads and writes (although file system caching obviously helps
somewhat).

VARCHAR for bit data would normally be used if you needed binary data as an
alternative to a BLOB, but I assume that regular VARCHAR or CLOB would work
for your text files.

[Knut Stolze]

Post by Mark A

Post by MattyB
Hello all,
What is the best approach to store small text files (< 50K) in a DB2
column? VARCHAR for BIT data, CLOB, ...?

If you can limit it to slightly less than 32K then a VARCHAR would be the
best in terms of performance in most cases as opposed to a CLOB. That is
because a CLOB do not make use of DB2 bufferpools and uses synchronous I/O
to disk for reads and writes (although file system caching obviously helps
somewhat).

A work-around for that is to wrap the CLOB inside a structured type and set
an appropriate inline length. For small CLOBs, you get the same benefits
as VARCHAR while not loosing the option to store larger data like LOBs
(with the synchronous I/O penalty). The disadvantage is that you have a
bit more complicated interface.