Getting binary LOBs from DB2 via the command line

Sometimes you just want to extract a single BLOB from a DB2 database... you don't want to write any software... you don't want to pull out some scripting language with a true DB2 database driver binding... you just want to get a BLOB from the database, and write it to a file - using nothing but the command line.

Well, there are two ways, that I know of, to do so. The first one, is the officially "correct" method, and should always work. The second method is even easier, but only works for very small BLOBs (which is all you need sometimes).

So, first up, the official way to get a BLOB out of DB2 via the command line, is by using the db2 EXPORT command, like this:

db2 "EXPORT TO tmp.del OF DEL LOBS TO . LOBFILE lob SELECT blob_col FROM table WHERE id = 1"

This command will output a comma-delimited Delimited ASCII (DEL) file called tmp.del that the db2 IMPORT command can use to import data. This tmp.del file is plaintext, so feel free to look inside it. In my case, it simply contains:

"lob.001.lob.0.83968/"

Which indicates that the BLOB I asked for, has been save to the file lob.001.lob. And that's it - I now have the BLOB I was after.

Now for a simple single-BLOB extraction like this you don't really need the DEL file at all, so I would normally send the DEL file to /dev/null (only for *nix systems, of course) via the command:

db2 "EXPORT TO /dev/null OF DEL LOBS TO . LOBFILE lob SELECT blob_col FROM table WHERE id = 1"

Then just look for the resulting lob.001.lob file.

Just an aside note: you may have been wondering about the two numbers at the end of the line in the DEL file... ie 0.83968 - this is actually the offset, and length of the BLOB within the lob.001.lob file. In this case, the single BLOB starts at position 0 (ie, the start of the file), and is little over 80KB in size. In the case of a single BLOB like this, that's kind of not important. But, you could use EXPORT to export multiple rows, in which case, the BLOBs will all be appended within the lob.001.lob file, and the db2 IMPORT command would use those numbers to work out where each BLOB begins and ends.

Now, there is a (sometimes) quicker and much dirtier way to get BLOBs from DB2 using just the command line... but this approach only works for very small BLOBs - ie less than approximately 16KB in size. I'm not suggesting that this approach is sensible, nor am I recommending it... I'm just presenting it here for interest sake. Anyway, the other approach looks like this:

db2 -x "SELECT col FROM table WHERE id = ID" |
  sed -n -e "s/^x'\(.*\)'$/\1/p" | tr -d '\n' |
  xxd -r -p > output.bin

That command works as follows: the db2 command retrieves the BLOB from the database and presents it in hexadecimal form like: x'D0CF11'. The sed command then extracts just the hexadecimal data. And finally, the xxd command converts the hexadecimal data to binary.

If you're quite familiar with sed, then you may realise that the -n parameter, and p script suffix are not strictly necessary... but then they are both needed if you forget the -x parameter to the db2 command (I often do), which causes db2 to include / exclude the column headers in its output.

Also, the reason this only works for small BLOBs is that the db2 command line processor has an upper limit on the width of rows that it returns. That limit is approximately 32K text characters, which, given the two-characters-per-byte hexadecimal BLOB representation, yields a maximum BLOB size of around 16KB... not enough for many things, but easily enough, for example, to check the magic bytes at the start of a binary file ;)