Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Unanswered: The statement is too long or too complex.

    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

  2. #2
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58
    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

  3. #3
    Join Date
    Feb 2004
    Posts
    107
    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

  4. #4
    Join Date
    Feb 2004
    Posts
    107
    i find the parameters and their default sizes by executing the below command... just fyi...

    db2 get db cfg for <database-name>

    Jake

  5. #5
    Join Date
    Feb 2004
    Posts
    107

    Still have the problem...

    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

  6. #6
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58
    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?

  7. #7
    Join Date
    Feb 2004
    Posts
    107

    What's the max size of stored proc????

    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

  8. #8
    Join Date
    Sep 2002
    Posts
    456
    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

  9. #9
    Join Date
    Feb 2004
    Posts
    107
    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

  10. #10
    Join Date
    Feb 2003
    Posts
    24

    Stored Procedure

    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.

  11. #11
    Join Date
    Feb 2004
    Posts
    107
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •