Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Location
    Sweden
    Posts
    11

    Unanswered: Store images as BLOB or VARGRAPHIC?

    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!

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2009
    Location
    Sweden
    Posts
    11
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by The developer View Post
    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.

  5. #5
    Join Date
    Aug 2009
    Location
    Sweden
    Posts
    11
    Oups! I haven't tried it yet but that information saves me some potential problem

    Thanks!

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Also note that "FOR BIT DATA" is important. Otherwise, DB2 would try to do code page conversion (if necessary) on regular VARCHAR data.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •