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

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-296
Examples of MODIFY
•
Move the second partition of a hash partitioned table to a new location:
MODIFY TABLE tab1 MOVE PARTITION
WHERE KEY = VALUE (2)
TO LOCATION $DATA02;
•
Move a partition of a hash partitioned table from $data02 to $data03:
MODIFY TABLE tab1 MOVE PARTITION
WHERE LOCATION $data02
TO LOCATION $data03
EXTENT (1024, 1024) MAXEXTENTS 256;
•
Modify table with an online operation
MODIFY TABLE MODT408A05 ADD PARTITION
WHERE KEY= first key upto key= value (30000)
TO LOCATION $data04
WITH SHARED ACCESS
COMMIT BEFORE '2007-04-05 16:25:40'
ONCOMMITERROR COMMIT WORK AFTER '2007-04-05 25:19:00';
•
The following example shows a simple rename of a single Guardian location of a
table. Consider a Table T1 with location as $DATA01.ZSDABCDE.SPARTN00.
MODIFY TABLE CAT.SCH.T1 RENAME WHERE LOCATION
$DATA01.ZSDABCDE.SPARTN00 TO TPARTN00;
The location SPARTN00 is renamed to TPARTN00.The volume and subvolume are
not renamed.
•
Following example shows rename of a set of specified locations of a table
MODIFY TABLE CAT.SCH.T2
RENAME LOCATION
( $DATA01.ZSDABCDE.ABCDEF00 TO FEDCBA00
, $DATA02.ZSDABCDE.GHIJKL00 TO LKJIHG00 );
The keyword WHERE is optional.
•
The following example shows the rename of all locations of a table matching a
pattern. Consider a table T1 with locations $DATA01.ZSDABCDE.SPARTX00 and
$DATA02.ZSDABCDE.SPARTY00.
MODIFY TABLE CAT.SCH.T1
RENAME WHERE LOCATION $*.ZSDABCDE.S* MAP NAMES TO
T???????;
Both locations match the pattern and therefore, are renamed. The resulting
locations contains the letter ‘T’ in the leftmost position, and the letters in the
remaining positions are same as the original location. Therefore, locations will be
renamed $DATA01.ZSDABCDE.TPARTX00 and
$DATA02.ZSDABCDE.TPARTY00.