Re: IDS reutrns "Cannot sort rows" after doubling the size of

This error probably indicates you are running out of space to sort the rows, which by default is going to be in the rootdbs, unless you specified a value for DBSPACETEMP in your onconfig or environment. Which means that even if the rootdbs is large enough to hold the tables, it may not be large enough to hold the temporary files created during sorting in addition to the tables. So set DBSPACETEMP in the onconfig file, preferable with multiple dbspaces so that the engine will automatically parallalize the sort using each dbspace on DBSPACETEMP in turn. Alternatively, if you don't have any dbspaces or temp dbspaces ready made you can use the environment variable PSORT_DBTEMP to specify one or more file systems. Rob Z. At 02:39 PM 11/24/2000 -0800, Matthew Denny wrote: >query >X-Mailer: VM 6.43 under 20.4 "Emerald" XEmacs Lucid >Message-ID: <14878.58841.736535.311388@caracas.CS.Berkeley.EDU> > > >I apologixe for sending this message to so many of the IIUG mailing >lists, but I'm not sure of which topic my prolem resides under. >I am currently running IDS.2000 Version 9.21.UC2 on RdHat Linux 6.2. >Via ESQL/C I am running the following query for the following schema: > >Schema > >table X >int a >int b >int c >int d >int e > >table Y >int a >int f >int g 4 >serial8 h > > >char *handleCursorName_in = (char*) malloc(sizeof(*handleCursorName) * 128); > >strcpy(handleCursorName_in, "The Cursor Name"); > >EXEC SQL DECLARE :handleCursorName_in SCROLL CURSOR FOR SELECT >X.a, X.b, X.c, X.d, X.e, Y.f, Y.g, Y.h, FROM X, Y >WHERE X.a = Y.a >ORDER BY X.b, X.a, Y.h; > >EXEC SQL OPEN :handleCursorName_in; > >This query works fine for certain sizes of tables, but when I increase >the size of X and Y past a certain point I get the following error on >the OPEN command: > >---------------------------------------------------------- >SQLSTATE: IX000 >SQLCODE: -564 > >EXCEPTIONS: Number=2 More? N >- - - - - - - - - - - - - - - - - - - - >EXCEPTION 1: SQLSTATE=IX000 >MESSAGE TEXT: Cannot sort rows. >CLASS ORIGIN: IX >SUBCLASS ORIGIN: IX000 >- - - - - - - - - - - - - - - - - - - - >EXCEPTION 2: SQLSTATE=IX000 >MESSAGE TEXT: No space left on device >CLASS ORIGIN: IX >SUBCLASS ORIGIN: IX000 > >As far as I can tell, there is plenty of space left on all the disks >on the server, and the root db space is several times the size of what >should hold the database. Both the logical and physical logs are on >doifferent dbspaces, so those shouldn't come into play. When I looked >at the error message description, the docs say the following: > >-564 Cannot sort rows. >This internal error reflects an unexpected condition >during a sort. Check the accompanying ISAM error code for more information. If >the error recurs, please note all circumstances and contact Informix Technical >Support. > >-28 No space left on device. >An operating-system error code with the meaning shown >was unexpectedly returned to the database server. Either a database table >or an >ASCII output file has probably filled the available disk space. Look for other >operating-system error messages that might give more information. > >Does anyone have any idea what could be causing this? I have tried >increasing the size of the rootdbs, increasing the number of shared >memory buffers, and increasing shared memory segment size (total shared memory >already set to unlimited). I'm really stumped as to what is going on, >so any help would be greatly appreciated. > >thanks, >Matt > >