Regis,

This script I use to clone a prod db and restore into a QC env 1x/week. Look
at the main section that should help you.

I too have an oracle background. Finding DB2 resources is very difficult.

Alex


#!/bin/ksh
#===========================================================================
====
# Name : db_replace_restore.sh
# Purpose: Script that does a replace/restore
# arg1 = source DB
# arg2 = destination DB
# arg3 = Logging option on destination DB script rredirect.sh
# Syntax : $db_replace_restore.sh fs8prod fs8qc N
#
# PERSON DATE COMMENTS
# Alex Zaleski 07/10/2002 Creation
#===========================================================================
====
# SET ENVIRONMENT VARIABLES
. /home/db2inst1/.profile
typeset -l PRIMARYDB=$1
typeset -l CLONEDB=$2

d=`date "+%Y%m%d"`
PBOX=10.30.3.48
WDIR=${HOME}/work/${CLONEDB}
BACKUPDIR=/db2backup
LOGFILE=/db2/trash/db_replace_refresh.log.$d

#===========================================================================
====
# Function to get backup information from Primary Box
#===========================================================================
====
get_remote_info()
{
typeset -u PRIMARYDBU=${PRIMARYDB}
PFILE=`rsh ${PBOX} "ls -rtl ${BACKUPDIR}/${PRIMARYDBU}*.Z" | \
tail -n 1 | awk '{print $9}'`
PTSTAMP=`echo ${PFILE} | awk 'BEGIN { FS = "." } {print $6 } END {}'`
RRFILE=${HOME}/work/${CLONEDB}/RR_From_${PRIMARYDB}_To_${CLONEDB}_$d.sql
printf " Latest backup file - pfile=${PFILE} \n"
printf " Timestamp on backup file - timestamp=${PTSTAMP} \n"
printf " Path to rredirect script - rrfile=${RRFILE} \n"
}
#===========================================================================
====
# Function to rollforward database restore with logs
#===========================================================================
====
get_log()
{
STATUS=`db2 rollforward database ${CLONEDB} query status | \
grep "Rollforward status" | awk '{print $5}'`
while [ ${STATUS} != "not pending" ]
do
CURLOG=`db2 rollforward database ${CLONEDB} query status | \
grep "Next log" | awk '{print $8}'`
printf " Log name needed for rollforward - curlog=${CURLOG}\n"
if [[ ${CURLOG} != ${OLDLOG} ]]
then
rcp ${PBOX}:/db2/${PRIMARYDB}/logs/${CURLOG} /db2/${CLONEDB}/logs
db2 rollforward database ${CLONEDB} to end of logs
STATUS=`db2 rollforward database ${CLONEDB} query status | \
grep "Rollforward status" | awk '{print $5}'`
printf " Rollforward status of DB restore - status=${STATUS}\n"
else
db2 rollforward database ${CLONEDB} stop
break
fi
OLDLOG=${CURLOG}
done
}
#===========================================================================
====
# MAIN PROCESSING BEGINS
#===========================================================================
====
(
printf "REPLACE RESTORE ${PRIMARYDB} INTO ${CLONEDB}\n"
get_remote_info

printf "\n`date` - Get the backup file from ${PBOX} and uncompress...\n"
rcp ${PBOX}:${PFILE} ${BACKUPDIR}
uncompress ${PFILE}

printf "\n`date` - Check the rcp'd file exists, exit if not\n"
if [ -s ${PFILE} ]
then
printf "Rcp'd file does not exist, exiting...\n"
exit 1
fi

printf "\n`date` - Create and get the rredirect.sql from ${PBOX}...\n"
rsh ${PBOX} "${HOME}/oper/rredirect.sh ${PRIMARYDB} ${CLONEDB} ${PTSTAMP}
$3"
rcp ${PBOX}:${RRFILE} ${HOME}/work/${CLONEDB}

printf "\n`date` - Restore the backup...\n"
compress /db2/${CLONEDB}/logs/*.LOG
db2 force applications all
db2 -vtf ${RRFILE}

printf "\n`date` - Rollforward database...\n"
get_log

printf "\n`date` - Update the ps.psdbowner table with clone DB name\n"
db2 connect to ${CLONEDB}
db2 "select * from ps.psdbowner"
db2 "update ps.psdbowner set dbname=upper('${CLONEDB}')"
db2 "select * from ps.psdbowner"
db2 terminate

printf "\n`date` - Compress and move backup file to trash...\n"
compress `echo ${PFILE} | sed 's/.Z//'`
mv ${PFILE} /db2/trash

printf "\n`date` - Running SQL for Anantha\n"
/home/psadm/qcupdate.sh

) > ${LOGFILE} 2>&1

# SEND THE MAIL
email_send.sh dba ${LOGFILE} db_replace_restore.sh

# END OF FILE

-----Original Message-----
From: Regis Biassala [mailto:Regis.[Email Address Removed]
Sent: Thursday, January 16, 2003 11:44 AM
To: [Email Address Removed] How do you pass parameter when running a sql script

> Hi Gurus,
> It is abit fustrating that IBM does not provide infos about things...I am
> from Oracle background.
> How do you pass certain parameter to a sql script being called by DB2 ??
>
> example:
> connect to <dbname> user <username> using <password>
>
> I want to pass these 3 parameters to my sql script:
>
> ### script ##
> connect to %1 user %2 using %3 ;
> ...
> more sql statements
>
>
> ## end of script ###
>
>
> This is urgent, please advice.
> Regis
>
>
>
>
>
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.

This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************


---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
To unsubscribe, e-mail: db2udbdba-[Email Address Removed] additional commands, e-mail: db2udbdba-[Email Address Removed]DB2 & UDB email list listserv db2-l LazyDBA home page