Allow more than 50 MB for UPDATE STATISTICS MEDIUM/HIGH sort operations

UPDATE STATISTICS MEDIUM/HIGH operations that do not use indices for sort are restricted to 50 MB before the system will use temporary space. This limit has remained unchanged for a long time and could be increased quite a lot on a modern system.

The maximum of 50 MB is currently set using the DBUPSPACE parameter and this idea proposes no change to this, merely that it should be possible to set a higher value.

Needed By

Quarter

Post comment

Guest

Reply
| Sep 26, 2024
Set the DS_NONPDQ_QUERY_MEM parameter to a value up to 25% of DS_TOTAL_MEMORY. That parameter specifies the amount of memory to use for queries that do not unse PDQPRIORITY, including sorting and grouping. You just need to set DS_TOTAL_MEMORY high enough to allow DS_NONPDQ_QUERY_MEM to be set large enough for all of your in-memory sorting needs.
You can monitor memory sorting versus disk sorting with the sysmaster:sysprofile table:
```
> select * from sysprofile where name matches '*sort*'; 

name                         value  

totalsorts                    4311 
memsorts                      4309 
disksorts                        2 
maxsortspace                   224 
```
0 reply Hide replies

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.

Please enter your email address

RELATED IDEAS

Allow more than 50 MB for UPDATE STATISTICS MEDIUM/HIGH sort operations