Data Movement Utilities Guide and Reference

AutoLoader Hints and Tips

Following is some information to consider before using the AutoLoader utility:

• Familiarize yourself with AutoLoader operations by using the utility with small amounts of data.

• If the input data is already sorted, or in some chosen order, and you wish to maintain that order during the loading process, only one database partition should be used for splitting. Parallel splitting cannot guarantee that the data will be loaded in the same order it was received.

• If large objects (LOBs) are being loaded from separate files (that is, if you are using the lobsinfile modifier through the load utility), all directories containing the LOB files must be read-accessible to all the database partitions where loading is taking place. The LOAD lob-path parameter must be fully qualified when working with LOBs.

• All temporary AutoLoader files reside in the directory specified through the LOGFILE AutoLoader configuration parameter. This directory must be network-accessible with both read and write access to all partitions where splitting is to be done. By specifying different directories for temporary files, you can run multiple concurrent AutoLoader jobs to load data into separate tables in different table spaces.

• The maximum number of active database connections in an AutoLoader job is the number of loading partitions defined in the OUTPUT_NODES AutoLoader configuration parameter. Ensure that the maxxappls (maximum number of active applications) database configuration parameter has been set high enough.

• You can force an AutoLoader job to continue even if the AutoLoader detects (at startup time) that some loading partitions or associated table spaces are offline, by specifying FORCE=YES in the AutoLoader configuration file.

• Use the STATUS_INTERVAL AutoLoader configuration parameter to monitor the progress of an AutoLoader job. AutoLoader returns messages at specified intervals, indicating how many megabytes of data have been processed.

• Better performance can be expected if the splitting partitions (as defined by the SPLIT_NODES parameter) are different from the loading partitions (as defined by the OUTPUT_NODES parameter), since there is less contention for CPU cycles. The AutoLoader utility itself should be invoked on a database partition that is not participating in either the splitting or the loading operation. On an SMP system, you can improve performance by ensuring that there is at least one splitter task for every available CPU.

• AutoLoader ignores the MESSAGES parameter in the LOAD command, and directs all messages from the LOAD command into the file load_log.XXX; this file contains messages from the load process on database partition XXX. AutoLoader also creates a file called splt_log.XXX; this file contains messages from the split process on database partition XXX. The utility also creates a file called autoload.log, containing messages from the main AutoLoader script. Check it to ensure that all pipes and temporary directories have been set up correctly.

• AutoLoader chooses only one output database partition on which to collect statistics. The RUN_STAT_NODE AutoLoader configuration parameter can be used to specify that partition.

• Multiple invocations of AutoLoader can be used to load data simultaneously into separate tables. Ensure that:

  • The tables reside in separate table spaces.

  • All of the AutoLoader operations are invoked from separate directories.

  • The data file name used to create temporary pipes is unique for each AutoLoader operation.

[ DB2 List of Books | Search the DB2 Books ]