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

DataLoader/MX can be customized to perform data-format transformation while using the import
utility for append operations. For more information, see the DataLoader/MX Reference Manual.
Guidelines for Appending Data to Tables
• Use the import command to add data to a table or partition without purging existing data.
• Use import to add data to SQL/MX tables. You cannot use import to add data to ASCII
files or any other files other than SQL/MX tables.
• import can use ASCII source files or input files from DataLoader/MX.
• Use import to insert data into an audited table without resetting the audit attribute.
• If an error occurs during an append operation and import is able to terminate gracefully,
no new data is added to the target table for the last transaction run. To determine if an append
operation succeeded, check the listing to see if error messages occurred.
• If a CPU failure, process failure, BREAK command, or event interrupts an import operation,
import terminates with the table still in a valid state. Rows are committed based on the -T
option if that option is specified. For more information, see the SQL/MX Reference Manual.
Appending Data to Multiple Partitions in Parallel
Use import to append data to partitioned tables in parallel. This strategy can improve append
performance if table partitions are distributed across disks, CPUs, and I/O channels. To append
data to partitions in parallel:
1. Start an import process for each partition. One way is to start each import process in the
CPU associated with the target partition. Another way is to start import processes in the
CPUs associated with the data sources for the import command.
2. Issue one import request for each table partition.
3. Supply each import command with the specific range of input data for the target partition.
Possible strategies are:
• Arrange the input data so that it is divided into separate files, each containing input for
a specific target partition. Use these files as input to import commands.
• Run one instance of the import command to load data into a partitioned table by using
a single input file.
• Use DataLoader/MX to read input data. Start each DataLoader/MX process as a named
process before entering the import command. Use the process name as the input file
for the import command. The DataLoader/MX process must wait for requests on its
$RECEIVE file and then supply data by replying to those requests. When using this
approach, be sure to balance processing for optimal performance.
You can use the DataLoader/MX product to help implement these tasks. For example, use
DataLoader/MX to arrange to have the input data delivered to the correct target partitions.
For more information, see the DataLoader/MX Reference Manual.
Example of Using import to Append Data
Suppose that you have a history table containing 80 weeks of data. You could partition the table
so that each week of data resides on one partition. This scheme, however, might create
disproportionate requests for data from certain partitions, particularly those containing the most
recent weeks of data. Therefore, the table is partitioned by a hash value so that each week of data
is striped (partitioned) across 16 partitions. To maximize parallel execution, each partition is
associated with a different CPU.
The next example uses the import command each week to add the most recent week of data to
the appropriate 16 partitions. It starts an import process for each target partition (in each
206 Reorganizing SQL/MX Tables and Maintaining Data