Moving data using a customized application (user exit)

The user exit must reside in the bin subdirectory of the Db2® installation directory (often known as sqllib). The load utility invokes the user exit executable with the following command line arguments:

<base pipename> <number of source media> 
<source media 1> <source media 2> ... <user exit ID> 
<number of user exits> <database partition number>

Where:

<base pipename >

Is the base name for named-pipes that the load utility creates and reads data from. The utility creates one pipe for every source file provided to the LOAD command, and each of these pipes is appended with .xxx, where xxx is the index of the source file provided. For example, if there are 2 source files provided to the LOAD command, and the <base pipename> argument passed to the user exit is pipe123, then the two named pipes that your user exit should feed with data are pipe123.000 and pipe123.001. In a partitioned database environment, the load utility appends the database partition (DBPARTITION) number .yyy to the base pipe name, resulting in the pipe name pipe123.yyy.xxx..

<number of source media>

Is the number of media arguments which follow.

<source media 1> <source media 2> ...

Is the list of one or more source files specified in the LOAD command. Each source file is placed inside double quotation marks.

<user exit ID>

Is a special value useful when the PARALLELIZE option is enabled. This integer value (from 1 to N, where N is the total number of user exits being spawned) identifies a particular instance of a running user exit. When the PARALLELIZE option is not enabled, this value defaults to 1.

<number of user exits>

Is a special value useful when the PARALLELIZE option is enabled. This value represents the total number of concurrently running user exits. When the PARALLELIZE option is not enabled, this value defaults to 1.

<database partition number>

Is a special value useful when the PARALLELIZE option is enabled. This is the database partition (DBPARTITION) number on which the user exit is executing. When the PARALLELIZE option is not enabled, this value defaults to 0.

The <user exit ID>, <number of user exits>, and <database partition number> arguments passed into each user exit reflect the unique identifier (1 to N), the total number of user exits (N), and the database partition (DBPARTITION) number on which the user exit instance is running, respectively. You should ensure that any data written to the named pipe by each user exit process is not duplicated by the other concurrent processes. While there are many ways your user exit application might accomplish this, these values could be helpful to ensure data is not duplicated. For example, if each record of data contains a unique integer column value, your user exit application could use the <user exit ID> and <number of user exits> values to ensure that each user exit instance returns a unique result set into its named pipe. Your user exit application might use the MODULUS property in the following way:

i = <user exit ID>
N = <number of user exits>

foreach record
{
   if ((unique-integer MOD N) == i)
   {
     write this record to my named-pipe
   }
}

Example 1: A Load userexit script that replaces all tab characters '\t' with comma characters ',' from every record of the source media file. To invoke the Load utility using this userexit script, use a command similar to the following:

DB2 LOAD FROM /path/file1 OF DEL INSERT INTO schema1.table1
SOURCEUSEREXIT example1.pl REDIRECT OUTPUT TO FILE /path/ue_msgs.txt

Note that the userexit must be placed into the sqllib/bin/ folder, and requires execute permissions.

example1.pl:

#!/bin/perl

# Filename: example1.pl
#
# This script is a simple example of a userexit for the Load utility
# SOURCEUSEREXIT feature. This script will replace all tab characters '\t'
# with comma characters ',' from every record of the source media file.
#
# To invoke the Load utility using this userexit, use a command similar to:
#
#  db2 LOAD FROM /path/file1 OF DEL INSERT INTO schema1.table1
#   SOURCEUSEREXIT example1.pl REDIRECT OUTPUT TO FILE /path/ue_msgs.txt
#
# The userexit must be placed into the sqllib/bin/ folder, and requires
# execute permissions.
#--------------------------------------------------------------------
if ($#ARGV < 5)
{
  print "Invalid number of arguments:\n@ARGV\n";
  print "Load utility should invoke userexit with 5 arguments (or more):\n";
  print "<base pipename> <number of source media> ";
  print "<source media 1> <source media 2> ... <user exit ID> ";
  print "<number of user exits> <database partition number> ";
  print "<optional: redirected input> \n";
  die;
}

# Open the output fifo file (the Load utility is reading from this pipe)
#-----------------------------------------------------------------------
$basePipeName = $ARGV[0];
$outputPipeName = sprintf("%s.000", $basePipeName);
open(PIPETOLOAD, '>', $outputPipeName) || die "Could not open $outputPipeName";

# Get number of Media Files
#--------------------------
$NumMediaFiles = $ARGV[1];

# Open each media file, read the contents, replace '\t' with ',', send to Load
#-----------------------------------------------------------------------------
for ($i=0; $i<$NumMediaFiles; $i++)
{
  # Open the media file
  #--------------------
  $mediaFileName = $ARGV[2+$i];
  open(MEDIAFILETOREAD, '<', $mediaFileName) || die "Could not open $mediaFileName";

  # Read each record of data
  #------------------------
  while ( $line = <MEDIAFILETOREAD> )
  {
    # Replace '\t' characters with ','
    #---------------------------------
    $line =~ s/\t/,/g;

    # send this record to Load for processing
    #-----------------------------------------
    print PIPETOLOAD $line;
  }
  # Close the media file
  #---------------------
  close MEDIAFILETOREAD;
}

# Close the fifo
#---------------
close PIPETOLOAD;

exit 0;