SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

• “Managing Triggers to Improve import Performance” (page 200)
• “Managing Data Types to Improve import Performance” (page 200)
Running import on Empty Tables
You can import data into a table most quickly by using the fast loading technique, which import
automatically uses when the table meets all these conditions and no transaction size is specified
on the import command line:
• It is empty.
• It has no indexes.
• It has no droppable primary key, unique key, or foreign key constraints.
• It has no enabled triggers.
To further improve the performance of the fast loading technique, import turns off the audit
attribute for the entire table at the start of the operation and turns it back on when the operation
ends.
If, while you are performing an import operation with the fast loading technique, another import
operation is attempted on the same table, the second operation fails with a concurrent access
error.
To improve the performance of an import operation on an empty table:
• Verify that all data being imported is contained in a single flat input file. OSS flat files cannot
exceed 2 GB. If the input file exceeds 2 GB, consider using DataLoader/MX, which supports
larger Guardian files. For more information about using DataLoader/MX with import to
load and maintain SQL/MP and SQL/MX databases, see the DataLoader/MX Reference
Manual.
• For an empty range-partitioned table:
Make sure the data in the input file is presorted according to the clustering key order if
a clustering key is present. Presorting the input file data prevents external sorts and extra
1.
block splits. When DP2 inserts unordered data into a partition, import performance is
slowed.
2. Use the CONTROL QUERY DEFAULT statement to set the UPD_ORDERED attribute to OFF.
When the UPD_ORDERED attribute is ON, the ESP may respond by sorting imported
data.
3. You must redo your online dumps for all partitions that are loaded using the fast loading
technique (turning off audit invalidates online dumps).
4. If the import operation fails because the process dies, you must run RECOVER to cancel
or resume the import operation.
Running import on Populated Tables
To speed up the performance of import on populated tables, you must properly manage SQL/MX
objects that are known to slow down performance, including constraints, indexes, triggers, and
certain data types.
These factors determine the specific method to use for importing data into a populated table:
• The amount of data being imported. When you import a small amount of data (for example,
less than 500,000 rows), you can either perform the import operation in parallel or in a
single stream. When you import large amounts of data, you need to examine the data being
imported and the partitions into which it will be inserted. For example, examine the range of
198 Reorganizing SQL/MX Tables and Maintaining Data