friends,

when i compile a procedure, it says

SQL0101N The statement is too long or too complex.
SQLSTATE=54001

it's a big procedure... i don't know which sql statement is too long... how can i find the sql that makes the issue... also point to me any resource that teaches me how to solve this issue...

Jake

what version of db2 are you using...? V.7 the max SQL statement size is 32 bytes...V.8 is much larger.

I remeber reading somewhere about the maximum size of a stored procedure...don't remember the actual size though. You probably hit it though.

You might want to try break the procedure into two.

Or

increase the heap size:
db2 update db cfg for YOURDB using stmtheap 12000

that's great! i tried the second option as my proc is too big... now it says applheapsz is not enough... can anyone know what are all the parameters are there & what is the default sizes for these parameters for various DB2 UDB products...
i'm using DB2 UDB v8.1.3/Windows 2000

Jake

i find the parameters and their default sizes by executing the below command... just fyi...

db2 get db cfg for <database-name>

Jake

as i told in my previous mail, one procedure get compiled by increasing the stmtheap, but other procedures not getting compiled saying 'The statement is too long or too complex'.. i increased the stmtheap & applheapsz to their max size '60000'... no use... the procedure has only 800 lines.. can anyone help me out... using DB2 v8.1.3 / windows 2000

Jake

Somebody correct me if i'm wrong but isn't the maximum size of a stored proc 32 KB.....?

How many KB is your statement?

all the files that are not getting compiled are of size more than 60kb... the files with less than 60kb get compiled....

so what's the max size of stored procedure in DB2 v8 UDB/windows

Jake

Jake,

If you want to find out the definition of all the parameters your are encountring take a look at the IBM DB2 site below:

http://publib.boulder.ibm.com/infoce...8luw/index.jsp

Type your search string and press enter...!!! If you are getting error with SP, type the error code, there might be some documentation for that as well.

dollar

the max size of stored procedure in DB2 v8 on windows is 64k...
i found this information in the Cross platform SQL procedure development red book at the IBM site
http://www.redbooks.ibm.com/redbooks...45485_148.html

this size seems to be small for the rereal time applications to me... oracle supports larger size though i don't know the exact max size in oracle....

but it forces to more modular programming, meaning splitting the big logic into small small units and put each in a separate procedure....

i don't know whether providing bigger size is the good thing to do...
any comments????

Jake

If Im not mistaken the new release of DB2 v8.2(still in beta) - named Stinger - the stored procedure size has increased to 2MB. Check the IBM website - the search keyword "Stinger" -.

Maybe thats the only way to solve your stored procedure problem, chopping up your complex stored procedure into a group of smaller one. Try to incorporate UDF or another stored procedure into your stored procedure.

thanks for all your help... good to know that stinger will allow sql stmt upto 2MB... i'm going with splitting up the procedures...

Jake