SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

'Dept/Num' -- NOT NULL NOT DROPPABLE
,JOBCODE NUMERIC(4, 0) UNSIGNED DEFAULT NULL
HEADING 'Job/Code'
,SALARY NUMERIC(8, 2) UNSIGNED DEFAULT NULL
,CONSTRAINT HRDATA.PERSNL.EMPLOYEE_428832188_2424 PRIMARY KEY (EMPNUM ASC)
NOT DROPPABLE
,CONSTRAINT HRDATA.PERSNL.EMPLOYEE_133512188_2424 CHECK
(HRDATA.PERSNL.EMPLOYEE.EMPNUM IS NOT NULL AND
HRDATA.PERSNL.EMPLOYEE.FIRST_NAME IS NOT NULL AND
HRDATA.PERSNL.EMPLOYEE.LAST_NAME IS NOT NULL AND
HRDATA.PERSNL.EMPLOYEE.DEPTNUM IS NOT NULL) NOT DROPPABLE
)
LOCATION \KRYPTON.$DATA10.ZSDT5356.NG59FJ00
NAME KRYPTON_DATA10_ZSDT5356_NG59FJ00
STORE BY (EMPNUM ASC)
;
CREATE INDEX XEMPNAME ON HRDATA.PERSNL.EMPLOYEE
(
LAST_NAME ASC, FIRST_NAME ASC
)
LOCATION HRDATA.PERSNL.EMPLOYEE_133512188_2424
NAME KRYPTON_DATA10.ZSDT5356_JN9V5J00
;
CREATE INDEX XEMPDEPT ON HRDATA.PERSNL.EMPLOYEE
(
DEPTNUM ASC
)
LOCATION \KRYPTON.$DATA10.ZSDT5356.NPFH8J00
NAME KRYPTON_DATA10_ZSDT5356_NPFH8J00
;
--- SQL operation complete.
Suppose you later discover that someone subsequently 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” (page 241)) 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
242 Performing Recovery Operations