By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,611 Members | 1,693 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,611 IT Pros & Developers. It's quick & easy.

SQL0101N The statement is too long or too complex

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/30-1;
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:)

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


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/30-1;
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 macro-expanded 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
Nov 12 '05 #2

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?

Nov 12 '05 #3

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/30-1;
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 macro-expanded 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
Nov 12 '05 #4

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
Nov 12 '05 #5

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/30-1;
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/30-1
from tt t1 inner join tt t2
on t1.sno < t2.sno and t1.x < t2.x;
end
@

Nov 12 '05 #6

P: n/a
thank you very much and sorry for late "thank you":)

developers're working on part the sql,hehe

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.