SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

Using DDL_DEFAULT_LOCATIONS to Distribute Primary Range Partitions
You can use the DDL_DEFAULT_LOCATIONS system default to specify one or more default volumes
for the primary range partition in a CREATE statement that does not include a LOCATION clause.
If DDL_DEFAULT_LOCATIONS specifies more than one default volume, it chooses one at random
for the location specification. This arrangement might be particularly useful for an application that
creates small, short-lived tables as part of processing a more complex task.
Because CREATE statements do not allow more than one default location,
DDL_DEFAULT_LOCATIONS never needs to select more than one location for a given CREATE
statement. This approach is a simple but effective method to distribute primary partitions evenly
across the set of volumes specified in DDL_DEFAULT_LOCATIONS.
For more information, see the SQL/MX Reference Manual.
Special Considerations for Decision Support Systems (DSS) Applications
DSS applications typically require periodic addition and deletion of data. Further, access must be
well balanced. For NonStop SQL/MX, the partitioning key can be different from the clustering
key. The choice of leftmost primary key column—and 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 clustering 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 one or more of these strategies:
• Mirror the volume associated with the primary partition to maximize availability. Queries and
programs that reference the primary partition require its availability when accessing data.
• Mirror the volume that contains the SMD tables.
• Configure mirrored disks on separate channels to maximize performance. Use this strategy
for volumes containing primary and secondary partitions where possible.
Examples for Creating Partitioned SQL/MX Tables
Example for Creating an SQL/MX Table With a Single Range Partition
CREATE TABLE cat1.sch1.ordersr
(location char(16) not null not droppable,
ordernumber integer unsigned not null not droppable,
ordertime timestamp,
primary key (location, ordernumber) not droppable)
location $DATA01;
Example for Creating an SQL/MX Table With Multiple Range Partitions
CREATE TABLE cat1.sch1.ordersr
(location char(16) not null not droppable,
ordernumber integer unsigned not null not droppable,
ordertime timestamp,
primary key (location, ordernumber) not droppable)
location $DATA01
partition(
add first key ('Chicago') location $DATA02,
add first key ('New York') location $DATA03 );
Example for Creating an SQL/MX Table With a Single Hash Partition
CREATE TABLE cat1.sch1.ordersh
(location char(16) not null not droppable,
ordernumber integer unsigned not null not droppable,
ordertime timestamp,
primary key (location, ordernumber) not droppable)
Creating SQL/MX Tables 85