Using BINARY data type in DB2 table

Abhishek Saurabh · Posted: Wed Aug 25, 2010 11:40 am

Hi.... I have declared a DB2 table with a column of data type BINARY (DB2 v 9.1). But I am not sure how to insert data into that. Can someone please explain, preferably, with an example.

arvind.m · Posted: Wed Aug 25, 2010 4:29 pm

Abhishek Saurabh · Posted: Wed Aug 25, 2010 4:41 pm

Are you asking about column data type?? It is BINARY. Actually, I read that in version 9.1 a new data type BINARY has been introduced to store binary string data. Hence, I wanted to know how does it work.

dick scherrer · Posted: Wed Aug 25, 2010 7:39 pm

Hello,

Where did you read this?

At the top of the page is a link to "IBM Manuals" among which are DB2 manuals. Look in the DB2 V9 documentation and if you find something that is not clear, post what you found and your doubt. Someone will be able to clarify.

The tubelight/flashlight near the top left is the search function for the manual.

Abhishek Saurabh · Posted: Thu Aug 26, 2010 3:48 pm

Hi, I am copying below an extract from the IBM Manual Title: DB2 V9.1 for z/OS SQL Reference, Document Number: SC18-9854-00. My question is about this BINARY data type mentioned here.

| 2.10.5 Binary strings

| A binary string is a sequence of bytes. The length of a binary string is
| the number of bytes in the sequence. Binary strings are not associated
| with any CCSID. There are three binary string data types: BINARY,
| VARBINARY (BINARY VARYING) and BLOB (BINARY LARGE OBJECT).

| 2.10.5.1 Fixed-length binary strings

| The type of fixed-length binary strings is BINARY. When fixed-length
| binary string distinct types, columns, and variables are defined, the
| length attribute is specified, and all values have the same length. For a
| fixed-length binary string, the length attribute must be between 1 and 255
| inclusive.

dbzTHEdinosauer · Posted: Thu Aug 26, 2010 4:21 pm

the different binary string data types all use Pic x(n) for the host variable.

what problems are you having? what does your sql look like?

Abhishek Saurabh · Posted: Thu Aug 26, 2010 6:09 pm

Well, I haven't written a program yet for this. I have just created a table with following three columns:

COL1...........NUMERIC(6)
COL2...........CHAR(5)
COL3...........BINARY(100)

I wrote the following INSERT statement, which did execute successfully.
INSERT INTO T2 VALUES(100001,'ABHI1',BLOB(X'3334'));

However, I am not sure what exactly is meant by this value BLOB(X'3334'). I came across a similar statement while searcing on internet & hence tried out this. I have not worked with BLOB, which has been present as a data type even in the versions before DB2 9.1.

So, this what I have done till now. Would need some help to proceed further.

dbzTHEdinosauer · Posted: Thu Aug 26, 2010 7:01 pm

here is a link to the
V9.1 Application and programmers guide
This is the SQL Reference

BLOB Returns a BLOB representation of its argument
why not use
BINARY Returns a fixed-length binary string representation of its argument

what are you going to store in the BINARY column?
what application are you programming?

Abhishek Saurabh · Posted: Thu Aug 26, 2010 7:26 pm

As per my knowledge, BINARY column can be used to store non-character data like compressed data, pictures, voices, etc.
My intention was to store a compressed/zipped file in the BINARY column. However, I was trying to store a picture also. In fact I copied a picture to a PS using FTP with Binary option. But how to put that binary data available in the PS into the column is not known to me.
One more doubt which I have is regarding the way in which the data gets stored in BINARY column, i.e. as I have declared a column with BINARY(100), so how much data can I store. If the data being inserted is larger than this what happens.