Many relational databases (including Sybase, Oracle, Microsoft® SQL Server, and Informix®) support the bit data type or Boolean data type for column definitions, and provide bitwise or Boolean functions for these types of data. T-SQL also provide bitwise operations – AND, OR, NOT, EXCLUSIVE OR between integer, smallint, and tinyint data types, while PL/SQL supports BITAND – or logical AND for the integer data type. DB2 UDB does not have native support for bit or Boolean data types, neither for bitwise operation nor for Boolean algebra operations.
This article offers a method that includes table creation with columns of the bit-like or Boolean data type, using constraints or triggers, and a set of user-defined functions (UDFs) to support bitwise and Boolean operations for columns that imitate bit or Boolean data type behavior. This article also offers a set of UDFs that perform bitwise operations between integer arguments.
Here is the definition for the bit data type from the T-SQL reference: "Use bit columns for true and false or yes and no types of data. Bit columns hold either 0 or 1. Integer values other than 0 or 1 are accepted, but are always interpreted as 1. Columns of datatype bit cannot be NULL and cannot have indexes on them."
For example, we have a table that is declared as follows in Sybase or SQL Server database:
create table mytab
(custname varchar(30) not null,
age integer not null,
flag1 bit not null,
flag2 bit not null) |
In order to convert this table to DB2, you can use DB2 SMALLINT data type and NOT NULL constraints:
CREATE TABLE mytab
(name varchar(30) not null,
age int not null,
flag1 smallint NOT NULL,
flag2 smallint NOT NULL); |
Also, we need to enforce special rules to duplicate the way T-SQL treats columns of bit data type. From the definition for the bit data type: "Bit columns hold either 0 or 1. Integer values other than 0 or 1 are accepted, but are always interpreted as 1." For example, in Sybase and Microsoft SQL Server, if you insert into bit column value 10, it will be interpreted as 1 and that column will hold value = 1. To ensure that columns flag1 and flag2 only hold 1 or 0 (no matter what value has been provided in INSERT statement), create the following INSERT trigger:
CREATE TRIGGER DB2ADMIN.INSFORBIT
NO CASCADE BEFORE INSERT ON DB2ADMIN.MAR1
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
if new.c2 <>0 then set new.c2 = 1;
end if;
END |
This trigger will ensure that any value other than 0 will be interpreted as 1. A similar trigger for UPDATE needs to be created to ensure the correct values of bit columns.
Now we need to provide bitwise operation functions: & (and), | (or), ^ (exclusive or), or ~ (not).Basically we need to write a set of UDFs that will realize the following bit operation truth tables.
| & (and) | 1 | 0 |
| 1 | 1 | 0 |
| 0 | 0 | 0 |
| | (or) | 1 | 0 |
| 1 | 1 | 1 |
| 0 | 1 | 0 |
| ^ (exclusive or) | 1 | 0 |
| 1 | 0 | 1 |
| 0 | 1 | 0 |
| ~ (not) | |
| 1 | FALSE |
| 0 | 0 |
Here is that set of UDFs:
CREATE FUNCTION DB2ADMIN.BIT_AND(X smallint, Y smallint)
RETURNS INTEGER
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
IF x =1 and y = 1 THEN
RETURN 1 ;
ELSEIF (x < 0 or x > 1) or (y < 0 or y > 1) THEN
SIGNAL SQLSTATE '77701' SET MESSAGE_TEXT ='ONLY 1 OR 0 ARGUMENTS VALUE ACCEPTED';
ELSE RETURN 0;
END IF;
END |
Please note that we restrict the argument value by raising an application error when arguments are not 1 or 0.
CREATE FUNCTION DB2ADMIN.BIT_OR(X smallint, Y smallint)
RETURNS INTEGER
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
BEGIN ATOMIC
IF x =0 AND y = 0 THEN
RETURN 0;
ELSEIF (x < 0 or x > 1) or (y < 0 or y > 1) THEN
SIGNAL SQLSTATE '77701' SET MESSAGE_TEXT ='ONLY 1 OR 0 ARGUMENTS VALUE ACCEPTED';
ELSE RETURN 1;
END IF;
END
CREATE FUNCTION DB2ADMIN.EXCLUSIVE_OR(X smallint, Y smallint)
RETURNS INTEGER
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
IF (x < 0 or x > 1) or (y < 0 or y > 1) THEN
SIGNAL SQLSTATE '77701' SET MESSAGE_TEXT ='ONLY 1 OR 0 ARGUMENTS VALUE ACCEPTED';
ELSEIF (x = y) THEN
RETURN 0;
ELSE RETURN 1;
END IF;
END
CREATE FUNCTION DB2ADMIN.bit_not(x smallint )
RETURNS INTEGER
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
IF x = 1 THEN
RETURN 0;
ELSEIF (x < 0 or x > 1) THEN
SIGNAL SQLSTATE '77701' SET MESSAGE_TEXT ='ONLY 1 OR 0 ARGUMENT VALUE ACCEPTED';
ELSE
RETURN 1;
END IF;
END |
Now, using the mytab table definition, we can convert T-SQL statements to DB2 using the above UDFs.
T-SQL SQL statement:
select flag1&flag2 from mytab where custname = 'JOHN SMITH' |
will be converted to DB2 as:
SELECT bit_and(flag1,flag2) FROM mytab where custname = 'JOHN SMITH'; |
while T-SQL SQL statement:
select flag1 | flag2 from mytab where custname = 'SAM BROWN' |
will be converted to DB2 as:
SELECT bit_or(flag1,flag2) where mytab where custname = 'SAM BROWN'; |
Imitating Oracle’s Boolean data type
Let's consider the following PL/SQL code that needs to be converted to DB2 UDB.
We have the following Oracle table:
create table myOracle_tab
(custname varchar(30) not null,
age integer not null,
flag1 BOOLEAN,
flag2 BOOLEAN); |
and we have the following PL/SQL SQL statements that manipulate with columns flag1 and flag2 (both of BOOLEAN data type):
select flag1 AND flag2 from mytab where custname = 'JOHN SMITH';
select flag1 OR flag2 from mytab where custname = 'SAM BROWN'; |
DB2 smallint data type can be used to convert Oracle Boolean data type. PL/SQL supports the following values for the Boolean columns -- TRUE, FALSE, NULL. We can use 1 for TRUE, 0 for FALSE, and permit the column to be nullable. Here’s how we can convert CREATE TABLE myOracle_tab statement:
create table myOracle_tab
(name char(20),
boolcol smallint constraint bool_cnst check (c2 in(0,1))); |
This bool_cnst will insure that only values that can be inserted will be 0 or 1. If no value is provided, the column will be NULL.
PL/SQL supports three operators -- AND, OR and NOT -- that operate on Boolean arguments and return a Boolean value. To convert this behavior to DB2, we need to create UDFs to support Boolean operation logic.
| x | y | x AND y | x OR y | NOT x |
|---|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE | FALSE |
| TRUE | FALSE | FALSE | TRUE | FALSE |
| TRUE | NULL | NULL | TRUE | FALSE |
| FALSE | TRUE | FALSE | TRUE | TRUE |
| FALSE | FALSE | FALSE | FALSE | TRUE |
| FALSE | NULL | FALSE | NULL | TRUE |
| NULL | TRUE | NULL | TRUE | NULL |
| NULL | FALSE | FALSE | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL |
The following UDF implements the above operations:
CREATE FUNCTION DB2ADMIN.bool_and(x smallint, y smallint)
RETURNS SMALLINT
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
IF x IS NULL OR y IS NULL THEN
ELSEIF x =1 AND y = 1 THEN
RETURN 1 ;
ELSE RETURN 0;
END IF;
END
CREATE FUNCTION DB2ADMIN.bool_NOT(x smallint)
RETURNS SMALLINT
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
BEGIN ATOMIC
IF x IS NULL THEN
RETURN NULL;
ELSEIF x=1 THEN RETURN 0;
ELSE RETURN 1;
END IF;
END
CREATE FUNCTION DB2ADMIN.bool_or(x smallint, y smallint)
RETURNS SMALLINT
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
BEGIN ATOMIC
IF x = 1 THEN RETURN 1;
ELSEIF x = 0 THEN
RETURN y;
ELSEIF y = 1 THEN RETURN 1;
ELSE RETURN NULL;
END IF;
END |
Oracle SQL statement
select flag1 AND flag2 from mytab where custname = 'JOHN SMITH' |
will be converted to DB2 as:
SELECT bool_and(flag1,flag2) FROM mytab where custname = 'JOHN SMITH'; |
Oracle SQL statement:
select flag1 OR flag2 from mytab where custname = 'SAM BROWN' |
will be converted to DB2 as:
SELECT bool_or(flag1,flag2) FROM mytab where custname = 'SAM BROWN'; |
Bitwise operations for integer arguments
As I mentioned in the introduction, PL/SQL and T-SQL supports bitwise operations between arguments declared as integer. Bitwise operations perform logical AND, OR, EXLUSIVE OR, and NOT operations on binary representations of their integer arguments.
Let’s look at a specific example of how it works. Let’s say we need to do logical AND and logical OR between two integers, 110 and 85.
First, let’s convert both numbers to binary, then apply bitwise AND and OR for each bit using our truth tables, and, finally, convert the binary result back to an integer number.
Integer Binary form
110 1101110
85 1010101
----------- Logical AND
64 1000100
Integer Binary form
110 1101110
85 1010101
----------- Logical OR
127 1111111 |
For people who cannot easily convert from integer to binary in their heads, the following UDF, which converts integer to binary, may be helpful.
CREATE FUNCTION int_to_binary (N1 Integer)
RETURNS varchar(32)
LANGUAGE SQL
SPECIFIC int2bin
BEGIN ATOMIC
DECLARE M1, i, len Integer default 0;
DECLARE temp_str varchar(32) default ' ';
DECLARE result_str varchar(32) default ' ';
SET M1 = N1;
WHILE M1 > 0 DO
SET temp_str = temp_str || cast(mod(m1,2) as char(1));
set m1 = m1/2;
END WHILE;
set len = length (temp_str);
while i < len do
set result_str = result_str || substr(temp_str,len-i,1);
set i = i+1;
end while;
RETURN result_str;
END |
Now that we understand the definition for bitwise operations and argument conversion, we can offer DB2 UDFs to support such operations.
Takashi Tokunaga has written a function to support BITAND in order to convert applications from Oracle to DB2, along with other useful migrations UDFs: http://www-128.ibm.com/developerworks/db2/library/samples/db2/0205udfs/index.html.
For completeness, the code for this function is included here as well:
CREATE FUNCTION BITAND (N1 Integer, N2 Integer) RETURNS Integer LANGUAGE SQL SPECIFIC BITANDOracle CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN ATOMIC DECLARE M1, M2, S Integer; DECLARE RetVal Integer DEFAULT 0; SET (M1, M2, S) = (N1, N2, 0); WHILE M1 > 0 AND M2 > 0 AND S < 32 DO SET RetVal = RetVal + MOD(M1,2)*MOD(M2,2)*power(2,S); SET (M1, M2, S) = (M1/2, M2/2, S+1); END WHILE; RETURN RetVal; END |
Now let’s call this function from CLP prompt:
C:\Program Files\IBM\SQLLIB\BIN>db2 values bitand(110,85)
1
-----------
68
1 record(s) selected. |
In order to support migration from Sybase and Microsoft SQL Server, we need to have UDFs for BITOR, EXLUSIVE OR, and NOT. Here is the BITOR UDF:
CREATE FUNCTION BITOR (N1 Integer, N2 Integer) RETURNS Integer LANGUAGE SQL SPECIFIC BITORCONV BEGIN ATOMIC DECLARE M1, M2, S , temp1 Integer; DECLARE RetVal Integer DEFAULT 0; SET (M1, M2, S) = (N1, N2, 0); WHILE ( M1 > 0 OR M2 > 0) AND S < 32 DO SET temp1 = bit_or(mod(m1,2),mod(m2,2)); SET RetVal = RetVal + temp1*power(2,S); SET (M1, M2, S) = (M1/2, M2/2, S+1); END WHILE; RETURN RetVal; END |
Please note that this function utilizes BIT_OR UDF we wrote to operate on bit-like data type parameters.
Now let’s call this function from CLP:
C:\Program Files\IBM\SQLLIB\BIN>db2 values bitor(110,85)
1
-----------
127
1 record(s) selected. |
The next UDF performs EXLUSIVE OR operation between two given integer values as translated to binary expressions:
CREATE FUNCTION BIT_EXLOR (N1 Integer, N2 Integer) RETURNS Integer LANGUAGE SQL SPECIFIC BITOREXL BEGIN ATOMIC DECLARE M1, M2, S, temp1 Integer; DECLARE RetVal Integer DEFAULT 0; SET (M1, M2, S) = (N1, N2, 0); WHILE ( M1 > 0 OR M2 > 0 ) AND S < 32 DO SET temp1 = EXCLUSIVE_OR(smallint(mod(m1,2)),smallint(mod(m2,2))); SET RetVal = RetVal + temp1*power(2,S); SET (M1, M2, S) = (M1/2, M2/2, S+1); END WHILE; RETURN RetVal; END |
Again, this function uses the previously provided function EXLUSIVE_OR, and can be executed as follows:
C:\Program Files\IBM\SQLLIB\BIN>db2 values bit_exlor (110,85)
1
-----------
59
1 record(s) selected. |
To verify that the function indeed works as designed, we, again, need to convert each integer to BINARY, perform EXLUSIVE OR for each bit, and then convert the result back to INTEGER:
Integer Binary form
110 1101110
85 1010101
----------- Exclusive OR
59 0111011 |
The next and last UDF to cover bitwise operations is bitwise NOT that performs a bitwise logical NOT operation for one given integer value as translated to a binary expression.
CREATE FUNCTION BITWISE_not (N1 Integer) RETURNS Integer LANGUAGE SQL SPECIFIC BITWNOT BEGIN ATOMIC DECLARE M1, S , temp1 Integer; DECLARE RetVal Integer DEFAULT 0; SET (M1, S) = (N1, 0); WHILE M1 > 0 AND S < 32 DO SET temp1 = bit_not(mod(m1,2)); SET RetVal = RetVal + temp1*power(2,S); SET (M1, S) = (M1/2, S+1); END WHILE; RETURN RetVal; END |
Here is how it works:
C:\Program Files\IBM\SQLLIB\BIN>db2 values bitwise_NOT(110)
1
-----------
17
1 record(s) selected.
Integer Binary form
110 1101110
----------- Logical NOT
17 0010001 |
Emulating bit and Boolean data types and functions does not have to be a challenging process. Using the UDFs and triggers we've talked about in this article, you can easily migrate your data and applications to DB2 Universal Database.
- The article Using DB2 routines to ease migration (developerWorks, February, 2004) offers a number of routines that iwll help you easily migrate some frequently used functionality in other database products.
- The Porting to DB2 UDB page contains technical resources and a roadmap for migrating to DB2 UDB from other database management systems.
- The developerWorks Migration station points you to many resources that will help you migrate from Microsoft SQL Server, Oracle, Sybase, and other database platforms to DB2.
- Browse for books on these and other technical topics.
Marina Greenstein is a Certified Technical Consultant with the DB2 Migration Team. She joined IBM in 1995 and is currently responsible for helping customers migrate from competitive database management systems to DB2 UDB. She has presented migration methodology and various database migration topics at numerous DB2 technical conferences and at SHARE.
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
