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

Performing Recovery Operations
HP NonStop SQL/MX Installation and Management Guide—544536-007
12-18
Recovering Tables
5. Use the DELETE and INSERT statements saved with the DDL to re-create access
privileges for the table and its indexes.
This example grants SELECT and DELETE privileges, as well as the privilege of
granting SELECT and DELETE privileges to others:
GRANT SELECT, DELETE ON TABLE HRDATA.PERSNL.EMPLOYEE TO
"sql.user1", "sql.user2" WITH GRANT OPTION;
6. Verify that all objects have been re-created by issuing the MXCI SHOWDDL
command:
>> SHOWDDL HRDATA.PERSNL.EMPLOYEE;
The resulting display is identical to the display shown at the start of Example:
Recovering an Accidentally Dropped Table on page 12-14; the objects exist once
again. However, the EMPLOYEE table does not yet contain any data.
7. When the objects were purged, TMF set the INVALID and RELEASED attributes of
the online dumps for the objects to ON. Before you can recover the objects, you
must first reset these attributes to OFF, using the TMFCOM ALTER DUMPS
command. In this command, you must specify the Guardian names of the objects.
You can use the MXGNAMES utility to format the list of Guardian file names
exactly as required for the ALTER DUMPS command and other TMFCOM
commands. An example illustrating the use of MXGNAMES appeared in Step 3.
Because the objects now exist, you can also enter the ANSI name of the table
directly into the command:
$DATA01 SUBVOL 16>$system.zmxtools.mxgnames
HRDATA.PERSNL.EMPLOYEE -tmf
TMF 16>ALTER DUMPS ($DATA10.ZDST5356.JN9V5J00, &
>>> $DATA10.ZDST5356.JN9V5J01, &
>>> $DATA10.ZDST5356.NG59FJ00, &
>>> $DATA10.ZDST5356.NG59FJ01, &
>>> $DATA10.ZDST5356.NPFH8J00, &
Note. GRANT and REVOKE statements are not saved as part of DDL text. However, the
DDL text includes DELETE statements to remove the default security information from the
metadata tables TBL_PRIVILEGES and COL_PRIVILEGES and INSERT statements to
record the correct security information. When recreating a dropped table, use a licensed
copy of MXCI to execute these DELETE and INSERT statements after you create the
table, to restore the security to the same state as when the table was dropped. For more
information, see Step 4 under Example: Recovering Table Privileges
on page 12-11.
Caution. If you do not exit the MXCI session at this point, the files remain open, in which case,
you might encounter File System Error 12 when recovering the files later.
Note. The ALTER DUMPS command does not allow partial wildcarding of the input file
names. Therefore, you will need to fully expand the file names from the MXGNAMES
output before using them in the ALTER DUMPS command.