NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-44
Examples—ALTER TABLE
Examples—ALTER TABLE
•
The following example alters the security and owner for a table:
ALTER TABLE \SYS1.$VOL2.PERSNL.EMPLOYEE
SECURE "nunu" OWNER 12,101;
•
The following example alters the date that a table can be purged:
ALTER TABLE SALES.ORDERS NOPURGEUNTIL JAN 01 1995;
•
The following example turns on TMF auditing (perhaps after temporarily setting the
NO AUDIT attribute to perform a LOAD operation) for a table:
ALTER TABLE SALES.ORDERS AUDIT;
•
The following example adds a column to a table:
ALTER TABLE CUSTOMER ADD COLUMN LAST_ORDER_DATE
NUMERIC(6) DEFAULT 860000 HEADING "Last Ordered";
•
The following example alters the maximum number of extents for a specific
partition:
ALTER TABLE \SYS2.$VOL2.INVENT.PARTLOC
PARTONLY MAXEXTENTS 300;
•
The following 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;
•
The following 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.
•
The following statement 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);
•
The following statement creates two new partitions of a table and moves data to
them from an existing partition, which it then 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);