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-17
Recovering Tables
Exit the current MXCI session, which will close these newly created files.
5. Use the GRANT statement 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-13; 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.
Note. If you use the SHOWDDL output to re-create the table and its indexes, execute all
CREATE INDEX and ALTER TABLE statements in the exact order indicated. Doing so
ensures that all indexes are re-created properly and can be recovered.
Note. Grant and revoke privileges 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 re-creating 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.
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.