Help - weird error 567 - fixed it, but need to know more
Hi all,
Try the following on a table that only has 2000 rows
SELECT * FROM TSTHESAURU WHERE ( tmSearchKey > '' ) ORDER BYtmSearchKey;
I get
-- [Informix][Dynamic Server, Workgroup Edition][grant_nt1] SQL Error
(-567) : Cannot write sorted rows. [ ISAM error -2 : No such file or
directory ]
-567 says see the accompanying messages. -2 is the good old No such
file or directory.
My only assumption was it's trying to sort to disk - and can't find
the directory.
So, hunted through my ONCONFIG, but couldn't find anything that looked
like a sort directory entry. Out of blind luck, I saw that \\tmp was
refered to in a few places, but for things like DRLOSTFOUND and
DUMPDIR. None of these suggest for a minute they have anything to do
with disk sorts. But, as soon as I put in \\tmp (e.g. c:\\tmp), the
error went away, and I can see sort files appearing temporarily in
this dir.
So, my question is, what ONCONFIG or environment parameter do I use to
tell my informix client or server where to do disk sorts?
Ciao
Fuzzy
:-)
Fuzzy wrote:
>
> Hi all,
>
> Try the following on a table that only has 2000 rows
>
> SELECT * FROM TSTHESAURU WHERE ( tmSearchKey > '' ) ORDER BY> tmSearchKey;
>
> I get
>
> -- [Informix][Dynamic Server, Workgroup Edition][grant_nt1] SQL Error
> (-567) : Cannot write sorted rows. [ ISAM error -2 : No such file or
> directory ]
>
> -567 says see the accompanying messages. -2 is the good old No such
> file or directory.
>
> My only assumption was it's trying to sort to disk - and can't find
> the directory.
>
> So, hunted through my ONCONFIG, but couldn't find anything that looked
> like a sort directory entry. Out of blind luck, I saw that \\tmp was
> refered to in a few places, but for things like DRLOSTFOUND and
> DUMPDIR. None of these suggest for a minute they have anything to do
> with disk sorts. But, as soon as I put in \\tmp (e.g. c:\\tmp), the
> error went away, and I can see sort files appearing temporarily in
> this dir.
>
> So, my question is, what ONCONFIG or environment parameter do I use to
> tell my informix client or server where to do disk sorts?
You can create temp dbspaces and list then in the DBSPACETEMP which is both
an ONCONFIG parameter and an environment variable the latter taking
precedence over the former and the engine will perform sorts there. If you
want to keep putting the sort-work files in filesystem space use the
environment variable PSORT_DBTEMP which is a list of filesystems or
directories. In either case sort proceed fastest if three or more temp
dbspaces or filesystems are listed and are placed on independent disk
spindles. The listed dbspaces or filesystems are written to round robin and
merging them goes fastest if there are at least three so you are never
reading and writing the same one.
Art S. Kagel