OK, several issues in this one.
1. Your DigitsOnly() function is saying to reserve 8K per invocation, VARCHAR(4000) in
and VARCHAR(4000) out. Your NumericDiffText() function says to reserve VARCHAR(256) in
and VARCHAR(256) out, plus the size of DECIMAL(25,10). Your invocatins SQL statement
says to call NDiffText() thrice per row, which calls DigitsOnly() twice per invocation, means the
call is chewing through (3 * 512) + (6 * 8K) = 25K per row. VARCHARs are supposed to talk
2 + strlen(), but space has to be reserved for the full size in the heap, just in case. Are you really
expecting numbers that are 4000 digits in length?
2. It would help the SQL processor if you declare the function DigitsOnly() as NOT VARIENT. This
says that calling DigitsOnly() with the same data gets the same results, so only call DigitsOnly()
with the DISTINCT input values. I would have to think harder, but I believe NumericTextDiff()
is NOT VARIENT as well. I always add NO EXTERNAL ACTION to the definition, which may be
a default.
3. I am not convinced I truely understand statement heap. We have some cases that require
HUGE statement heaps for no appearent reason. One database we have STMTHEAP at 15000.
That does not mean that I have put 8G of RAM in the system to run DB2.
4. You should run EXPLAIN on the SQL statement. We thought a statement was small and
simple, but found out that a poorly written trigger was causing a "trigger storm" that was not
intended. Three lines of SQL turned into 100s of lines in the explain diagram.
Hope some of this helps. If not, yell back. I have implemented a similar function as DigitsOnly()
as a C UDF not as a SQL UDF, which may help.
Richard Reppert
On Mon, 20 Jan 2003 15:47:40 -0800, Bill Province wrote:
>Hi Group,
>
>I have an issue that I hope somebody can help me with. We have a
>user-defined function that we have converted from Oracle to DB2. The
>user-defined function has pseudo-code that looks as follows:
>
> NumericTextDiff (textInputA, textInputB)
> Returns DECIMAL(25,10)
> {
> DECIMAL(25,10) numericValueA = TextToNumeric(textInputA);
> DECIMAL(25,10) numericValueB = TextToNumeric(textInputB);
>
> return numericValueA - numericValueB;
> }
>
> The actual code has to do a bit more like handling null input values,
>improper numeric text formats, etc. At one point, it uses the standard
>DECIMAL function, but is not otherwise interesting. (The actual
>function text is given in a follow-on e-mail).
>
>The issue that I am having is that this simple little function is
>apparently consuming heap space in huge quantities.
>
>Assume that I have a table that appears as follows:
>
>Table Foo (
> Numeric fooPrimaryKeySeq PRIMARY KEY,
> Varchar(255) attr1,
> Varchar(255) attr2,
> Varchar(255) attr3,
> Varchar(255) attr4,
> ...
> Varchar(255) attrN
> )
>
> (I'm stating this in a generic fashion, not trying to describe the
>precise DB2 syntax).
>
>If I issue the SQL statement:
>
>Select NumericTextDiff(attr1, attr2) as diff1_2,
> NumericTextDiff(attr1, attr3) as diff1_3,
> NumericTextDiff(attr2, attr3) as diff2_3
>From Foo
>Where fooPrimaryKeySeq = (
> Select min(fooPrimaryKeySeq)
> From Foo
> )
>
>This result will complete successfully.
>
>As soon as I add Select NumericTextDiff(attr1, attr4) as diff1_4, if I
>have stmtheap set to the default value of 4K (16M of memory), the
>process fails due to error SQL0101N (statement too long or complex). If
>I increase the stmtheap size to 8K (32M of memory), I can get a little
>bit further, in terms of the number of times that I can invoke
>NumericTextDiff on the same sql statement, but nowhere near enough to
>explain the consumption of an additional 16M of memory (I can add
>another 3 or 4 invocations).
>
>My question is: Why is it that a User-Defined Function that should
>consume at most 0.5 k per invocation is consuming 32M? What am I
>missing?
>
> -- Bill Province
>
>
Richard J Reppert
Sumx Inc.
972-355-8004 x102
972-355-8048 fax
DB2 & UDB email list listserv db2-l LazyDBA home page