poor query performance

[Ion]

Hi all,

I have a query that takes almost 1 hour to complete. This is acceptable
in certain situations, but unacceptable when no rows should qualify.

Something like:

Select list

From LargeTable (200 mil rows)

Join ExpensiveTableF unction (based on LargeTable)
ON simple_join_con dition
JOIN SmallTable ON simple_join_con dition
WHERE BitAND(SmallTab le.Coulmn, 2) <> 0

The SmallTable table has exactly 2 rows, and neither of them passes the
where clause predicate.
If I could make the optimizer to somehow evaluate first the WHERE
clause the query would be fast - as no row qualifies.

Running statistics, rewriting the query in 1 million ways didn't help,
is there any way to trick the optimizer?

I use DB2 8.2 UDB Windows FP8

thanks

[Knut Stolze]

Ion wrote:

Hi all,

I have a query that takes almost 1 hour to complete. This is acceptable
in certain situations, but unacceptable when no rows should qualify.

Something like:

Select list

From LargeTable (200 mil rows)

Join ExpensiveTableF unction (based on LargeTable)
ON simple_join_con dition
JOIN SmallTable ON simple_join_con dition
WHERE BitAND(SmallTab le.Coulmn, 2) <> 0

The SmallTable table has exactly 2 rows, and neither of them passes the
where clause predicate.
If I could make the optimizer to somehow evaluate first the WHERE
clause the query would be fast - as no row qualifies.

Running statistics, rewriting the query in 1 million ways didn't help,
is there any way to trick the optimizer?

I use DB2 8.2 UDB Windows FP8

It would help if you gave a few examples that show what you actually have
tried already. Have you tried the following queries?

SELECT list
FROM ( SELECT ...
FROM small-table
WHERE BitAND(small-table.column, 2) <> 0 ) JOIN
( large-table JOIN table-function ON condition ) ON condition

Or wrap the sub-select in a common table expression to force its evaluation
right up front.

Another idea might be to use generated columns for the BitAND() function
call. Then the optimizer could re-route to that column and, with
statistics on that column, figure out right away if and how many columns
qualify.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[Ion]

Hi Knut,

Yes, I have tired both the common table expression and it did not seem
to force it's evaluation first, as well as the query you suggested.
Adding a column is not an option.
As the select query happens in a stored procedure I can't think of
anything else than maybe using a temp table.
Thanks for your suggestions

[Phil Sherman]

Can you separately query the small table and use a "found rows" type
condition to then execute the query. This would completely avoid the
long running query when there are no matching rows in SmallTable.

You might also try replicating the WHERE predicate as part of the ON
predicate for the join to SmallTable. Use EXPLAIN to see if it makes a
difference. If a very small number of rows from Large Table match the
rows in SmallTable then using an IN clause with a subselect on
SmallTable to limit rows from LargeTable may help the optimizer evaluate
SmallTable first.

If you don't have an index on the LargeTable columns that match
SmallTable, you'll be forced into a scan to locate the matching rows. As
mentioned by Knut, statistics are necessary for the optimizer to make
better choices.

Phil Sherman

Ion wrote:

Hi Knut,

Yes, I have tired both the common table expression and it did not seem
to force it's evaluation first, as well as the query you suggested.
Adding a column is not an option.
As the select query happens in a stored procedure I can't think of
anything else than maybe using a temp table.
Thanks for your suggestions

[Serge Rielau]

Ion wrote:

Hi Knut,

Yes, I have tired both the common table expression and it did not seem
to force it's evaluation first, as well as the query you suggested.
Adding a column is not an option.
As the select query happens in a stored procedure I can't think of
anything else than maybe using a temp table.
Thanks for your suggestions

How is BITAND defined? If it's an external UDF, then make suer it's
defined as NO EXTERNAL ACTION otherwise DB2 can push the predicate.
Also once the UDF is tested sufficiently define it as UNFENCED, so it
runs in DB2's process space.
Otherwise I supect you used an SQL Function. How is is defined?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

Ion wrote:

Adding a column is not an option.
Why not? Could you please explain the reason for the restriction?
As the select query happens in a stored procedure I can't think of
anything else than maybe using a temp table.

Have a look at the access plan. It should show if and where a temp table is
to be used.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[Ion]

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(SmallTab le.Coulmn, 2) <> 0 WITH WHERE SmallTable.Coul mn<0, will
still not persuate the optimizer to evaluate this predicate first
(detailed and distribution statistics collected on all columns of Small
table)

[Serge Rielau]

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(SmallTab le.Coulmn, 2) <> 0 WITH WHERE SmallTable.Coul mn<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 *outNumberNullI nd,
SQLUDF_TRAIL_AR GS)
{
if (*number1NullIn d == -1 || *number2NullInd == -1)
{
*outNumberNullI nd = -1;
}
else
{
*outNumber = *number1 & *number2;
*outNumberNullI nd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Or(SQLUDF_B IGINT *number1,
SQLUDF_BIGINT *number2,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *number1NullInd ,
SQLUDF_SMALLINT *number2NullInd ,
SQLUDF_SMALLINT *outNumberNullI nd,
SQLUDF_TRAIL_AR GS)
{
if (*number1NullIn d == -1 || *number2NullInd == -1)
{
*outNumberNullI nd = -1;
}
else
{
*outNumber = *number1 | *number2;
*outNumberNullI nd = 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 *outNumberNullI nd,
SQLUDF_TRAIL_AR GS)
{
if (*number1NullIn d == -1 || *number2NullInd == -1)
{
*outNumberNullI nd = -1;
}
else
{
*outNumber = *number1 ^ *number2;
*outNumberNullI nd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Not(SQLUDF_ BIGINT *number1,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *number1NullInd ,
SQLUDF_SMALLINT *outNumberNullI nd,
SQLUDF_TRAIL_AR GS)
{
if (*number1NullIn d == -1)
{
*outNumberNullI nd = -1;
}
else
{
*outNumber = ~*number1;
*outNumberNullI nd = 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 *outNumberNullI nd,
SQLUDF_TRAIL_AR GS)
{
if (*number1NullIn d == -1 || *shiftNullInd == -1)
{
*outNumberNullI nd = -1;
}
else if (*shift < 0 || *shift > 63)
{
strcpy(SQLUDF_S TATE, "38999");
strcpy(SQLUDF_M SGTX, "Shift out of range");
}
else
{
*outNumber = *number1 << *shift;
*outNumberNullI nd = 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 *outNumberNullI nd,
SQLUDF_TRAIL_AR GS)
{
if (*number1NullIn d == -1 || *shiftNullInd == -1)
{
*outNumberNullI nd = -1;
}
else if (*shift < 0 || *shift > 63)
{
strcpy(SQLUDF_S TATE, "38999");
strcpy(SQLUDF_M SGTX, "Shift out of range");
}
else
{
*outNumber = *number1 >> *shift;
*outNumberNullI nd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Hex2Num(SQL UDF_VARCHAR *hex,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *hexNullInd,
SQLUDF_SMALLINT *outNumberNullI nd,
SQLUDF_TRAIL_AR GS)
{
if (*hexNullInd == -1)
{
*outNumberNullI nd = -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_S TATE, "38999");
strcpy(SQLUDF_M SGTX, "Unknown character");
}
}
}
*outNumberNullI nd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Num2Hex(SQL UDF_BIGINT *number,
SQLUDF_VARCHAR *outHex,
SQLUDF_SMALLINT *numberNullInd,
SQLUDF_SMALLINT *outHexNullInd,
SQLUDF_TRAIL_AR GS)
{
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_AR GS)
{
if (*numberNullInd == -1 || *bitPosNullInd == -1)
{
*outBoolNullInd = -1;
}
else if (*bitPos < 0 || *bitPos > 63)
{
strcpy(SQLUDF_S TATE, "38999");
strcpy(SQLUDF_M SGTX, "Bit position out of range");
}
else
{
if (*number & (((SQLUDF_BIGIN T) 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 *outNumberNullI nd,
SQLUDF_TRAIL_AR GS)
{
if (*numberNullInd == -1 || *bitPosNullInd == -1)
{
*outNumberNullI nd = -1;
}
else if (*bitPos < 0 || *bitPos > 63)
{
strcpy(SQLUDF_S TATE, "38999");
strcpy(SQLUDF_M SGTX, "Bit position out of range");
}
else
{
*outNumber = *number | (((SQLUDF_BIGIN T) 1) << *bitPos);
*outNumberNullI nd = 0;
}
}

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN Bit_Clear(SQLUD F_BIGINT *number,
SQLUDF_INTEGER *bitPos,
SQLUDF_BIGINT *outNumber,
SQLUDF_SMALLINT *numberNullInd,
SQLUDF_SMALLINT *bitPosNullInd,
SQLUDF_SMALLINT *outNumberNullI nd,
SQLUDF_TRAIL_AR GS)
{
if (*numberNullInd == -1 || *bitPosNullInd == -1)
{
*outNumberNullI nd = -1;
}
else if (*bitPos < 0 || *bitPos > 63)
{
strcpy(SQLUDF_S TATE, "38999");
strcpy(SQLUDF_M SGTX, "Bit position out of range");
}
else
{
*outNumber = *number & ~(((SQLUDF_BIGI NT) 1) << *bitPos);
outNumberNullIn d = 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_Shi ft_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_Shi ft_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(VAR CHAR(16)) RETURNS BIGINT
SPECIFIC BIT_HEX2NUM EXTERNAL NAME 'udfbit!Bit_Hex 2Num'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_NUM2HEX(BIG INT) RETURNS VARCHAR(16)
SPECIFIC BIT_NUM2HEX EXTERNAL NAME 'udfbit!Bit_Num 2Hex'
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_Tes t'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_CLEAR(BIGIN T, INTEGER) RETURNS BIGINT
SPECIFIC BIT_CLEAR EXTERNAL NAME 'udfbit!Bit_Cle ar'
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_And Vc'
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_OrV c'
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_Xor Vc'
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_Not Vc'
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_Shi ft_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_Shi ft_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(VARCHA R(128) FOR BIT DATA, INTEGER)
RETURNS SMALLINT
SPECIFIC BIT_TEST EXTERNAL NAME 'udfbit!Bit_Tes tVc'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

CREATE FUNCTION BIT_CLEAR(VARCH AR(128) FOR BIT DATA, INTEGER)
RETURNS VARCHAR(128) FOR BIT DATA
SPECIFIC BIT_CLEAR EXTERNAL NAME 'udfbit!Bit_Cle arVc'
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_Set Vc'
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