Re: Informix 7.24 Temp Tables Not Dropping

jeffm@lysander.co.uk wrote: > > In article <7d74rd$hrt$2@news-1.news.gte.net>, > June Tong <june_t@hotmail.com> wrote: > > jeffm@lysander.co.uk wrote: > > > > > I have a system running IDS 7.24 on HP-UX 10.20. Some software is creating > > > temporary tables using 'SELECT...INTO TEMP' and these are going into the > > > rootdbs. The database is logged and tables are NOT created 'WITH NOLOG'. > > > > > > This in itself isn't the problem because these tables must be created WITH > > > NOLOG to go to the temporary dbspaces (despite what manuals say!)rather than > > > rootdbs when running in a logged database. > > > > I don't know what the manuals say (don't actually have any, anymore, and can't > > be bothered trying to open up the PDF files), but it's important to > distinguish > > between temporary dbspaces and DBSPACETEMP. DBSPACETEMP does not have to be a > > temporary dbspace, and tables do not have to be created WITH NO LOG in order > to > > go into DBSPACETEMP. If your DBSPACETEMP is a temporary dbspace, however, > then > > you DO need to create the tables WITH NO LOG in order for them to be created > in > > your DBSPACETEMP. This is a function of whether it is a temporary dbspace, > not > > generic to DBSPACETEMP. > > > > June > > -- > > june_t@hotmail.com > > Unfortunately I do know what the manuals say and it doesn't add up to what > happens. Unless you specify 'WITH NO LOG' temporary tables created whilst > connected to a logged database go to 'rootdbs' and NOT the temporary dbspaces > (whether listed in DBSPACETEMP or NOT). Only by specifying 'WITH NO LOG' can > we get them into the temporary dbspaces. However, the real problem is that > when created in 'rootdbs' they NEVER get dropped (whether application is > existed or crashed) until the recovery stage if the engine is bounced. > I don't know what to do with dropping issue, but what June as well as documentation says is: ... The list of dbspaces can contain standard dbspaces, temporary dbspaces, or both. Use a colon or comma to separate the dbspaces in your list. If both standard and temporary dbspaces are listed in the DBSPACETEMP configuration parameter or environment variable, the following rules apply: * Sort, backup, implicit, and nonlogging explicit temporary tables are created in temporary dbspaces if adequate space exists. * Explicit temporary tables created without the WITH NO LOG option are created in standard (rather than temporary) dbspaces. ... Therefore if you will list standard dbspaces in DBSPACETEMP, then explicit temporary tables for logged database without 'WITH NO LOG' _should_ be in that dbspaces. Anyway not so much help for dropping :-( Vardan > Thanks, > > Jeff > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own > -- vaar@geocities.com