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-14
Recovering Tables
(
DEPTNUM ASC
)
LOCATION \KRYPTON.$DATA10.ZSDT5356.NPFH8J00
NAME KRYPTON_DATA10_ZSDT5356_NPFH8J00
;
--- SQL operation complete.
At some point, you discover that someone issued an MXCI DROP TABLE command
that purged the table:
>>DROP TABLE HRDATA.PERSNL.EMPLOYEE;
---SQL operation complete.
You determine that this table was dropped inadvertently. Now, you want to recover it:
1. Verify that the EMPLOYEE table has indeed been removed from the database, by
entering the MXCI SHOWDDL command:
>> SHOWDDL HRDATA.PERSNL.EMPLOYEE;
*** ERROR [4082] Table, view or stored procedure HRDATA.PERSNL.EMPLOYEE
does not exist or is inaccessible.
--- SQL operation completed with errors.
>>
These error messages confirm that the table has been removed.
2. Identify any of the EMPLOYEE table's indexes that might also have been dropped.
To do this, check the output from the last SHOWDDL command issued for this
table (at the start of Example: Recovering an Accidentally Dropped Table on
page 12-13) to determine what objects depend on the table. You conclude that
along with the EMPLOYEE table, the XEMPDEPT and XEMPNAME indexes have
been dropped too.
3. Confirm that these objects were actually dropped by issuing a TMFCOM INFO
DUMPS, DETAIL command for each object. The INFO DUMPS, DETAIL
commands list the online dump entries in the TMF catalog for the objects. When
you enter these commands, you must use the Guardian names of the purged
objects.
The saved DDL definition in the OSS directory /usr/tandem/sqlmx/ddl
contains the Guardian name for the dropped table. If the table’s indexes were
dropped implicitly as part of the DROP TABLE operation, their Guardian names
(and the DDL to re-create them) will also be present in the saved DDL definition. If
the indexes were dropped separately, then you must refer to the DDL you saved
earlier—either when the index was created or before the index was dropped— to
retrieve the Guardian names.)
The saved DDL for a dropped table is stored in a file named
catalog.schema.table-yyyymmdd-hhmmss.ddl, where yyyymmdd-hhmmss
is the time when the table was dropped. In this case, the file is named
HRDATA.PERSNL.EMPLOYEE-20031114-160629.ddl. To use MXGNAMES to
build the list of files for the INFO DUMPS command, you must first transfer this file
to the Guardian space, and then use it as input to the MXGNAMES utility: