437,611 Members | 1,693 Online 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
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

 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

 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

 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. 