DB2 BLOB data type

SQL samples of INSERT/SELECT DB2 BLOB data type.

db2 => drop table <TABLE>

db2 => create table <TABLE>(a varchar(4), b varchar(4) for bit data, c blob(4))


db2 => describe select * from <TABLE>

 Column Information

 Number of columns: 3

                                                                    Name                Data type  Schema                      Name
 SQL type              Type length  Column name                     length  Lob length  schema     length  Data type name      length
 --------------------  -----------  ------------------------------  ------  ----------  ---------  ------  ------------------  --------
 449   VARCHAR                   4  A                                    1           0  SYSIBM          6  VARCHAR                    7
 449   VARCHAR                   4  B                                    1           0  SYSIBM          6  VARCHAR                    7
 405   BLOB                      0  C                                    1           4  SYSIBM          6  BLOB                       4

INSERT

insert into <TABLE> values('1234', x'F1f2F3f4', blob(x'F1f2F3f4'))
insert into <TABLE> values('2345', x'F2ffF4f5', blob('2345'))

Insert 2 row data, all fields values are character '1234', and '2345'.

SELECT

db2 => select a,b,c from <TABLE>

A    B           C          
---- ----------- -----------
1234 x'F1F2F3F4' x'F1F2F3F4'
2345 x'F2FFF4F5' x'F2F3F4F5'

                         
db2 => select hex(a), hex(b), hex(c) from <TABLE>

                          
-------- -------- --------
F1F2F3F4 F1F2F3F4 F1F2F3F4
F2F3F4F5 F2FFF4F5 F2F3F4F5


db2 => select a, varchar(b) from <TABLE>

A               
---- -----------
1234 x'F1F2F3F4'
2345 x'F2FFF4F5'

  2 record(s) selected.

But another SELECT

db2 => select varchar(c) from <TABLE>
SQL0171N  The data type, length or value of the argument for the parameter in
position "1" of routine "VARCHAR" is incorrect. Parameter name: "".  
SQLSTATE=42815

See, the BLOB could not be converted to CHAR/VARCHAR, and there is no way i could find to convert a BLOB column into characters type.

The only way to show a BLOB is using hex(...) function converting into hexadecimal characters. (Note, the hex(...) function could only convert 16K length of a BLOB)