Mainframe : How DB2 load utility works

The DB2 load utility operates in following phases.

UTILINIT

RELOAD

SORT

BUILD

SORTBLD

INDEXVAL

ENFORCE

DISCARD

REPORT

UTILTERM

In this article I will be concentrating only on RELOAD, SORT and BUILD phases. Also I will not detail about the various validations that will happen in those phases and also restart process in order to keep this article very simple.

RELOAD phase

The following activities will happen during this phase

1. Read a record from the input file
2. Extract key values for each index defined on the table
3. Write those key values as a record in the SYSUT1 file(SORTIN file). If a table has 4 indexes, then it will write 4 records into the SYSUT1 file for each input record
4. Write the input record in the table space

The above activities are repeated until all the records are processed from the input file

SORT phase

This phase reads the SYSUT1 file (SORTIN file) and sorts the index records and writes the sorted records into the SORTOUT file

If the input file had 10 records and there are 4 indexes defined on the table, then SORTOUT file will have 40 records as shown below.

Index 1 key value from record 1

Index 1 key value from record 2

.

.

Index 1 key value from record 10

Index 2 key value from record 1

Index 2 key value from record 2

.

.

Index 2 key value from record 10

.

.

Index 4 key value from record 1

.

.

Index 4 key value from record 10

BUILD phase

This phase reads the SORTOUT file and writes the records into the INDEX space. Once it writes all the records to the index space for the 1^st index, it will move on to 2^nd index and so on. That is, it builds the index spaces one by one sequentially.