RE: explicit TEMP table in rootdbs?
Temporary Tables
=============
Temporary tables are always untyped tables. The following CREATE TABLE
statement creates a temporary table:
CREATE TEMP TABLE transient
(col1 integer,
col2 char(20))
After a temporary table is created, you can build indexes on the table.
However, you are the only user who can see the temporary table.
Temporary tables that you create with the CREATE TEMP TABLE statement are
explicit temporary tables. You can also create explicit temporary tables
with the SELECT ... INTO TEMP statement. Temporary tables that the database
server creates as a part of processing are called implicit temporary tables.
Implicit temporary tables are discussed in the INFORMIX-Universal Server
Administrator's Guide.
When an application creates an explicit temporary table, the table exists
until one of the following situations occur:
The application terminates.
===================
The application closes the database where the table was created. In this
case, the table is dropped only if the database does transaction logging,
and the temporary table was not created with the WITH NO LOG option.
The application closes the database where the table was created and opens a
database in a different database server.
When any of these events occur, the temporary table is deleted.
DB: You cannot use the INFO statement and the Info Menu Option with
temporary tables.
Temporary table names must be different from existing table, view, or
synonym names in the current database. However, they need not be different
from other temporary table names used by other users.
You can specify where temporary tables are created with the CREATE TEMP
TABLE statement, environment variables, and ONCONFIG parameters. If you do
not specify a storage location, the temporary tables are created in the same
dbspace as the database. The database server stores temporary tables in the
following order:
The IN dbspace clause
You can specify the dbspace where you want the temporary table stored with
the IN dbspace clause of the CREATE TABLE statement.
2. The dbspaces you specify when you fragment temporary tables
Use the FRAGMENT BY clause of the CREATE TABLE statement to fragment regular
and temporary tables.
3. The DBSPACETEMP environment variable
The DBSPACETEMP environment variable lists dbspaces where temporary tables
can be stored. This list can include standard dbspaces, temporary dbspaces,
or both. If the environment variable is set, the database server assigns
each temporary table to a dbspace in round-robin sequence.
4. The ONCONFIG parameter DBSPACETEMP
You can specify a location for temporary tables with the ONCONFIG parameter
DBSPACETEMP.
Tip: Use the PUT clause to specify a separate storage area for smart large
objects.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---
"I have no special talents, I am only passionately curious" - Albert
Einstein
-----Mensaje original-----
De: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] En
nombre de Simmons, Keith
Enviado el: Martes, 15 de Marzo de 2005 09:39 a.m.
Para: Darren_Jacobs@carmax.com
CC: ids@iiug.org; informix-list@iiug.org
Asunto: RE: explicit TEMP table in rootdbs?
Darren
As follows :-
create TEMP table <tabname>
(
column list ....
) WITH NO LOG;
will create the temp table in your temp dbspaces. There are several previous
threads on the whys and wherefores of this if you need further information.
Keith
-> -----Original Message-----
-> From: Darren_Jacobs@carmax.com [mailto:Darren_Jacobs@carmax.com]
-> Sent: Tuesday, March 15, 2005 2:08 PM
-> To: ids@iiug.org; informix-list@iiug.org
-> Subject: explicit TEMP table in rootdbs?
->
->
-> All,
->
-> I'm having a problem with an explicit TEMP table being created in my
-> rootdbs. I have 6 temp dbspaces created at 1 gig each. My entry for
-> dbspace temp in my onconfig file is below.
->
-> create TEMP table <tabname>
-> (
-> column list ....
-> );
->
-> Any help would be greatly appreciated.
->
-> $ onstat -c|grep TEMP
-> # DBSPACETEMP:
-> # Dynamic Server equivalent of DBTEMP for SE. This is the list of
-> dbspaces
-> DBSPACETEMP temp1:temp2:temp3:temp4:temp5:temp6 #
-> Default temp dbspaces
->
-> sending to informix-list
->
****************************************************************************
******
This message is sent in strict confidence for the addressee only. It may
contain legally privileged information. The contents are not to be disclosed
to anyone other than the addressee. Unauthorised recipients are requested to
preserve this confidentiality and to advise the sender immediately of any
error in transmission.
This footnote also confirms that this email message has been swept for the
presence of computer viruses, however we cannot guarantee that this message
is free from such problems.
****************************************************************************
******
sending to informix-list
sending to informix-list