TMPSPACE_LIMIT Environment Option (XPS)

Use the TMPSPACE_LIMIT environment option to specify the amount of temporary space on each coserver that a query can use for query operator overflow. Temporary space limits do not affect the creation of temporary tables. The limits apply only to the query-operator overflow that occurs when a query cannot get enough memory to complete execution.

By default, TMPSPACE_LIMIT is OFF, and a query can use all available temporary space for operator overflow. If the DS_TOTAL_TMPSPACE configuration parameter is not set, then setting TMPSPACE_LIMIT has no effect.

If you enter a value between '0' and '100' as an argument to the TMPSPACE_LIMIT option, the database server sets the temporary space quota to the percent of available temporary space that the ONCONFIG parameter DS_TOTAL_TMPSPACE, specifies.

• If the value is 100, queries can use only the amount of temporary space on each coserver that DS_TOTAL_TMPSPACE specifies.
• If the value is 0, query operators cannot overflow to temporary space.
• If you do not specify a value, a query can use all available temporary space on each coserver, as limited by DS_TOTAL_TMPSPACE, for query operator overflow.

To require queries to use only the amount of temporary space specified by the setting of DS_TOTAL_TMPSPACE on each coserver, execute this statement:

SET ENVIRONMENT TMPSPACE_LIMIT ON

To limit queries to 50 percent of DS_TOTAL_TMPSPACE on each coserver, execute the following statement:

SET ENVIRONMENT TMPSPACE_LIMIT "50";