Hi,

I'm a newbie for DB2. Could you please suggest about the data type that is proper to keep the file in table?

Thank you in advance

Need to know the contents of the file or file types.

I've 2 kinds of data.
1. just text file
2. pdf file

1. CLOB
2. BLOB

If the data was less than about 32K, you could use the following for better performance, but you will need a 32K bufferpool, tablespace, and system temporary tables:

1. VARCHAR
2. VARCHAR FOR BIT DATA

Thank you very much.

Hi,

I've estimated the file and found that we the file size will be around 200 - 300 kb per row so based on your suggestion I'd better used blob right?

How about the performance for this file size?

Thank you in advance.

You would use BLOB for binary data (including a PDF), and CLOB for text data. Performance will not as good as regular data since LOB's do not use DB2 bufferpools (memory caching of DB2 tables) and will require synchronous disk I/O required for read and writes. But it may not be that bad depending on your requirements.

Best option is to define a separate 32K tablespace for LOB's and then use it the Create Table command ("LONG IN TS_XXXX" -- see Create Table command in manual), and make sure that file caching is turned on for that tablespace where the LOB's reside. If the LOB will not ever (or only rarely) be updated after it is inserted (deleted is a separate issue), then use the COMPACT keyword when defining the LOB column.

If you have a bunch of small files (smaller than 32K), you can wrap the BLOB inside a structured type and use a large INLINE LENGTH for that type. This will allow you to exploit the buffer pool for short BLOBs (< 32K) while everything longer is treated as a regular BLOB with disk I/O.