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

Recommended Practices for Improving import Performance
This subsection describes recommended practices for improving the performance of import
operations on SQL/MX tables:
• “Running import on Empty Tables” (page 195)
• “Running import on Populated Tables” (page 196)
• “Managing Partitions to Improve import Performance” (page 196)
• “Managing Constraints to Improve import Performance” (page 197)
• “Managing Indexes to Improve import Performance” (page 197)
• “Managing Triggers to Improve import Performance” (page 197)
• “Managing Data Types to Improve import Performance” (page 197)
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.
Using import to Load SQL/MX Tables 195