Neoview Database Administrator's Guide (R2.3)

'INSERT'
adds the specified table to the list of tables on which automated UPDATE
STATISTICS runs. The option name is case-insensitive.
'DELETE'
deletes the specified table from the list of tables on which automated UPDATE
STATISTICS runs. The option name is case-insensitive.
'schema'
is the name of the database schema in which the table resides. To specify all existing
schemas, use '*'. If you specify '*' for the schema, you must also specify '*'
for the table parameter. To delimit the schema name, see the “Considerations”
(page 104).
'table' is the name of the database table. To specify all existing tables, use '*'. If you
specify '*' for the table, you must also specify '*' for the schema parameter. To
delimit the schema name, see the “Considerations” (page 104).
response
is a message about the results of the operation.
Considerations
• Use the CALL statement to execute the stored procedure, CHG_AUTO_LIST(). For more
information about CALL, see the Neoview SQL Reference Manual and the Neoview Guide to
Stored Procedures in Java.
• To call CHG_AUTO_LIST(), you must be logged in as ROLE.DBA (or super.services or the
super ID if you are HP support).
• In NCI, you can execute the CALL statement in SQL mode only.
• By default, the automated UPDATE STATISTICS and REORG operations occur within a
maintenance window of six hours, starting at midnight. To change the maintenance window,
see “Scheduling Automated UPDATE STATISTICS and REORG” (page 107).
• To add all existing tables to the list of automated tables, call this stored procedure:
CHG_AUTO_LIST('insert', '*', '*', ?)
After calling the procedure, automated UPDATE STATISTICS adds new nonvolatile user
tables to the list of tables at the start of every maintenance window.
• After you insert or delete specific tables from the list by calling CHG_AUTO_LIST(), the list
becomes static. To make the list dynamic again, call this stored procedure:
CHG_AUTO_LIST('insert', '*', '*', ?)
After calling the procedure, automated UPDATE STATISTICS automatically adds new
nonvolatile tables to the list of tables.
• If you call this stored procedure, automated UPDATE STATISTICS deletes all table entries
in the list, and the automated list become static:
CHG_AUTO_LIST('delete', '*', '*', ?)
In this case, automated UPDATE STATISTICS does not automatically generate histograms
for any table.
NOTE: When you delete all tables from the automated list, warnings 6010 and 6011 continue
to occur instead of warnings 6007 and 6008. To restore the warning messages to the ones
that occurred before automation, contact HP support.
• For delimited schema and table names, if the schema or table name passed to
CHG_AUTO_LIST() contains single quotes, Neoview SQL requires that the character be
represented by two single quotes. For example, you should specify the table name,
SCHEMA."'A brown ""fox""'" as shown below:
CHG_AUTO_LIST('insert', 'schema', '"''A brown ""fox""''"', ?)
Examples
• This CALL statement invokes the CHG_AUTO_LIST() procedure, which adds all existing
tables to the list of automated tables:
104 Automating Update Statistics and Reorganize Operations