Breaking the limits...
A developer hit "SQL Error (-528): Maximum output rowsize (32767) exceeded" with a Java/Spring/Hibernate app against IDS 11.50 on RHEL5, and asked whether the 32K limit could be raised. Art Kagel and Eric Rowell said no — it's a hard limit — so the query must be split or the returned data reduced. Fernando Nunes and Jonathan Leffler suggested switching large columns (e.g. LVARCHAR) to BYTE/TEXT/BLOB/CLOB, which count only ~56-64 bytes toward the row size; Nunes also floated trying DRDA. The poster opted to break the query into smaller parts; no further outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello everyone I have a small problem with an application we are developing under Java (Spring / Hibernate). The RDBMS is IDS 11.50 on RHEL5. In some cases the results returned by Hibernate querys exceed 32K of data and I get the following error: SQL Error (-528): Maximum output rowsize (32767) exceeded. Apart from the recommendations of IBM about the partitioning of these querys, Is there a way to increase that 32k limit? Best regards and thanks in advance. -- Javier Perez Arenal - jperez@uniovi.es Jefe del Area Técnica de Informática y Comunicaciones Vicerrectorado de Informática y Comunicaciones Universidad de Oviedo Edificio Severo Ochoa C/ Fernando Bongera s/n, Campus del Cristo 33006 - Oviedo, Asturias --Boundary_(ID_mENLkoPfhVILD97uZOIPhQ)
Javier, I'm only aware of one way to solve this and that is to make the return data smaller. Breaking the query up into logical parts or making sure the returned data doesn't exceed the limit are the only ways I'm aware of. I don't think there is a case to try and get the limit increased. Sadly I have never run into this issue. Eric B. Rowell 2009/11/9 Javier Pérez Arenal <jperez@uniovi.es> > Hello everyone > > I have a small problem with an application we are developing under Java > (Spring / Hibernate). The RDBMS is IDS 11.50 on RHEL5. > > In some cases the results returned by Hibernate querys exceed 32K of data > and I get the following error: > > SQL Error (-528): Maximum output rowsize (32767) exceeded. > > Apart from the recommendations of IBM about the partitioning of these > querys, Is there a way to increase that 32k limit? > > Best regards and thanks in advance. > > -- > > Javier Perez Arenal - jperez@uniovi.es > > Jefe del Area Técnica de Informática y Comunicaciones > > Vicerrectorado de Informática y Comunicaciones > > Universidad de Oviedo > > Edificio Severo Ochoa > > C/ Fernando Bongera s/n, Campus del Cristo > > 33006 - Oviedo, Asturias > > --Boundary_(ID_mENLkoPfhVILD97uZOIPhQ) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell --0016e6d464cf4a0f040477f505f3
No. The 32K is a hard limit. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. 2009/11/9 Javier Pérez Arenal <jperez@uniovi.es> > Hello everyone > > I have a small problem with an application we are developing under Java > (Spring / Hibernate). The RDBMS is IDS 11.50 on RHEL5. > > In some cases the results returned by Hibernate querys exceed 32K of data > and I get the following error: > > SQL Error (-528): Maximum output rowsize (32767) exceeded. > > Apart from the recommendations of IBM about the partitioning of these > querys, Is there a way to increase that 32k limit? > > Best regards and thanks in advance. > > -- > > Javier Perez Arenal - jperez@uniovi.es > > Jefe del Area Técnica de Informática y Comunicaciones > > Vicerrectorado de Informática y Comunicaciones > > Universidad de Oviedo > > Edificio Severo Ochoa > > C/ Fernando Bongera s/n, Campus del Cristo > > 33006 - Oviedo, Asturias > > --Boundary_(ID_mENLkoPfhVILD97uZOIPhQ) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747b76e1c8ae60477f54055
Thanks for your fast answers Art & Eric. I'll try to break the query in smaller parts... -- Javier Perez Arenal - jperez@uniovi.es Jefe del Area Técnica de Informática y Comunicaciones Vicerrectorado de Informática y Comunicaciones Universidad de Oviedo Edificio Severo Ochoa C/ Fernando Bongera s/n, Campus del Cristo 33006 - Oviedo, Asturias -----Mensaje original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de Art Kagel Enviado el: lunes, 09 de noviembre de 2009 20:32 Para: ids@iiug.org Asunto: Re: Breaking the limits... [18016] No. The 32K is a hard limit. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. 2009/11/9 Javier Pérez Arenal <jperez@uniovi.es> > Hello everyone > > I have a small problem with an application we are developing under Java > (Spring / Hibernate). The RDBMS is IDS 11.50 on RHEL5. > > In some cases the results returned by Hibernate querys exceed 32K of data > and I get the following error: > > SQL Error (-528): Maximum output rowsize (32767) exceeded. > > Apart from the recommendations of IBM about the partitioning of these > querys, Is there a way to increase that 32k limit? > > Best regards and thanks in advance. > > -- > > Javier Perez Arenal - jperez@uniovi.es > > Jefe del Area Técnica de Informática y Comunicaciones > > Vicerrectorado de Informática y Comunicaciones > > Universidad de Oviedo > > Edificio Severo Ochoa > > C/ Fernando Bongera s/n, Campus del Cristo > > 33006 - Oviedo, Asturias > > --Boundary_(ID_mENLkoPfhVILD97uZOIPhQ) > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747b76e1c8ae60477f54055 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Can you change some long fields to CLOB for example? Also, can you check that the length of the data returned is effectively greater that 32K? You don't mention the fixpack level... and some string operations that resulted in relatively small sizes could raise this error in pre 11.50.xC3 versions.... And a shot in the dark, since I couldn't find any info about this (maybe others can comment), can you try to setup the application/server to use DRDA? I'm not sure if the limit is internal (engine itself) or at the protocol level... Regards. 2009/11/9 Javier Pérez Arenal <jperez@uniovi.es> > Hello everyone > > I have a small problem with an application we are developing under Java > (Spring / Hibernate). The RDBMS is IDS 11.50 on RHEL5. > > In some cases the results returned by Hibernate querys exceed 32K of data > and I get the following error: > > SQL Error (-528): Maximum output rowsize (32767) exceeded. > > Apart from the recommendations of IBM about the partitioning of these > querys, Is there a way to increase that 32k limit? > > Best regards and thanks in advance. > > -- > > Javier Perez Arenal - jperez@uniovi.es > > Jefe del Area Técnica de Informática y Comunicaciones > > Vicerrectorado de Informática y Comunicaciones > > Universidad de Oviedo > > Edificio Severo Ochoa > > C/ Fernando Bongera s/n, Campus del Cristo > > 33006 - Oviedo, Asturias > > --Boundary_(ID_mENLkoPfhVILD97uZOIPhQ) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd5731478dda404780247b3
Hi Fernando The fixpak is FC5... I'll give a try to your suggestion of set DRDA at the application server, but i don't have much spectatives of success... Thanks at all! -- Javier Perez Arenal - jperez@uniovi.es Jefe del Area Técnica de Informática y Comunicaciones Vicerrectorado de Informática y Comunicaciones Universidad de Oviedo Edificio Severo Ochoa C/ Fernando Bongera s/n, Campus del Cristo 33006 - Oviedo, Asturias -----Mensaje original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de Fernando Nunes Enviado el: martes, 10 de noviembre de 2009 12:05 Para: ids@iiug.org Asunto: Re: Breaking the limits... [18027] Can you change some long fields to CLOB for example? Also, can you check that the length of the data returned is effectively greater that 32K? You don't mention the fixpack level... and some string operations that resulted in relatively small sizes could raise this error in pre 11.50.xC3 versions.... And a shot in the dark, since I couldn't find any info about this (maybe others can comment), can you try to setup the application/server to use DRDA? I'm not sure if the limit is internal (engine itself) or at the protocol level... Regards. 2009/11/9 Javier Pérez Arenal <jperez@uniovi.es> > Hello everyone > > I have a small problem with an application we are developing under Java > (Spring / Hibernate). The RDBMS is IDS 11.50 on RHEL5. > > In some cases the results returned by Hibernate querys exceed 32K of data > and I get the following error: > > SQL Error (-528): Maximum output rowsize (32767) exceeded. > > Apart from the recommendations of IBM about the partitioning of these > querys, Is there a way to increase that 32k limit? > > Best regards and thanks in advance. > > -- > > Javier Perez Arenal - jperez@uniovi.es > > Jefe del Area Técnica de Informática y Comunicaciones > > Vicerrectorado de Informática y Comunicaciones > > Universidad de Oviedo > > Edificio Severo Ochoa > > C/ Fernando Bongera s/n, Campus del Cristo > > 33006 - Oviedo, Asturias > > --Boundary_(ID_mENLkoPfhVILD97uZOIPhQ) > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd5731478dda404780247b3 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
2009/11/9 Javier Pérez Arenal <jperez@uniovi.es> > I have a small problem with an application we are developing under Java > (Spring / Hibernate). The RDBMS is IDS 11.50 on RHEL5. > > In some cases the results returned by Hibernate querys exceed 32K of data > and I get the following error: > > SQL Error (-528): Maximum output rowsize (32767) exceeded. > > Apart from the recommendations of IBM about the partitioning of these > queries, Is there a way to increase that 32k limit? > Consider whether your field types are appropriate. Could you (and should you) use BYTE or TEXT or BLOB or CLOB types for some of the values instead of LVARCHAR, for example? Your data row is still limited to 32K, but the BYTE/TEXT columns can be up to 2 GB each (and only count as 56 bytes towards the row size), and the BLOB/CLOB columns can be larger (and, IIRC, count as 64 bytes towards the row size). However, there isn't a way to bypass the 32K limit otherwise. Can you elaborate on how many columns you are selecting and indicate roughly what the mix of types is? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. Marie von Ebner-Eschenbach<http://www.brainyquote.com/quotes/authors/m/marie_von_ebneresch enbac.html> - "Even a stopped clock is right twice a day." --000e0cd20a5cb30a0504780e5f3a