Hi folks,

i have some problems with our server.
we run a SCO 5.0.5 at 2,2 GhZ Xeon Processor and 512MB-RAM, with an Informix IDS 9.20.UC1.

If we run some specific sql-statments with an order by statement it simply hang up, with ALL free pages consumed.
it appear NOT at all statemenst, just some specific ones

output of onstat -d ist that:

227# onstat -d

Informix Dynamic Server 2000 Version 9.21.UC2 -- On-Line -- Up 04:32:32 -- 176128 Kbytes

Dbspaces
address number flags fchunk nchunks flags owner name
18d167d0 1 0x1 1 3 N informix rootdbs
1 active, 2047 maximum

Chunks
address chk/dbs offset size free bpages flags pathname
18d16918 1 1 0 1000000 148871 PO- /dev/rootdbs
18d555f0 2 1 0 1000000 3 PO- /dev/rootdbs1
18d55758 3 1 0 1000000 0 PO- /u/informix/dbspace/rootdbs2

i hope somebody can help me, cause i dont know where to search

on our other server,
the statement works fine there

thx

Harlequin

Do you see errors... sqlcode 229, ISAM 131 ?

This looks like a nasty setup. Can you attach the config file?

How is this server set up compared to the other? Less dbspace available here? A temp dbspace over there?

yes
both errors -229 and -131 occurs in some our programs when they execute some sql sorts, but not regurlary
sometimes they work fine, sometimes they got down with these errors

when i run the sql statement in the shel, no error occurs, it hangs only :-(

the server ist setup identicaly with 2 other servers, where the sqlstatement works.. but there we get sometimes altough -299 and -131
(same dbspace on them) but on other sqlstatements :-)

but no extra temp dbtemp at all (we use the rootdbs for temp)

i attach our onconfig for u

The issue is, that you are running out of sort space. Most informix setups will have a dedicated area (a temp dbspace) that is used when informix is working to create indexes, do sorts and joins on SQL queries, etc. If several people are running 'big' queries doing sorts and joins at the same time you'll probably get the error pretty quickly and on several sessions. You may always get a failure on 1 type of query if it has to sort lots of data (for example) and the space available is never going to be enough (because of data volumes) even when no other user is on the system. The frequency depends on data size, how many concurrent users and the types of SQL their sessions execute relative to the temp space available for infomrix to work in.

I guess what has happened is that your data has now grown to the size where you now will get this problem more frequently. You may never have had it before even, but now that the data has reached that critical size, where temp tables for executing SQL statements exceed the size of available space to work in.

Simple solution is to extend the space or create a temp dbspace (you'd need to update your config file for that and restart informix).

Alternatively, you could reduce the size of your database by archiving (then delete) data (if that's appropriate).

i just added a temp dbspace with 500mb,, but nothing happens.
the sql statements dont run :-(

Did you update the config file and restart informix?

yes i did

and restartet the server....

hm... i have tried my old onconfig and now it works?!?!?!?!?!?

maybe its the optimizer flag in the onconfig?

An easy way to see what is happening in the dbspaces is get someone to execute the query when you are logged in as informix user.

As informix, keep running the onstat -d command.
This shows the status of the dbspaces.

If the temp dbspace is being used, then you see it filling up with data and then releasing the space when the query is finished.
If you see the space is not being used, then there is something wrong with your config file because it should be used for your query.
If you see it fill up and get the errors, then there is not enought temp dbspace for your query to complete.

I just thought of something else, pretty obvious thing really, but ....

Do you regularly make a statistics update? This can improve your performance (As long as you set them correctly and keep them updated).

You could also rebuild the indexes before the stats update if your data is pretty old and gets heavy updates,additions,deletions.

no we dont

think im going to introduce here some new database rulez, 'cause the former dba was not realy a good dba

when i look at the database design.. oh my god
:-)

grr

ive found the reason

OPTCOMPIND 0 # To hint the optimizer

that was it

if i switch to 2, it works perfectly

if i witch to 0, it hangs up :-(

can someone explain me this parameter?

OPTCOMPIND is one of the PDQ parameters. Setting it to 2 hints the query optimiser to use various join methods to improve performance when making typically big table joins and sorting output for OLAP-type environments. Setting it to 0 is used when you are tuning for OLTP type installation.
In a mixed environment I guess it's a toss-up and you just have to play with the setting. I never thought of it I must admit, because 2 is the default value (OLTP queries will not really be slowed down much anyway when you set it to 2, because they are small/quick by nature anyway - so selecting 2 is a good default. Never heard of anyone changing that value ;-) ).

hm, thx
BUT

if i change it to 0, our programms runs VERY well, if i change it to 2, the programs slows down

(we have much sequentiel reading)

hm, i think i do a COMPLETE new config file for our databases, and do a ALL OVER new indexing for our tables....

much work to do :-(