We're running UDB 8.2 on AIX 5.2 .

Until recently we only had tablespaces of 4k pagesize and 1 system temporary tablespace of 4k pagesize.

We've just implemented some tablespaces with 32k pagesize.

The question is whether UDB truly requires/expects a system temporary tablespace of 32k pagesize should there be sort overflow to disk for the 32k pagesize tables/tablespaces or will UDB see if the sorted rows can fit in the 4k pagesize system temporary tablespace, then UDB will be a happy camper.

TIA,
Ruby

If you have a 32K page user tablespace, you need a 32K system temporary tablespace.

That's what I thought and implemented (and is such documented in Admin: Implementation). However, someone suggested that if a 32k pagesize system temporary tablespace didn't exist, UDB might, if possible, use a smaller system temporary tablespace ???

If you have a 32K page user tablespace, you need a 32K system temporary tablespace.

Stay tuned ...
I just spoke with IBM and I was told that if a system temporary tablespace of 32k pagesize doesn't exist, UDB will try to use, let's say, a 4k pagesize system temporary tablespace.
I then asked when/how will an SQL1584N message get issued. I await feedback from IBM.
Ruby

IBM feedback:
If you search on our V8.1 Information Center.

http://publib.boulder.ibm.com/infoce...help/index.jsp

--Search on: tempspace row size

--Choose: Table space design





If a database uses more than one temporary table space and a new temporary
object is needed, the optimizer will choose an appropriate page size for
this object. That object will then be allocated to the temporary table
space with the corresponding page size. If there is more than one
temporary table space with that page size, then the table space will be
chosen in a round-robin fashion. In most circumstances, it is not
recommended to have more than one temporary table space of any one page
size.
If queries are running against tables in table spaces that are defined
with a page size larger than the 4 KB default (for example, an ORDER BY on
1012 columns), some of them may fail. This will occur if there are no
temporary table spaces defined with a larger page size. You may need to
create a temporary table space with a larger page size (8 KB, 16 KB, or 32
KB). Any DML (Data Manipulation Language) statement could fail unless
there exists a temporary table space with the same page size as the
largest page size in the user table space.
You should define a single SMS temporary table space with a page size
equal to the page size used in the majority of your user table spaces.
This should be adequate for typical environments and workloads.


You will not find any documentation on a 'scenario' of how or why you
would encounter SQL1584N.
The only docuumentation you'll find on this is "Message Reference" or
"Information Center".

If you have a 32K page size because your row size requires it, then you will need a 32K system temporary tablespace.

If your row size is less than 4K, but you defined a 32K page size for the tablespace, the you might be able to get away without a 32K system temporary tablespace.

I don't understand why you even care about this. Just define the 32K system temporary tablespace as SMS and DB2 will use it if necessary, but will not use any disk space when not being used.

I always create one very small 32K bufferpool and one 32K system temproary tablespace (SMS) for every database. You never know when someone will want to join a lot of tables together.