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

Performing Recovery Operations
HP NonStop SQL/MX Installation and Management Guide—523723-004
12-8
Recovering SPJs
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 it in an OSS file, catalog.schema.tablename-yyyymmddtimestamp.ddl,
in the OSS directory /usr/tandem/sqlmx/ddl. 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 B, 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
recovery script for that table would be needed.) You can change the minimum age at
which the file is deleted by replacing the 7 in the example script with a value that is
appropriate for your environment.
find /usr/tandem/sqlmx/ddl -mtime +7 -print | grep "/ddl/" | sed
"s/./rm &/" sh
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.