User-defined aggregate function

[Larry Menard]

Folks,

I know that DB2 does not (yet?) support this, but I wonder if anyone can suggest a work-around.

I've seen article http://www-128.ibm.com/developerwork...504greenstein/, which was very helpful for developing the scalar functions I needed, but it does not cover how to emulate aggregate functions.

And I've seen Knut's dW article (http://www-128.ibm.com/developerwork...09stolze.html), but frankly it's a bit advanced for me. For example, AFAIK I don't need a UDT (do I?).

Here's my scenario.

I'm porting a PHP application that currently uses other DBs as a back-end. This application has a query:

SELECT BIT_OR(mycolumn) FROM ...

The BIT_OR() aggregate function is my problem.

The 'mycolumn' column is defined as VARCHAR(32) FOR BIT DATA. The BIT_OR() function is supposed to do a bitwise OR of all of the values in that column.

Can anyone offer any simple, to-the-point guidance on any way to accomplish this? I'm using DB2 V8.2.2 on Windows XP. I can write scalar UDFs all I want, but I'm limited as to the amount of major changes I can make to the SQL itself (most specifically, I can't bust it into multiple statements).

Thanks.

(Jeez, in this porting project I seem to be hitting everything that DB2 doesn't support. And I'm not even getting paid for it! ;-)

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

[Serge Rielau]

Larry Menard wrote:

Folks,

I know that DB2 does not (yet?) support this, but I wonder if anyone
can suggest a work-around.

I've seen article
http://www-128.ibm.com/developerwork...504greenstein/,
which was very helpful for developing the scalar functions I needed, but
it does not cover how to emulate aggregate functions.

And I've seen Knut's dW article
(http://www-128.ibm.com/developerwork...09stolze.html),
but frankly it's a bit advanced for me. For example, AFAIK I don't need
a UDT (do I?).

Here's my scenario.

I'm porting a PHP application that currently uses other DBs as a
back-end. This application has a query:
SELECT *BIT_OR(mycolumn)* FROM ...
The BIT_OR() aggregate function is my problem.

The 'mycolumn' column is defined as VARCHAR(32) FOR BIT DATA. The
BIT_OR() function is supposed to do a bitwise OR of all of the values in
that column.

Can anyone offer any simple, to-the-point guidance on _any_ way to
accomplish this? I'm using DB2 V8.2.2 on Windows XP. I can write
scalar UDFs all I want, but I'm limited as to the amount of major
changes I can make to the SQL itself (most specifically, I can't bust it
into multiple statements).

Thanks.

(Jeez, in this porting project I seem to be hitting everything that
DB2 doesn't support. And I'm not even getting paid for it! ;-)

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

You can write a recursive query (WITH clause).

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Larry Menard]

Thanks, Serge. I was under the impression (reinforced by Knut's article)
that recursive queries should be avoided.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3u************@individual.net...

Larry Menard wrote:

Folks,
I know that DB2 does not (yet?) support this, but I wonder if anyone can
suggest a work-around.
I've seen article
http://www-128.ibm.com/developerwork...504greenstein/,
which was very helpful for developing the scalar functions I needed, but
it does not cover how to emulate aggregate functions.
And I've seen Knut's dW article
(http://www-128.ibm.com/developerwork...09stolze.html),
but frankly it's a bit advanced for me. For example, AFAIK I don't need
a UDT (do I?).
Here's my scenario.
I'm porting a PHP application that currently uses other DBs as a
back-end. This application has a query:
SELECT *BIT_OR(mycolumn)* FROM ...

The BIT_OR() aggregate function is my problem.
The 'mycolumn' column is defined as VARCHAR(32) FOR BIT DATA. The
BIT_OR() function is supposed to do a bitwise OR of all of the values in
that column.
Can anyone offer any simple, to-the-point guidance on _any_ way to
accomplish this? I'm using DB2 V8.2.2 on Windows XP. I can write scalar
UDFs all I want, but I'm limited as to the amount of major changes I can
make to the SQL itself (most specifically, I can't bust it into multiple
statements).
Thanks.
(Jeez, in this porting project I seem to be hitting everything that DB2
doesn't support. And I'm not even getting paid for it! ;-)

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

You can write a recursive query (WITH clause).

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[--CELKO--]

Generally speaking you can do aggregate function by putting expressions
into the SUM(), MIN(), or MAX() functions. Here is a version of the
aggregate product function in SQL. You will need to have the logorithm
and exponential functions. They are not standards, but they are very
common. The idea is that there are three special cases - all positive
numbers, one or more zeroes, and some negative numbers in the set.
Within the case where you have negative numbers, there are two
sub-cases: (1) an even number of negatives or (2) an odd number of
negatives. You then need to apply some High School algebra to
determine the sign of the final result.

SELECT CASE MIN(ABS(SIGN(nbr)))
WHEN 0 THEN 0.00 -- some zeroes
ELSE -- no zeroes
EXP(SUM(LOG(ABS(NULLIF(nbr, 0)))))
* CASE WHEN MOD (CAST(SUM(ABS(SIGN(nbr)-1)/2) AS INTEGER), 2)
= 1
THEN -1.00 ELSE 1.00 END
END AS big_pi

However, in your particular situation, you are stuck because some diot
thought that SQL is a language for bit fiddling and not a high level
database language. Nobody uses bits and bytes in SQL. The real
problem is a completely scerewed up schema that needs to be re-done
properly from scratch.

And I'm not even getting paid for it! <<

No good deed goes unpunished.

[Larry Menard]

Thanks Joe & Serge.

I've boned up a bit more on UDFs (Don Chamberlin's "Complete Guide to DB2" book), and I think I should be able to implement this fairly easily using a java scalar SCRATCHPAD UDF.

In Don's book he says (paraphrased):

In statement "SELECT foo(col1) FROM tb1", function "foo" is invoked for each value in column "col1".
So I think that on each invocation, my UDF should:

if (scratchpad is empty) then
{
simply copy all 32 bits of current row value into the scratchpad.
}
else
{
for (each bit of the current scratchpad string contents)
{
if (the appropriate bit in the scratchpad contents is already "non-0") then
{
nop.
}
else // scratchpad bit must be "0"
{
if (the corresponding bit in the current row value is also "0") then
{
set the appropriate bit in the scratchpad string to "0".
}
else // row value bit is "non-0"
{
set the appropriate bit in the scratchpad string to the value of the bit in the current row value.
}
}
}
}
return (current scratchpad string)

And the SQL invocation should remain pretty much unchanged:

SELECT BIT_OR(mycolumn) FROM ...

But I'm not 100% sure of one thing:

Note that my UDF always returns the current scratchpad string. If the UDF is re-invoked for each row value in the column, does the overall SQL statement ever see the returned values for the interim invocations, or does it see only the value returned by the last invocation?

Thanks.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"--CELKO--" <jc*******@earthlink.net> wrote in message news:11*********************@o13g2000cwo.googlegro ups.com... Generally speaking you can do aggregate function by putting expressions
into the SUM(), MIN(), or MAX() functions. Here is a version of the
aggregate product function in SQL. You will need to have the logorithm
and exponential functions. They are not standards, but they are very
common. The idea is that there are three special cases - all positive
numbers, one or more zeroes, and some negative numbers in the set.
Within the case where you have negative numbers, there are two
sub-cases: (1) an even number of negatives or (2) an odd number of
negatives. You then need to apply some High School algebra to
determine the sign of the final result.

SELECT CASE MIN(ABS(SIGN(nbr)))
WHEN 0 THEN 0.00 -- some zeroes
ELSE -- no zeroes
EXP(SUM(LOG(ABS(NULLIF(nbr, 0)))))
* CASE WHEN MOD (CAST(SUM(ABS(SIGN(nbr)-1)/2) AS INTEGER), 2)
= 1
THEN -1.00 ELSE 1.00 END
END AS big_pi

However, in your particular situation, you are stuck because some diot
thought that SQL is a language for bit fiddling and not a high level
database language. Nobody uses bits and bytes in SQL. The real
problem is a completely scerewed up schema that needs to be re-done
properly from scratch.

And I'm not even getting paid for it! <<

No good deed goes unpunished.

[Larry Menard]

Never mind, I take back my question. Thanks.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:2Z******************************@rogers.com.. .
Thanks Joe & Serge.

I've boned up a bit more on UDFs (Don Chamberlin's "Complete Guide to DB2" book), and I think I should be able to implement this fairly easily using a java scalar SCRATCHPAD UDF.

In Don's book he says (paraphrased):

In statement "SELECT foo(col1) FROM tb1", function "foo" is invoked for each value in column "col1".
So I think that on each invocation, my UDF should:

if (scratchpad is empty) then
{
simply copy all 32 bits of current row value into the scratchpad.
}
else
{
for (each bit of the current scratchpad string contents)
{
if (the appropriate bit in the scratchpad contents is already "non-0") then
{
nop.
}
else // scratchpad bit must be "0"
{
if (the corresponding bit in the current row value is also "0") then
{
set the appropriate bit in the scratchpad string to "0".
}
else // row value bit is "non-0"
{
set the appropriate bit in the scratchpad string to the value of the bit in the current row value.
}
}
}
}
return (current scratchpad string)

And the SQL invocation should remain pretty much unchanged:

SELECT BIT_OR(mycolumn) FROM ...

But I'm not 100% sure of one thing:

Note that my UDF always returns the current scratchpad string. If the UDF is re-invoked for each row value in the column, does the overall SQL statement ever see the returned values for the interim invocations, or does it see only the value returned by the last invocation?

Thanks.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"--CELKO--" <jc*******@earthlink.net> wrote in message news:11*********************@o13g2000cwo.googlegro ups.com... Generally speaking you can do aggregate function by putting expressions
into the SUM(), MIN(), or MAX() functions. Here is a version of the
aggregate product function in SQL. You will need to have the logorithm
and exponential functions. They are not standards, but they are very
common. The idea is that there are three special cases - all positive
numbers, one or more zeroes, and some negative numbers in the set.
Within the case where you have negative numbers, there are two
sub-cases: (1) an even number of negatives or (2) an odd number of
negatives. You then need to apply some High School algebra to
determine the sign of the final result.

SELECT CASE MIN(ABS(SIGN(nbr)))
WHEN 0 THEN 0.00 -- some zeroes
ELSE -- no zeroes
EXP(SUM(LOG(ABS(NULLIF(nbr, 0)))))
* CASE WHEN MOD (CAST(SUM(ABS(SIGN(nbr)-1)/2) AS INTEGER), 2)
= 1
THEN -1.00 ELSE 1.00 END
END AS big_pi

However, in your particular situation, you are stuck because some diot
thought that SQL is a language for bit fiddling and not a high level
database language. Nobody uses bits and bytes in SQL. The real
problem is a completely scerewed up schema that needs to be re-done
properly from scratch.

And I'm not even getting paid for it! <<

No good deed goes unpunished.

[Knut Stolze]

Larry Menard wrote:

Thanks, Serge. I was under the impression (reinforced by Knut's
article)
that recursive queries should be avoided.

Really, I didn't want to say that.

And to answer your other question: no, you don't need structured types for
the aggregation. That was just meant as an example.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany