Ion wrote:
It's a SQL function (the one posted on the ibm support site) and it's
declared as
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
Some more details:
Large_table is joined with SmallTable on an INT column. The 2 rows in
SmallTable have a value of 1 and 0 for the join column.
99% of the rows in LargeTable have the value 1 for the join column.
Even getting rid of the BitAND function and replacing the Where
BitAND(SmallTable.Coulmn, 2) <> 0 WITH WHERE SmallTable.Coulmn<0, will
still not persuate the optimizer to evaluate this predicate first
(detailed and distribution statistics collected on all columns of Small
table)
May be the function is too complex (inlcudes BEGIN TOMIC .. END) and
prevents some rewrite rules (e.g. pushdowns).
Here is soem source I wrote a while ago which uses C, as always no
warranty in thsi group ... :-)
udfbit.c:
/************************************************** **************************
** 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;
}
}
udfbit.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;
udfbit.exp:
Bit_And
Bit_Or
Bit_Xor
Bit_Not
Bit_Shift_Right
Bit_Shift_Left
Bit_Hex2Num
Bit_Num2Hex
Bit_Test
Bit_Set
Bit_Clear
udfbit.def (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
compile and deploy using the samples/c/bldrtn script.
Enjoy
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab