RDF System Management Manual for J-series and H-series RVUs (RDF Update 13)

Adding a New Column
This is an operation that cannot be performed With Shared Access. To minimize application
downtime, you can coordinate the operation as follows. Stop the RDF updaters with a simple STOP
UPDATE command. When the updaters have stopped, add the column to your backup database
and then restart update. Note, at this point, the new column is in the backup database but not yet
on the primary. When the updaters update subsequent rows with the new column, the disk process
adds the default value to the new column. Next, you perform a switchover operation (detailed in
Chapter 5), start RDF on your backup system with update off, and then start your applications on
your backup system. Add the column to the database on your former primary system (it is now the
backup of your new RDF environment), and then START UPDATE. When RDF has caught up and
at your convenience, perform a new switchover operation to move your application processing
back to your primary system.
Guidelines for Create Index and Alter Table Move Operations
The following guidelines apply to NonStop SQL/MP and NonStop SQL/MX DDL operations:
• Creating an index or loading data into an added table partition does not interfere with RDF
protection. Although a CREATE INDEX or ALTER TABLE MOVE FROM FIRST KEY UP TO KEY
operation seems to create an audited index or partition within a transaction, only the updates
to the catalog and file labels are audited. The index or partition is created nonaudited, and
audit is not turned on until after the operation is complete. Performing either of these DDL
operations on the backup system for a corresponding DDL operation on the primary system
does not cause problems because the operation on the primary system proceeds internally:
1. Create a nonaudited table (index or partition).
2. Move the data without logging by TMF.
3. Issue an ALTER TABLE table-name AUDIT statement for the table.
It is safe to perform these operations just like other DDL operations on the primary system.
Example for CREATE INDEX With Shared Access
This example shows the SQLCI/MXCI commands for adding an index to a table and the order of
the operations:
1. Specify the default catalog for the primary system.
CATALOG \PRIM.$DATA.DBCAT;
2. Create an index based on first names in a database on the primary system.
CREATE INDEX \PRIM.$DATA1.DB.FIRST
ON \PRIM.$DATA1.DB.EMPLOYEE ( FIRST-NAME, LAST-NAME ), WITH SHARED ACCESS;
3. Watch for the purger to log RDF event 908.
4. On the backup system, set the default catalog for the backup database.
CATALOG \BACK.$DATA.DBCAT;
5. Create the index for the backup database. Note, because the updaters are stopped, you do
no need to include the With Shared Access option and the operation in fact completes faster.
CREATE INDEX \BACK.$DATA1.DB.FIRST
ON \BACK.$DATA1.DB.EMPLOYEE ( FIRST-NAME, LAST-NAME );
You should use WITH SHARED ACCESS for the CREATE INDEX operations in the example if both
RDF and the application are running.
Multiple Indexes on a Single Base Table
The following issues apply to both NonStop SQL/MP and NonStop SQL/MX.
If there are multiple indexes on a single base table, special considerations apply when you use
SQLCI CREATE INDEX commands on the backup system to coordinate NonStop SQL/MP DDL
operations between the primary and backup databases.
152 Maintaining the Databases