Re: Multiple temp tables

In article <czlGHRAZTXfzEwdp@smooth1.demon.co.uk>, David Williams <djw@smooth1.demon.co.uk> wrote: > > In article <5le44e$dar$1@news13.gte.net>, Paul Drake <pdrake1@gte.net> > writes > >When I make a temp table using "WITH NO LOG", it appears that multiple > >tables are made for that one temp table (multiple entries in systabnames). > >Trying it on two different systems, it appears the number of tables it > >makes is the same as the number of dbspaces in DBSPACETEMP. This does not > >happen with logging (only 1 table made). Has anyone else noticed this? Is > >there a reason that this happens? > > > > > > Thanks, > > Paul > > Performance - the table gets fragments across the entries in > DBSPACETEMP each entry should be on a different disk thus allowing > parallel access and distributing the load across several disks. > > -- > David Williams If you have temporary dbspaces (created with Temp flag) listed in DBSPACETEMP environment variable or configuration parameter, these dbspaces are used only for unlogged temporary tables. Temporary dbspaces are unlogged and ignored during archive process. You need to add WITH NO LOG clause to your CREATE TEMP TABLE and SELECT .. INTO TEMP statements in order to use temporary dbspaces. If you omit WITH NO LOG clause, implicit temp tables are created in rootdbs and explicit temp tables are created in dbspaces where current database is. Result is that WITH NO LOG temp tables are fragmented across dbspaces listed in DBSPACETEMP as David said, logged temp tables are unfragmented in rootdbs or some other dbspace. Regards, Miroslav Lorenc SSA Czech Republic -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet