ITEM: BH9066L

DB2 -How to export more than 2 gig

ENV:
  DB2 v2.1
 DESC:
  DB2 how to export a table with more than 2 gig of data since no 
  file can be larger than 2 gigs?

ACTION:
  There are two ways to do this.  The suggested (and supported method)
  is use selective sort which produce less than 2 gig results.  With a
  couple of these export the results to individual files.

  A more direct method (which is unsupported but works) is to export
  to a named pipe.  With a monitor program reading from the pipe prior
  to the start of the export you can redirect the output to a file.  
  If that files gets near 2 gig you can change the file name.

  Below is a script which can work as the monitor program.

  You will need to first create a pipe:

     EXAMPLE
     =======

     $ mknod todisk p

  Then start the script:

     EXAMPLE
     =======

     $ piped todisk

  Now start the export:

     EXAMPLE
     =======

     $ db2 "export to todisk of del select * from table.name"

  Please note that this process assume that there will be sufficient
  disk space to hold these exported files.

  SPECIAL NOTICES
  ===================================================================
       Information in this document is correct to the best of our
       knowledge at the time of this writing.

       Please use this information with care.  IBM will not be
       responsible for damages of any kind resulting from its use.
       The use of this information is the sole responsibility of
       the customer and depends on the customer's ability to eval-
       uate and integrate this information into the customer's
       operational environment.

___________________________ SCRIPT START ____________________________

\#!/bin/ksh
\#-----------------------------------------------------------------
\# This script takes input from a named pipe (or file, if you like)
\# and copies it to a file until the file is ~1.5 gig at which time
\# a new file is created and used for data storage.
\#
\# This script expects three arguments:
\#
\#    piped [name of pipe] [Destination File]
\# to start the transfer, or
\#
\#    piped -k
\# to stop the program.
\#
\# This script is provided for your information only; there is
\# no implied or explicit support for this script from IBM.
\#
\# key to VM symbols:  (look for \#\# at start of line)
\#    A = Left Square Bracket       B = Right Square Bracket
\#    C = Left Curly Brace          D = Right Curly Brace
\#    E = Pound Sign (Shift-3)
\#-----------------------------------------------------------------

\# used to decide if the file is large enough to need to switch
\# to a new one
typeset -i FILE=0

\# build name of file to put pid in...
pid_file=/tmp/${0\#\#*/}.pid
\#\#             C EE  D

\#-----------------------------------------------------------------
\# check to see if we are just killing off the old piped
\#\# AA           BB
if [ $1 = "-k" ]
then
    kill $(cat $pid_file)
    rm $pid_file
    exit 0
fi

\# check for proper number of arguments otherwise
\#\# AA E      BB
if [ $\# != 2 ]
then
    print -u2 "Usage: $0 [pipe] [file dest]"
\#\#                       A    B A         B
    exit -1
fi

\#-----------------------------------------------------------------
\# save our PID in a convenient location (/tmp)
echo $$ > $pid_file

\#-----------------------------------------------------------------
\# open pipe for reading (just put it on standard input...)
exec 0\< $1

\#-----------------------------------------------------------------
\# and loop until we are killed...
while true
do
     if read x
     then
         echo $x >> $2.${FILE}
         if [ `/usr/bin/ls -l $2 | awk '{print $5}'` -ge 1500000 ]
         then
             let FILE="${FILE} + 1"
         fi
     fi
done

\#-----------------------------------------------------------------
\# should never get here, but let's make it look nice...
exit 0

------------------------ SCRIPT END ------------------------------




Support Line: DB2 -How to export more than 2 gig ITEM: BH9066L

Dated: April 1996 Category: N/A

This HTML file was generated 99/06/24~13:30:21

Comments or suggestions?
Contact us