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-13
Recovering Tables
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 catalogs may be wrong
for every partition. You can use a single mxtool FIXUP command to correct the
timestamps for all partitions.
Tables With Triggers
When a dropped table with triggers is recovered, the trigger temporary tables are
re-created from scratch.
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.
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 lists 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
'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