SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Performing Recovery Operations
HP NonStop SQL/MX Installation and Management Guide—544536-007
12-8
Recovering SPJs
Some inconsistencies reported as a result of this verification might be due to user
errors in the file recovery operation. For example, not all files to be recovered were
properly specified. However, even if the recovery was done correctly, VERIFY might
report inconsistencies between the labels and the metadata. In this case, you must run
the mxtool FIXUP command.
For information about the VERIFY and FIXUP commands, see the SQL/MX Reference
Manual.
Recovering SPJs
To recover an SPJ, reexecute the CREATE PROCEDURE statement. No special
action is done to recover SPJs; there is no provision for preserving DDL from a DROP
PROCEDURE operation. Security on the re-created SPJ must be explicitly readded.
Recovering Tables
When a table is dropped, NonStop SQL/MX automatically saves the DDL needed to re-
create the table in an OSS file in the OSS directory /usr/tandem/sqlmx/ddl that
uses this naming format:
catalog.schema.tablename-yyyymmddtimestamp.ddl
Where yyyymmddtimestamp identifies the time the table was dropped. The DDL is
saved so you can later retrieve it if you need to re-create the dropped table for any
reason.
When recovering SQL/MX tables, you must use TMF file sets, consisting of the
Guardian names of the underlying files that make up the tables and related indexes, in
the RECOVER FILES command. To convert the ANSI names of the tables to their
underlying Guardian file names, you can run the MXGNAMES utility as described in
Appendix A, Using Guardian Names with TMF, RDF, and Measure and the SQL/MX
Reference Manual.
Removing Unneeded DDL Files
Because NonStop SQL/MX does not remove saved DDL files, you must remove the
unneeded files. If you do not periodically remove these files, the OSS directory
becomes full and DROP TABLE no longer succeeds. Database administrators should
monitor the saved DDL in the directory /usr/tandem/sqlmx/ddl for the
accumulation of unneeded files.
You can regularly run an OSS script to clean up old DDL files from the
/usr/tandem/sqlmx/ddl directory. The script deletes all files in the directory that
are older than, for example, seven days. (After seven days, it is unlikely that the
Note. The DDL is saved only if the SAVE_DROPPED_TABLE_DDL control query is set to ON
(the default). HP recommends that you do not change this default value in production
environments. In development and testing environments, or for specific applications where
tables are frequently created and dropped, however, you should reset this value to OFF.