Kbase P151335: 4GL/ABL/SQL: Is there a way to run UPDATE STATISTICS automatically?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
19/08/2009 |
|
Status: Unverified
GOAL:
4GL/ABL/SQL: Is there a way to run UPDATE STATISTICS automatically?
GOAL:
Can a 4GL procedure be run manually or automatically to UPDATE INDEX STATISTICS, TABLE STATISTICS and ALL COLUMN STATISTICS?
GOAL:
How to UPDATE INDEX STATISTICS, TABLE STATISTICS and ALL COLUMN STATISTICS programmatically using 4GL/ABL?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
OpenEdge Category: Language (4GL/ABL)
OpenEdge Category: SQL
FIX:
Yes, the following 4GL/ABL procedure, Main.p, demonstrates how to
1. Connect to the database.
2. Generates the SQL script to UPDATE INDEX STATISTICS, TABLE STATISTICS and ALL COLUMN STATISTICS for all the user tables of the connected database.
3. Disconnects from the database.
4. Executes the SQL script generated to UPDATE INDEX STATISTICS, TABLE STATISTICS and ALL COLUMN STATISTICS for all the user tables of the connected database.
This procedure, like any other program or task, may be run manually or scheduled to run automatically at predefined time intervals. Under Windows operating systems, tasks may be scheduled to run automatically at predefined time intervals by using the Task Scheduler (Start > Settings > Control Panel > Task Scheduler ). Under UNIX operating systems, tasks may be scheduled to run automatically at predefined time intervals by using the cron command.
Note that the database connection parameters in the ConnectToDatabase.p and ExecuteSQLScript.p procedures must be modified to reflect your own database name, hostname, connection port, userid and password:
/* Main.p */
RUN ConnectToDatabase.p.
RUN GenerateSQLScript.p.
RUN DisconnectDatabase.p.
RUN ExecuteSQLScript.p.
/* ConnectToDatabase.p */
CONNECT Sports2000 -H localhost -N tcp -S 23456 NO-ERROR.
/* GenerateSQLScript.p */
OUTPUT TO "UpdateStatisticsScript.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.
/* DisconnectDatabase.p */
DISCONNECT Sports2000 NO-ERROR.
/* ExecuteSQLScript.p */
DEFINE VARIABLE cCommandLine AS CHARACTER NO-UNDO.
ASSIGN
cCommandLine = "sqlexp -char -db sports2000 -S 23456 -infile UpdateStatisticsScript.sql -outfile UpdateStatisticsScript.out -user myUserId -password myPassword".
OS-COMMAND SILENT VALUE(cCommandLine).