RDF System Management Manual for H-Series RVUs (RDF 1.8)

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 );
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
These issues also apply to 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
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/MP 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 return
fewer rows for the index than indicated by the FUP INFO command. The row count continues
to grow in the base table, but remains the same for the index.
You can avoid this problem by always using the KEYTAG clause in the CREATE INDEX command
to define a meaningful key specifier for each index you create.
If you encounter the problem described above, use SQLCI to DROP and re-CREATE the offending
indexes, doing so in the proper creation order. The following annotated output illustrates the
necessary index creation order:
>SQLCI FILEINFO $DATA.RDFSQL.MASTER, DETAIL
$DATA.RDFSQL.MASTER
SQL BASE TABLE
CATALOG $DATA.RDFSQL
VERSION 2
TYPE K
EXT ( 16 PAGES, 64 PAGES, MAXEXTENTS 160 )
REC 416
PACKED REC 415
BLOCK 4096
KEY ( COLUMN 0, OFFSET 0, LENGTH 4, ASC )
INDEX ( 1, $DATA.RDFSQL.MASTXYZ, <<create this index first
COLUMN 18, OFFSET 54, LENGTH 2, ASC.
COLUMN 19, OFFSET 56, LENGTH 2, ASC.
NOT UNIQUE )
INDEX ( 2, $DATA.RDFSQL.MASTABC, <<create this index second
Backing Up Altered Database Structures 151