SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Performing Recovery Operations
HP NonStop SQL/MX Installation and Management Guide—523723-004
12-12
Recovering Tables
4. Use a licensed copy of MXCI to execute the DELETE and INSERT statements. For
example:
delete from CAT.DEFINITION_SCHEMA_VERSION_1200.TBL_PRIVILEGES
where TABLE_UID =(table-uid-subquery);
delete from CAT.DEFINITION_SCHEMA_VERSION_1200.COL_PRIVILEGES
where TABLE_UID =(table-uid-subquery);
insert into CAT.DEFINITION_SCHEMA_VERSION_1200.TBL_PRIVILEGES
values
(-2,'S ',65535,'U ',(table-uid-subquery),'D ','Y'),
(-2,'S ',65535,'U ',(table-uid-subquery),'I ','Y'),
(-2,'S ',65535,'U ',(table-uid-subquery),'R ','Y'),
(-2,'S ',65535,'U ',(table-uid-subquery),'S ','Y'),
(-2,'S ',65535,'U ',(table-uid-subquery),'U ','Y');
insert into CAT.DEFINITION_SCHEMA_VERSION_1200.COL_PRIVILEGES
values
((table-uid-subquery),0,-2,'S ',65535,'U ','R ','Y'),
((table-uid-subquery),0,-2,'S ',65535,'U ','U ','Y'),
((table-uid-subquery),1,-2,'S ',65535,'U ','R ','Y'),
((table-uid-subquery),1,-2,'S ',65535,'U ','U ','Y'),
((table-uid-subquery),2,-2,'S ',65535,'U ','R ','Y'),
((table-uid-subquery),2,-2,'S ',65535,'U ','U ','Y'),
((table-uid-subquery),3,-2,'S ',65535,'U ','R ','Y'),
((table-uid-subquery),3,-2,'S ',65535,'U ','U ','Y');
5. Use the TMFCOM ALTER DUMPS command to reset the INVALID and
RELEASED attributes of the online dumps for the dropped table to OFF.
6. Execute this RECOVER FILES command:
RECOVER FILES ($DATA4.ZSDADHFW.AL0P3N00, &
$DATA4.ZSDADHFW.BGSB3N00, $DATA4.ZSDADHFW.AL0P3N01,&
$DATA4.ZSDADHFW.BGSB3N01)
Tables With Indexes
Always remember that if a table has indexes, it is better to re-create the indexes along
with the table and then to recover them with the table in the same TMFCOM
RECOVER FILES command. This is because the table and the indexes both need to
agree on which rows the table actually contains. If you do not use the approach, the
recovery will face even greater problems. The number of indexes is maintained in the
file label in the disk directory. When you use MXCI to create just the table and not the
indexes, and later recover the table, additional mismatches will occur between the
SQL/MX catalog or schema and the file label in the directory. This can make the
recovery process even more difficult.
If you implicitly drop the indexes when you drop a table and later use the script
generated by NonStop SQL/MX to recover the table, the indexes will be automatically
re-created and can then be recovered by TMF.