John:
You have several issues, let me address each separately:
- On temp dbspace filling up -
There can be several different causes
- An individual session with DBSPACETEMP set to that one temp space in its environment which overrides the server level setting in the ONCONFIG file or the server's startup environment. Check sysmaster:sysconfig, sysmaster:sysenv, and sysmaster:sysenvses.
- An archive in progress. While an archive is running physical log records are copied into temp tables one per dbspace and as each dbspace's archive is completed its physical log temp table is itself written to the archive target and dropped (there have been bugs from time-to-time where they were not dropped until the end of the archive, but I don't think that has happened in v12.10). If one or more of your busiest dbspaces as regards writing modified and new data is also one of the last to be archived that temp table can fill up a temp dbspace until the dbspace's portion of the archive completes. Those temp tables are supposed to be created partitioned across all temp dbspaces, but they are not always.
- A session creating a large temp table explicitly with CREATE TEMP TABLE or SELECT ...INTO TEMP ... could leave that table around for a long time if it is not explicitly dropped and the session does not exit.
- Sessions creating implied temp tables when queries include any of the following: some WHERE and ON clause filters, GROUP BY, HAVING, ORDER BY, hierarchical queries, OLAP queries, etc.
- The temp spaces in DBSPACETEMP are used round robin and on an active system it is possible that the temp table that failed just happened to be assigned to the same dbspace as one that is already nearly full with other temp tables.
- PSORT_DBTEMP - You set this variable to filesystem space (not dbspaces) and it is ONLY used for temp tables creating during sorting, so many of those listed in #4 above.
- DBSPACETEMP - As noted above, there are three levels at which DBSPACETEMP can be set.
What to do about it? If it is any of the numbered issues except #1, reissuing the query will usually succeed. If it is #1, correct the issue with environment variables overriding the ONCONFIG setting, either in the server or within client sessions.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Mon January 31, 2022 11:43 AM
From: John Smith
Subject: Only on temp dbspace is used
Hello Team
on 12.1 server , i have many temp dbspaces , but got an error when one of them is full !!
is there a way to fix it ? without increasing their sizes ?
i also set PSORT_DBTEMP variable but nothing changed !
thanks in advance for your help
------------------------------
John Smith
------------------------------
#Informix