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

Recovering Dropped Database Objects
The method for recovering an accidentally dropped SQL/MX object depends on whether that
object is a catalog, table, index, or view.
Recovery of a single view or index is usually a straightforward operation. Recovery of a table or
catalog, however, can be complex and difficult if there are multiple dependent objects. So, for
safety's sake, you should take these precautions to prevent accidental loss of an object or to simplify
the recovery process if it does become necessary:
• Ensure that the SAVE_DROPPED_TABLE_DDL Control Query Default (CQD) remains set on.
CAUTION: When set ON, the SAVE_DROPPED_TABLE_DDL attribute preserves the DDL
(including full Guardian file names) required to re-create SQL/MX tables and indexes if they
are dropped. The SAVE_DROPPED_TABLE_DDL attribute is set ON by default, and HP
recommends that you do not change or override this setting. Otherwise, recovery of dropped
SQL/MX objects becomes much more difficult and you will probably need to work closely
with your HP NonStop service provider to accomplish the recovery.
The SAVE_DROPPED_TABLE_DDL attribute only affects the DROP TABLE statement, not the
DROP VIEW statement or the DROP INDEX statement. Therefore, it only protects you against
dropping an entire table, and thus against losing the data in that table.
SAVE_DROPPED_TABLE_DDL does not protect against losing the definitions of indexes or
views that were individually dropped. To ensure that individual indexes and views can be
re-created if necessary, you should either run the SHOWDDL command immediately after
issuing the CREATE VIEW or CREATE INDEX statement or save the actual DDL used for such
statements.
Always remember that no DDL is ever saved for a view, regardless of whether the view was
dropped individually or as part of a DROP TABLE, CASCADE command.
This discussion assumes that the SAVE_DROPPED_TABLE_DDL attribute setting is ON.
• Maintain OBEY command files containing current MXCI command scripts for recreating your
SQL/MX tables, indexes, and views. In particular, you must maintain scripts for creating all
views, because NonStop SQL/MX does not save the DDL for views. You can use the MXCI
SHOWDDL command to display the DDL syntax used to create an object.
• Save GRANT and REVOKE statements that you execute. The SHOWDDL command does not
display security information for tables. When a table is dropped, the security information is
saved in the form of DELETE and INSERT statements on the security-related metadata tables.
For more information, see the “Recovering Table Privileges” (page 241).
• If you alter an object, be sure to alter the OBEY file used to create that object too.
• Any time you perform an MXCI DDL operation, also request a TMF online dump for the affected
object. With each new dump, you decrease the number of tapes that must be processed during
any future recovery operation.
• Maintain a hard copy of the entire TMF catalog, using the TMFCOM INFO DUMPS,
OBEYFORM command.
• Whenever you request a TMF online dump, back up that dump to tape and use the TMFCOM
INFO DUMPS, OBEYFORM command to obtain a hard copy with that tape. For good TMF
practice, be sure to maintain a backup copy of the entire TMF catalog on tape.
◦ If you perform the dumps with separate groups of disks (for example, a dump for each
group attached to a particular processor), the dumps for your SQL/MX objects and
catalogs will be scattered among numerous tapes. The advantage of this approach is
that you are less likely to miss a vital object during the recovery process. The disadvantage
Planning Database Recovery 47