Kbase 21206: SQL-92 - Temp Tables (Mxxxx) Limit and How to Work Around It
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/5/2008 |
|
Status: Verified
GOAL:
What are SQL-92 temp tables and how to get around their built-in size limit ?
FACT(s) (Environment):
OpenEdge 10.x
Progress 9.x
FIX:
SQL-92 temp tables, with name format of Mxxxx, are created during a transaction and released when the transaction ends. These temp files are (currently) designed to efficiently handle very large amounts of data. There is a 500 MB size limit on these tables.
With Progress 9.1D05 or earlier there is no supported, reliable way to increase this limit. To get around it however, add one or more indexes so that temp tables are not needed. If tables are created in the 4GL Data Dictionary, then add indexes from the Data Dictionary; if tables are created by SQL, add indexes from SQL.
However in progress 9.1D06 or later the -SQLTempDisk database startup parameter was introduced which allows you to increase the limit of the SQL-92 temp tables. The default value for this parameter is 500000 (500 MB).