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-12
Recovering Tables
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.
4. Use a licensed copy of MXCI to execute the DELETE and INSERT statements. For
example:
delete from CAT.DEFINITION_SCHEMA_VERSION_1200.TBL_PRIVILEGES where
TABLE_UID =(table-uid-subquery);
delete from CAT.DEFINITION_SCHEMA_VERSION_1200.COL_PRIVILEGES where
TABLE_UID =(table-uid-subquery);
insert into CAT.DEFINITION_SCHEMA_VERSION_1200.TBL_PRIVILEGES values
(-2,'S ',65535,'U ',(table-uid-subquery),'D ','Y'),
(-2,'S ',65535,'U ',(table-uid-subquery),'I ','Y'),
(-2,'S ',65535,'U ',(table-uid-subquery),'R ','Y'),
(-2,'S ',65535,'U ',(table-uid-subquery),'S ','Y'),
(-2,'S ',65535,'U ',(table-uid-subquery),'U ','Y');
insert into CAT.DEFINITION_SCHEMA_VERSION_1200.COL_PRIVILEGES values
((table-uid-subquery),0,-2,'S ',65535,'U ','R ','Y'),
((table-uid-subquery),0,-2,'S ',65535,'U ','U ','Y'),
((table-uid-subquery),1,-2,'S ',65535,'U ','R ','Y'),
((table-uid-subquery),1,-2,'S ',65535,'U ','U ','Y'),
((table-uid-subquery),2,-2,'S ',65535,'U ','R ','Y'),
((table-uid-subquery),2,-2,'S ',65535,'U ','U ','Y'),
((table-uid-subquery),3,-2,'S ',65535,'U ','R ','Y'),
((table-uid-subquery),3,-2,'S ',65535,'U ','U ','Y');
Note. For readability and formatting purposes, this sub-SELECT from the OBJECTS
table:
(select object_uid from CAT.DEFINITION_SCHEMA_VERSION_1200.OBJECTS
WHERE OBJECT_TYPE = 'BT' and OBJECT_SECURITY_CLASS = 'UT' and
OBJECT_NAME_SPACE =
'TA' and SCHEMA_UID = 6744416459638333760 and
OBJECT_NAME = 'T')
has been replaced in this example by the placeholder:
(table-uid-subquery)