SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-49
Examples—ALTER TABLE

This example alters the maximum number of extents for a specific partition:
ALTER TABLE \SYS2.$VOL2.INVENT.PARTLOC
PARTONLY MAXEXTENTS 300;

This example drops a table partition (after deleting all rows):
DELETE FROM =PARTLOC
WHERE LOC_CODE >= "H00" AND LOC_CODE < "K00";
ALTER TABLE =PARTLOC DROP PARTITION =PART_SOUTH;

This example moves a table partition to another disk, specifying WITH SHARED
ACCESS to keep the table available to other process during the move:
ALTER TABLE $DISK1.USERS.DATA PARTONLY MOVE TO $DISK2
WITH SHARED ACCESS NAME MOVE2D2 COMMIT BY REQUEST;
...
CONTINUE MOVE2D2 ONCOMMITERROR COMMIT BY REQUEST;
The ALTER TABLE statement specifies COMMIT BY REQUEST so that the user
can control entry to the commit phase of the operation, which locks out other
processes. The CONTINUE statement starts the commit phase, directing SQL to
return control to the user if a retryable error occurs during the phase.

This example moves the latter portion of a table partition into a new partition (a
one-way split):
ALTER TABLE =PART_LOC PARTONLY MOVE
FROM KEY (RH00S, 0) TO =PART_EUROPE EXTENT (8,8);

This example creates two new partitions of a table and moves data to them from
an existing partition, which it deletes (a two-way split):
ALTER TABLE $DISK1.SALES.ORDERS PARTONLY MOVE
(FROM FIRST KEY UP TO KEY 50 TO $DISK2 CATALOG =CAT2,
FROM KEY 50 UP TO LAST KEY TO $DISK3 CATALOG =CAT3);

This example reuses a partition named $DISK5.SALES.ORDERS, in which the
new primary first key is 5000 after the partition is reused:
ALTER TABLE $DISK1.SALES.ORDERS REUSE PARTITION
$DISK5.SALES.ORDERS FIRST KEY “5000”

This example moves the latter portion of a table partition into a new partition (a
one-way split) in which the new partition is Format 2:
ALTER TABLE =PART_LOC PARTONLY MOVE
FROM KEY (RH00S, 0) TO =PART_EUROPE EXTENT (8,8) FORMAT 2;