IBM Support

A table does not show load pending state when a DB2 load job aborts

Technote (troubleshooting)


Interrupting a LOAD may result in a table with NORMAL state instead of LOAD PENDING.


interrupting a load may result in the table state becoming NORMAL instead of LOAD PENDING if you are using named pipe. The LOAD may be from a remote client. The load may be a DB2 command line or LOAD API,


In a LOAD using named pipe, there are 3 factors :

1 - the process that is writing to the pipe
2 - the LOAD that is reading from the pipe,
3 - and the DB2 server that is putting the data into the table.

If the pipe writer process is interrupted, it stops writing and close the output file pointer. This is default signal handling for SIGINT. As far as the LOAD process is concerned, the writer has finished, and there is nothing more to read. DB2 server then place the table in NORMAL state.
DB2 is working as designed.

However, if the LOAD process is interrupted, the table will be placed in LOAD PENDING state as documented.


Any system and version that tries to use named pipe.

Diagnosing the problem

This problem can be easily simulated in a TEST environment using the following steps :

1 - as root make a named pipe with the mkfifo command : mkfifo /tmp/LOADPIPE - and chmod to ensure it is writable by all users

2 - on DB2 server, create a simple table of structure ( int , int )

3 - this is a very simple Perl program to generate data and write to the named pipe /tmp/LOADPIPE


open FH , "> /tmp/LOADPIPE" or die ;
for ( $i = 1000 ; $i < 50000000 ; $i++ ) {
print FH "$i,$i\n" ;
close FH ;

4 - use the LOAD command to load data into table using the named pipe /tmp/LOADPIPE ( specify nonrecoverable to avoid logging )

5 - interrupt the program that is writing to the pipe

6 - observe the table state using LOAD QUERY command and watch it change from "Load in Progress" to "NORMAL".

Resolving the problem

The LOAD is working as designed. If you plan to use a named pipe - you need to factor in the situation when the pipe writer gets interrupted or terminated abnormally.

Document information

More support for: DB2 for Linux, UNIX and Windows
Data Movement - Load

Software version: 9.1, 9.5, 9.7, 9.8, 10.1, 10.5

Operating system(s): AIX, HP-UX, Linux, Solaris

Reference #: 1646842

Modified date: 18 November 2015

Translate this page: