P: n/a

One of my customers have a sql statement totaled more than 400 lines,
about 40KB. when excuted, error arrised saying "SQL0101N The statement
is too long or too complex".
I tried one of his functions,
create function xxxx(x1 decimal(20,2),x2 decimal(20,2),x3
decimal(20,2),x4 decimal(20,2),x5 decimal(20,2),x6 deci
mal(20,2))
returns decimal(20,2)
language sql
begin atomic
declare Result decimal(20,2);
set Result=0;
if x1=x2 and x1=x3 and x1=x4 and x1=x5 and x1=x6
then
return 0;
end if;
if x1<x2 then set Result=Result+1; end if;
if x1<x3 then set Result=Result+1; end if;
if x1<x4 then set Result=Result+1; end if;
if x1<x5 then set Result=Result+1; end if;
if x1<x6 then set Result=Result+1; end if;
if x2<x3 then set Result=Result+1; end if;
if x2<x4 then set Result=Result+1; end if;
if x2<x5 then set Result=Result+1; end if;
if x2<x6 then set Result=Result+1; end if;
if x3<x4 then set Result=Result+1; end if;
if x3<x5 then set Result=Result+1; end if;
if x3<x6 then set Result=Result+1; end if;
if x4<x5 then set Result=Result+1; end if;
if x4<x6 then set Result=Result+1; end if;
if x5<x6 then set Result=Result+1; end if;
set Result=4*Result/301;
return Result;
end
of course it has inferior efficiency, but when I call it 20 times from
a sql statement, it raise the same error.
since I have little develop experience so I hasn't clear knowledge on
what's the limit,can anyone explain it for me in a little detail? thank
you:)  
Share this Question
P: n/a

Hardy wrote: One of my customers have a sql statement totaled more than 400 lines, about 40KB. when excuted, error arrised saying "SQL0101N The statement is too long or too complex".
I tried one of his functions,
create function xxxx(x1 decimal(20,2),x2 decimal(20,2),x3 decimal(20,2),x4 decimal(20,2),x5 decimal(20,2),x6 deci mal(20,2)) returns decimal(20,2) language sql begin atomic declare Result decimal(20,2);
set Result=0; if x1=x2 and x1=x3 and x1=x4 and x1=x5 and x1=x6 then return 0; end if; if x1<x2 then set Result=Result+1; end if; if x1<x3 then set Result=Result+1; end if; if x1<x4 then set Result=Result+1; end if; if x1<x5 then set Result=Result+1; end if; if x1<x6 then set Result=Result+1; end if; if x2<x3 then set Result=Result+1; end if; if x2<x4 then set Result=Result+1; end if; if x2<x5 then set Result=Result+1; end if; if x2<x6 then set Result=Result+1; end if; if x3<x4 then set Result=Result+1; end if; if x3<x5 then set Result=Result+1; end if; if x3<x6 then set Result=Result+1; end if; if x4<x5 then set Result=Result+1; end if; if x4<x6 then set Result=Result+1; end if; if x5<x6 then set Result=Result+1; end if; set Result=4*Result/301; return Result; end
of course it has inferior efficiency, but when I call it 20 times from a sql statement, it raise the same error.
since I have little develop experience so I hasn't clear knowledge on what's the limit,can anyone explain it for me in a little detail? thank you:)
Keep in mind that SQL Functions are macroexpanded into the query.
You likely have to increase the statement heap. (and after that the
package cache, ...
But let's fix thsi at the root:
create function xxxx(x1 decimal(20,2),
x2 decimal(20,2),
x3 decimal(20,2),
x4 decimal(20,2),
x5 decimal(20,2),
x6 decimal(20,2))
returns decimal(20,2)
language sql
RETURN CASE WHEN x1=x2 and x1=x3 and x1=x4 and x1=x5 and x1=x6
THEN 0
ELSE CASE WHEN x1 < x2 THEN 1 ELSE 0 END
+ CASE WHEN x1 < x3 THEN 1 ELSE 0 END
+ you get the picture
END
Now, this puppy will fly and live on thin air.
Cheers
Serge

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab  
P: n/a

thank you, Serge. it's a much better version.
for the big sql with more than 400 colomns out, equiped with the new
function, now original error disappear and new one comes out asking for
more applheapsz. but I have tune it to 60000, the up limit.
I'm digging into db2 memory model to see if there's a tip.
Serge, how's your opinion?  
P: n/a

Serge Rielau wrote: Hardy wrote: One of my customers have a sql statement totaled more than 400 lines, about 40KB. when excuted, error arrised saying "SQL0101N The statement is too long or too complex".
I tried one of his functions,
create function xxxx(x1 decimal(20,2),x2 decimal(20,2),x3 decimal(20,2),x4 decimal(20,2),x5 decimal(20,2),x6 deci mal(20,2)) returns decimal(20,2) language sql begin atomic declare Result decimal(20,2);
set Result=0; if x1=x2 and x1=x3 and x1=x4 and x1=x5 and x1=x6 then return 0; end if; if x1<x2 then set Result=Result+1; end if; if x1<x3 then set Result=Result+1; end if; if x1<x4 then set Result=Result+1; end if; if x1<x5 then set Result=Result+1; end if; if x1<x6 then set Result=Result+1; end if; if x2<x3 then set Result=Result+1; end if; if x2<x4 then set Result=Result+1; end if; if x2<x5 then set Result=Result+1; end if; if x2<x6 then set Result=Result+1; end if; if x3<x4 then set Result=Result+1; end if; if x3<x5 then set Result=Result+1; end if; if x3<x6 then set Result=Result+1; end if; if x4<x5 then set Result=Result+1; end if; if x4<x6 then set Result=Result+1; end if; if x5<x6 then set Result=Result+1; end if; set Result=4*Result/301; return Result; end
of course it has inferior efficiency, but when I call it 20 times from a sql statement, it raise the same error.
since I have little develop experience so I hasn't clear knowledge on what's the limit,can anyone explain it for me in a little detail? thank you:) Keep in mind that SQL Functions are macroexpanded into the query. You likely have to increase the statement heap. (and after that the package cache, ...
But let's fix thsi at the root: create function xxxx(x1 decimal(20,2), x2 decimal(20,2), x3 decimal(20,2), x4 decimal(20,2), x5 decimal(20,2), x6 decimal(20,2)) returns decimal(20,2) language sql RETURN CASE WHEN x1=x2 and x1=x3 and x1=x4 and x1=x5 and x1=x6 THEN 0 ELSE CASE WHEN x1 < x2 THEN 1 ELSE 0 END + CASE WHEN x1 < x3 THEN 1 ELSE 0 END + you get the picture END
I think this can be simplified even more because the first condition  where
the equality of all 6 values is tested  is actually not needed. The sum
of all the CASE expressions will be 0 if all values are equal.
The question is if the equality is so common that a special handling up
front will be beneficial.

Knut Stolze
DB2 Information Integration Development
IBM Germany  
P: n/a

Hardy wrote: thank you, Serge. it's a much better version.
for the big sql with more than 400 colomns out, equiped with the new function, now original error disappear and new one comes out asking for more applheapsz. but I have tune it to 60000, the up limit.
I'm digging into db2 memory model to see if there's a tip.
Serge, how's your opinion?
You blow the app limit with a single SQL statement after my rewrite?
I'm speechless. Care to share a bit more about the query.
This function of mine times 400 should not stretch the limits by itself.
Cheers
Serge

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab  
P: n/a

Hardy wrote: One of my customers have a sql statement totaled more than 400 lines, about 40KB. when excuted, error arrised saying "SQL0101N The statement is too long or too complex".
I tried one of his functions,
create function xxxx(x1 decimal(20,2),x2 decimal(20,2),x3 decimal(20,2),x4 decimal(20,2),x5 decimal(20,2),x6 deci mal(20,2)) returns decimal(20,2) language sql begin atomic declare Result decimal(20,2);
set Result=0; if x1=x2 and x1=x3 and x1=x4 and x1=x5 and x1=x6 then return 0; end if; if x1<x2 then set Result=Result+1; end if; if x1<x3 then set Result=Result+1; end if; if x1<x4 then set Result=Result+1; end if; if x1<x5 then set Result=Result+1; end if; if x1<x6 then set Result=Result+1; end if; if x2<x3 then set Result=Result+1; end if; if x2<x4 then set Result=Result+1; end if; if x2<x5 then set Result=Result+1; end if; if x2<x6 then set Result=Result+1; end if; if x3<x4 then set Result=Result+1; end if; if x3<x5 then set Result=Result+1; end if; if x3<x6 then set Result=Result+1; end if; if x4<x5 then set Result=Result+1; end if; if x4<x6 then set Result=Result+1; end if; if x5<x6 then set Result=Result+1; end if; set Result=4*Result/301; return Result; end
of course it has inferior efficiency, but when I call it 20 times from a sql statement, it raise the same error.
since I have little develop experience so I hasn't clear knowledge on what's the limit,can anyone explain it for me in a little detail? thank you:)
Here is a shorter equivalent function, without the long IF .. sequence;
I am not sure if it will be faster, but you may try.
N. Shamsundar
University of Houston
__________________________________________________ ___________________
create function xxxx(x1 decimal(20,2),x2 decimal(20,2),
x3 decimal(20,2),x4 decimal(20,2),
x5 decimal(20,2),x6 decimal(20,2))
returns decimal(20,2)
language sql
reads sql data
begin atomic
return with tt(sno,x) as
(values (1,x1),(2,x2),(3,x3),(4,x4),(5,x5),(6,x6))
select count(1)*4.0/301
from tt t1 inner join tt t2
on t1.sno < t2.sno and t1.x < t2.x;
end
@  
P: n/a

thank you very much and sorry for late "thank you":)
developers're working on part the sql,hehe   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 5089
 replies: 6
 date asked: Nov 12 '05
