Kbase P44256: Certain SQL-92 queries are giving error 7631.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
06/03/2004 |
|
Status: Unverified
SYMPTOM(s):
Peer Direct fails because of environment variables need to be set.
Certain SQL-92 queries are giving error 7631.
Unable to allocate disk block for temp table use [MM - No data block] (7631)
CAUSE:
The SQL-92 server process while executing a query or command needs to use SQL temp tables. Temp tables are used for aggregation, sorting, update statistics usage, etc. The SQL-92 server needs to allocate a disk block for some temp table, but it failed in the allocation. Among the possible reasons, there are: disk space not available in chosen work directory, SQL-92 limit for temp tables exceeded, I/O failure on disk device, incorrect use of indexes.
FIX:
- Use indexes where possible and verify this with _Sql_Qplan (See Solution 20007 ). You can eventually run UPDATE STATISTICS to improve performance (See Solution 20952 ).
- Make sure there is enough disk space available where the temp tables are being created. You can specify the location with the environment variable WRKDIR (See Solution 19920 ).
- Do not use ORDER BY if not needed. Depending on the query plan, it might create huge temp tables for sorting.
- If you are hitting the 500MB limit for the temp tables (See Solution 21206 ), then increase the limit by specifying the TPE_MM_SWAPSIZE environment variable. The default value is 500000 (500MB). The database needs to be restarted to take this change into account.