SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

find /usr/tandem/sqlmx/ddl -mtime +7 -print | grep "/ddl/" | sed
"s/./rm &/" sh
Recovering Table Privileges
NonStop SQL/MX does not save the DDL for GRANT and REVOKE statements, so if you accidentally
drop a table that includes security information created by these statements, you must restore the
table’s security attributes in the table’s metadata before you can recover the table data, labels,
and resource forks from a TMF online dump. See “Example: Recovering Table Privileges” (page 242)
for more information.
Table Recovery Procedures
To recover a dropped table, you must execute the statements or commands in the exact order
shown in its saved DDL. For example:
1. The CREATE TABLE statement
2. ALTER TABLE statements as specified to add columns originally added by ALTER TABLE
3. CREATE INDEX statements as specified to enforce unique constraints or primary key constraints
4. Any other CREATE INDEX statements
5. ALTER TABLE statements as specified to add constraints
6. CREATE TRIGGER statements as specified
7. If you executed CREATE TRIGGER statements in Step 6, you will also need to execute ALTER
TRIGGER statements on disabled triggers for the dropped table. Enabled triggers do not
generate ALTER TRIGGER statements in the dropped table’s saved DDL. For more information,
see the “Tables With Triggers” (page 244).
8. If applicable, DELETE and INSERT statements to reapply previously granted or revoked table
privileges. For more information, see the “Example: Recovering Table Privileges” (page 242).
9. A RECOVER FILES command with the names of all partitions and resource forks of the tables
and indexes just re-created
The recovery process for tables can be much more complex than one involving only views and
indexes. For example, if TMF is not configured for file recovery, recovery may not be possible. For
this reason, it is vital that you heed the precautions listed earlier in this discussion. And, as cautioned
previously, you should always leave the SAVE_DROPPED_TABLE_DDL attribute set ON.
CAUTION: Unless performed with great care and precision, SQL/MX table recovery involves
risk of database corruption and loss of data integrity. This operation should be performed only by
experienced users of NonStop SQL/MX and TMF who understand:
• How objects are defined in the SQL/MX metadata and the ramifications of altering those
definitions
• How to use the mxtool utility as described in the SQL/MX Reference Manual
If no one with this expertise is present at your site, contact yourr HP NonStop service provider
before proceeding.
For most cases, the recommended approach for recovering a table and its indexes involves these
tasks:
• Determine what dependent objects (views, indexes, and other tables) might have been dropped
along with the table.
• Use MXCI to re-create the table, its indexes, and any privileges that you have granted or
revoked. If the SAVE_DROPPED_TABLE_DDL attribute has remained set ON, use the saved
DDL information for re-creating the objects. This information is preserved in the
/usr/tandem/sqlmx/ddl folder.
Recovering Database Objects 241