Db2 – IBM DB2 – store large files

blobdb2db2-luw

I want to store large files in a DB2 database and I need more information about which is the best technique to use as I am new in this IBM product (previous experience with T-SQL only).

The situation:

  1. I am using DB2 C-express 10.1 (I do not know how to use the compression feature but sure that this distribution has not got it – this is not problem I can change the distribution if it may help me.

  2. OS – windows 7

  3. Data files types – ms word/pdf/ppt ( I think they will be store as
    binary objects)

So, could some one advice for best technique which I can use (talking about both security and performance)?

I suppose I am looking for something like MS File-Stream Enable Database with BLOB objects.

Best Answer

DB2 shouldn't have issues at all with storage of large files. Some of how you store them will depend on what exactly you are storing. Are you storing XML files? Large text files? Word documents or PDFs? Video? Audio?

You have several options available to you. Graphic strings, binary strings, your large objects (CLOB, BLOB, and DBCLOB), and just plain XML.

If you are using just character data I would side with a CLOB. But check your sizes, if you are consistently lower than 32K it might just be better to use a VARCHAR. (Reason being is that large objects in DB2 are stored outside the table by default (unless you use the INLINE option, but even then it only works to certain sizes of LOBs), and they are also not cached in bufferpools. DB2 always retreives LOB's straight from disk (unless they are INLINE'd as mentioned above). If you are storing XML documents, you might want to look into the XML type as DB2 has a more optimal way to store and retrieve XML. You can set up special XML indexes on XML types too. And you can query the XML using SQL/XML or XQuery using XPath.

If you are storing binary objects, then I'd go with BLOB.

I think you may be right about the compression piece. (Although it wouldn't hurt to test it. There are about four or five different compression levels in DB2 and I know I can do at least backup compression with 9.7 Express-C).

Here is a 10.1 Edition comparison chart. And here is a article on the different editions and what they all offer.

Also take a look at space requirements for tables to help you better pick what you need.

Related Question