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

Technote (troubleshooting)


Problem(Abstract)

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

Symptom

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,


Cause

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.


Environment

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

<code>
#!/usr/bin/perl

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

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.

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows
Data Movement - Load (Client Side)

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:

2013-09-23

Translate my page

Machine Translation

Content navigation