Re: Temporary dbspaces

Jonathan Myatt wrote: > > A quick question for the gurus... > > If I have four temporary dbspaces defined, will a large sort which > creates a large temporary table use all four, or can the temp table > not "span" dbspaces? Would I be better off consolidating the available > space into two bigger temp dbspaces? You do not state what version you are using so this is a multi-part, version spanning answer: If you do not have the PSORT_DBTEMP environment variable defined in either the server's or the client's environment then temp dbspaces listed in DBSPACETEMP are used to created sort work temp tables round robin across as many of the available spaces as is practical (depending on the number of sort threads and the size of the result set). Essentially each sort thread creates a set of temp tables for its intermediate results and each of these temp tables lives in one temp dbspace but the multiple temp tables are created in the various available temp dbspaces round robin. If PSORT_DBTEMP is set then the UNIX filesystems listed there are used instead (for large sorts this CAN be faster). In 7.[12]x implicit temp tables for SCROLL CURSORS, as the result of INTO TEMP clauses, holding preimages during an archive, and for sort-work files are EACH created in only one dbspace with a table being assigned to a dbspace round robin as are explicit temp tables that do not contain an IN <dbspace> clause. In version 7.30+ each temp table is created fragmented across ALL of the dbspaces named in DBSPACETEMP. (This BTW will break code that assumes that rows in a temp table have a rowid!) So, bottom line, keep the multiple temp dbspaces, they are being used. It is just that HOW they are used depends on the version. Art S. Kagel