I scribbled up a bit of a library for this a while back. Use the bld script
in samples/c


.c file:
/****************************************************************************
** Written by Serge Rielau
****************************************************************************/

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include <sqludf.h>

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_And(SQLUDF_BIGINT *number1,
SQLUDF_BIGINT *number2,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *number1NullInd,
SQLUDF_SMALLINT *number2NullInd,
SQLUDF_SMALLINT *outNumberNullInd,
SQLUDF_TRAIL_ARGS)
{
if (*number1NullInd == -1 || *number2NullInd == -1)
{
*outNumberNullInd = -1;
}
else
{
*outNumber = *number1 & *number2;
*outNumberNullInd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Or(SQLUDF_BIGINT *number1,
SQLUDF_BIGINT *number2,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *number1NullInd,
SQLUDF_SMALLINT *number2NullInd,
SQLUDF_SMALLINT *outNumberNullInd,
SQLUDF_TRAIL_ARGS)
{
if (*number1NullInd == -1 || *number2NullInd == -1)
{
*outNumberNullInd = -1;
}
else
{
*outNumber = *number1 | *number2;
*outNumberNullInd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Xor(SQLUDF_BIGINT *number1,
SQLUDF_BIGINT *number2,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *number1NullInd,
SQLUDF_SMALLINT *number2NullInd,
SQLUDF_SMALLINT *outNumberNullInd,
SQLUDF_TRAIL_ARGS)
{
if (*number1NullInd == -1 || *number2NullInd == -1)
{
*outNumberNullInd = -1;
}
else
{
*outNumber = *number1 ^ *number2;
*outNumberNullInd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Not(SQLUDF_BIGINT *number1,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *number1NullInd,
SQLUDF_SMALLINT *outNumberNullInd,
SQLUDF_TRAIL_ARGS)
{
if (*number1NullInd == -1)
{
*outNumberNullInd = -1;
}
else
{
*outNumber = ~*number1;
*outNumberNullInd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Shift_Left(SQLUDF_BIGINT *number1,
SQLUDF_INTEGER *shift,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *number1NullInd,
SQLUDF_SMALLINT *shiftNullInd,
SQLUDF_SMALLINT *outNumberNullInd,
SQLUDF_TRAIL_ARGS)
{
if (*number1NullInd == -1 || *shiftNullInd == -1)
{
*outNumberNullInd = -1;
}
else if (*shift < 0 || *shift > 63)
{
strcpy(SQLUDF_STATE, "38999");
strcpy(SQLUDF_MSGTX, "Shift out of range");
}
else
{
*outNumber = *number1 << *shift;
*outNumberNullInd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Shift_Right(SQLUDF_BIGINT *number1,
SQLUDF_INTEGER *shift,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *number1NullInd,
SQLUDF_SMALLINT *shiftNullInd,
SQLUDF_SMALLINT *outNumberNullInd,
SQLUDF_TRAIL_ARGS)
{
if (*number1NullInd == -1 || *shiftNullInd == -1)
{
*outNumberNullInd = -1;
}
else if (*shift < 0 || *shift > 63)
{
strcpy(SQLUDF_STATE, "38999");
strcpy(SQLUDF_MSGTX, "Shift out of range");
}
else
{
*outNumber = *number1 >> *shift;
*outNumberNullInd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Hex2Num(SQLUDF_VARCHAR *hex,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *hexNullInd,
SQLUDF_SMALLINT *outNumberNullInd,
SQLUDF_TRAIL_ARGS)
{
if (*hexNullInd == -1)
{
*outNumberNullInd = -1;
}
else
{
*outNumber = 0;
for (*outNumber = 0; *hex != '\0'; hex++)
{
*outNumber <<= 4;
switch (*hex)
{
case '0': break;
case '1': *outNumber |= 1; break;
case '2': *outNumber |= 2; break;
case '3': *outNumber |= 3; break;
case '4': *outNumber |= 4; break;
case '5': *outNumber |= 5; break;
case '6': *outNumber |= 6; break;
case '7': *outNumber |= 7; break;
case '8': *outNumber |= 8; break;
case '9': *outNumber |= 9; break;
case 'A':
case 'a': *outNumber |= 10; break;
case 'B':
case 'b': *outNumber |= 11; break;
case 'C':
case 'c': *outNumber |= 12; break;
case 'D':
case 'd': *outNumber |= 13; break;
case 'E':
case 'e': *outNumber |= 14; break;
case 'F':
case 'f': *outNumber |= 15; break;
default:
{
strcpy(SQLUDF_STATE, "38999");
strcpy(SQLUDF_MSGTX, "Unknown character");
}
}
}
*outNumberNullInd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Num2Hex(SQLUDF_BIGINT *number,
SQLUDF_VARCHAR *outHex,
SQLUDF_SMALLINT *numberNullInd,
SQLUDF_SMALLINT *outHexNullInd,
SQLUDF_TRAIL_ARGS)
{
int i;
if (*numberNullInd == - 1)
{
*outHexNullInd = -1;
}
else
{
for (i = 15; i >= 0; *number >>= 4, i--)
{
switch (*number & 0xf)
{
case 0: outHex[i] = '0'; break;
case 1: outHex[i] = '1'; break;
case 2: outHex[i] = '2'; break;
case 3: outHex[i] = '3'; break;
case 4: outHex[i] = '4'; break;
case 5: outHex[i] = '5'; break;
case 6: outHex[i] = '6'; break;
case 7: outHex[i] = '7'; break;
case 8: outHex[i] = '8'; break;
case 9: outHex[i] = '9'; break;
case 10: outHex[i] = 'A'; break;
case 11: outHex[i] = 'B'; break;
case 12: outHex[i] = 'C'; break;
case 13: outHex[i] = 'D'; break;
case 14: outHex[i] = 'E'; break;
case 15: outHex[i] = 'F'; break;
default: {}
}
}
outHex[16] = 0;
*outHexNullInd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Test(SQLUDF_BIGINT *number,
SQLUDF_INTEGER *bitPos,
SQLUDF_SMALLINT *outBool,
SQLUDF_SMALLINT *numberNullInd,
SQLUDF_SMALLINT *bitPosNullInd,
SQLUDF_SMALLINT *outBoolNullInd,
SQLUDF_TRAIL_ARGS)
{
if (*numberNullInd == -1 || *bitPosNullInd == -1)
{
*outBoolNullInd = -1;
}
else if (*bitPos < 0 || *bitPos > 63)
{
strcpy(SQLUDF_STATE, "38999");
strcpy(SQLUDF_MSGTX, "Bit position out of range");
}
else
{
if (*number & (((SQLUDF_BIGINT) 1) << *bitPos))
{
*outBool = 1;
}
else
{
*outBool = 0;
}
*outBoolNullInd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Set(SQLUDF_BIGINT *number,
SQLUDF_INTEGER *bitPos,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *numberNullInd,
SQLUDF_SMALLINT *bitPosNullInd,
SQLUDF_SMALLINT *outNumberNullInd,
SQLUDF_TRAIL_ARGS)
{
if (*numberNullInd == -1 || *bitPosNullInd == -1)
{
*outNumberNullInd = -1;
}
else if (*bitPos < 0 || *bitPos > 63)
{
strcpy(SQLUDF_STATE, "38999");
strcpy(SQLUDF_MSGTX, "Bit position out of range");
}
else
{
*outNumber = *number | (((SQLUDF_BIGINT) 1) << *bitPos);
*outNumberNullInd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Clear(SQLUDF_BIGINT *number,
SQLUDF_INTEGER *bitPos,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *numberNullInd,
SQLUDF_SMALLINT *bitPosNullInd,
SQLUDF_SMALLINT *outNumberNullInd,
SQLUDF_TRAIL_ARGS)
{
if (*numberNullInd == -1 || *bitPosNullInd == -1)
{
*outNumberNullInd = -1;
}
else if (*bitPos < 0 || *bitPos > 63)
{
strcpy(SQLUDF_STATE, "38999");
strcpy(SQLUDF_MSGTX, "Bit position out of range");
}
else
{
*outNumber = *number & ~(((SQLUDF_BIGINT) 1) << *bitPos);
outNumberNullInd = 0;
}
}

.clp
-- Bigint functions 64 Bit wide
CREATE FUNCTION BIT_AND(BIGINT, BIGINT) RETURNS BIGINT
SPECIFIC BIT_AND EXTERNAL NAME 'udfbit!Bit_And'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_OR(BIGINT, BIGINT) RETURNS BIGINT
SPECIFIC BIT_OR EXTERNAL NAME 'udfbit!Bit_Or'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_XOR(BIGINT, BIGINT) RETURNS BIGINT
SPECIFIC BIT_XOR EXTERNAL NAME 'udfbit!Bit_Xor'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_NOT(BIGINT) RETURNS BIGINT
SPECIFIC BIT_NOT EXTERNAL NAME 'udfbit!Bit_Not'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_SHIFT_RIGHT(BIGINT, INTEGER) RETURNS BIGINT
SPECIFIC BIT_SHIFT_RIGHT EXTERNAL NAME 'udfbit!Bit_Shift_Right'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_SHIFT_LEFT(BIGINT, INTEGER) RETURNS BIGINT
SPECIFIC BIT_SHIFT_LEFT EXTERNAL NAME 'udfbit!Bit_Shift_Left'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_HEX2NUM(VARCHAR(16)) RETURNS BIGINT
SPECIFIC BIT_HEX2NUM EXTERNAL NAME 'udfbit!Bit_Hex2Num'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_NUM2HEX(BIGINT) RETURNS VARCHAR(16)
SPECIFIC BIT_NUM2HEX EXTERNAL NAME 'udfbit!Bit_Num2Hex'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_TEST(BIGINT, INTEGER) RETURNS SMALLINT
SPECIFIC BIT_TEST EXTERNAL NAME 'udfbit!Bit_Test'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_CLEAR(BIGINT, INTEGER) RETURNS BIGINT
SPECIFIC BIT_CLEAR EXTERNAL NAME 'udfbit!Bit_Clear'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_SET(BIGINT, INTEGER) RETURNS BIGINT
SPECIFIC BIT_SET EXTERNAL NAME 'udfbit!Bit_Set'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

-- Varchar fucntions (1024 Bit wide
CREATE FUNCTION BIT_AND(VARCHAR(128) FOR BIT DATA,
VARCHAR(128) FOR BIT DATA)
RETURNS VARCHAR(128) FOR BIT DATA
SPECIFIC BIT_AND EXTERNAL NAME 'udfbit!Bit_AndVc'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_OR(VARCHAR(128) FOR BIT DATA,
VARCHAR(128) FOR BIT DATA)
RETURNS VARCHAR(128) FOR BIT DATA
SPECIFIC BIT_OR EXTERNAL NAME 'udfbit!Bit_OrVc'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_XOR(VARCHAR(128) FOR BIT DATA,
VARCHAR(128) FOR BIT DATA)
RETURNS VARCHAR(128) FOR BIT DATA
SPECIFIC BIT_XOR EXTERNAL NAME 'udfbit!Bit_XorVc'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION (VARCHAR(128) FOR BIT DATA,
VARCHAR(128) FOR BIT DATA)
RETURNS VARCHAR(128) FOR BIT DATA
SPECIFIC BIT_NOT EXTERNAL NAME 'udfbit!Bit_NotVc'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION (VARCHAR(128) FOR BIT DATA,
VARCHAR(128) FOR BIT DATA)
RETURNS VARCHAR(128) FOR BIT DATA
SPECIFIC BIT_SHIFT_RIGHT EXTERNAL NAME 'udfbit!Bit_Shift_RightVc'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION (VARCHAR(128) FOR BIT DATA,
VARCHAR(128) FOR BIT DATA)
RETURNS VARCHAR(128) FOR BIT DATA
SPECIFIC BIT_SHIFT_LEFT EXTERNAL NAME 'udfbit!Bit_Shift_LeftVc'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_TEST(VARCHAR(128) FOR BIT DATA, INTEGER)
RETURNS SMALLINT
SPECIFIC BIT_TEST EXTERNAL NAME 'udfbit!Bit_TestVc'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_CLEAR(VARCHAR(128) FOR BIT DATA, INTEGER)
RETURNS VARCHAR(128) FOR BIT DATA
SPECIFIC BIT_CLEAR EXTERNAL NAME 'udfbit!Bit_ClearVc'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_SET(VARCHAR(128) FOR BIT DATA, INTEGER)
RETURNS VARCHAR(128) FOR BIT DATA
SPECIFIC BIT_SET EXTERNAL NAME 'udfbit!Bit_SetVc'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

.exp (for *ix):
Bit_And
Bit_Or
Bit_Xor
Bit_Not
Bit_Shift_Right
Bit_Shift_Left
Bit_Hex2Num
Bit_Num2Hex
Bit_Test
Bit_Set
Bit_Clear

.def (for Windows)
LIBRARY UDFBIT
DESCRIPTION 'Library for DB2 Bit UUDF'
EXPORTS
Bit_And
Bit_Or
Bit_Xor
Bit_Not
Bit_Shift_Right
Bit_Shift_Left
Bit_Hex2Num
Bit_Num2Hex
Bit_Test
Bit_Set
Bit_Clear



Serge Rielau
DB2 Solutions Development
DB2 Universal Database for Linux, Unix, Windows
IBM Toronto Lab
Tel.: (905) 413 3907 Internet: [Email address protected]
Fax: (905) 413 4840 T/L: 969 3907





"Kevin Knaus "
<db2udbdba-ezmlms
hield-x52623039.x To
[Email Address Removed] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
08/30/2005 11:07
AM Subject
bitmap data, setting and using










All,

When defining a column as a CHAR (10) FOR BIT DATA, I want to use it by
having each bit position represent a particular thing. Then I want to
use it to select rows from the table where certain bits are set.

Is that the normal use of "FOR BIT DATA"? If so, how do I set the
correct bits and how do I reference bit positions in a query where
clause?

If this is not the intended purpose of "FOR BIT DATA", what is the
intended purpose?

In general I am trying to use the bit data to work with approximately
1000 different objects, so I would need up to 1000 bits.

Thanks

Kevin Knaus


---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




DB2 & UDB email list listserv db2-l LazyDBA home page