RDF/IMP and IMPX System Management Manual (RDF 1.4+)

Maintaining the Databases
HP NonStop RDF/IMP and IMPX System Management Manual—524388-001
6-6
NonStop SQL Databases
It is safe to perform these operations just like other DDL operations on the primary
system.
DDL Operation Example
This example shows the SQLCI commands for adding an index to a table and the
order of the operations:
1. Specify the default catalog for the backup system.
CATALOG \BACK.$TEST.DBCAT;
2. Create an index on the backup system.
CREATE INDEX \BACK.$DATA1.DB.FIRST
ON \BACK.$DATA1.DB.EMPLOYEE( FIRST_NAME, LAST_NAME );
3. Specify the default catalog for the primary system.
CATALOG \PRIM.$TEST.DBCAT;
4. Create an index on the primary system that corresponds to the index created on
the backup system.
CREATE INDEX \PRIM.$DATA1.DB.FIRST
ON \PRIM.$DATA1.DB.EMPLOYEE( FIRST_NAME, LAST_NAME );
Multiple Indexes On a Single Base Table
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 SQL
DDL operations between the primary and backup databases.
Each SQL 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 a backup index to be different than that of the primary
index. In such a case, the index rows transmitted from the primary system to the
backup system will be corrupt with regard to their key values. Although the records are
physically present in the index on the backup system, NonStop SQL does not see
them because the actual key specifier value does not match the expected one.
Consequently, a FUP INFO <index>, STAT display will show the correct number of
records for the index, but a SQLCI SELECT COUNT (*) FROM <index> command will