IBM Support

Update Statistics fails because of limited sort space, and the query plan shows the Sort Data is far larger than the disk space allocated to the table.

Technote (troubleshooting)


Problem(Abstract)

You run update statistics and it fails with errors 567 and 179. The errors indicate that the IDS instance does not have enough temporary dbspace on disk for the temporary files created during the sort. However, the space occupied by the table on disk is far less than the Sort data reported by the query plan.

Symptom

Your update statistics command fails, returning the following errors:

  567: Cannot write sorted rows.

  179: ISAM error: no free disk space for sort


Cause

Check to determine if you have a column or columns of varchar data type. The space occupied by varchar data on disk in a regular dbspace is only the space required for the stored data, or the minimum length of the data type if it is declared. However, the data is expanded to its maximum size when read from tablespace on disk into memory, and that size is retained when the temporary sort files are written to temporary dbspaces. For example, a string of two characters in a varchar(255) field will occupy only several bytes on disk. However, when the data is read into memory, it will occupy the full field length, in this case memory required for storing 255 bytes of data.

The Sort data reported in the query plan is based on the full data length. In the example that follows, the table occupied 31.9 MB on disk and the instance was configured with 80 MB temporary dbspace. However, update statistics failed with errors 567 and 179. The optimizer calculated the Sort data at 127.5 MB. The table had one field of varchar(255) and two constant-length fields of char(10). The string lengths in the varchar field varied from one to six bytes, thereby occupying far less than the maximum of 255 bytes on disk.


Table: informix.tab1
Mode: HIGH
Number of Bins:      267     Bin size     5000
Sort data   127.5 MB Sort memory granted    15.0 MB
Estimated number of table scans 3
PASS #1 col1
PASS #2 col2
PASS #3 col3

Resolving the problem

  • Increase memory available for sorting. This should also decrease the time required for the update statistics to complete.
  • Increase temporary dbspace size.
  • Change the update statistics so that it sorts less data. For example, change from update statistics high to update statistics medium.
  • Decrease the maximum length of the varchar data type.


Note: There are other possible sources of the 567 and 179 errors.

Related information

Output for UPDATE STATISTICS from the SET EXPLAIN state
The VARCHAR data type.

Document information

More support for: Informix Servers

Software version: 11.5, 11.7, 12.1

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1358640

Modified date: 08 January 2015


Translate this page: