Statement length exceeds maximum (In Procedure)
A user couldn't create a 103KB stored procedure, hitting error "-460: Statement length exceeds maximum" due to the 64KB limit on CREATE PROCEDURE. Responders explained the 64KB cap applies to older versions, but that the SQL/SPL statement limit was raised (to effectively 2GB/4GB) in 11.70.xC5 and later, including 12.10, so upgrading solves it. Workarounds until then: split the procedure body into several sub-procedures each under 64KB, or shrink the text by stripping whitespace and shortening variable names. One poster also suggested creating the procedure through a DRDA listener.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello All, I have a procedure which is 103KB, now when i am trying to do CREATE PROCEDURE it gives error "460: Statement length exceeds maximum." I googled a bit then I come across "entire length of a CREATE PROCEDURE statement must be less than 64 kilobytes." Now is there a way to increase this size (64 KB). or is there a something else that is causing this error. please give help me out here.
AFAIK this is the current hard limit Cheers Paul Paul Watson Oninit www.oninit.com +1 913 387 7529 On Jun 13, 2013, at 7:34, "PRIYANKA MOKHADKAR" <priyanka@avantisoftware.in> w= rote: > Hello All,=20 >=20 > I have a procedure which is 103KB, now when i am trying to do CREATE PROCE= DURE=20 >=20 > it gives error "460: Statement length exceeds maximum." I googled a bit th= en I=20 >=20 > come across "entire length of a CREATE PROCEDURE statement must be less th= an=20 > 64=20 >=20 > kilobytes."=20 >=20 > Now is there a way to increase this size (64 KB). or is there a something=20= >=20 > else that is causing this error. please give help me out here.=20 >=20 >=20 > **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20
On 13/06/13 13:34, PRIYANKA MOKHADKAR wrote: > Hello All, > > I have a procedure which is 103KB, now when i am trying to do CREATE PROCEDURE > > it gives error "460: Statement length exceeds maximum." I googled a bit then I > > come across "entire length of a CREATE PROCEDURE statement must be less than > 64 > > kilobytes." > > Now is there a way to increase this size (64 KB). or is there a something > > else that is causing this error. please give help me out here. What you can do is split the procedure body into multiple subprocedures (each less than 64k) and have the procedure call each of the subprocedures in sequence -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Hello. In Informix 11.70.xC5, SQL and SPL routines were improved to max length of 4GB, instead of 64KB of previous releases. http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.po.doc/new_f eatures.htm#xc_5__SQLS Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: priyanka@avantisoftware.in > Subject: Statement length exceeds maximum (In Procedure) [30519] > Date: Thu, 13 Jun 2013 08:34:17 -0400 > > Hello All, > > I have a procedure which is 103KB, now when i am trying to do CREATE PROCEDURE > > it gives error "460: Statement length exceeds maximum." I googled a bit then I > > come across "entire length of a CREATE PROCEDURE statement must be less than > 64 > > kilobytes." > > Now is there a way to increase this size (64 KB). or is there a something > > else that is causing this error. please give help me out here. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Hi, Indeed, you cannot have a stored procedure which is greater than 64 Kb. At my knowledge, the only solution : Split your procedure into several procedures which are smaller than 64 Kb. Regards, Le 13/06/2013 14:34, PRIYANKA MOKHADKAR a écrit : > Hello All, > > I have a procedure which is 103KB, now when i am trying to do CREATE PROCEDURE > > it gives error "460: Statement length exceeds maximum." I googled a bit then I > > come across "entire length of a CREATE PROCEDURE statement must be less than > 64 > > kilobytes." > > Now is there a way to increase this size (64 KB). or is there a something > > else that is causing this error. please give help me out here. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Franck Thomas ConsultiX franck.thomas@consult-ix.fr http://www.consult-ix.fr Téléphone : 33 (0) 1 39 12 18 00 Mobile : 33 (0) 6 78 81 09 33 Fax : 33 (0) 1 39 12 18 18
Upgrade to 12.10, the statement limit was finally raised to 2GB. Meanwhile, until you upgrade, the only thing you can do is either: - Compress out as much white space as you can and make local variable names shorter to get under the 64K limit in your version, or - Rip out blocks of contiguous code to sub-procedures. The guts of a loop is an ideal target for extraction to a sub-procedure/function, though if you can extract non-loop code that will be more efficient. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jun 13, 2013 at 8:34 AM, PRIYANKA MOKHADKAR < priyanka@avantisoftware.in> wrote: > Hello All, > > I have a procedure which is 103KB, now when i am trying to do CREATE > PROCEDURE > > it gives error "460: Statement length exceeds maximum." I googled a bit > then I > > come across "entire length of a CREATE PROCEDURE statement must be less > than > 64 > > kilobytes." > > Now is there a way to increase this size (64 KB). or is there a something > > else that is causing this error. please give help me out here. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93d8ede7bcb3704df0961ff
But as someone else has posted I really need to read the release notes more often :-) Paul Watson Oninit www.oninit.com +1 913 387 7529 On Jun 13, 2013, at 8:23, "Paul Watson" <paul@oninit.com> wrote: > AFAIK this is the current hard limit > > Cheers > Paul > > Paul Watson > Oninit www.oninit.com > +1 913 387 7529 > > On Jun 13, 2013, at 7:34, "PRIYANKA MOKHADKAR" <priyanka@avantisoftware.in> w= > rote: > >> Hello All,=20 >> =20 >> I have a procedure which is 103KB, now when i am trying to do CREATE PROCE= > DURE=20 >> =20 >> it gives error "460: Statement length exceeds maximum." I googled a bit th= > en I=20 >> =20 >> come across "entire length of a CREATE PROCEDURE statement must be less th= > an=20 >> 64=20 >> =20 >> kilobytes."=20 >> =20 >> Now is there a way to increase this size (64 KB). or is there a > something=20= > >> =20 >> else that is causing this error. please give help me out here.=20 >> =20 >> =20 >> **************************************************************************= > *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.
Alexandre is correct, I forgot that the SQL size limit was raised in 11.70.xC5 and later. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jun 13, 2013 at 9:48 AM, Art Kagel <art.kagel@gmail.com> wrote: > Upgrade to 12.10, the statement limit was finally raised to 2GB. > Meanwhile, until you upgrade, the only thing you can do is either: > > - Compress out as much white space as you can and make local variable > > names shorter to get under the 64K limit in your version, or > > - Rip out blocks of contiguous code to sub-procedures. The guts of a > > loop is an ideal target for extraction to a sub-procedure/function, though > > if you can extract non-loop code that will be more efficient. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Thu, Jun 13, 2013 at 8:34 AM, PRIYANKA MOKHADKAR < > priyanka@avantisoftware.in> wrote: > > > Hello All, > > > > I have a procedure which is 103KB, now when i am trying to do CREATE > > PROCEDURE > > > > it gives error "460: Statement length exceeds maximum." I googled a bit > > then I > > > > come across "entire length of a CREATE PROCEDURE statement must be less > > than > > 64 > > > > kilobytes." > > > > Now is there a way to increase this size (64 KB). or is there a something > > > > else that is causing this error. please give help me out here. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae93d8ede7bcb3704df0961ff > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c26a30bd438a04df09de9b
I didn't check your version, but assuming it's currently supported, create a DRDA listener and use it to create the procedure. Regards On Jun 13, 2013 3:24 PM, "Art Kagel" <art.kagel@gmail.com> wrote: > Alexandre is correct, I forgot that the SQL size limit was raised in > 11.70.xC5 and later. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Thu, Jun 13, 2013 at 9:48 AM, Art Kagel <art.kagel@gmail.com> wrote: > > > Upgrade to 12.10, the statement limit was finally raised to 2GB. > > Meanwhile, until you upgrade, the only thing you can do is either: > > > > - Compress out as much white space as you can and make local variable > > > > names shorter to get under the 64K limit in your version, or > > > > - Rip out blocks of contiguous code to sub-procedures. The guts of a > > > > loop is an ideal target for extraction to a sub-procedure/function, > though > > > > if you can extract non-loop code that will be more efficient. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > > other organization with which I am associated either explicitly, > > implicitly, or by inference. Neither do those opinions reflect those of > > other individuals affiliated with any entity with which I am affiliated > nor > > those of the entities themselves. > > > > On Thu, Jun 13, 2013 at 8:34 AM, PRIYANKA MOKHADKAR < > > priyanka@avantisoftware.in> wrote: > > > > > Hello All, > > > > > > I have a procedure which is 103KB, now when i am trying to do CREATE > > > PROCEDURE > > > > > > it gives error "460: Statement length exceeds maximum." I googled a bit > > > then I > > > > > > come across "entire length of a CREATE PROCEDURE statement must be less > > > than > > > 64 > > > > > > kilobytes." > > > > > > Now is there a way to increase this size (64 KB). or is there a > something > > > > > > else that is causing this error. please give help me out here. > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --14dae93d8ede7bcb3704df0961ff > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c26a30bd438a04df09de9b > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0112c63a15e68904df0a4111