Temporary Tablespace

Alfred Hock

unread,

Mar 29, 2001, 7:13:12 AM3/29/01

to

Hello,

we have created a temporary dbspace in our datebase with the -t option
of onspaces. But informix dynamics server doesn't use the temporary
tablespace for queries like

select * from blabla into temp tbla;

Instead, the command uses the rootdbs tablespace.

The onstat -d command shows the following output:
Informix Dynamic Server 2000 Version 9.21.UC4 -- On-Line -- Up
15:20:27 -- 102988 Kbytes

Dbspaces
address number flags fchunk nchunks flags owner name
155d27d0 1 0x1 1 2 N informix rootdbs

15602830 2 0x1 2 1 N informix ims
15602978 3 0x1 3 1 N informix imsii
15602ac0 4 0x2001 4 2 N T informix temp
15602c08 5 0x1 5 3 N informix zuks101

15602d50 6 0x1 6 1 N informix gobdb
15602e98 7 0x1 7 2 N informix log
15c91018 8 0x1 8 1 N informix pmid
8 active, 2047 maximum

Chunks
address chk/dbs offset size free bpages flags pathname
155d2918 1 1 0 15000 7228 PO-
/opt/informix/dbspaces/root.dbs
156112a8 2 2 0 524288 522072 PO- /dev/sdb5
15611418 3 3 0 524288 523627 PO- /dev/sdb6
15611588 4 4 0 524288 524235 PO- /dev/sdb7
156116f8 5 5 0 1048064 1048011 PO- /dev/sdb8
15611868 6 6 0 1048064 468250 PO- /dev/sdb11
156119d8 7 7 0 1048064 548011 PO- /dev/sdb12
15611b48 8 8 0 524288 524235 PO- /dev/sdb14
15611cb8 9 4 524288 523776 523773 PO- /dev/sdb7
15611e28 10 5 0 1048064 1048061 PO- /dev/sdb9
155d2a88 11 5 0 1048064 1048061 PO- /dev/sdb10
155d2bf8 12 7 0 1048064 1048061 PO- /dev/sdb13
155d2d68 13 1 0 50000 49997 PO-
/opt/informix/dbspaces/root2.dbs
13 active, 2047 maximum

We have also looked at the DBSPACETEMP variable in the onconfg file. The
value is set to the "temp" dbspace.
What do we have to do, in order to use the correct temporary dbspace?
Are there any other variables, we have to set?

Thanks in advance
Alfred

Susanne Hüsing

unread,

Mar 29, 2001, 7:51:51 AM3/29/01

to

Hi!
try to set the DBSPACETEMP environment variable
bye

Alfred Hock schrieb:

Alfred Hock

unread,

Mar 29, 2001, 8:57:56 AM3/29/01

to

Hello again,

we have set the DBSPACETEMP environment variable, but this doesn't work.
Is "temp" a valid dbspace name?
The informix documentation for "onstat -d" doesn't mention the dbspace flag
"T". This flag is set for the chunks. Is the documentation wrong?

Excerpt from the Informix-Universal Server Administrator Guide of onstat -d
dbspace:
flags describes each space using the following letter codes:
Position 1: M --Mirrored
N -- Not Mirrored
Position 2: X -- Newly mirrored
P -- Physically recovered, waiting for logical recovery
L -- Being logically recovered
R-- Being recovered
Position 3: B -- Blobspace
S -- Sbspace

chunks:
flags gives the chunk status information as follows:
Position 1: P -- Primary
M -- Mirror
Position 2: O -- On-line
D -- Down
X -- Newly mirrored
I -- Inconsistent
Position 3: B -- Blobspace
- -- Dbspace
S -- Sbspace
T - Temporary dbspace

The chunk flag and the dbspace flag for "T" is swapped in the onstat -d
output. Is this correct???????

Thanks in advance
Alfred

Leonid Vorontsov

unread,

Mar 29, 2001, 9:08:42 AM3/29/01

to

"Alfred Hock" <alfre...@stb-ag.com> wrote in message
news:3AC326D7...@stb-ag.com...

> Hello,
>
> we have created a temporary dbspace in our datebase with the -t option
> of onspaces. But informix dynamics server doesn't use the temporary
> tablespace for queries like
>
> select * from blabla into temp tbla;
>
> Instead, the command uses the rootdbs tablespace.

Old bug returns?
:-(

> What do we have to do, in order to use the correct temporary dbspace?

Please notify Informix Technical Support.
:-)

Mr Tricky

unread,

Mar 29, 2001, 11:09:18 AM3/29/01

to

The problem is that you are creating a logged temporary table, and for
that it needs a dbspace that is logged. You have to either:

create a non-logged temp table:

select * from blabla into temp tbla with no log;

or put a logged (non-temporary) dbspace in DBSPACETEMP

Cheers

Richb

John Carlson

unread,

Mar 29, 2001, 3:00:59 PM3/29/01

to

Got it!

When you "select into temp", Informix will automatically put the temp table
into rootdbs. In order to get the temp table to exist in the temporary
dbspaces, use the following:

select * from blabla into temp tbla with no log;

John Carlson
Informix Database Administrator
EDS - WHSmith USA
3200 Windy Hill Drive
Suite 1500, West Tower
Atlanta, GA 30339

-----Original Message-----
From: Leonid Vorontsov [mailto:Leonids....@dati.lv]
Sent: Thursday, March 29, 2001 9:09 AM
To: inform...@iiug.org
Subject: Re: Temporary Tablespace

"Alfred Hock" <alfre...@stb-ag.com> wrote in message
news:3AC326D7...@stb-ag.com...

> Hello,
>
> we have created a temporary dbspace in our datebase with the -t option
> of onspaces. But informix dynamics server doesn't use the temporary
> tablespace for queries like
>
> select * from blabla into temp tbla;
>
> Instead, the command uses the rootdbs tablespace.

Old bug returns?
:-(

> What do we have to do, in order to use the correct temporary dbspace?

Murray Wood

unread,

Mar 29, 2001, 5:45:02 PM3/29/01

to

Have you restarted your engine? Or set the environment variable
DBSPACETEMP?
Are these tables real non logged temp tables? If not then they cannot go
into a type T tblspace.

MW

Bob Roussey

unread,

Mar 30, 2001, 4:43:39 PM3/30/01

to

You have to add that dbspace name to DBTEMP in your $ONCONFIG file,
take the engine down and back up. If you add more of them, make it a
comma-separated list and repeat the engine bounce.

/bobr/

Alex Bernaards

unread,

Apr 2, 2001, 2:20:41 AM4/2/01

to

Hi,

I think, we had the same problem, last friday we fixed it with 2
Informix-patches, one of them has just been released for two weeks.
A bug in 7.31.UCx (higher then UC2)
Contact Informix-support.

Lex