HP NonStop RDF, Руководство по управлению

Страница: 162 / 478

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 above
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.
Each NonStop SQL/MP index is assigned a unique key specifier that is stored as part of the key
for that index. You can explicitly define the key specifier by including the KEYTAG clause in the
CREATE INDEX command. If you do not do so, then the CREATE INDEX operation assigns a
numeric value based on the order of index creation (1, 2, 3, and so forth).
Because the key specifier is part of the key of every index row created on an RDF primary system,
it also becomes part of the associated TMF audit record. RDF transmits the audit record to the
backup system where it is then applied to the backup copy of the index.
If a CREATE INDEX command on the backup system does not include the KEYTAG clause (and
if you are not extremely careful to create the indexes in the order shown by a SQLCI FILEINFO
base table
, DETAIL command on the primary system), it is possible for the key specifier of
162 Maintaining the Databases