Topic
1 reply Latest Post - ‏2006-05-03T20:28:56Z by Jeff_Richardson
Jeff_Richardson
Jeff_Richardson
2 Posts
ACCEPTED ANSWER

Pinned topic Linux utilities to load DB2 tables from tape via named pipe

‏2006-05-03T20:25:39Z |
I was working with a customer who couldn't load some data on tape, created on DB2 for zVM, into DB2 for zSeries Linux. I didn't have immediate access to a zSeries tape subsystem so I tried it on a trusty IBM 7028 PowerPC system that had an internal SCSI DDS tape drive. I ran into the same problem: DB2 couldn't read the data from the tape by itself. So, I used [b]mt[/b], [b]tar[/b], [b]dds2tar[/b] packages, along with a [b]named pipe[/b] and the DB2 [b]load[/b] command to load the data. This was a fun little scripting exercise. I also use [b]sed[/b], [b]awk[/b] and [b]split[/b]. Scripting is a blast!

This is the DB2 DML (Data Manipulation Language) input file that loads the data.
It's not something you'd necessarily want to put directly into a script.
code
connect to sample;

delete from staff;
select * from staff;

  • Use the following line to load from named pipe
load from /db2pipe of asc
modified by nullindchar=-
method l (4 6, 8 16, 22 23, 25 29, 35 36, 39 46, 50 56)
null indicators (0,0,0,0,36,0,56)
messages loadrpt.txt
insert into staff
without prompting;

select * from staff;
connect reset;
[/code]

Here's the code that sets everything up.
code
#-----------------------------------------------------------
  1. db2pipe.sh Jeff Richardson 05/03/2006
  2. Load DB2 data from tape via named pipe on pSeries Linux.
  3. You can run this in a "script" session to save a console log.
  4. Requirements:
  5. mt (magnetic tape) and dds2tar packages must be installed
  6. DB2 must be installed, DB2 instance must db2inst1.
  7. db2pipe.sql must be in /home/db2inst1.
  8. SAMPLE database will be created if it doesn't exist already.
#------------------

if
then
echo -e "\n You must be root to execute this script."
exit
else
echo -e "\nLoad a blank tape into the drive."
echo -e "Press Enter when ready to continue.\c"
read input
cd /home/db2inst1
export TAPE=/dev/st0
mt -f $TAPE rewind
fi

#-----------------------------------------------------------
  1. Create the SAMPLE database, extract all data from staff table,
  2. put data only into staffdata.txt.
  3. The sed command deletes the prologue lines.
  4. The split command deletes the epilogue lines.
#-----------------------------------------------------------
echo "Creating input data."
su - db2inst1 -c "db2sampl" >/dev/null
su - db2inst1 -c 'db2 connect to sample >/dev/null; db2 "select * from staff"' >temp.txt

sed '1,3d' temp.txt >temp1.txt
fileSize=`expr \`wc -l temp1.txt | awk '{print $1}'\` - 3 `
split -l $fileSize temp1.txt temp
mv tempaa staffdata.txt
rm -f temp.txt temp1.txt tempab

#-----------------------------------------------------------
  1. Archive staffdata.txt to tape.
  2. Create a tape index to be used later.
#-----------------------------------------------------------
echo "Archiving input data to tape."
tar -cvf $TAPE staffdata.txt
dds2index -t tape.idx

#-----------------------------------------------------------
  1. Create a named pipe.
  2. Read data from tape into named pipe in background mode as root.
  3. Load data from DB2 via named pipe in foreground mode as db2inst1.
  4. db2pipe.sql contains the load command sequence.
#-----------------------------------------------------------
echo "Loading input data into the database."
rm -rf /db2pipe
mknod -m 666 /db2pipe p
dds2tar -f $TAPE -t tape.idx --body 'staffdata.txt' >/db2pipe &
rm -f loadrpt.txt
su - db2inst1 -c "db2 -tvf db2pipe.sql"

#-----------------------------------------------------------
  1. You should have 35 rows now.
#-----------------------------------------------------------
[/code]
Updated on 2006-05-03T20:28:56Z at 2006-05-03T20:28:56Z by Jeff_Richardson
  • Jeff_Richardson
    Jeff_Richardson
    2 Posts
    ACCEPTED ANSWER

    Re: Linux utilities to load DB2 tables from tape via named pipe

    ‏2006-05-03T20:28:56Z  in response to Jeff_Richardson
    Darn it, I hit submit too soon. The filename of the DML statements is db2pipe.sql. It's used by the "db2 -tvf db2pipe.sql" statement at the end of the script.