Kbase P115266: How to generate SQL-92 script to execute UPDATE STATISTICS for all user tables of a database using 4
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
25/03/2008 |
|
Status: Verified
GOAL:
How to generate SQL-92 script to execute UPDATE STATISTICS for all user tables of a database using 4GL?
GOAL:
How to programmatically create SQL-92 script to run UPDATE STATISTICS for all tables, columns and indexes of the connected database
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.1x
OpenEdge 10.x
FIX:
The following code outputs a file named "UpdateStatistics.sql" which may be executed via any ODBC or a JDBC client to update all the statistics of all the tables for a given Progress database.
OUTPUT TO "UpdateStatistics.sql".
FOR EACH _File NO-LOCK WHERE _Tbl-Type = "T":
PUT UNFORMATTED "UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB." _File-Name ";" SKIP.
END.
PUT UNFORMATTED "COMMIT WORK;".
OUTPUT CLOSE.
The SQL-92 script file, "UpdateStatistics.sql", generated by running the above 4GL code will look like the following sample:
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Benefits;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.BillTo;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Bin;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Customer;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Department;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Employee;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Family;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Feedback;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.InventoryTrans;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Invoice;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Item;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.LocalDefault;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Order;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.OrderLine;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.POLine;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.PurchaseOrder;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.RefCall;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Salesrep;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.ShipTo;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.State;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Supplier;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.SupplierItemXref;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.TimeSheet;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Vacation;
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Warehouse;
COMMIT WORK;
The SQL-92 statements in the above "UpdateStatistics.sql" file may be executed using any ODBC or JDBC client. For example, the following command executes the above script using the Progress SQL Explorer Tool Character JDBC Client:
sqlexp -char -db sports2000 -S 23456 -infile UpdateStatistics.sql -outfile UpdateStatistics.out -user username -password userpassword