Availability Guide for Change Management

Making Application Subsystem Changes Online
Availability Guide for Change Management–125506
4-13
NonStop SQL/MP Changes You Can Perform Online
can move all or part of a partition to other disk volumes while allowing full update
access to the data in the partition.
Reducing the Frequency of Database Reorganizations
Another way you can reduce the impact of database reorganizations on application
availability is to reduce their frequency. The following guidelines can help you avoid
reorganizing your database:
•
Consider partitioning tables for future growth. Partitioning allows the index
structure to be spread across more media, thereby reducing the number of index
blocks and possibly the index levels required to support the data.
•
Review the value of the table BLOCKSIZE attribute. Increasing the block size also
reduces the number of index blocks and index levels.
•
Specify an appropriate amount of slack space when reloading a table to ensure
adequate growth capabilities without the immediate need for block splits.
•
If data is badly fragmented, the BACKUP and RESTORE activities can help make
more extents contiguous, but only if the volume has large enough free extents to
accomodate the file.
•
Use an anchor partition—a partition that contains no data and that you never
move—for partitioned SQL tables to provide better flexibility when moving
partitions. By creating an anchor partition and using it in your queue, you can more
easily move partitions containing data and thus obtain better availability.
Recompiling SQL Statements
Although many database operations can be completed with no disruption to the
application, other operations require recompilation of SQL statements to achieve
maximum performance. For example, data definition language (DDL) operations on
SQL objects can invalidate programs that access the object. An invalid program must be
either statically recompiled or autorecompiled before it is executed. Static recompilation
requires the application to be unavailable; autorecompilation of a program can cause a
deterioration in the response time of the program because it recompiles the program
each time the program is executed.
In certain situations, you can reduce recompilation time caused by invalidated programs
by using the following compiler options:
•
COMPILE INOPERABLE PLANS—This option directs the SQL compiler to
perform similarity checks during explicit SQL compilation in order to explicitly
compile only statements with inoperable plans. Using the COMPILE
INOPERABLE PLANS option may be preferable when a program contains only a
few query plans that are invalid and need to be recompiled.
•
CHECK INOPERABLE PLANS—This option directs the SQL executor to perform
similarity checks at run time in order to automatically recompile only statements
with inoperable plans. The CHECK INOPERABLE PLANS option can also prevent
certain DDL operations performed on SQL objects from invalidating SQL programs