Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Posts
    14

    Unanswered: Statement too long

    Hi,

    I am writing a stored procedure in DB2 UDB v8.1. It has more than 2000 lines of code. When build, it throws error:

    -----------------------------------------------------------------------
    DB201.p_testing - Build started.
    DROP SPECIFIC PROCEDURE DB201.SQL030904103642547
    DB201.p_testing - Drop stored procedure completed.
    Create stored procedure returns -101.
    [IBM][CLI Driver][DB2/SUN64] SQL0101N The statement is too long or too complex. SQLSTATE=54001

    DB201.p_testing - Build failed.
    DB201.p_testing - Roll back completed successfully.
    ------------------------------------------------------------------------

    Does any one have ideas about the maximum length of a stored procedure? Many thx.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Statement too long

    I remember the max length is 64 KB

    BTW , what version are you on?

    Cheers
    Sathyaram

    Originally posted by db2worker
    Hi,

    I am writing a stored procedure in DB2 UDB v8.1. It has more than 2000 lines of code. When build, it throws error:

    -----------------------------------------------------------------------
    DB201.p_testing - Build started.
    DROP SPECIFIC PROCEDURE DB201.SQL030904103642547
    DB201.p_testing - Drop stored procedure completed.
    Create stored procedure returns -101.
    [IBM][CLI Driver][DB2/SUN64] SQL0101N The statement is too long or too complex. SQLSTATE=54001

    DB201.p_testing - Build failed.
    DB201.p_testing - Roll back completed successfully.
    ------------------------------------------------------------------------

    Does any one have ideas about the maximum length of a stored procedure? Many thx.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I searched the 8.1 manuals and the web and there I found nothing that definitively stated the maximum size of a SQL stored procedure. Although if you look at the catalog, IBM stored the text of the SQL procedure in a 1MB CLOB, so inferring from that, I would say that the limit is 1MB.

    I have had the same error on building some stored procedures (none nearly as long as 2000 lines), and increasing the stmtheap DB parameter fixed it.

    HTH

    Andy

  4. #4
    Join Date
    Feb 2003
    Posts
    20
    I think you are receiving the error during precompilation. Are you using the default configuration? You should try to increase the stmtheap.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Andy,

    Neither could I find the info ...

    I based my assumption on the maximum length of an SQL Statement ...
    But if my assumption is right, isn't it wierd to have a 1 MB clob for SQL Statement text

    sathyaram

    Originally posted by ARWinner
    I searched the 8.1 manuals and the web and there I found nothing that definitively stated the maximum size of a SQL stored procedure. Although if you look at the catalog, IBM stored the text of the SQL procedure in a 1MB CLOB, so inferring from that, I would say that the limit is 1MB.

    I have had the same error on building some stored procedures (none nearly as long as 2000 lines), and increasing the stmtheap DB parameter fixed it.

    HTH

    Andy
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Dec 2002
    Posts
    134

    Re: Statement too long

    I think the problem is not with the length, but with the complexity of the SQL inside procedure.

    If you do "db2 ? SQL0101N" you can get some suggestions on how to fix the problem. In short it says

    1. simplify sql
    2. increase stmtheap (used for access plan creation)
    3. reduce triggers, constraints affected by the SQL

    If I'm not mistaken, reducing query optimization level can help as well (be carefull here).

    regards,
    dmitri

  7. #7
    Join Date
    Jul 2003
    Posts
    14
    Hi,

    Finally I got a little hint on this.

    The max length of source code(SQL procedure) for UNIX/Windows/OS2 is 64KB.

    Btw, I have tried to adjust the STMTHEAP, however, even I used its maximum value, it doesn't work.

    db2worker

  8. #8
    Join Date
    Jun 2003
    Posts
    8
    We've seen this error also with triggers, when you have multiple INSERT/UPDATE statements in the same trigger, even if they are in IF clauses.
    The problem is that DB2 has to allocate memory for those statements too, because it doesnot know which one will execute.
    We've solved this by using local variables in triggers and setting their values along the execution path. Then you usually come to one or two INSERT/UPDATE statements. And the upgrade to 8.1 helped too. )

    Hope this helps.

  9. #9
    Join Date
    Sep 2003
    Posts
    4
    According to book "DB2 SQL Procedural Language for Linux, Unix and Windows - IBM Press" Pg.306, there is statement clearly stated that the maximun source code size for SQL stored procedure is 64KB.

    And the type for STMTHEAP is unsigned short number, the maximun value is 65536 which is equivalent to 64K.

    Btw, we're just wondering whether this statement is valid for both 32 bits & 64 bits DB2 on Unix.

  10. #10
    Join Date
    Sep 2003
    Posts
    4
    Btw, SQL code size for Sybase 12.5 is 16M, MSSQL2000 and Oracle 9i are 250M. Thus, 64K seems......

Posting Permissions

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