SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Utilities
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
5-101
Examples of MODIFY
Examples of MODIFY

Move all records of an existing range partition to a new location:
MODIFY TABLE tab1 MOVE PARTITION
WHERE LOCATION $data02
TO LOCATION $data03;

Move records of an existing range partition, whose key is equal to 10000 to the last
key, to a new location:
MODIFY TABLE tab1 MOVE PARTITION
WHERE KEY = VALUE (10000) THRU KEY = LAST KEY
TO LOCATION $data02
EXTENT (512, 512) MAXEXTENTS 256;

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.