SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
M-29
Examples—MODIFY LABEL with REPLACE
NODENUMBER
This SELECT statements illustrate this point:
>> SELECT FILENAME,PARTITIONNAME,CATALOGNAME
+> FROM \A.$DA1.CATSUBV.PARTNS;
FILENAME PARTITIONNAME CATALOGNAME
-------------- -------------- ---------------
\A.$DA1.SQL.T1 \A.$DA1.SQL.T1 \A.$DA1.CATSUBV
\A.$DA1.SQL.T1 \A.$DB1.SQL.T1 \A.$DB1.CATSUBV
\A.$DA1.SQL.T1 \C.$DC1.SQL.T1 \C.$DC1.CATSUBV
>> SELECT FILENAME,PARTITIONNAME,CATALOGNAME
+> FROM \A.$DB1.CATSUBV.PARTNS;
FILENAME PARTITIONNAME CATALOGNAME
-------------- -------------- ---------------
\A.$DB1.SQL.T1 \A.$DA1.SQL.T1 \A.$DA1.CATSUBV
\A.$DB1.SQL.T1 \A.$DB1.SQL.T1 \A.$DB1.CATSUBV
\A.$DB1.SQL.T1 \C.$DC1.SQL.T1 \C.$DC1.CATSUBV
>> SELECT FILENAME,PARTITIONNAME,CATALOGNAME
+> FROM \C.$DC1.CATSUBV.PARTNS;
FILENAME PARTITIONNAME CATALOGNAME
-------------- -------------- ---------------
\C.$DC1.SQL.T1 \A.$DA1.SQL.T1 \A.$DA1.CATSUBV
\C.$DC1.SQL.T1 \A.$DB1.SQL.T1 \A.$DB1.CATSUBV
\C.$DC1.SQL.T1 \C.$DC1.SQL.T1 \C.$DC1.CATSUBV
Now suppose that the $DB1 volume is moved from the \A node (node number 101) to
the \B node (node number 102). The MODIFY DICTIONARY commands needed to
make the NonStop SQL/MP database consistent after the move are shown:
On node \B, to modify the volume moved from \A to \B:
>> MODIFY LABEL $DB1.*.*
+> REPLACE NODENUMBER 101 ($DB1) WITH 102;
>> MODIFY CATALOG $DB1.CATSUBV
+> REPLACE NODENAME \A ($DB1) WITH \B;
>> MODIFY REGISTER CATALOG $DB1.CATSUBV;
On node \A, to modify the references to the partition moved from \A to \B:
>> MODIFY LABEL $DA1.*.*
+> REPLACE NODENUMBER 101 ($DB1) WITH 102;
>> MODIFY CATALOG $DA1.CATSUBV
+> REPLACE NODENAME \A ($DB1) WITH \B;
On node \C, to modify the references to the partition moved from \A to \B:
>> MODIFY LABEL $DC1.*.*
+> REPLACE NODENUMBER 101 ($DB1) WITH 102;
>> MODIFY CATALOG $DC1.CATSUBV
+> REPLACE NODENAME \A ($DB1) WITH \B;
Notice that the REPLACE clauses are specified as:
REPLACE NODENUMBER 101 ($DB1)
REPLACE NODENAME \A ($DB1).