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

Using VERIFY and FIXUP
After using TMF RECOVER to recover partitions of an SQL/MX table, you should perform a
consistency check on all recovered files by using mxtool VERIFY. The VERIFY command confirms
that all data forks and resource forks exist and contain consistent information.
When using mxtool VERIFY, specify the ANSI name of the table. For example, using the same
table name from the previous example:
mxtool VERIFY CAT.SCH.T039UC3
VERIFY performs these steps:
1. Compares metadata to information in the resource fork.
2. Compares metadata to information in the DP2 label.
3. Verifies the consistency of partition information.
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 more information, 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 recreate 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.
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.
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 “Using Guardian Names with TMF, RDF, and Measure”
(page 309) 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.
240 Performing Recovery Operations