Kbase 18848: Excessive Temp-Table Defintions May Cause Error 40 or 14675
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/12/2010 |
|
Status: Verified
SYMPTOM(s):
Error 40 running an application.
Error 14675 running an application.
SYSTEM ERROR: Attempt to define too many indexes. (40)
SYSTEM ERROR: Attempt to define too many indexes for area <area number> database <db name>. (40) (14675)
FACT(s) (Environment):
Application is using temp tables
Progress 8.x
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
If error 40 occurs while running an application, rather than while modifying Data Dictionary definitions, then the problem is that there are too many TEMP-TABLEs defined by the application.
In current releases, this is also identifiable by the fact that the database name listed is that of the DBI* temporary file for the client.
TEMP-TABLEs are stored in their own database, local to each client session. As with any other Progress database, the TEMP-TABLE database can only have up to 32,000 indexes defined. The indexes belonging to each TEMP-TABLE defined by the application will add up to reach this limit.
It's highly unlikely, though, that an application will purposely define so many TEMP-TABLEs that this limit will be exceeded; as such, error 40 usually indicates some kind of anomaly. The most typical ones are:
- Static TEMP-TABLEs are defined inside non-persistent procedures that are called synchronously from within a loop that is contained inside an active TRANSACTION. Until the transaction expires, these temp-tables cannot be removed from the DBI file.
- Static TEMP-TABLEs are defined inside persistent procedures that are instanced but not cleaned up properly. As with the previous case, over time there can be hundreds of persistent procedures in memory, with thousands of TEMP-TABLE definitions.
- Dynamic TEMP-TABLEs are created by the application, and then the code does not clean them up properly. Over time, this can lead to hundreds of stray dynamic TEMP-TABLEs, and at some point the limit of 32,000 indexes will be hit, causing error 40.
- Dynamic TEMP-TABLEs are created automatically by Progress; even though these TEMP-TABLEs are created by Progress, it's up to the application to clean them up. Please see Solution P112397 for further details regarding when Progress creates dynamic TEMP-TABLEs automatically.
- An occurrence of error 40 could be due to a Progress bug, if all the above have been positively ruled out.
FIX:
It should be investigated whether stray dynamic TEMP-TABLEs and/or persistent procedures are lingering in memory after their due life expectancy.
One way of achieving that is to add some code in a place where (according to your application logic) there should be no or few dynamic TEMP-TABLEs and persistent procedures, and produce warnings in case the number of dynamic TEMP-TABLEs or persistent procedures starts climbing up. Solution P113170 and Solution P21951 can provide coding tips to this end.
If the above rules out application problems, then please search the Knowledge Center for known Progress bugs that may cause error 40, or call Technical Support for further help.
FIX:
Another way of tracking this problem down, in the event that the problem is caused by making numerous synchronous calls to non-persistent procedures which define static temp-tables during a transaction, would be to use the 4gltrans and 4gltrace log entry types together and reviewing the client log to determine which procedures are being executed within a transaction.
Following is an example of using these log entry types:
-clientlog <logfilename> -logentrytypes "4gltrans,4gltrace" -logginglevel 3