Does any body has any links for documentation on how SMS tempspace is allocated in a multi node systems.

I have four containers on each node and i have 7 nodes.
How will the tempaces be allocated ?
Will it allocate one extent on one node and goes to next node or will it allocate one extent on all the containers and goes to next node ?

Thanks a head
Prasad

I don't have any links, but each node is considered its own database and will have its own tempspace. The only thing really different about each node/partition is the cordinatior/primary will only have the system catalogs and none of the other partitions will have them. It will allocate the space on the node that its on or bring that data back to the cordinating node.

If you submit the query to pull the info from that table it would submit the same query to each partition that has data on it. It will sort and do some of the work on the node/partition that it got the data for and will use that tempspace definded in that node/partition. If you have data on partition/node 1 it will not use the tempspace defined to partition 2, etc. The only partition/node that all of them would use would be the primary/coordinator partition when it brings all the data back from the other partitions.

If you have a test system, change the sort to a real small number and then run a large select with a group by or sort and it should spill to disk, run it against that one partition and you can see the files being created in the directory since its sms, you can check the other directory where you put the temporary space at and you'll see it not being used.