SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-35
Creating Table Partitions
Special Considerations for DSS Applications
DSS applications typically require periodic addition and deletion of data. Further,
access must be well balanced. The choice of leftmost primary key column—and thus
the partitioning strategy—can greatly affect access. Choose a leftmost key column that
minimizes contention while allowing acceptable load and delete performance.
Possibilities include a date column (if access is distributed evenly across date values),
a nondate column in the primary key (if access across the date column is weighted
toward specific ranges), or an artificial “partition number” value, with rows distributed
across all partitions.
Further, when defining partitions for a DSS application, consider using one or more of
these strategies:
•
Leave the primary partition empty (with minimal size) and use secondary partitions
for all data. The primary partition of a partitioned table cannot easily be dropped. If
all your data resides in secondary partitions, you have the greatest flexibility for
adding, dropping, and splitting partitions.
•
Mirror the volume associated with the primary partition to maximize availability.
Queries and programs that refer to the primary partition require its availability when
accessing data.
•
Configure mirrored disks on separate channels. This strategy maximizes
performance and should be used for volumes containing primary and secondary
partitions where possible.
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 because of
insufficient memory in the process file segment (PFS). To increase the PFS size for any of
these SQL processes, use the BIND statement CHANGE PFS command. For programs run
from TACL, you can specify the PFS size in the TACL RUN command. Save the original copy of
any program you modify. If you alter PFS size for SQLCAT, SQLUTIL, or AUDSERV, you must
license the modified copy and re-alter the PFS size when you install a newer version of
SQL/MP.
SQL tables and indexes with many partitions might also cause the PARTNS catalog table and
its associated index, IXPART01, to become full. The PARTNS table contains N**2 rows for
each table and index with N partitions. Thus, three tables of 400 partitions each would fill the
PARTNS table. To remedy this situation, distribute the definitions of objects across catalogs.