SQL/MX Release 2.0 Best Practices

Database Sizing Considerations 20
In NonStop SQL/MX, a scratch file can overflow to another disk. So, if a scratch file becomes full or if the
disk becomes full, the operation does not necessarily fail. An additional scratch file on another disk is
selected (using the criterion procedure). As a result, there is no 2 GB limit on scratch space.
In NonStop SQL/MX, the operations that can create scratch files are sort, hash join, and hash groupby.
They all use the criterion procedure to determine which scratch disk to use.
• NonStop SQL/MX does not manage swap file space directly. Instead, SQL/MX processes rely on the
Kernel-Managed Swap Facility (KMSF), which is setup in the NonStop operating system with the
NSKCOM tool. Each CPU has an associated swap file.
Scratch Disk Management
These attributes determine how NonStop SQL/MX manages scratch disks for the SORT operation:
• SCRATCH_DISKS_EXCLUDED
• Set to a list of scratch disk volumes, where each item in the list has the form [\node.]$volume,
and the items in the list are separated by a comma (,). Use this default to exclude certain
volumes from being used for scratch disks.
If none of the three scratch disk defaults are set, the system determines the scratch disk volumes
to be used.
• SCRATCH_DISKS_PREFERRED
• Set to a list of scratch disk volumes, where each item in the list has the form [\node.]$volume,
and the items in the list are separated by a comma (,). Use this default to indicate preference for
volumes to be used for scratch disks.
If none of the three scratch disk defaults are set, the system determines the scratch disk volumes
to be used.
• SCRATCH_FREESPACE_THRESHOLD_PERCENT
• Indicates how much free space, as a percentage, is left on a disk as a threshold. When that
threshold is reached, hash or sort operations will use a different disk. If all disks reach their
threshold, NonStop SQL/MX displays an error.
The default value is 10. When disk usage reaches the point where only 10% of the space remains,
hash or sort operations stop using that disk.
Data-type Considerations
Use either standard DATE or DATETIME data types for date columns. Do not use YEAR TO MONTH, because
YEAR TO MONTH is not standard ANSI and can lead to problems when accessing data through tools that
rely on the ODBC or ANSI standards.
No auto-incrementing column type is available. This function exists in some other databases, but is not
currently available in NonStop SQL/MX. It is recommended that you handle this action programmatically
by using a cache of numbers from a source table. This arrangement eliminates any bottlenecks that arise
from returning to the source table for each number needed.
Consideration for Summary Tables
Some queries might benefit from the creation of summary tables, where pre-aggregated data is created
along one or more dimension attributes such as claim type, region, month, and so on. Although these
tables require more effort to build and maintain, they can produce profound performance improvements
for suitable queries that are currently long running, especially because these tables are expected to
reduce row access by a factor of 100 or more. Queries must be analyzed individually to determine
whether summary tables would be useful.
Summary tables are also useful for materializing calculated columns, based on existing table columns.
Applications that perform frequent extensive calculations can benefit from accessing precalculated
columns.