RE: DBSPACETEMP Variable [3248]

Anthony A few things to check: 1. The environment variable takes priority over the 'onconfig' setting. Perhaps unset the environment variable in case it is being picked up incorrectly. 2. onstat -c reads from the configuration file on disk and is therefore not necessarily what you are running with. 3. if my memory serves me right, temporary dbspaces must be raw - are you using the character special devices. Regards David Linthwaite Lintel Software Consultancy Ltd IBM Business Partner Tel: 01244 316297 Fax: 01244 357248 mailto:dlinthwaite@lintel.co.uk -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Olson, Kenn.... Sent: 15 July 2004 14:18 To: ids@iiug.org Subject: RE: DBSPACETEMP Variable [3248] Wow, can anyone confirm this? Thanks. -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of Simmons, Keith Sent: Thursday, July 15, 2004 2:45 AM To: ids@iiug.org Subject: RE: DBSPACETEMP Variable [3246] Anthony There are two types of temporary table, those created 'WITH NO LOG', which are unlogged, and those without this caveat which are logged in the same way as any permanent tables in the database. There are also two type of dbspaces. Those created with the '-t' option which can only contain unlogged tables and those created without this option which can only contain logged tables. The '-t' option doesn't really mean temporary, more it means unlogged. The solution to you problem is to create a dbspace without the '-t' option, but include it in the colon (':') separated list of dbspaces in the DBSPACETEMP parameter of you onconfig file. If you do this the logged temp tables will go in the logged temp dbspace (until it fills then they will default to rootdbspace) and the unlogged tables will go into the unlogged temp space. -> -----Original Message----- -> From: Anthony.Oje.... [mailto:Anthony.Ojeda@LW.com] -> Sent: Wednesday, July 14, 2004 10: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 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

"David Linthwaite" <dlinthwaite@lintel.co.uk> wrote in message news:cd6ab3$4r8$1@news.xmission.com... > > 3. if my memory serves me right, temporary dbspaces must be raw - > are you using the character special devices. I don't think this is true (in fact I'm certain that it isn't). Could you be thinking of the opinion sometimes stated (I think Art Kagel may be one of its advocates) that sorting for index builds can be more efficient if file system space is used for the sorts rather than Informix's own temporary dbspaces?

On Fri, 16 Jul 2004 07:55:20 -0400, My Name Is Bruce and I'm A Sock Puppet wrote: > Neil Truby wrote: > >> "David Linthwaite" <dlinthwaite@lintel.co.uk> wrote in message >> news:cd6ab3$4r8$1@news.xmission.com... >>> >>> 3. if my memory serves me right, temporary dbspaces must be raw - are you >>> using the character special devices. >> >> I don't think this is true (in fact I'm certain that it isn't). Could you >> be thinking of the opinion sometimes stated (I think Art Kagel may be one >> of its advocates) that sorting for index builds can be more efficient if >> file system space is used for the sorts rather than Informix's own >> temporary dbspaces? > > G'day digger deviant! > > Fair dinkum, old Art's got a point there. The parallel sort package is much > faster than temporary dbspaces for index builds. > G'day Bruce, Your comparing apples to golf balls my dear puppet. The parallel sort package can be applied to either temp dbspaces or filesystem space. Yes I advocate the latter and hold that it's faster, but one can, by not setting PSORT_DBTEMP but only PSORT_NPROCS, invoke parallel sorting using the temp dbspace(s) listed in DBSPACETEMP. BTW, either way, sorting is fastest if you have at least three sort-work areas listed either in DBSPACETEMP or PSORT_DBTEMP and I find as many as 6 provides some incremental improvement. Art S. Kagel

Neil Truby wrote: > "David Linthwaite" <dlinthwaite@lintel.co.uk> wrote in message > news:cd6ab3$4r8$1@news.xmission.com... >> >> 3. if my memory serves me right, temporary dbspaces must be raw - >> are you using the character special devices. > > I don't think this is true (in fact I'm certain that it isn't). > Could you be thinking of the opinion sometimes stated (I think Art Kagel > may be one of its advocates) that sorting for index builds can be more > efficient if file system space is used for the sorts rather than > Informix's own temporary dbspaces? G'day digger deviant! Fair dinkum, old Art's got a point there. The parallel sort package is much faster than temporary dbspaces for index builds. -- Strewth! Stick a sock in it, Sheila!