Neoview SQL Reference Manual (R2.5)

NOTE: The MAINTAIN command is not supported for use with volatile tables.
RATE percentage
specifies that the reorg operation should spend a percentage of its time doing the reorg
and the rest of its time delaying. percentage is an integer in the range of 1 through 100. If
this option is not specified, the default value of 40 is used.
Considerations for MAINTAIN
Using MAINTAIN in JDBC or ODBC Applications
If you plan to use the MAINTAIN command in a JDBC or ODBC application, keep in mind that
the MAINTAIN command executes with cursor-like behavior and returns status information for
each task that it performs. In ODBC or JDBC applications, you must issue fetch calls while
SQLNumResultCols is greater than zero (ODBC) or while rs.next() is true (JDBC) to retrieve
the status and complete the query. Use the same fetch calls as you would for a SELECT statement
that returns multiple rows of data.
If you do not make fetch calls for MAINTAIN, the MAINTAIN operation will not complete, and
you will not see any error information. If an error occurs during an SQLExecDirect or
execute() call, the error will not be returned to the application but will be returned as part of
one of the fetch calls. Therefore, an application that runs a MAINTAIN command must keep
fetching rows until an End of Data (EOD) or an error is returned.
Multiple Table Reorg in Parallel
In previous releases, only a single table was allowed in a MAINTAIN command. If you wanted
to reorganize multiple tables, you had to put each table in a separate MAINTAIN command.
Although partitions within each table are reorganized in parallel, multiple tables are reorganized
in sequential mode. For example, table1 may have some partitions finish their reorg faster, but
the control is not returned to the user until all partitions in table1 complete. As a result, table2
cannot start reorg until the slowest partition in table1 completes.
With multiple tables support, you can put all tables into a single MAINTAIN command and all
partitions from all tables will be reorganized in parallel. Using the same example, partitions in
table2 can start their reorg as soon as table1 partitions on the same cpu or disk complete.
Retrieving Maintain Status for Multiple Tables
Use this syntax to retrieve maintain status for multiple tables:
MAINTAIN TABLES (...), REORG ', STATUS [ AFTER TIMESTAMP ''timestamp'']'
If MAINTAIN/REORG is issued on a table that was previously reorganized, the STATUS command
may return old data from partitions that were reorganized by the previous maintain but have
not yet been reorganized by the current maintain. This results in return status for some of the
currently reorganized partitions and some older reorganized partitions. To avoid this situation,
you can specify the AFTER TIMESTAMP clause of the STATUS command. The timestamp
value is the time when the current reorg started. The timestamp value does not need to be
exact—an approximate value is acceptable. SQL will use this value to return data for those
partitions which were reorganized after the specified time. This eliminates older reorganized
partitions.
Examples that show multiple tables MAINTAIN are included in “Examples of MAINTAIN”
(page 217).
Forcing REORG With the OVERRIDE Option
The OVERRIDE option of REORG forces the Encapsulated SQL Access Manager (ESAM) to
perform a REORG operation. For example, if you want to change the slack space or reclaim free
blocks, use the OVERRIDE option. If you do not specify the OVERRIDE option, ESAM uses
216 SQL Utilities