SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Performing Recovery Operations
HP NonStop SQL/MX Installation and Management Guide—544536-007
12-14
Recovering Tables
(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
'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
;