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

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, 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
COLUMN 88, OFFSET 300, LENGTH 15, ASC.
COLUMN 87, OFFSET 285, LENGTH 15, ASC.
NOT UNIQUE )
AUDIT
BUFFERED
AUDITCOMPRESS
SECURITY (RWEP); NCNC
MODIF: 27 Dec 1997, 20:01
CREATION DATE: 02 Dec 1997, 12:37
REDEFINITION DATE: 10 Jan 1998, 14:46
LAST OPEN: 10 Jan 1998, 14:46
EOF 466944 (2.2% USED)
EXTENTS ALLOCATED: 160
INDEX LEVELS: 1
PARTITION ARRAY STANDARD
Making Changes to Database Structures 153