SQL/MX Release 2.0 Best Practices

Data-loading Techniques and Considerations 39
block is reserved for future inserts. Because most fact tables are loaded once and seldom updated, this
free space is never used. Furthermore, each I/O reads less data. Setting the Slack to 0 ensures that all
available space is used for data. This parameter is specified in the FUP RELOAD utility.
Reorganizing Tables
For a database table to deliver good query scan performance, it must be reorganized periodically. Table
reorganization arranges the physical data and index blocks efficiently, and compacts each block so the
block is completely full. As blocks are scanned for a subsequent query, their physical ordering on disk
matches their logical access order, and each block contains the maximum number of rows. This
arrangement ensures the highest data-scan rate possible, because the disk subsystem can use optimized
data-access facilities to retrieve the data.
Tables usually become fragmented when rows are randomly inserted into a table, causing blocks to split
and decreasing the effective use of space. Additionally, the physical ordering no longer matches the
logical ordering.
Tables that experience random or pseudo-random inserts, random deletes, and updates that change
row lengths need to be reorganized periodically to ensure maximum scan performance. (Tables used in
database applications are often reloaded with zero slack space to provide maximum scan
performance.)
This FUP command is useful for determining which partitions of a table to reorganize:
FUP INFO tablename, STAT
Only those partitions that show fragmentation should be reorganized. The Tandem Reload Analyzer
(Reload Analyzer) can also help determine what files need to be reloaded.
Table reorganization in NonStop SQL/MX is an online maintenance function that provides full read and
write access during the process, using the FUP RELOAD utility. Many customers schedule batch jobs to
perform regular online reorganizations. The database administrator should maintain a list of tables that do
or do not require periodic reorganization, and a schedule of when reorganization should be performed.
An exhaustive verification of which tables are fragmented should be initiated to ensure that the
fragmentation does not contribute to poor performance.
Run FUP RELOADs before updating statistics. Updating statistics can be influenced by the index level of
the file. You can reduce the index levels by using FUP RELOAD. FUP currently recognizes only Guardian
names; it does not recognize ANSI names. ANSI names are the only names recognized in the SQL/MX
environment. You can obtain the Guardian names for FUP RELOAD by using a SHOWDDL or SHOWLABEL
for the table. To automate the process, or to obtain multiple entries to process, you can obtain the
information about the Guardian names from the SQL/MX catalog.
All SQL/MX database and ODBC Server catalog tables should be reorganized, especially after many
tables have been created or made available to ODBC. This action can improve SQL/MX database
compile performance and ODBC Server access.
Some database functionality cannot be done online while data is being accessed by queries or
applications. For example, you cannot create indexes online or move a partition while the partition is in
use.
The built-in delay within a TMF transaction (for boxcarring) allows TMF to wait for additional information
that can be combined within the transaction coming, and can reduce messaging within the system.
When collapsing a partition, you should reload data without slack both in the source partition and the
destination partitions. This action aids in accommodating the data into the table. Collapsing from two
partitions to one can result in a failure if you have a version of NonStop SQL/MX earlier than the ABJ SPR.
When migrating from one environment to another, use caution when recompiling prototypes-code from
source code. Recompiling only the executable code that contains prototypes sometimes causes the
code to attempt to access the previously compiled environment linked for the code.
Make sure that you do not have a “Halloween” problem when you use cursors. This problem occurs when
you update information that could place the row forward into the selection path, thus making the row