I want to store small images (JPEG, PNG, GIF about 200 X 140 pixels) in a DB2 table but as I still am a bit new to DB2 am I not quite sure if they should be stored as BLOB or VARGRAPHIC. One of these two is at least what looks most appropriate according to what I have found about the DB2 datatypes. A quick advice on this from any of you experts on this would be appreciated, thanks!

GRAPHIC and VARGRAPHIC are data types for strings that encode characters in UTF-16. Since you have binary data and not character data, you don't want to use those types.

The question is rather whether to use BLOB or VARCHAR FOR BIT DATA. And here the answer depends on the size of the images. If they are really small, you could use VARCHAR FOR BIT DATA. The benefit is then that all images are stored like regular data together with the rest of the row and go through the buffer pool. With BLOBs, DB2 will store internally a LOB locator in the row itself and has to access the BLOB separately. The latter case is going to take longer, but it may reduce the average row size and, thus, more rows may fit on a page and data access that doesn't need the BLOBs may be better because less disk I/O is needed.

Another question is which DB2 version on which platform you are running. There are options to use "inlining" for (the first part) of BLOBs, which gives you the same benefits as VARCHAR FOR BIT DATA while keeping the flexibility of storing longer BLOBs = larger images.

Thanks for a good and instructive answer! I should of course have mentioned which DB2 version I use, it is 9.7 on Win XP, but it is still mostly for exercising to get more used to DB2 so performance is not that required in this case, but it is still good to know about the considerations you describe until a situation when performance actually may be a matter. According to your answer seems the best choice for me to be VARCHAR as my images are small, about 50 kB for an image.

Not so, because a VARCHAR FOR BIT DATA can only be 32K long.

Oups! I haven't tried it yet but that information saves me some potential problem

Thanks!

Also note that "FOR BIT DATA" is important. Otherwise, DB2 would try to do code page conversion (if necessary) on regular VARCHAR data.