DBUPSPACE environment variable
Use the DBUPSPACE environment variable to specify the amount of system disk space and the amount of memory that the UPDATE STATISTICS MEDIUM and UPDATE STATISTICS HIGH statement can use when it reads and sorts column values to construct column distributions. The DBUPSPACE setting can also request SET EXPLAIN output to describe the execution path for calculating the statistical distributions.
- disk
- is an unsigned integer, specifying the disk space (in KiB) to allocate for sorting in UPDATE STATISTICS MEDIUM and HIGH operations.
- memory
- is an unsigned integer, specifying the maximum amount of sorting memory (in MiB, in the range from 4 to 50 megabytes) to allocate without using PDQ.
- directive
- is an unsigned integer, encoding one of the following directives
for the UPDATE STATISTICS execution plan:
- 1: Do not use any indexes for sorting. Print the entire plan for update statistics in the sqexplain.out file.
- 2: Do not use any indexes for sorting. Do not print the plan for update statistics.
- 3 or greater: Use available indexes for sorting. Print the entire plan for update statistics in explain output file.
setenv DBUPSPACE 2500:1 After you set this value, the database server will attempt to use no more than 2,500 KiB of disk space during the execution of an UPDATE STATISTICS MEDIUM or HIGH statement. If a table requires 5 megabytes of disk space for sorting, then UPDATE STATISTICS accomplishes the task in two passes; the distributions for one half of the columns are constructed with each pass. For a table of a given storage size, this parameter determines the number of passes, but no pass can write less than a full column.
If you do not set DBUPSPACE, the default setting is 1 megabyte (1,024 KiB) for disk, and 15 megabytes for memory. If you attempt to set the first DBUPSPACE parameter to any value less than 1,024 KiB, it is automatically set to 1,024 KiB, but no error message is returned. If this disk value is not large enough to allow more than one distribution to be constructed at a time, at least one distribution is done, even if the amount of disk space required to do this is more than what DBUPSPACE specifies. That is, regardless of the disk parameter setting for DBUPSPACE, the largest individual column storage requirement of a table determines the actual upper limit on disk space for a single pass in any UPDATE STATISTICS HIGH or MEDIUM operation.