SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
L-10
Limits
When you create or alter a table or index with a large number of partitions, the
PARTNS catalog table and associated IXPART01 index might become full. To
correct the situation, distribute object and partition definitions across multiple
catalogs.
Actual limits depend upon the definition of the SQL tables and indexes, but the
PARTNS and IXPART01 catalog tables can contain approximately 500,000 rows.
Each table or index with N partitions stores N**2 rows of information in the
PARTNS catalog table. Thus, three tables of 400 partitions each can be defined in
a single catalog.
DDL and DML operations on tables or indexes that have large numbers of
partitions might return file-system error 31 or 34 because of insufficient memory in
the process file segment (PFS) used by the SQL file system. Actual limits depend
upon the definition of the SQL tables and indexes and the SQL statement being
executed, but memory limitations typically appear when a table or index has 400 or
more partitions.
If you see one of these errors, you can increase PFS size using the BINDER SET
PFS integer command:
1>RENAME SQLCAT,ZZSQLCAT
2>BIND
@ADD * FROM ZZSQLCAT
@SET PFS 256
@SELECT LIST (* OFF)
@BUILD SQLCAT !
@EXIT
3>FUP LICENSE SQLCAT
Alternately, for programs executed using TACL, you can specify PFS size in the
TACL RUN command.
Increase PFS size selectively, only as the need arises. Keep the original copy of
any program that requires a larger PFS setting. If you increase the PFS setting for
SQLCAT or SQLUTIL, you must license the new copy. If you increase the PFS
setting for SQLCI2, you must SQL compile the new copy.

Partitions per index or key-sequenced table, standard partition array
Typically, if the primary key of the index or key-sequenced table is between 10 and
50 bytes, the maximum number of partitions allowed can be from approximately
230 (for the smaller key size) down to 110 (for the larger key size).
If the key of an index is between 10 and 50 bytes, the maximum number of index
partitions is typically between approximately 180 (for keys of 10 bytes) and 60 (for
keys of 50 bytes). In some situations, the maximum might be as few as 38.
Factors that affect the number of allowed partitions are the data types of the
columns of the primary key and the number of catalogs used to describe the
partitions. Also, the description of the partitioned table or index must fit in a file
label.