db2 blob import

We need to store some photos, so this is how we will try to do it.

  • Create a table
  • get photos
  • create a load script

Create Table

To start with I will use this

drop table OW_PHOTO.PHOTO;
drop schema OW_PHOTO RESTRICT;
create schema OW_PHOTO;
create table OW_PHOTO.PHOTO (ID int not NULL , IMAGE blob(2M) NOT LOGGED COMPACT, primary key (ID
));

You will want to add a checksum, and date created - this makes adding photo's easier (you will need a different key then).

Get Photos

I am pulled in 3 photo's from the web - going for a puppy dog theme here

dog1dog1dog1

Create Load script

#!/bin/bash

cnt=0
rm import.txt
for f in $(ls *.jpg)
do
   let cnt=$cnt+1
   echo "insert into OW_PHOTO.PHOTO values ($cnt,blob('$f'));" >> import.txt
done


echo "Please now run db2 -tvf import.txt"

This creates a file called import.txt which looks like...

insert into OW_PHOTO.PHOTO values (1,blob('dog1.jpg'));
insert into OW_PHOTO.PHOTO values (2,blob('dog2.jpg'));
insert into OW_PHOTO.PHOTO values (3,blob('dog3.jpg'));

Loading

As the script says "Please now run db2 -tvf import.txt"

So

db2 -tvf import.txt

Output looks like this

insert into OW_PHOTO.PHOTO values (1,blob('dog1.jpg'))
DB20000I  The SQL command completed successfully.

insert into OW_PHOTO.PHOTO values (2,blob('dog2.jpg'))
DB20000I  The SQL command completed successfully.

insert into OW_PHOTO.PHOTO values (3,blob('dog3.jpg'))
DB20000I  The SQL command completed successfully.