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

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 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
Making Changes to Database Structures 153