error code 567 Cannot write sorted rows

[Stefaan Delanghe]

Hi,

When ever I try to use a order by clause on my sql statement by using the jdbc driver for informix I get the sql error -567: cannot write sorted rows.

I checked out the details about this error and it say that there is an internal conflict which prevents informix from sorting the rows.

What is wrong?
How can I solve this?

Thanks in advance
Stefaan

[Rainer von Bongartz]

using the order by clause informix has to sort the rows but cannot.

Sorting is done in temporary dbspaces and it seems your tmpdbs has not enough space.


Look for the ONCONFIG variable TMPDBS and check the available space in this dbspace using onstat -d.


You could also specify a regular directory for your sorts by specifying the environemnet variable PSORT_DBTEMP

Regards
Rainer

[Stefaan Delanghe]

Hi,

Thanks for your response. But I am a bit confused how to get this done.

I'm new to informix and still have to find my way with it.

I don't find the tmpdbs I suppose another name is used I think therefore it is the dbspace.

I send you my onconfig file so you can see what the problem is.

Thanks in advance
Stefaan

[Rainer von Bongartz]

Stefaan

read this part of your ONCONFIG

# DBSPACETEMP:
# Dynamic Server equivalent of DBTEMP for SE. This is the list of dbspaces
# that the Dynamic Server SQL Engine will use to create temp tables etc.
# If specified it must be a colon separated list of dbspaces that exist
# when the Dynamic Server system is brought online. If not specified, or if
# all dbspaces specified are invalid, various ad hoc queries will create
# temporary files in /tmp instead.

DBSPACETEMP # Default temp dbspaces


As your dont have DBSPACETEMP definned it creates sorts in yout /tmp directory.

I guess /tmp is quite full and INFORMIX can't create sortfiles in there.

You could empty your /tmp and try again but you should be aware that sorting in /tmp is not a good idea.(it always tends to grow)

You should create a temporary dbspace using onspaces and set DBSPACETEMP in your ONCONFIG accordingly.

i.e.

onspaces -c -d tmpdbs -t -p -o 0 -s

edit onconfig
DBSPACETEMP tmpdbs

restart INFORMIX onmode -ky ; oninit

Regards
Rainer