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

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:
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)
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');
5. Use the TMFCOM ALTER DUMPS command to reset the INVALID and RELEASED attributes of
the online dumps for the dropped table to OFF.
6. Execute this RECOVER FILES command:
RECOVER FILES ($DATA4.ZSDADHFW.AL0P3N00, &
$DATA4.ZSDADHFW.BGSB3N00, $DATA4.ZSDADHFW.AL0P3N01,&
$DATA4.ZSDADHFW.BGSB3N01)
Tables With Indexes
Always remember that if a table has indexes, it is better to re-create the indexes along with the
table and then to recover them with the table in the same TMFCOM RECOVER FILES command.
This is because the table and the indexes both need to agree on which rows the table actually
contains. If you do not use the approach, the recovery will face even greater problems. The number
Recovering Database Objects 243