Consultor Eletrônico

Status: Verified

GOAL:

SQL: How to generate SQL script to UPDATE a database STATISTICS one object at a time using 4GL?

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.1x
OpenEdge 10.x

FIX:

The following 4GL procedure generates an SQL script to update the TABLE, INDEX and COLUMN STATISTICS for the connected database. To avoid taxing the system's memory and storage resources and possible errors and failure of the UPDATE process, the resulting SQL script will UPDATE the database STATISTICS in separate small transactions an item at a time:
To generate the SQL script, connect to the database whose STATISTICS you want to update and run the following 4GL/ABL code. The resulting SQL script file, UpdateStatistics.sql may be executed using any JDBC client like the Progress SQL Explorer Tool. To run the code using an ODBC client like WinSQL, remove the ";" characters from the end of all the SQL statements in the generated UpdateStatistics.sql file and replace all the COMMIT WORK statements with GO statements:
OUTPUT TO "UpdateStatistics.sql".
FOR EACH _File NO-LOCK WHERE _Tbl-Type = "T":
PUT UNFORMATTED "UPDATE TABLE STATISTICS FOR PUB." QUOTER(_FILE-NAME) ";" SKIP.
PUT UNFORMATTED "COMMIT WORK;" SKIP.
PUT UNFORMATTED "UPDATE INDEX STATISTICS FOR PUB." QUOTER(_FILE-NAME) ";" SKIP.
PUT UNFORMATTED "COMMIT WORK;" SKIP.
PUT UNFORMATTED "UPDATE ALL COLUMN STATISTICS FOR PUB." QUOTER(_FILE-NAME) ";" SKIP.
PUT UNFORMATTED "COMMIT WORK;" SKIP.
END.
OUTPUT CLOSE.
The generated SQL script in the "UpdateStatistics.sql" file looks like the following:
UPDATE TABLE STATISTICS FOR PUB."FirstTable";
COMMIT WORK;
UPDATE INDEX STATISTICS FOR PUB."FirstTable";
COMMIT WORK;
UPDATE ALL COLUMN STATISTICS FOR PUB."FirstTable";
COMMIT WORK;
UPDATE TABLE STATISTICS FOR PUB."SecondTable";
COMMIT WORK;
UPDATE INDEX STATISTICS FOR PUB."SecondTable";
COMMIT WORK;
UPDATE ALL COLUMN STATISTICS FOR PUB."SecondTable";
COMMIT WORK;
...
...