IDS Forum

RE: DBSPACETEMP Variable

Posted By: Anthony.Ojeda@LW.com
Date:

Daniel thanks for the detail reply. Very much appreciated. I agree,
it surely sounds that it'd makes more sense to use the "WITH NO LOG"
when creating temp tables for efficiency. I have some developers who
haven't been doing this, but have squeezed by because they're tables
don't blow out the rootdbs dbspace. So I'm faced with legacy programs
that we may not have the time to go back and re-tool until later, if
ever. I'm going to take your advice and create a 2GB fake temp
dbspace to accommodate the temp table create without the "WITH NO LOG"
statement.

Best Regards, Anthony

-----Original Message-----
From: Michaelis, Daniel [mailto:dcmichaelis@eoriginal.com]
Sent: Thursday, July 15, 2004 6:56 AM
To: Ojeda, Anthony (GSO); ids@iiug.org
Subject: RE: DBSPACETEMP Variable [3245]

Anthony,

This is one of my "favourite" problems to run into (the first time I
hit it, it took me about a week to figure out what the heck was going
on). The reason it's behaving this way is that NO ACTIVITY that
happens in a dbspace that is created as a "temp" dbspace can be logged
(it's just the way that temp spaces behave). If you create a temp
table without the "with no log"
statement, the engine assumes that you want it logged, and so can't
put it in a temp space. Absent any other information, the engine puts
the temp table in the only space that it KNOWS exists, and is logged,
which is rootdbs.

That said, the solution is relatively simple, and generally not
something that I've seen talked about much. What you need to do is
create some additional NON-TEMP dbspaces, and add them to the
DBSPACETEMP line in the onconfig file. That way, the engine will
allocate temp tables created "with no log" to the true temp dbspaces,
and it will allocate the temp tables created without the "with no log"
to the "fake" temp dbspaces.

I would ask, however, why you'd not want to add the "with no log"
statement.
The real benefit is that the temp tables will perform measurably
better with that statement, and given that they're temp tables, you
probably don't need to be able to roll them back anyway. It's just a
thought...

Thanks.

Dan Michaelis
Senior Software Developer
eOriginal, Inc
Suite 800
351 West Camden Street
Baltimore, MD 21201

410-625-5187 -- Office
407-758-3395 -- Cell


-----Original Message-----
From: Anthony.Oje.... [mailto:Anthony.Ojeda@LW.com]
Sent: Wednesday, July 14, 2004 5:47 PM
To: ids@iiug.org
Subject: DBSPACETEMP Variable [3245]


I'm running into a situation where temporary tables created by "SELECT
.... INTO TEMP tmptablename" are using space from the root dbspace
instead of our temp dbspace. Both the onconfig and environment
DBSPACETEMP variables are set to the temp dbspace that is flagged as
temporary space (see below). The only way I can force the temporary
table to use the temp dbspace is to add "WITH NO LOG" to the SQL
statement. I've consulted Ron Flannery's "The Informix Handbook",
"Informix Online Dynamic Server Performance Guide", and the "Informix
Guide to SQL", each of which say that all I need to do is set the
DBSPACETEMP variable in either the onconfig file or environment, and
make sure my temp dbspace was created with onspaces' "-t" control
argument.



:/ >onstat -c |grep -i dbspacetemp
# DBSPACETEMP:
DBSPACETEMP tempdbs #Default temp dbspaces



:/ >onstat -d |more
Dbspaces
address number flags fchunk nchunks flags owner name
9de4c158 1 1 1 1 N informix rootdbs
9e8a5a58 2 1 2 1 N informix logdbs
9e8a5b18 3 1 3 56 N informix
elitedbs
9e8a5bd8 4 2001 29 8 N T informix tempdbs



Thanks,=20

Anthony Ojeda
Senior Systems Administrator

LATHAM & WATKINS LLP
555 W. 5th Street, Suite 800
Los Angeles, CA 90013-1010
Direct Tel: (213) 891-7211
=46ax: (213) 891-7123
E-mail: anthony.ojeda@lw.com
www.lw.com


This email may contain material that is confidential, privileged
and/or attorney work product for the sole use of the intended
recipient. Any review, reliance or distribution by others or
forwarding without express permission is strictly prohibited. If you
are not the intended recipient, please contact the sender and delete
all copies.

Latham & Watkins LLP





This email may contain material that is confidential, privileged and/or attorney work product for the sole use of the intended recipient. Any review, reliance or distribution by others or forwarding without express permission is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies.

Latham & Watkins LLP

Messages In This Thread

• DBSPACETEMP Variable
  Anthony.Ojeda@LW.com -- Wednesday, 14 July 2004, at 5:47 p.m.
  • RE: DBSPACETEMP Variable
    Simmons, Keith -- Thursday, 15 July 2004, at 3:44 a.m.
    • RE: DBSPACETEMP Variable
      Olson, Kenneth \(GEI, GEFA\) -- Thursday, 15 July 2004, at 9:18 a.m.
      • RE: DBSPACETEMP Variable
        Konikoff, Rob (Contractor) -- Thursday, 15 July 2004, at 10:56 a.m.
    • RE: DBSPACETEMP Variable
      Simmons, Keith -- Thursday, 15 July 2004, at 10:42 a.m.
  • RE: DBSPACETEMP Variable
    Michaelis, Daniel -- Thursday, 15 July 2004, at 10:05 a.m.
  • RE: DBSPACETEMP Variable
    Anthony.Ojeda@LW.com -- Thursday, 15 July 2004, at 10:22 a.m.
  • Re: DBSPACETEMP Variable
    GARY GU -- Tuesday, 20 July 2004, at 2:41 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.