SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

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 in
“Example: Recovering Table Privileges” (page 239).
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” (page 241); the objects exist once again. However, the EMPLOYEE
table does not yet contain any data.
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.
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
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.
TMF 16>ALTER DUMPS ($DATA10.ZDST5356.JN9V5J00, &
>>> $DATA10.ZDST5356.JN9V5J01, &
>>> $DATA10.ZDST5356.NG59FJ00, &
>>> $DATA10.ZDST5356.NG59FJ01, &
>>> $DATA10.ZDST5356.NPFH8J00, &
>>> $DATA10.ZDST5356.NPFH8J01), INVALID OFF,&
>>> RELEASED OFF, SERIAL 70
NOTE: If, for any reason, the dumps were completely removed from the TMF catalog, you
would need to add them again, using the TMFCOM ADD DUMPS command. In this command,
you would also set the INVALID and RELEASED attributes to OFF.
8. Recover the table and its indexes.
CAUTION: Objects to be recovered are spread across different disk volumes and subvolumes.
However, if some of the objects are located in the same subvolume as the SQL/MX catalog
or schema, be careful to avoid recovering the catalog or schema tables, so that the current
state of the catalog is maintained.
Recovering Database Objects 245