Hi,
I am using Informix 10. We have created 10 temporary dbspaces. whenever i am running a query with left outer join, it is using only one temporary dbspaces then it is coming out with error. "Could not write to temporary file".

Remaining 9 dbspaces was absolutely free. why the query is returing with error?

Please reply me ASAP.

Thanks in Advance,
Senthil Kumar R V

Please, post output
onstat -d
and
cat onconfig.<your_servername> | grep DBSPACETEMP

I am copied only root and temporary dbspace.
Onstat -d Output:-

c00000017d388e90 1 0x60001 1 1 2048 N B informix ddj_rootdbs
c000000180173c00 2 0x40001 2 2 2048 N B informix ddj_logdbs1
c000000180173da0 3 0x40001 4 2 2048 N B informix ddj_logdbs2
c000000180176030 4 0x40001 6 1 2048 N B informix ddj_physdbs
c0000001801761d0 5 0x42001 7 1 2048 N TB informix ddj_temp_dbs1
c000000180176370 6 0x42001 8 1 2048 N TB informix ddj_temp_dbs2
c000000180176510 7 0x42001 9 1 2048 N TB informix ddj_temp_dbs3
c0000001801766b0 8 0x42001 10 1 2048 N TB informix ddj_temp_dbs4
c000000180176850 9 0x42001 11 1 2048 N TB informix ddj_temp_dbs5
c0000001801769f0 10 0x42001 12 1 2048 N TB informix ddj_temp_dbs6
c000000180176b90 12 0x42001 14 1 2048 N TB informix ddj_temp_dbs8
c000000180176d30 13 0x42001 15 1 2048 N TB informix ddj_temp_dbs9

c000000180156d10 7 5 0 5242880 5124955 PO-B /opt/informix/ddj_temp_dbs1.1
c000000180157030 8 6 0 5242880 5150083 PO-B /opt/informix/ddj_temp_dbs2.1
c0000001801571e0 9 7 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs3.1
c000000180157390 10 8 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs4.1
c000000180157540 11 9 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs5.1
c0000001801576f0 12 10 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs6.1
c0000001801578a0 14 12 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs8.1
c000000180157a50 15 13 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs9.1


Onconfig for DBSPACETEMP:-

DBSPACETEMP ddj_temp_dbs1,ddj_temp_dbs2,ddj_temp_dbs3,ddj_temp _dbs4,ddj_temp_dbs5,ddj_temp_dbs6,ddj_temp_dbs7,dd j_temp_dbs8,ddj_temp_dbs9,ddj_temp_dbs10

Thanks for your reply,

Senthil Kumar

1. Do you reboot server when you create temp dbspace?
2. Post output env | grep TEMP.
3. Which number of ISAM error when you get error "Could not write to temporary file"?
4. Post onstat -d when you will get "Could not write to temporary file".
5. Please post your onconfig.

I think your string of dbspaces should be a colon separated list, not comma separated. I have this comment line in my onconfig, OS is AIX:

"OnLine equivalent of DBTEMP for SE. This is the list of dbspaces that the
OnLine SQL Engine will use to create temp tables etc. If specified, it must be a colon separted list of dbspaces that exist when the OnLine 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."

My sincere thanks to all of you ...Here the output which you requested;

1. Do you reboot server when you create temp dbspace?
Yes, I have rebooted the server.
2. Post output env | grep TEMP.
No Reults. Nothing has been assigned. But DBTEMP is pointing to /tmp
3. Which number of ISAM error when you get error "Could not write to temporary file"?
264: Could not write to a temporary file.
131: ISAM error: no free disk space
Query has run for around 30 minutes
4. Post onstat -d when you will get "Could not write to temporary file".
Status of TEMPORARY CHUNK before running Query:-

c00000017d389030 1 1 0 1048576 1044295 PO-B /opt/informix/ddj_rootdbs.1
c000000180156d10 7 5 0 5242880 5124955 PO-B /opt/informix/ddj_temp_dbs1.1
c000000180157030 8 6 0 5242880 5150083 PO-B /opt/informix/ddj_temp_dbs2.1
c0000001801571e0 9 7 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs3.1
c000000180157390 10 8 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs4.1
c000000180157540 11 9 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs5.1
c0000001801576f0 12 10 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs6.1
c0000001801578a0 14 12 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs8.1
c000000180157a50 15 13 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs9.1
c00000018015a540 39 36 0 256000 255947 PO-B /opt/informix/ddj_ntemp_dbs10.1
c00000018016a8a0 131 120 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs10_1

Status of TEMPORARY CHUNK After running Query:-(When it throws Error)
c00000017d389030 1 1 0 1048576 1044295 PO-B /opt/informix/devlinks/10/ddj_rootdbs.1
c000000180156d10 7 5 0 5242880 475 PO-B /opt/informix/ddj_temp_dbs1.1
c000000180157030 8 6 0 5242880 5150083 PO-B /opt/informix/ddj_temp_dbs2.1
c0000001801571e0 9 7 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs3.1
c000000180157390 10 8 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs4.1
c000000180157540 11 9 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs5.1
c0000001801576f0 12 10 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs6.1
c0000001801578a0 14 12 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs8.1
c000000180157a50 15 13 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs9.1
c00000018015a540 39 36 0 256000 255947 PO-B /opt/informix/ddj_ntemp_dbs10.1
c00000018016a8a0 131 120 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs10_1

5. Please post your onconfig.

onconfig has been attached. File Name is onconfig.txt

In Informix Document, we can use comma or colon to separate dbspaces name in DBSPACETEMP parameter.
Any way, i will try to do those changes and check the output.

I think that your server uses /tmp for temporary tables.
I think that /tmp is full when you get error.
Analyse it.
Try to remove DBTEMP variable.

And... from documentation.
Important:
The dbspaces that you list in the DBSPACETEMP configuration parameter must consist of chunks that are allocated as raw UNIX devices. On Windows, you can create temporary dbspaces in NTFS files.

See here.

This could be nothing but your have a ddj_temp_dbs10 in your onconfig file and I don't see that dbspace included in the onstat -d output.

Your onconfig includes the DBSPACETEMP comment that the list of dbspaces for DBSPACETEMP should be separated by colons, perhaps thats why it uses the first dbspace (because ddj_temp_dbs1 is first in the list and is valid) but ignores the rest

I have changed the parameter seggregation from comma to colon and the same error is coming. i am checking the /tmp directory. It is not getting filled up by the execution of the query.

If i create a temp table with no log, it is using all the temporary tablespaces.
Still, it is a mystery for us for that query...

Did you bounce the instance after changing from commas to colons? I don't know if that parameter is dynamic (like changing ltape) but I doubt that it is.

After changing, I have restarted the informix server.

Hi,

Did u check the file system.......Is it free

First, probably your session are using only one DBSPACE because the SQL create a implicit temp table , and you don't have a PDQ priority active.
Before execute your SQL, in the same session, execute:
Copied from Performance Guide:

To get the problem exactly at the time they occur , execute the commands below.
But, if this is production server, be careful with this suggestion because they can "freeze" temporarily your server :

1) disable any Shared memory Dump: parameters DUMP* on your onconfig and restart the server
2) add a trap : onmode -I 264
See this documentation
How to trap an Informix error to collect additional information for diagnostics
3) execute the SQL and wait for the error.
4) After the error, disable the trap : onmode -I
5) look your log (onstat -m) , a AF file is created with a picture at the moment of the error, this way you can see exactly what dbspace are full.
If have doubt to identify the problem, copy the AF file here...

This problem got solved. Earlier I used Postjoin in the query then I have changed the query from ansi method. It is working fine.

Earlier view query written like this:-

("informix".ddj_task x1 left join "informix".user x2 on (x1.username = x2.username ) )

New View query:-

"informix".ddj_task x1, outer "informix".user x2
where (x1.username = x2.username )

Once changed the query, It has been solved. It is using all temporary dbspaces.

Thanks,
Senthil

Funny,
The last week I tunning a query from a developer exactly on the same situation, they use ANSI SQL with a lot of LEFT OUTER JOIN and the performance are terrible returning in 3-4 minutes (but don't have problem with temp space), the database execute a lot of sequencial scans , after change to non-ansi the query returns in 2 seconds.