Kbase P141450: Running UPDATE ALL COLUMN STATISTICS on database results in lock table overflow
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
2/16/2009 |
|
Status: Unverified
SYMPTOM(s):
Running UPDATE ALL COLUMN STATISTICS on database results in lock table overflow
Lock table overflow, increase -L on server (915)
Executing SQL command:
UPDATE ALL COLUMN STATISTICS;
COMMIT;
FACT(s) (Environment):
Affects databases with larger schemas
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
-L is not set high enough
FIX:
Option #1
Increase -L setting of the database. To calculate an approximate value for -L for this operation, refer to solution P121200 for more information
Option #2
Execute UPDATE ALL COLUMN STATISTICS against each table in the database separately, rather than all tables in the database in one go.
To make this task easier, the following code can be used to generate SQL scripts for this purpose:
/* Tables created via ABL */
OUTPUT TO updAllColStats.sql.
FOR EACH _file WHERE _tbl-type = "T" NO-LOCK:
PUT UNFORMATTED "UPDATE ALL COLUMN STATISTICS FOR PUB." + _File-Name + ";" SKIP.
END.
PUT UNFORMATTED "COMMIT;" SKIP.
OUTPUT CLOSE.
/* Tables created via SQL - change <owner> appropriately */
SELECT 'UPDATE ALL COLUMN STATISTICS FOR ' + creator + '.' + tbl + ';' FROM sysprogress.systables WHERE creator = '<owner>' ORDER BY tbl;