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

NOTE: If you have granted or revoked table privileges, see “Example: Recovering Table Privileges”
(page 239) for guidelines on how to recover the privileges.
For complete information on saving DDL and using it to re-create SQL/MX objects, see these
discussions:
• “Retrieving Saved DDL” (page 59).
• In the SQL/MX Reference Manual, the information about the DROP TABLE statement and table
management for metadata tables.
The DDL definition of the newly created table must match exactly the DDL definition of the dropped
table. When you re-create the table, execute all of the statements in the exact order indicated in
the saved DDL. Then use TMF to recover the files for the tables and indexes.
NOTE: If you use the SHOWDDL output to re-create the table, execute all the statements in the
exact order indicated. Doing so ensures that the table and all associated objects are properly
re-created and can be recovered.
• Use the TMFCOM ALTER DUMPS command to reset the INVALID and RELEASED attributes of
the online dumps for the dropped objects to OFF.
• Recover the partitions of the re-created table and indexes with the TMFCOM RECOVER FILES
command, using the FROMARCHIVE and TOFIRSTPURGE options. To determine the Guardian
names of the purged objects, you can use the MXGNAMES utility. This utility can also be used
to build lists of these Guardian files for use with TMFCOM commands.
• For the recovered objects, use the mxtool VERIFY command to verify that the redefinition
timestamps, catalog UIDs, schema UIDs, and object UIDs in the file labels or resource forks
match those in the SQL/MX metadata.
• For every mismatch that mxtool VERIFY identifies, use mxtool FIXUP to update the file labels
and resource forks to make the object consistent.
• If any views were dropped because of the CASCADE option, be sure to re-create them.
• Update the statistics for the recovered table.
• SQL compile any SQL/MX modules that access this table.
Example: Recovering Table Privileges
Suppose you create an SQL/MX table using the following DDL, and then the table is dropped
accidentally:
CREATE TABLE CAT.SCH.T039UC3
(
A INT NO DEFAULT -- NOT NULL NOT DROPPABLE
, CONSTRAINT CAT.SCH.T039UC3_100345126_0022 CHECK
(CAT.SCH.T039UC3.A IS NOT NULL) NOT DROPPABLE
)
LOCATION \NSK.$DATA4.ZSDADHFW.AL0P3N00
;
-- The following index is a system created index --
CREATE UNIQUE INDEX T039UC3_103704950_0023 ON CAT.SCH.T039UC3
(
A ASC
)
LOCATION \NSK.$DATA4.ZSDADHFW.BGSB3N00
;
ALTER TABLE CAT.SCH.T039UC3
ADD CONSTRAINT CAT.SCH.T039UC3_103704950_0023 UNIQUE (A)
DROPPABLE;
Recovering Database Objects 239