Results 1 to 7 of 7

Thread: DB2 Blob

  1. #1
    Join Date
    Nov 2008
    Location
    Delhi, India
    Posts
    15

    Unanswered: DB2 Blob

    Hi,

    Is there any way to know whether the blob field is empty or it have some data in it?

    I have to create a join, to find out all rows that have blob field is empty/nothing stored in it

    Thanks,

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Something "like":

    select length(cast blob-col-name as VARCHAR(nnnn)) from table-name.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2008
    Location
    Delhi, India
    Posts
    15
    Quote Originally Posted by Marcus_A View Post
    Something "like":

    select length(cast blob-col-name as VARCHAR(nnnn)) from table-name.

    i tried
    select length(cast(CT as VARCHAR(200))) from Content
    and getting multiple rows with one column having '200' as value

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I said "something like." I expected you to figure out how to adapt it to your own needs, such as:

    select * from Content WHERE length(cast(CT as VARCHAR(200))) = 0
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The question is what "no data" really means. An empty BLOB could be interpreted like an empty string - it is well-defined information, which is different from NULL. So if "no data" means "is there a value or is it NULL", you would use a predicate "blob-column IS [NOT] NULL". If you are interested in BLOBs that have a size of 0, simply use the LENGTH function as in "LENGTH(blob-column) = 0".

    p.s: You cannot cast a BLOB to a VARCHAR. One is binary data, the other is string data (with code page, yada yada...).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze View Post
    p.s: You cannot cast a BLOB to a VARCHAR. One is binary data, the other is string data (with code page, yada yada...).
    According to the OP he was able to do it, if understand his post above correctly. I haven't tried it myself.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Yes, to find the BLOB without anything stored in it is considered as NULL ( as you correctly mentioned above). To find out the length of the particular Blob field in each row, it shows the length of the string which is considered the link of the location of the Blob image. For finding the actual size of each BLOB image, you can add an additional field in your table which will store the image size during the time of insert only with the help of the API / application loading the blob files.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

Posting Permissions

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