Comparison between RAW and VARCHAR FOR
BIT DATA
In Oracle , RAW (255) means , 255 Bytes have been allocated
. Which internally means that 510 hex values can be stored ( As one hex value
takes 4 bits).
Similarly in DB2 , VARCHAR(255) FOR BIT DATA , means max 255
Bytes will be allocated .
However, there are some difference in the behavior of how
BIT Data is handled in DB2 when an insert statement is executed.
While insertion , Oracle treats the input as HEX, the input
can be in the range of [0-9],[A-F]). Therefore, a string of length up to 510
can be inserted in Oracle.
On the contrary, DB2 treats the input string as a character
string and due to which a string of size more than 255 is not accepted.
DB2 takes the string as character and stores it in hexadecimal
format.
Resolution : To
make DB2 understand that the input string is a hex string , we need to use
HEXTORAW function which will make the string to be stored as HEX.
Please refer following examples that compare Oracle and DB2
for the same inputs:
Example 1 :
This example will show the difference in how DB2 and Orcale
deals with it.
|
Oracle
|
DB2
|
Conclusion
|
|
create table tab1 (c1 raw(255));
Table created.
SQL> insert into tab1 values ('abc');
1 row created.
SQL> select * from tab1
2 ;
C1
--------------------------------------------------------------------------------
0ABC
SQL> insert into tab1 values ('abxv');
insert into tab1 values ('abxv')
*
ERROR at line 1:
ORA-01465: invalid hex number
|
db2 create table tabv (c1 varchar(255) for bit data)
DB20000I The SQL
command completed successfully.
db2 insert into tabv values ('abc')
DB20000I The SQL
command completed successfully.
db2 select * from tabv
C1
---------------------------------
x'616263'
1 record(s)
selected.
db2 insert into tabv values ('abxv')
DB20000I The SQL
command completed successfully.
db2 select * from tabv
C1
---------------------------------
x'616263'
x'61627876'
2 record(s)
selected.
|
DB2 takes the input as char(If it is supplied without any
HEXTORAW function or x’) and converts it into HEX values and store it in the
column. So varchar(255) will allow upto 255 characters .
Oracle treats the values as hex only .
So there RAW(255) means , it has been allotted 255 bytes
to store hex values . And each hex value contains 4 bits only . So we can
store upto 510 hex values.
|
Note 1 : In oracle the insertion of value ‘x/v’ failed
with the message : Invalid hex number. But DB2 accepts it , because for DB2 ,
it is a character , whose hex value will be stored in column.
Note 2 :
To make DB2 treat it as hex , we’ll have to specify it with HEXTORAW or x’ like
following :
db2 insert into tabv values (HEXTORAW('abxv'))
SQL0171N The data
type, length or value of the argument for the parameter in
position "1" of routine
"SYSIBM.VARCHAR_BIT_FORMAT" is incorrect. Parameter
name: "character-expression". SQLSTATE=42815
In the above example,
the insertion of ‘xv’ failed because DB2 is treating it as HEX value and
reporting invalid hex value.
db2 "insert into tabv values (x'abxv')"
SQL0105N The string
constant beginning with "x'abxv'" is not valid.
SQLSTATE=42604
db2 "insert into tabv values (x'abcc')"
DB20000I The SQL
command completed successfully.
db2 insert into tabv values (HEXTORAW('abcd'))
DB20000I The SQL
command completed successfully.
db2 select * from tabv
C1
---------------------------------
x'ABCC'
x'ABCD'
2 record(s)
selected.
Note 3 :
In Oracle , if the specified HEX string is of odd length , it automatically
puts 0 at the start to make it even to match the standards .
But in DB2 , the user has to be careful while specifying the
string . The string should be of even length as one hexadecimal digit
represents 4 bits .
db2 insert into tabv values (HEXTORAW('abc'))
SQL0171N The data
type, length or value of the argument for the parameter in
position "1" of routine
"SYSIBM.VARCHAR_BIT_FORMAT" is incorrect. Parameter
name: "character-expression". SQLSTATE=42815
Now , please refer
below examples for better understanding :
Example 2 :
|
Oracle
|
DB2
|
Conclusion
|
|
SQL> create
table tab2 (c1 raw(7));
Table created.
SQL> insert into tab2 values ('abcabcabc');
1 row created.
SQL> select * from tab2
2 ;
C1
--------------
0ABCABCABC
SQL> insert into
tab2 values ('abcdabcdabcdabcd');
insert into tab2
values ('abcdabcdabcdabcd')
*
ERROR at line 1:
ORA-12899: value too large for column
"CAMP"."TAB2"."C1" (actual: 8, maximum:
7)
SQL> insert into tab2 values ('abcdabcdabcdaba');
insert into tab2 values ('abcdabcdabcdaba')
*
ERROR at line 1:
ORA-12899: value too large for column
"CAMP"."TAB2"."C1" (actual: 8, maximum:
7)
SQL> insert into tab2 values ('abcdabcdabcdab');
1 row created.
|
db2 create table tabv1 (c1 varchar(7) for bit data)
DB20000I The SQL
command completed successfully.
db2 insert into tabv1 values ('abxv')
DB20000I The SQL
command completed successfully.
db2 select * from tabv1
C1
-----------------
x'61627876'
1 record(s)
selected.
db2 insert into tabv1 values ('abxvaaaa')
SQL0433N Value
"abxvaaaa" is too long.
SQLSTATE=22001
db2 insert
into tabv1 values
('abxvaaa')
DB20000I The SQL
command completed successfully.
|
In Oracle , whatever value you enter in a RAW column , it
just takes as even (If it is odd string , it adds 0 at the start).
In example , we had declared RAW(7) , and we had entered a
string of 16 values , then it fails reporting that “Actual length specified
is 8 and the limit is 7)
It accepts a string upto 14 hex values for RAW(7).
In DB2 , if it is declared as varchar(7) , it will take
only 7 characters , if specified without HEXTORAW or x’.
|
Example 3:
|
Oracle
|
DB2
|
Conclusion
|
|
SQL> insert into tab1 values (RAWTOHEX('abxv'));
1 row created.
SQL> select * from tab1
C1
----------------------------------
61627876
SQL> create table tab2 (c1 raw(7));
Table created.
SQL> insert into tab2 values (RAWTOHEX('abxvabxv'));
insert into tab2 values (RAWTOHEX('abxvabxv'))
*
ERROR at line 1:
ORA-12899: value too large for column
"CAMP"."TAB2"."C1" (actual: 8, maximum:
7)
|
db2 insert into tabv values ('abxv')
DB20000I The SQL
command completed successfully.
db2 select * from tabv
C1
---------------------------------
x'61627876'
1 record(s)
selected.
db2 create table tabv1 (c1 varchar(7) for bit data)
DB20000I The SQL
command completed successfully.
db2 insert into tabv1 values ('abxvaaaa')
SQL0433N Value
"abxvaaaa" is too long.
SQLSTATE=22001
|
If we want to transfer a varchar data to RAW table , whose
characters are actually RAW , so we can insert string of upto
RAW(<bytes>) only.
This is the actual length.
DB2 automatically does a RAWTOHEX conversion while
inserting input varchar string to column.
In oracle , either you give a hex input or give a varchar through
RAWTOHEX.
|
Example 4 :
Created a table with varchar(3) for bit data . Try inserting
with HEXTORAW(6 digits) value . It should succeed . Then enter with 7 digits ,
it should fail
|
Oracle
|
DB2
|
|
|
SQL> create table
tab1 (c1 raw(3),c2 int);
Table created.
SQL> insert into
tab1(c1) values (HEXTORAW('000FFF'));
1 row created.
SQL> select * from
tab1;
C1 C2
--------------- -------
000FFF -
|
db2 create table tabv2 (c1 varchar(3) for bit data)
DB20000I The SQL command
completed successfully.
db2 "insert into tabv2 values ('6162')"
SQL0433N Value
"6162" is too long.
SQLSTATE=22001
db2 "insert into tabv2 values
(HEXTORAW('6162'))"
DB20000I The SQL
command completed successfully.
db2 select * from tabv2
C1
---------
x'6162'
1 record(s)
selected.
db2 "insert into tabv2 values
(HEXTORAW('61626'))"
SQL0171N The data
type, length or value of the argument for the parameter in
position "1" of routine
"SYSIBM.VARCHAR_BIT_FORMAT" is incorrect. arameter
name: "character-expression". SQLSTATE=42815
db2 "insert into tabv2 values
(HEXTORAW('61626264'))"
SQL0433N Value
"abbd" is too long.
SQLSTATE=22001
|
|
The issue Barbara
faced while SEED DATA :
db2 "insert into dvk_sourcing_user.PERMISSION_GROUP
values ('00000000000000000000000000000000','Contract > Read','Access this
Contract in any manner and view header and party
properties.','Contract','2005-06-24 16:48:27.0','2005-06-24 16:48:27.0','d'
,'800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000','System','2005-06-24
16:48:27.0','a7137a035579
4e029f379d2894db85f5','2005-06-24 16:49:24.0','1','Read')
DB21034E The command
was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0433N Value
"800000000000000000000000000000000000000000000000000000000000"
is too long.
SQLSTATE=22001
Because this column is taking the hex value string as
Character String of size 510 , while its own size is 255.
So , if we specify HEXTORAW before insertion , it would work
fine .
Like below :
db2 "insert into dvk_sourcing_user.PERMISSION_GROUP
values ('00000000000000000000000000000
000','Contract > Read','Access this Contract in any
manner and view header and p
arty properties.','Contract','2005-06-24
16:48:27.0','2005-06-24 16:48:27.0','d'
,HEXTORAW('800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'),'System','2005-06-24
16:48:27.0','a7
137a0355794e029f379d2894db85f5','2005-06-24
16:49:24.0','1','Read')
DB20000I The SQL command completed successfully.
db2 "select PERMISSION_MASK from
dvk_sourcing_user.PERMISSION_GROUP"
PERMISSION_MASK
---------------------------------
x'800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
1 record(s)
selected.
A quick comment on Example 1...irony that you have pointed in bold red the error which is thrown by Oracle and the similar statement works well with DB2. But I want to throw some light into something else.
ReplyDeleteThe previous SELECT statement returns the original value in Oracle but it returns the ASCII value of 'abc' in DB2. Can you imagine that the use has to actually covert that ASCII into 'abc' using a funtion. I do not remember the exact function. So this is an extra overhead of a function call to get the actual value incase the value is used inside some SQL query. Imagine the performance impact it can have on a larger context.
Thanks for your comments !!
DeletePlease find my answers to your questions below :
First of all thanks for raising this point , as i can relate the usage as well.
It all depends upon the usage as how we will use this column.
1)If we are doing a load/import/export operation , than we do not have to worry about using any function or conversion. DB2 is smart enough to convert this ascii into 'abc'.
In this regard I have clearly explained it in my other blog :
http://balecha.blogspot.in/2012/06/how-loadimport-would-work-in-case-of.html
2) If we select this column value in a variable and use it at run time in a PL/SQL object , we still don't have to worry about any conversion.
Example :
db2 create table tab1 (c1 varchar(255) for bit data , c2 int)
db2 insert into tab1 values ('abc',5)
db2 "declare var1 varchar(255) for bit data;begin select c1 into var1 from tab1 where c2=5;dbms_output.put_line('value of var1 is ' || var1) ; end"
DB20000I The SQL command completed successfully.
value of var1 is abc
3) The only issue that I can think of is in representation .
As you might be worried that you have inserted 'abc' in the column and it shows you some x'616263'. So if you want to display the output as 'abc' , we have solution for that as well.
We will use two functions here called HEX and UNHEX.
Here , HEX is an in-built DB2 function while UNHEX is a function written by us.
First I'll mention the usage then will provide the definition of UNHEX.
Usage :
db2 create table tab1 (c1 varchar(255) for bit data , c2 int)
db2 insert into tab1 values ('abc',5)
db2 "select * from var1"
C1 C2
--------- -----------
x'616263' 5
1 record(s) selected.
Now to get the output as 'abc' , we will run the following query :
db2 "select unhex(hex(c1)),c2 from var1"
C1 C2
--------- -----------
abc 5
1 record(s) selected.
I'll put the definition of UNHEX in the next post due to space constraint.
Defintion of UNHEX :
DeleteCREATE OR REPLACE FUNCTION unhex(in VARCHAR(100) FOR BIT DATA)
RETURNS VARCHAR(50)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE pos INTEGER DEFAULT 1;
DECLARE hex CHAR(2);
DECLARE ascii INTEGER;
DECLARE result VARCHAR(50) DEFAULT '';
IF in IS NULL THEN
RETURN NULL;
END IF;
WHILE ( pos < LENGTH(in) ) DO
SET hex = UPPER(SUBSTR(in, pos, 2));
SET ascii = CASE SUBSTR(hex, 1, 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
ELSE 15
END * 16 +
CASE SUBSTR(hex, 2, 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
ELSE 15
END;
SET result = result || CHR(ascii);
SET pos = pos + 2;
END WHILE;
RETURN result;
END@