DB2 Load Utility top parameters example JCL

The Load utility in DB2 is a series of steps. When you need load utility is the first step you always need to know. The answer is if you want to import data into DB2 tables in bulk, then you need to use Load utility. The series of steps also called different phases of DB2 Load utility execution.

The load utility is a little bit like the supercharged, speed version of the import utility.

There are sample JCLs those are frequently used in Load and Unload of data from to and pro to DB2. In simple terms, LOAD means importing data, and UNLOAD means exporting the data. This is one of the interview question.

It supports the full range of capabilities of the import command, but has many more options to control the speed and efficiency of a given load process.

But with added speed comes a cost. To provide this breakneck capability to load data, the load utility sacrifices several important checks and defers several others.

The de-merits of LOAD utility

The load utility only minimally logs its actions and completely ignores table triggers. It defers the changes to indexes and the checks for referential integrity, potentially masking problems until late in the load process.

One more important point, while working on LOAD utility you may get error like COPY PENDING. There are few resolutions you can read here.

Load utility in DB2 come with some key phrases or parameters. These are good for your interviews and to have command over these also good.

data buffer

Enables multiple 4KB buffers to be allocated for dealing with data transfer

sort buffer sortheap

Enables a load-specific sort buffer to be allocated, rather than the default


Controls the number of threads allocated for data preparation, such as reading, parsing, and formatting


Controls the number of threads allocated to write out loaded data to the DB2 containers


Controls the capability to spawn multiple threads for fetching source data from a cursor defined from another database

indexing mode

Enables the relevant index updates on target tables to be deferred


Enables a program to be called to provide the source data


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.