It's not really possible to accurately predict TempSize since it depends largely on your workload. The best way to tune it is to start with a 'reasonable' value (i.e. one that allows your workload to run without any 'out of temp space' errors) - a good guestimate might be 10% - 25% of PermSize. Then run your workload and monitor space usage via the ttIsql 'dssize' command. This displays 6 values:
The 'ALLOCATED_SIZE' value is the amount of memory allocated (as specified in PermSize / TempSize).
The 'IN_USE_SIZE' is how much of that is in use at the moment you run the 'dssize' command.
The 'IN_USE_HIGH_WATER' value is the highest usage since the database was loaded into memory (i. e. started up). For Temp space, if this value is close to TEMP_ALLOCATED_SIZE then you should consider increasing TempSize while if it is much less than TEMP_ALLOCATED_SIZE then you can safely reduce TempSize.
Message was edited by: ChrisJenkins
Thanks Chris...We are monitoring dssize command output and is aware of the values in it...Wanted to know whether T10 has got any recommendation for TempSize calculation depending on data size or any other value. The guestimate reply was a information for us...thanks for that