Role of DBSPACETEMP

Hi, We have some questions about of the role and the effect of the envvar DBSPACETEMP. Sometimes I read that in databases that are logged, temporary tables are logged by default and WILL NOT BE CREATED IN 'DBSPACETEMP'. In that case it is necessary to use the WITH NO LOG option of the CREATE TEMP TABLE statement. Our experience is that temp tables without the WITH NO LOG option will be created in the root dbspace. This may cause the root dbspace to be filled up. Understanding the above the DBSPACETEMP will have no effect when database is logged. How can I get around this and force that temp tables are created in the database space of my choice? Anybody who can clearify this? regards, Henk

Henk van der Geld wrote: > > Hi, > > We have some questions about of the role and the effect of the envvar > DBSPACETEMP. Sometimes I read that in databases that are logged, > temporary tables are logged by default and WILL NOT BE CREATED IN > 'DBSPACETEMP'. In that case it is necessary to use the WITH NO LOG > option of the CREATE TEMP TABLE statement. Our experience is that temp > tables > without the WITH NO LOG option will be created in the root dbspace. This > may cause the root dbspace to be filled up. Understanding the above the > DBSPACETEMP will have no effect when database is logged. How can I get > around this and force that temp tables are created in the database space > of my choice? Anybody who can clearify this? Your analysis is correct. Temp tables are logged by default and so cannot be created in tempdbspaces which are not logged or backed up. The solutions are two: 1) Create all explicit temp tables in tempdbspaces by specifying WITH NO LOG. 2) Add a normal dbspace to the DBSPACETEMP for the creation of logged temp tables. The engine will place logged temp tables ONLY in the normal dbspaces listed and WITH NO LOG temp tables ONLY in the TEMPORARY dbspaces. Art S. Kagel

Henk van der Geld wrote: > > Hi, > > We have some questions about of the role and the effect of the envvar > DBSPACETEMP. Sometimes I read that in databases that are logged, > temporary tables are logged by default and WILL NOT BE CREATED IN > 'DBSPACETEMP'. In that case it is necessary to use the WITH NO LOG > option of the CREATE TEMP TABLE statement. Our experience is that temp > tables > without the WITH NO LOG option will be created in the root dbspace. This > > may cause the root dbspace to be filled up. Understanding the above the > DBSPACETEMP will have no effect when database is logged. How can I get > around this and force that temp tables are created in the database space > > of my choice? Anybody who can clearify this? > > regards, > Henk Hi Henk, DBSPACETEMP sounds a bit ambigious, therefore people often misunderstand its meaning. Generally DBSPACETEMP tells the server where it should place temporary objects. Implicit temporary objects are those objects that are created by "hash joins", "create index", "update statistics", and so on. Explicit objects are objects, created by SELECT ... INTO TEMP or CREATE TEMP TABLE. Explicit objects can be either WITH or WITHOUT logging. Implicit objects will never use logging. Temporary dbspaces are dbspaces, that will never be backed up. Very important is, that all the data that is stored inside temporary dbspaces will not be logged; temporary dbspaces on a secondary server ( used by HDR ) cannot be filled by the primary server. Therefore temporary dbspaces are a must if you use HDR and want to run queries which need temporary space. DBSPACETEMP is used to describe, where ALL the temporary data is stored. Temporary objects are stored depending on their type ( WITH or WITHOUT LOGGING ) in one or more dbspaces listed in DBSPACETEMP. Unlogged temporary objects can be stored in regular dbspaces as well as in temporary dbspaces. Logged temporary objects can be stored just in regular dbspaces. Without any settings in DBSPACETEMP, temporary objects will be stored as follows: Implicit temporary objects in: /tmp, $DBTEMP, $PSORT_DBTEMP ( ascending order from left to right ) Explicit temporary objects in: DbSpace of the current database, if you do not mention a special dbspace. DBSPACETEMP is a configuration parameter as well as an environment variable. The environment variable overrules the config parameter. If you do not place temporary dbspaces in your DBSPACETEMP settings, all temporary objects will be stored in the dbspaces, listed in the DBSPACETEMP parameter. If you use CREATE TEMP TABLE, the dbspaces will be used in a round robin method. If you create the temporary object with SELECT ... INTO TEMP, the resulting temporary table might be fragemented over all dbspaces, listed in the DBSPACETEMP parameter. If you mix temporary and regular dbspaces in the DBSPACETEMP parameter, then all data WITHOUT LOGGING will be stored in the temporary dbspaces as long as the objects will fit in the dbspace. Logged objects will be stored in the other regular dbspaces listed in DBSPACETEMP. If you do not enter a regular dbspace in the DBSPACETEMP parameter, all the temporary objects WITH LOGGING will be stored into the "rootdbs". That's the classic failure. This happens only if you set DBSPACETEMP just to temporary dbspaces. Uff, that's it. It's really tricky. Hope, this will help. Best regards, -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----