Re: DBSPACETEMP & PSORT_DBTEMP
John Coyle wrote:
>
> Hi everyone,
>
> I have not set DBSPACETEMP within my onconfig file
> because I have chosen to use environment variables to
> point to other locations. According to both the
> Administrator's Guide (page 14-27) and the Performance
> Guide (page 2-39), if the environment variables are set the
> engine will supersede any definition (or lack thereof) within
> the onconfig file. I have placed these variables within the
> Informix startup shell (.profile) and rebooted the engine.
> However, all explicit temp table creation is occurring in the
> rootdbs. The same is happening with group by and order
> by clauses within SQL statements. The Informix techie in
> Lenexa has stated though these variables seem to be OK.
> The real question comes down to whether I can use different
> locations for explicit temp tables and order by groups for
> client processes.
>
> System:
> HP-UX universe B.10.20 D 9000/849
> INFORMIX-OnLine Version 7.22.UC3
>
> Onconfig definition:
> DBSPACETEMP>
> Environment variables:
> DBSPACETEMP=/sort2
> PSORT_DBTEMP=/sort1,/sort2
>
> This is now an open case with Informix Tech Support
> regarding these two environment variables, which they have
> not resolved. What am I missing?
I'm sure you know most of this, but just to recap. The types of
temporary object are as follows:
Temporary files:
ORDER BY or GROUP BY in a SELECT statement
UNIQUE or DISTINCT in a SELECT statement
Sort merge join
Index builds
Temporary tables:
Implicit (SELECT INTO TEMP)
Explicit (CREATE TEMP TABLE)
BLOB values are passed from and to stored procedures and used as
global variables
These are stored based on the following precedence:
Temporary files:
PSORT_DBTEMP environment variable
DBSPACETEMP environment variable
DBSPACETEMP configuration variable
/tmp
Temporary tables:
DBSPACETEMP environment variable
DBSPACETEMP configuration variable Root dbspace or dbspace where the database was created
Temporary dbspaces listed in DBSPACETEMP can only be used if the table
is NOT logged.
Notice also that DBSPACETEMP is a list of DBSPACES, NOT directories.
Notice also that temp tables can only be stored in a DBSPACE.
You say explicit temp tables are created in rootdbs. If you don't set
DBSPACETEMP (you have one unset and the other incorrectly set) then
EXPLICIT tables will be created in the dbspace where the database
resides. Possibly you have your database created in rootdbs.
As for your sort files, if you are sure you have set and exported
PSORT_DBTEMP before running, then I would check the delimiter. As far as
I know the only valid delimiter for PSORT_DBTEMP is a colon (:). But I
have been corrected before on delimiters. ;-)
Hope that helps,
--
Mark.
+----------------------------------------------------------+-----------+
|Mark D. Stock - Informix SA http://www.informix.com |//////// /|
|mailto:mdstock@informix.com http://www.informix.com/idn |///// / //|
|http://www.iiug.org +-----------------------------------+//// / ///|
| Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////|
| Fax: +27 838250 2325 |If it's fast, the users keep quiet.|// / /////|
|Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////|
+----------------------+-----------------------------------+-----------+
In article <7b3nmt$eie$1@news.xmission.com>, Mark D. Stock
<mdstock@informix.com> writes
>
>I'm sure you know most of this, but just to recap. The types of
>temporary object are as follows:
>
Another for the FAQ..
>Temporary files:
> ORDER BY or GROUP BY in a SELECT statement
> UNIQUE or DISTINCT in a SELECT statement
> Sort merge join
> Index builds
>
>Temporary tables:
> Implicit (SELECT INTO TEMP)
> Explicit (CREATE TEMP TABLE)
> BLOB values are passed from and to stored procedures and used as
> global variables
>
>These are stored based on the following precedence:
>
>Temporary files:
> PSORT_DBTEMP environment variable
> DBSPACETEMP environment variable
> DBSPACETEMP configuration variable
> /tmp>
>Temporary tables:
> DBSPACETEMP environment variable
> DBSPACETEMP configuration variable> Root dbspace or dbspace where the database was created
>
>Temporary dbspaces listed in DBSPACETEMP can only be used if the table
>is NOT logged.
>
>Notice also that DBSPACETEMP is a list of DBSPACES, NOT directories.
>Notice also that temp tables can only be stored in a DBSPACE.
>
>You say explicit temp tables are created in rootdbs. If you don't set
>DBSPACETEMP (you have one unset and the other incorrectly set) then
>EXPLICIT tables will be created in the dbspace where the database
>resides. Possibly you have your database created in rootdbs.
>
>As for your sort files, if you are sure you have set and exported
>PSORT_DBTEMP before running, then I would check the delimiter. As far as
>I know the only valid delimiter for PSORT_DBTEMP is a colon (:). But I
>have been corrected before on delimiters. ;-)
>
>Hope that helps,
--
David Williams