Export all the tables in a given schema

This ksh script will log into the database, find all tables in the given schema, create the export script and run it.

This ksh script will log into the database, find all the tables in the given schema, and dynamically create the export script and run it. The DATADIR will contain the latest .ixf and .txt files. This is useful for complete schema exports instead of backing up every day. It will work on DB2 7.2.

All the files in a given full export (.ixf, .txt, exp.ksh, run_exp_tablelist) will be tar'd and compressed in the format of fullexpYYYY-MM-DD-HH24.MI.SS.tar.Z. Make sure to change the schema name and DATADIR locations as per your configuration. Also, it will send you an e-mail once it's finished.

#!/bin/ksh
if [[ $1 = '' ]] then
   DBNAME='TESTDB';
else
   DBNAME=$1;
fi

. ~dbadmin/sqllib/db2profile

hostname > ./hostfile
HOSTUPPER=`tr '[:lower:]' '[:upper:]' < ./hostfile`
echo $HOSTUPPER

export DATADIR=/data4/dbbackup/full/${DBNAME}

mkdir $DATADIR
 
cd ~dbadmin/schema/utilities/

LOGFILE=${HOSTUPPER}runexp.run
PERMLOGFILE=${HOSTUPPER}RUNEXP.LOG

#-------------------------------------------------------------------------
# Log everything to mail to dbadmin's forwarding list.
#-------------------------------------------------------------------------

RECIPIENTS=dbadmin

cat $LOGFILE >> $PERMLOGFILE

exec > $LOGFILE 2>&1
exec >> $LOGFILE

DATE=`date "+20%y-%m-%d-%H.%M.%S"`

print "**-- Starting RunExp in ${HOSTUPPER} --** $DATE" >> ${LOGFILE}

print "RECIPIENTS is set to: ${RECIPIENTS}" >> ${LOGFILE}

#-------------------------------------------------------------------------
# Connect to the database.
#------------------------------------------------------------------------- 

echo "CONNECTING TO $DBNAME"

db2 connect to $DBNAME 

if [[ $? -ne 0 ]]; then
#
# Need to send a file to terminate the mail.
#
   /usr/bin/mailx -s "${HOSTUPPER} $0 failed on Connect" ${RECIPIENTS} < ${LOGFI
LE}
   exit 2
fi

#-------------------------------------------------------------------------
# Check whether the runexp.sem file exists. If it does then the previous run
# was not successful.
#-------------------------------------------------------------------------

if [[ -f /tmp/runexp.sem ]]; then
   /usr/bin/mailx -s "${HOSTUPPER} runexp.sem already exists" ${RECIPIENTS} < /tmp/runexp.sem
   exit 2
fi

umask 022

touch /tmp/runexp.sem

if [[ $? -ne 0 ]]; then
   /usr/bin/mailx -s "${HOSTUPPER} Unable to create runexp.sem file" ${RECIPIENTS} <  ${LOGFILE}
   exit 2
fi

#-------------------------------------------------------------------------
# Remove the temporary files. the db2 command appends the list files.
#-------------------------------------------------------------------------

rm -f ${DBNAME}find_tbls.tmp
rm -f ${DBNAME}exp.ksh 
rm -f ${DBNAME}run_exp_tablelist 
rm -f ${DATADIR}/[A-Z]*.txt ${DATADIR}/[A-Z]*.ixf

#-------------------------------------------------------------------------
# Find all the tables to be exported. 
#-------------------------------------------------------------------------

db2 -t -r${DBNAME}find_tbls.tmp -ffind_tbls.sql  

#-------------------------------------------------------------------------
# Clean up the file generated by the db2 command above. 
#-------------------------------------------------------------------------

grep -v '^-----' ${DBNAME}find_tbls.tmp | grep -iv 'table_name' | grep -v 'selected.' | sed 's/ *$//' | sed '/^$/d' > ${DBNAME}run_exp_tablelist 

#-------------------------------------------------------------------------
# Dynamically create the export command list. 
# And run it. 
#-------------------------------------------------------------------------

./write_export_db.ksh ${DBNAME} ${DATADIR} ${DBNAME}run_exp_tablelist > ${DBNAME}exp.ksh

EXPDATE=`date "+20%y-%m-%d-%H.%M.%S"`

echo $EXPDATE

chmod +x ${DBNAME}exp.ksh


echo "Export Started at: `date "+20%y-%m-%d-%H.%M.%S.000000"`" >> ${LOGFILE}

./${DBNAME}exp.ksh

echo "Export Ended at: `date "+20%y-%m-%d-%H.%M.%S.000000"`" >> ${LOGFILE}

#-------------------------------------------------------------------------
# Disconnect from the database. Connection is established in exp.ksh. 
#-------------------------------------------------------------------------

db2 connect reset

#-------------------------------------------------------------------------
# tar the relevant files with the correct start timestamp and compress it. 
#-------------------------------------------------------------------------

cp ${DBNAME}run_exp_tablelist ${DATADIR}
cp ${DBNAME}exp.ksh ${DATADIR}
 
cd ${DATADIR}

tar -cvf ${HOSTUPPER}fullexp${EXPDATE}.tar *.ixf *.txt ${DBNAME}run_exp_tablelist ${DBNAME}exp.ksh

echo "Compress Started at: `date "+20%y-%m-%d-%H.%M.%S.000000"`" >> ${LOGFILE}
compress ${DATADIR}/${HOSTUPPER}fullexp${EXPDATE}.tar
echo "Compress Ended at: `date "+20%y-%m-%d-%H.%M.%S.000000"`" >> ${LOGFILE}

#-------------------------------------------------------------------------
# Remove the runexp.sem file and send mail on success.
#-------------------------------------------------------------------------

rm -rf /tmp/runexp.sem

/usr/bin/mailx -s "${HOSTUPPER} $0 Successful" ${RECIPIENTS} < ${LOGFILE}

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free DB2 tips and scripts.
  • Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: DB2 tips, tutorials, and scripts from around the Web.

This was first published in February 2003

Dig deeper on DB2 UDB (universal databases)

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close