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

of indexes is maintained in the file label in the disk directory. When you use MXCI to create just
the table and not the indexes, and later recover the table, additional mismatches will occur between
the SQL/MX catalog or schema and the file label in the directory. This can make the recovery
process even more difficult.
If you implicitly drop the indexes when you drop a table and later use the script generated by
NonStop SQL/MX to recover the table, the indexes will be automatically re-created and can then
be recovered by TMF
Tables With Partitions
Recovery of partitioned tables requires special attention. After you have re-created the partitions
and recovered them with TMF, the timestamps in the file labels and UIDs in the resource forks might
be wrong for every partition. You would use one mxtool FIXUP command to correct the timestamps
for all partitions and a second mxtool FIXUP command to correct the UIDs.
Tables With Triggers
When a dropped table with triggers is recovered, the trigger temporary tables for disabled triggers
can be re-created from scratch using the table’s saved DDL. Enabled triggers cannot be recovered.
When an existing table with triggers is recovered, you need not recover the partitions of the trigger
temporary tables unless they have been physically destroyed or damaged.
Suppose that the saved DDL for the dropped table CAT.SCH.TO46_TRIG_T2 displays this output:
CREATE TABLE CAT.SCH.T046_TRIG_T2
(
A INT NO DEFAULT -- NOT NULL NOT DROPPABLE
, B VARCHAR(20) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT -- NOT NULL NOT DROPPABLE
, CONSTRAINT CAT.SCH.T046_TRIG_T2_839176264_8137 CHECK
(CAT.SCH.T046_TRIG_T2.A IS NOT NULL AND
CAT.SCH.T046_TRIG_T2.B IS NOT
NULL) NOT DROPPABLE
)
LOCATION \SQUAW.$DATA08.ZSDBNHZH.D6VSVM00
NAME SQUAW_DATA08_ZSDBNHZH_D6VSVM00
;
CREATE TRIGGER CAT.SCH.T046_ATRIG111_2 AFTER UPDATE ON CAT.SCH.T046_TRIG_T2
REFERENCING OLD AS OLDR, NEW AS NEWR FOR EACH ROW INSERT INTO
CAT.SCH.T046_TRIG_T3 VALUES (NEWR.A + 20, OLDR.B || CHAR(ASCII(NEWR.B)));
ALTER TRIGGER DISABLE CAT.SCH.T046_ATRIG111_2 ;
The dropped table’s saved DDL contains an ALTER TRIGGER statement because the trigger was
disabled when the table was dropped.
Example: Recovering an Accidentally Dropped Table
Suppose that you have defined a table named EMPLOYEE in the SQL/MX catalog HRDATA and
the schema PERSNL. An MXCI SHOWDDL command displays the EMPLOYEE table and its two
indexes named XEMPDEPT and XEMPNAME:
>>SHOWDDL HRDATA.PERSNL.EMPLOYEE;
CREATE TABLE HRDATA.PERSNL.EMPLOYEE
(
EMPNUM NUMERIC(4, 0) UNSIGNED NO DEFAULT HEADING
'Employee/Number' -- NOT NULL NOT DROPPABLE
,FIRST_NAME CHAR(15) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT _ISO88591' ' HEADING 'First Name'
-- NOT NULL NOT DROPPABLE
,LAST_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT _ISO88591' ' HEADING 'Last Name'
-- NOT NULL NOT DROPPABLE
,DEPTNUM NUMERIC(4, 0) UNSIGNED NO DEFAULT HEADING
244 Performing Recovery Operations