SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-9
Adding Partitions to Tables and Indexes
4. If you are splitting a partition, check that ample disk space is available for the new
partition. For information about space requirements, see the ALTER TABLE or
ALTER INDEX statement in the SQL/MP Reference Manual.
5. Use the DISPLAY USE OF command to determine which programs depend on the
table. These programs will be invalidated unless the programs are compiled with
the CHECK INOPERABLE PLANS option and the table has similarity checking
enabled, as described under Using Similarity Checks on page 10-15.
6. If you wish to add Format 2 partitions to a Format 2-enabled table, enter the
ALTER TABLE or ALTER INDEX statement with the FORMAT clause to specify the
format of the new partition. For more information about Format 2 partitions, see
Appendix C, Format 2 Partitions.
7. Enter the ALTER TABLE or the ALTER INDEX statement with the PARTONLY
MOVE clause to add the partition.
8. SQL compile the invalidated programs identified by the DISPLAY USE OF
command in Step 5.
9. For an audited table or index, make new TMF online dumps of all affected
partitions.
Note that the new partition inherits the partition array value associated with the
base table. If PARTITION ARRAY is EXTENDED, the partition can make use of the
larger number of partitions available for versions 320 and later of SQL/MP
software. If PARTITION ARRAY is FORMAT2ENABLED, the partition can make
use of the larger size of partitions available for versions 350 and later of SQL/MP
software. In Step 6, use the FORMAT clause to specify whether the added partition
should be Format 1 or Format 2. Partitions added to Format 2-enabled tables and
indexes will be Format 2 partitions by default.
For information about redistributing rows across partitions, see Splitting, Moving, and
Merging Partitions on page 7-20.
Example
This example adds an empty partition to a key-sequenced table, leaving existing data
in the existing partition (assuming there are no key values past 4999). In this one-way
split operation, the starting key value for the new partition is 5000.
>> ALTER TABLE $VOL1.SALES.CUSTOMER
+> PARTONLY MOVE FROM KEY 5000 TO $VOL3.SALES.CUSTOMER
+> CATALOG $VOL1.SALES
+> EXTENT (1000,200);
--- SQL operation complete.
Note. SQL tables and indexes with many partitions (typically around 400) might cause
SQLCAT, SQLUTIL, or AUDSERV processes to incur file-system error 31 or 34 or cause the
PARTNS catalog table and its associated index, IXPART01, to become full. For more
information about this situation, and for general information about adding a partition, see
Creating Table Partitions
on page 5-32.