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-11
Recovering Tables
•
SQL compile any SQL/MX modules that access this table.
Example: Recovering Table Privileges
Suppose that this DDL was saved when a table was dropped accidentally:
CREATE TABLE CAT.SCH.T039UC3
(
A INT NO DEFAULT -- NOT NULL NOT DROPPABLE
, CONSTRAINT CAT.SCH.T039UC3_100345126_0022 CHECK
(CAT.SCH.T039UC3.A IS NOT NULL) NOT DROPPABLE
)
LOCATION \NSK.$DATA4.ZSDADHFW.AL0P3N00
;
-- The following index is a system created index --
CREATE UNIQUE INDEX T039UC3_103704950_0023 ON CAT.SCH.T039UC3
(
A ASC
)
LOCATION \NSK.$DATA4.ZSDADHFW.BGSB3N00
;
ALTER TABLE CAT.SCH.T039UC3
ADD CONSTRAINT CAT.SCH.T039UC3_103704950_0023 UNIQUE (A)
DROPPABLE;
GRANT UPDATE (A) ON TABLE CAT.SCH.T039UC3 TO PUBLIC;
When the table is dropped, NonStop SQL/MX produces a file containing the DDL for all
the statements in this example. However, in place of any GRANT statements used to
define privileges on the table, the DDL includes these statements:
•
DELETE statements to remove the default security information from the metadata
tables TBL_PRIVILEGES and COL_PRIVILEGES
•
INSERT statements to record the correct security information in these two
metadata tables
During table recovery, you must use a licensed copy of MXCI to execute the DELETE
and INSERT statements after you create the table and before you recover the table
data, as in this example:
1. Execute the statement to create table CAT.SCH.T039UC3.
2. Execute the statement to create index CAT.SCH.T039UC3_103704950_0023.
3. Execute the statement to add constraint CAT.SCH.T039UC3_103704950_0023.