LOAD

The LOAD utility is the fastest way to add a large amount of data to a DB2 table. It reads rows from a sequential input file and writes them directly into the table, bypassing most of SQL processing. Use LOAD when INSERT statements would be too slow - for example, loading millions of rows during an initial setup or a table refresh.

What is LOAD

• LOAD is a DB2 utility that runs under the DSNUTILB program.
• It copies data from a sequential input data set (SYSREC) into one or more DB2 tables.
• It is much faster than INSERT because it writes data pages directly and can skip DB2 logging.
• LOAD puts the table space into COPY pending status when LOG NO is used - you must run COPY after it.
• You can load into a table or a single partition, but not into a view.

How LOAD works

• The layout of the input file is described in a field specification list using POSITION, CHAR, DECIMAL and similar keywords.
• LOAD runs in phases: UTILINIT, RELOAD, SORT, BUILD, INDEXVAL, ENFORCE, DISCARDS, LOG and UTILTERM.
• Rows that fail validation are written to the discard data set (SYSDISC) instead of failing the whole job.
• If indexes exist on the table, LOAD sorts the keys and builds the indexes during the load.
• Referential integrity can be checked during the load with ENFORCE CONSTRAINTS.

Important LOAD options

• RESUME YES adds the new rows to the existing data; RESUME NO replaces all rows in the table space.
• LOG YES logs every row (safe but slow); LOG NO is fast but needs a full COPY afterwards.
• REPLACE deletes all existing rows before loading - be very careful with it in production.
• DISCARDDN names the discard data set; check it after every load to see rejected rows.
• STATISTICS TABLE(ALL) collects catalog statistics during the load and saves a separate RUNSTATS run.

Example of a LOAD control statement:-