Role of DBSPACETEMP (II)

Hi, More than a week ago I posted a question about the role of the envvar DBSPACETEMP. I got some usefull responses but I'm still not satisfied.I think that the documentation is not very clear about DBSPACETEMP. So here is my second post. Below is the output of onstat -d. Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 16:32:08 -- 41232 Kbytes Dbspaces address number flags fchunk nchunks flags owner name c9150158 1 1 1 1 N informix rootdbs c9150958 2 1 2 1 N informix logdbs c9150a18 3 1 3 2 N informix wocasdbs c9150ad8 4 2001 4 1 N T informix tempdbs 4 active, 2047 maximum Chunks address chk/dbs offset size free bpages flags pathname c9150218 1 1 0 25000 4145 PO- /dev/vg01/raboom_root c91505d8 2 2 0 25000 9947 PO- /dev/vg01/raboom_log c91506b8 3 3 0 125000 23 PO- /dev/vg01/raboom_wocas c9150798 4 4 0 25000 24947 PO- /dev/vg01/raboom_tmp c9150878 5 3 0 1024000 981250 PO- /dev/vg01/rinfaboom 5 active, 2047 maximum As you can see we configured a dbspace called tempdbs and a dbspace called rootdbs. If I create a temp table and DO NOT specify the WITH NO LOG option, the temp table will ALWAYS be created in the root dbspace, even if I specify DBSPACETEMP=tempdbs in my environment. If I create the temp table WITH the WITH NO LOG option however, the temp will be created in the dbspace that is specified through the DBSPACETEMP envvar. I can't imagine that this is the intented behaviour. Can someone clarify my problem. regards, Henk

Henk, I too have has confusion in the past about where and when temporary space is used. But let me share what I know, and hopefully another person will shed additional light on this matter. I don't believe that DBSPACETEMP is an environment variable per se. If you are setting it in the application environment, either by putting it the environment of the process that is launching the database application or by altering the environment with the database application, I don't believe the will be desired effect. I do believe that DBSPACETEMP is configurable engine parameter, that can be set in the onconfig file. If it is set to one or dbspaces, in your case "tempdbs", then I believe that all of your temp files will obtain their resources from tempdbs and not rootdbs (or from /tmp for that matter). After changing your onconfig file, you will need to bounce the engine for this change to take effect. Vic Henk van der Geld wrote: > Hi, > > More than a week ago I posted a question about the role of the envvar > DBSPACETEMP. I got some usefull responses but I'm still not satisfied.I > think that the documentation is not very clear about DBSPACETEMP. So > here is my second post. Below is the output of onstat -d. > > Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 16:32:08 -- > 41232 Kbytes > > Dbspaces > address number flags fchunk nchunks flags owner name > c9150158 1 1 1 1 N informix rootdbs > c9150958 2 1 2 1 N informix logdbs > c9150a18 3 1 3 2 N informix wocasdbs > c9150ad8 4 2001 4 1 N T informix tempdbs > 4 active, 2047 maximum > > Chunks > address chk/dbs offset size free bpages flags pathname > c9150218 1 1 0 25000 4145 PO- > /dev/vg01/raboom_root > c91505d8 2 2 0 25000 9947 PO- > /dev/vg01/raboom_log > c91506b8 3 3 0 125000 23 PO- > /dev/vg01/raboom_wocas > c9150798 4 4 0 25000 24947 PO- > /dev/vg01/raboom_tmp > c9150878 5 3 0 1024000 981250 PO- > /dev/vg01/rinfaboom > 5 active, 2047 maximum > > As you can see we configured a dbspace called tempdbs and a dbspace > called rootdbs. If I create a temp table and DO NOT specify the WITH NO > LOG option, the temp table will ALWAYS be created in the root dbspace, > even if I specify DBSPACETEMP=tempdbs in my environment. If I create the > temp table WITH the WITH NO LOG option however, the temp will be created > in the dbspace that is specified through the DBSPACETEMP envvar. I can't > imagine that this is the intented behaviour. Can someone clarify my > problem. > > regards, > Henk -- ; manifest.init; ; WARNING - Do not edit this file. It will likely be overwritten if you do so. VendorID = "Netscape" ProductID = "Communicator4.7" PlatformID = "Win32" BuildID = "19990909" ManifestVersion = 1 ApplicationName = "Communicator4.7" DisableDontAsk = 1 MaxTriggerCount = 1 DisableUI = 0 DisableWizard = 0 EnableSaveAs = 1 KeyVetoDisabled = 0 NubFirstTimeTrigger = 1 UserEvents = "0", 0, 40, 0x00000000 ServerCount = 1 ServerAddress0 = 1, "http://talkback.netscape.com/spiral-bin/Collector.dll"; NubCollectors = UIProcess, CommandLine, StackDump, CurrentUser, ModuleList, MemoryStatus, ProcessList95, ProcessListNT, ExceptionType, Registers, PCMemory, PC, StackTrace, ThreadList95, ThreadListNT, ThreadRegisters, ThreadStackDump, ThreadIDList, ThreadIDTrigger, ThreadStackTrace, Trigger, TriggerTime UIProcess = 0xa000000f, "SWin32 UI Process" CommandLine = 0xa000000d, "SWin32 Command Line" StackDump = 0xa0000001, "SDump of Stack windows", 2048 CurrentUser = 0xa000000e, "SWin32 Current User" ModuleList = 0xa0000003, "SLoaded Module list Win32" MemoryStatus = 0xa000000b, "SWin32 MEMORYSTATUS struct" ProcessList95 = 0xa0000009, "SWindows 95 process list" ProcessListNT = 0xa0000007, "SWindows NT process list" ExceptionType = 0xa0000004, "SWin32 Processor exception type" Registers = 0xa0000000, "SWin32 x86 registers" PCMemory = 0xa000000a, "SCode memory windows", 0, 32 PC = 0xa0000002, "SPC at time of crash" StackTrace = 0xa0000005, "SWin32 stack trace" ThreadList95 = 0xa0000008, "SWindows 95 thread list" ThreadListNT = 0xa0000006, "SWindows NT thread list" ThreadRegisters = 0xa0000010, "SWin32 x86 thread registers" ThreadStackDump = 0xa0000011, "SStack dump thread" ThreadIDList = 0xa0000013, "SWin32 thread id list" ThreadIDTrigger = 0xa0000014, "SWin32 trigger thread id" ThreadStackTrace = 0xa0000012, "SWin32 thread stack trace" Trigger = 0x80000000, "STrigger Event" TriggerTime = 0x80000001, "SNub trigger event time" TransceiverCollectors5 = CurrentUser,MemoryStatus,XcvrProcessList95,XcvrProcessListNT XcvrProcessList95 = 0x3000000e, "SWindows 95 process list" XcvrProcessListNT = 0x3000000f, "SWindows NT process list" TransceiverCollectors = ModuleListInfo, DriveList, ScreenInfo, NetworkCard, ComputerName, GetWindowsVersionEx, ManifestVersionColl, DeploymentIDColl, VendorIDColl, ProductIDColl, PlatformIDColl, BuildIDColl, Platform ModuleListInfo = 0x3000000b, "SWin32 module list info" DriveList = 0x30000006, "SWin32 Drive Info" ScreenInfo = 0x3000000c, "SWindows Screen Info" NetworkCard = 0x30000007, "SWin32 NIC info" ComputerName = 0x30000010, "SWindows Computer Name" GetWindowsVersionEx = 0x30000001, "SWindows GetVersionEx" ManifestVersionColl = 1, "SManifest ver transceiver init" DeploymentIDColl = 2, "SDeployment ID", 1 VendorIDColl = 2, "SVendor ID", 2 ProductIDColl = 2, "SProduct ID", 3 PlatformIDColl = 2, "SPlatform ID", 4 BuildIDColl = 2, "SBuild ID", 5 Platform = 3, "SPlatform Identifier", 0x30000000 UserEvents = UserEvents TraceConfig = 128, 0, 20 AssertConfig = 0, 20, 0 TraceParamTrackCount = 32 AssertParamTrackCount = 32 MaxBoxAge = 172800 RandomFilter = 100, 100 APIErrorConfig = 0, 20 FullCircleURL0 = 1, 1, "http://www.fullcirclesoftware.com/";

Henk van der Geld wrote: > If I create a temp table and DO NOT specify the WITH NO > LOG option, the temp table will ALWAYS be created in the root dbspace, > even if I specify DBSPACETEMP=tempdbs in my environment. BUGS FIXED IN THE 7.31.TC5 RELEASE 116590 TEMP TABLES WITH LOG WILL BE PLACED IN ROOTDBS WHEN DBSPACETEMP IS SET Really temp table (with log) must be created in dbspace where currently connected database is created. > Henk Leonid.

The temp. dbspaces cannot contain logged files. It's recommended to have multiple temp dbspaces split across different disks. Dbspaces listed in the onconfig for parameter DBSPACETEMP do not have to be flagged as temporary. So by creating more than one dbspace to use for temp. and by having some of them flagged as temp. and some created as normal you can resolve this issue. E.g. DBSPACETEMP dbs_temp1:dbs_temp2:dbs_temp3:dbs_temp4:dbs_temp5 where the even numbered spaces are created as temporary and the odd numbered are created as normal dbspaces. The optimiser fragments the temp. tables round-robin across all available temp. dbspaces. HTH -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf . Henk van der Geld wrote in message <388307CA.12CDDFEB@centric.nl>... >Hi, > >More than a week ago I posted a question about the role of the envvar >DBSPACETEMP. I got some usefull responses but I'm still not satisfied.I >think that the documentation is not very clear about DBSPACETEMP. So >here is my second post. Below is the output of onstat -d. > >Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 16:32:08 -- >41232 Kbytes > >Dbspaces >address number flags fchunk nchunks flags owner name >c9150158 1 1 1 1 N informix rootdbs >c9150958 2 1 2 1 N informix logdbs >c9150a18 3 1 3 2 N informix wocasdbs >c9150ad8 4 2001 4 1 N T informix tempdbs > 4 active, 2047 maximum > >Chunks >address chk/dbs offset size free bpages flags pathname >c9150218 1 1 0 25000 4145 PO- >/dev/vg01/raboom_root >c91505d8 2 2 0 25000 9947 PO- >/dev/vg01/raboom_log >c91506b8 3 3 0 125000 23 PO- >/dev/vg01/raboom_wocas >c9150798 4 4 0 25000 24947 PO- >/dev/vg01/raboom_tmp >c9150878 5 3 0 1024000 981250 PO- >/dev/vg01/rinfaboom > 5 active, 2047 maximum > >As you can see we configured a dbspace called tempdbs and a dbspace >called rootdbs. If I create a temp table and DO NOT specify the WITH NO >LOG option, the temp table will ALWAYS be created in the root dbspace, >even if I specify DBSPACETEMP=tempdbs in my environment. If I create the >temp table WITH the WITH NO LOG option however, the temp will be created >in the dbspace that is specified through the DBSPACETEMP envvar. I can't >imagine that this is the intented behaviour. Can someone clarify my >problem. > >regards, >Henk >

Victor Glass wrote: > > Henk, > > I too have has confusion in the past about where and when temporary space is > used. But let me share what I know, and hopefully another person will shed > additional light on this matter. > > I don't believe that DBSPACETEMP is an environment variable per se. If you > are setting it in the application environment, either by putting it the > environment of the process that is launching the database application or by > altering the environment with the database application, I don't believe the > will be desired effect. DBSPACETEMP is BOTH an ONCONFIG parameter and an environment variable. The parameter becomes the default for connections that do not specify a value for themselves or for which the value is invalid (which gets a mention in the message log). The connection protocol includes passing several environment variables amoung which is DBSPACETEMP. > I do believe that DBSPACETEMP is configurable engine parameter, that can be > set in the onconfig file. If it is set to one or dbspaces, in your case > "tempdbs", then I believe that all of your temp files will obtain their > resources from tempdbs and not rootdbs (or from /tmp for that matter). > > After changing your onconfig file, you will need to bounce the engine for > this change to take effect. > > Vic > > Henk van der Geld wrote: > > > Hi, > > > > More than a week ago I posted a question about the role of the envvar > > DBSPACETEMP. I got some usefull responses but I'm still not satisfied.I > > think that the documentation is not very clear about DBSPACETEMP. So > > here is my second post. Below is the output of onstat -d. [SNIP] > > > > As you can see we configured a dbspace called tempdbs and a dbspace > > called rootdbs. If I create a temp table and DO NOT specify the WITH NO > > LOG option, the temp table will ALWAYS be created in the root dbspace, > > even if I specify DBSPACETEMP=tempdbs in my environment. If I create the > > temp table WITH the WITH NO LOG option however, the temp will be created > > in the dbspace that is specified through the DBSPACETEMP envvar. I can't > > imagine that this is the intented behaviour. Can someone clarify my > > problem. [SNIP] OK, IFF there are non-temporary dbspaces listed in DBSPACETEMP then logged temp tables will be created there, otherwise in ROOTDBS. Non-logged temp tables are created, by default, in temporary dbspaces listed in DBSPACETEMP otherwise in normal dbspaces listed in DBSPACETEMP otherwise in rootdbs. Art S. Kagel

Art, Thanks for the info about logged temp tables. I knew about the non-logged ones, but thought that you had little to no control over the ones that were logged. -- Dan Michaelis Database Administrator dan@kax.com Sent via Deja.com http://www.deja.com/ Before you buy.