Browse Prior Art Database

Support for bit-like or Boolean columns in DB2 tables

IP.com Disclosure Number: IPCOM000035498D
Original Publication Date: 2005-Jan-21
Included in the Prior Art Database: 2005-Jan-21
Document File: 3 page(s) / 55K

Publishing Venue

IBM

Abstract

A database coversion method that includes table creation with columns of bit-like or Boolean data type using constraints or triggers, and a set of user-defined functions to support bitwise and Boolean operations in a DB2® database.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 58% of the total text.

Page 1 of 3

Support for bit-like or Boolean columns in DB2 tables

This method provides the ability to simulate bit datatype functionality from T-SQL (Sybase, MS SQL Server) to DB2 which provides a means to automate the conversion from a Sybase database to a DB2 database.

As an example, consider the T-SQL code in a Sybase table that is to be converted to DB2 UDB:

Create table mytab

(custname varchar(30) not null,

age integer not null,

flag1 bit not null,

flag2 bit not null)

go

Further, the following SQL statements manipulate with column flag1 and flag2 (both bit datatypes):

select flag1&flag2 from mytab where custname = 'JOHN SMITH'

go

select flag1| flag2 from mytab where custname = 'SAM BROWN'

go

In order to convert this Sybase schema to DB2 it is first necessary to convert the table defintion. DB2 smallint datatype can be used to convert from Sybase bit datatype, but there are special rules that need to be enforced to duplicate the way that Sybase and MS SQL Server treat columns of bit datatype. Several methods must be provided in order to support any datatype. As T-SQL only allows bit columns to contain either 0 or 1, integer values other than 0 or 1 are accepted but any non-zero values are always interpreted as 1. For example, in Sybase and MS SQL Server an insertion into bit column value 10 will be interpreted as 1 and that column will hold a value = 1. Note that the value of a bit column cannot be NULL. This set of rules can be converted to DB2 as follows:

CREATE TABLE mytab

(name varchar(30) not null,

age int not null,

flag1 smallint NOT NULL,

flag2 smallint NOT NULL);

To insure that bitflag is only 1 or 0 no matter what value has been provided in the INSERT statement, a trigger is created before the insert that will enforce this rule. A similar trigger for UPDATE must be created to ensure the correct values of bit columns.

Next, the bitwise operation functions "&" (and), "|" (or), "^" (exclusive or) and "~" (not) are performed. The following UDFs will provide bitwise operation:

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 ;

else return 0 ;

end if ;
END

A similar UDF must be created to simulate bit_or, exclusive_or and bit_not bit operations.

1

Page 2 of 3

Using the converted DB2 table declaration and these UDFs, the original Sybase SQL statements will be converted to DB2 as follows:

SELECT bit_and(flag1,flag2) where mytab where custname = 'JOHN SMITH';

SELECT bit_or(flag1,flag2) where mytab where custname = 'SAM BROWN';

The present method also provides the ability to simulate Boolean datatype functionality from PL/SQL (Oracle) to DB2, which provides a means to automate the conversion from an Oracle...