DB2: db refresh script

#!/bin/ksh

#-----------------------------------------------------------------------------------------#

# db2.refresh.sh

# version: 20240619035

# This script performs a refresh using a backup image from production or stage

# to refresh a dev or tst database.

# When using this script for database WCTST3 additional steps are needed after the refresh

# that this script cannot perform because that database is part of an HADR cluster.

# This script will exit if it encounters a failure with a command.

# The error message will display what stage or command failed.

# The script can be run again, after correcting the failure without rerunning previously

# successful stages. This is done by setting the stage variable to the stage where the

# script should pickup from.

# TO RUN:

# ./db2.refresh.sh -s <source db> -t < target db> [-b <#>] [-e <#>] [-l </path/to/backup>]

# EXAMPLE:

# ./db2.refresh.sh -s wcdev2 -t wcdev3

# STAGE CODING EXPLAINED

# # drop database <--- Simple description of work to be performed

# if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then <--- This if statement checks if this stage is to be run or not

# beginStage "Prepping target database for restore" <--- Announce stage starting

# db2 -v terminate <-,- These are the commands being executed in the stage

# db2 -v force applications all |

# db2 -v deactivate db ${target_db} |

# errorChk $? "deactivating the database" |

# db2 -v drop db ${target_db} <-'

# errorChk $? "dropping the database" <--- Checks previous command for errors (warnings will prompt to continue; failures will exit)

# endStage <--- Announce completion of stage

# fi

# (( next++ )) <--- This variable tracks script progress and prevents the need to hard code each stage number

#-----------------------------------------------------------------------------------------#

# functions

#-----------------------------------------------------------------------------------------#

endScript(){

# call using endScript <message>

echo " " | tee -a $log

echo "Stage ${stage}: ${1}" | tee -a $log

echo " " | tee -a $log

exit 5

}

errorChk(){

err=${1}

msg=${2}

# call using errorChk <exit code> <message>

if [[ ${err} -ge ${failure} ]]; then

endScript "Failure returned ${msg}"

elif [[ ${err} -eq ${warning} ]]; then

until [ ${response:-maybe} = "yes" ] || [ ${response:-maybe} = "no" ]; do

echo "Warning returned ${msg}"

echo -n "continue? (enter yes or no) "

read response

if [ ${response:-maybe} = "no" ]; then

endScript "Warning returned ${msg}"

done

response=maybe

}

beginStage(){

# call using beginStage <stage title>

echo "--------------------------------------------------------------------" | tee -a $log

echo "Beginning stage ${stage}: $1 " | tee -a $log

echo "--------------------------------------------------------------------" | tee -a $log

sleep 2

}

endStage(){

#call using endStage

echo "Stage ${stage} complete" | tee -a $log

stage=$(( stage + 1 ))

}

Help(){

cat <<USAGE_END

Script for refreshing lower environments

Each major function performed by the script is part of a stage.

These stages can be used to restart the script after a failure without

having to redo work that has already been done. The end option (-e) allows

you to stop executing the script after a specific stage. This can be

useful if you want to step through the refresh process carefully.

To run only one stage set the begin (-b) and end (-e) to the same number,

that being the number of the stage to run.

STAGES:

0 = Verify target database

1 = Backup database ddl

2 = Backup key db cfg settings

3 = Deactivate target database

4 = Create restore script

5 = Edit the restore script

6 = Restore the database

7 = Rollforward database

8 = Perform post-restore grants

9 = Update HADR settings

10 = Check and fix referential integrity

11 = Validate restored database

12 = Post refresh backup

Usage:

$0 -t <target_db> -s <source_db> [-b <#>] [-e <#>] [-l </path/to/backup>]

-b <begin#> Number value that reflects which stage to start from

Default = 0

-e <end#> Number value of stage to stop after

Default = 20 (don't stop at any stage)

-h Prints help and exits

-l </path> Storage path containing backup image

Default = /usr/opt/app/dbdump

-s <source db> REQUIRED - Name of database from backup

-t <target db> REQUIRED - Name of database to refresh

MUST BE RUN FROM THE INSTANCE OWNER OF THE TARGET DATABASE

USAGE_END

exit 0

}

#-----------------------------------------------------------------------------------------#

# Script

#-----------------------------------------------------------------------------------------#

# read in options from the command line

while getopts "b:e:hl:s:t:" opt; do

case $opt in

b) stage=$OPTARG ;;

e) stop=$OPTARG ;;

h) Help ;;

l) location=$OPTARG ;;

s) source_db=$OPTARG ;;

t) target_db=$OPTARG ;;

\?) echo "Invalid option: -$OPTARG" ; Help ;;

esac

done

trap 'endScript "Script terminated"' SIGINT SIGHUP

set -o pipefail

# check that the target database is set

if [ ${target_db:+1} -ne 1 ]; then

endScript "The target database (-t) was not set"

# check that the source database is set

if [ ${source_db:+1} -ne 1 ]; then

endScript "The source database (-s) was not set"

# set variables

#constants used in error checking

success=0

warning=2

failure=4

# change target_db var to all lower case

target_db=$(echo $target_db | tr '[:upper:]' '[:lower:]')

# set instance name and change to all lower case

instance=$(whoami)

instance=$(echo ${instance} | tr '[:upper:]' '[:lower:]')

# capture home directory of instance

# this is done to source the db2profile in the next step

instance_home="$(grep -i "$instance" /etc/passwd | awk -F: '{ print $6 }')"

# source the db2profile

if [ -f ${instance_home}/sqllib/db2profile ]; then

. ${instance_home}/sqllib/db2profile

else

echo "The script was unable to find or source the db2profile"

echo "Please check that the instance name [ $instance ] is correct"

endScript "Failed to source db2profile"

# set default database

# this prevents the need to connect to database when running db2 cmds

export DB2DBDFT="${target_db}"

# default stage var to 0 if not set at cmd line

# var1 is used for displaying settings

if [ ${stage:-null} = "null" ]; then

stage="${stage:-0}"

var1="at the beginning"

else

var1="at stage ${stage}"

# default stop var to total count of keyword occurrences in script minus 2, unless set at cmd line

# var2 is used for displaying settings

if [ ${stop:-null} = "null" ]; then

stop="${stop:-$(( $(grep -o scaena $0 | wc -l | awk '{$1=$1};$1') - 2 ))}"

var2="after completing all stages"

else

var2="after stage ${stop}"

# set next var to zero

next=0

# set backup_path to default if not set at cmd line

backup_path="${location:-/usr/opt/app/dbdump/db_refresh}"

# append the instance name to the base active log path

newlogpath="/rd300/db2logs/${instance}"

# change source_db var to all upper case (needed for finding backup image)

source_db=$(echo $source_db | tr '[:lower:]' '[:upper:]')

# set image var to most recent file for source_db located on the path

image=$(ls -t ${backup_path}/${source_db}*DBPART* | head -1 )

# if a backup was not found for source_db exit script

if [[ ${image:+1} -ne 1 ]]; then

endScript "Failed to find backup image at ${backup_path}"

# strip timestamp from backup image

backup_timestamp=$(echo ${image} | cut -d. -f5 )

here=$(cd -- $(dirname $0) >/dev/null 2>&1; pwd -P)

# append the backup timestamp to the refresh_path var

refresh_path="${here}/${target_db}/${backup_timestamp}"

# create refresh directory as needed

if [ ! -d ${refresh_path} ]; then

mkdir -p ${refresh_path} || endScript "Error while creating directory ${refresh_path}"

log="${refresh_path}/${target_db}_refresh.log"

echo "$(date)" >> $log

echo "" >> $log

# display settings and prompt for approval to continue

echo "REFRESH SETTINGS" | tee -a $log

echo " (-b):Script will start ${var1}" | tee -a $log

echo " (-e):Script will stop ${var2}" | tee -a $log

echo " (-l):Backup image located at ${backup_path}" | tee -a $log

echo " Path for storing output files is ${refresh_path}" | tee -a $log

echo " Instance = ${instance}" | tee -a $log

echo " (-t):Target database = ${target_db}" | tee -a $log

echo " (-s):Source database = ${source_db}" | tee -a $log

echo " Backup image being used is ${image}" | tee -a $log

echo "PLEASE READ"

echo " Ensure that these settings are correct before continuing"

errorChk 2 "user approval needed"

#-----------------------------------------------------------------------------------------#

# Refresh Stages

#-----------------------------------------------------------------------------------------#

# scaena

# verify target database exists

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Verifying target database"

dbChk=$(db2 list database directory | grep -i alias | grep -i ${target_db})

if [ ${dbChk:-null} = ${target_db} ]; then

echo "The script attempted to execute a stage that requires the target database." | tee -a $log

echo "Perhaps the target database name is spelled incorrectly?" | tee -a $log

echo "" tee -a $log

echo "If stages 0-3 have already been completed please rerun the script" | tee -a $log

echo "with the begin option set to the appropriate stage, such as:" | tee -a $log

echo "$0 -s $source_db -t $target_db -l $backup_path -b 4" | tee -a $log

echo ""

endScript "Target database not found"

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# backup key ddl from database

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Backing up database DDL"

# extract the ddl from the target database

db2look -d ${target_db} -createdb -printdbcfg -e -a -x -o ${refresh_path}/${target_db}_ddl.sql

[[ $? -gt $success ]] && endScript "Failure retuned collecting database ddl"

# extract the grant statements from the target database

db2look -d ${target_db} -x -o ${refresh_path}/${target_db}_grants.sql

[[ $? -gt $success ]] && endScript "Failure retuned collecting database grants"

# extract the role creation statements from target database ddl

grep "CREATE ROLE" ${refresh_path}/${target_db}_ddl.sql > ${refresh_path}/${target_db}_roles.sql

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# backup key db cfg settings

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Backing up key db cfg settings"

db2 -v connect to ${target_db}

# collect database configuration

db2 get db cfg for ${target_db} > ${refresh_path}/${target_db}_db2Cfg.txt

# collect current hadr config

db2 get db cfg for ${target_db} | grep HADR > ${refresh_path}/${target_db}_hadr.txt

errorChk $? "collecting hadr settings"

# collect storage groups

db2 -x "select path from sysibmadm.dbpaths where type = 'DB_STORAGE_PATH'" > ${refresh_path}/${target_db}_stogrps.txt

errorChk $? "collecting storage paths"

db2 -v terminate

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# deactivate database

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Prepping target database for restore"

# ensure you are not connected to target database

db2 terminate | tee -a $log

# force off all other connections

db2 -v force applications all | tee -a $log

# deactivate the target database

db2 -v deactivate db ${target_db} | tee -a $log

errorChk $? "deactivating the database"

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# create restore script

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Creating restore script"

# create the restore script using the source database backup image

db2 -v "restore db ${source_db} from ${backup_path} taken at ${backup_timestamp} into ${target_db} redirect generate script ${refresh_path}/${target_db}_restore_raw.sql without prompting" | tee -a $log

errorChk $? "creating restore script"

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# edit the restore script

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Editing the restore script"

set -A stogrps_array $(cat ${refresh_path}/${target_db}_stogrps.txt)

[[ ${stogrps_array[0]:+1} -ne 1 ]] && endScript "Failed to set database storage path(s)"

for i in "${stogrps_array[@]}"; do

# remove the trailing '/' and empty space

var=$(echo $i | awk '{$1=$1};1' | sed 's/\/$//')

# handle multiple paths

if [[ ${#stogrps_array[*]} -gt 1 && ${storage:+1} -eq 1 ]]; then

storage="${storage}, '${var}'"

else

storage="'${var}'"

done

# escape the '/' from path variables for use with sed

escbackuppath=$(echo ${refresh_path} | sed -e 's/\//\\\//g')

esclogpath=$(echo ${newlogpath} | sed -e 's/\//\\\//g')

escstorage=$(echo ${storage} | sed -e 's/\//\\\//g')

# edit the restore script for use against the target database

cat ${refresh_path}/${target_db}_restore_raw.sql | \

sed -e "s/NODE0000\.out/NODE0000\.${backup_timestamp}\.out/" \

-e "s/^-- ON.*/ON ${escstorage}/" \

-e "s/^-- LOGTARGET.*/LOGTARGET ${escbackuppath}/" \

-e "s/^-- NEWLOGPATH.*/NEWLOGPATH ${esclogpath}/" \

-e "s/^-- REPLACE EXISTING/REPLACE EXISTING/" \

-e "s/^-- SET STOGROUP/SET STOGROUP/" \

-e "s/^-- ;/;/" \

> ${refresh_path}/${target_db}_restore_updated.sql

if [[ $? -gt 0 ]]; then

endScript "Failure returned editing the restore script"

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# restore database

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Restoring the database"

# perform the restore using the edited restore script

db2 -tvf ${refresh_path}/${target_db}_restore_updated.sql | tee -a $log

errorChk $? "restoring the database"

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# rollforward database

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Rolling database forward"

# rollforward the target database to the end of the backup

db2 -v "rollforward db ${target_db} to end of backup and complete overflow log path ('${refresh_path}')" | tee -a $log

errorChk $? "rolling forward the database"

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# perform post restore grants

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Executing grant statements"

db2 -v connect to ${target_db} | tee -a $log

# errors are ignored as these commands are run for due diligence and not from necessity

# some errors are to be expected

if [[ -s "${refresh_path}/${target_db}_roles.sql" ]]; then

# execute any role creation statements from target database prior to restore

db2 -tvf ${refresh_path}/${target_db}_roles.sql | tee -a $log

if [[ -s "${refresh_path}/${target_db}_grants.sql" ]]; then

# execute the remaining grant statements from target database prior to restore

# this is done to ensure that user access is maintained after the restore

db2 -tvf ${refresh_path}/${target_db}_grants.sql | tee -a $log

db2 -v terminate | tee -a $log

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# update HADR settings

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Configuring HADR settings"

# create db cfg update statement

set -A hadr_array LOCAL_HOST LOCAL_SVC REMOTE_HOST REMOTE_SVC REMOTE_INST TIMEOUT TARGET_LIST PEER_WINDOW

for i in "${hadr_array[@]}"; do

var=$(grep HADR_${i} ${refresh_path}/${target_db}_hadr.txt | awk -F= '{print $2}' | awk '{$1=$1};1')

[[ ${var:+1} -eq 1 ]] && options+=(HADR_${i} ${var})

if [[ ${i} = "TARGET_LIST" ]] && [[ ${var:+1} -ne 1 ]]; then

options+=(HADR_TARGET_LIST 'NULL')

done

db2 -v "update db cfg for ${target_db} using ${options[*]} immediate" | tee -a $log

errorChk $? "updating HADR settings"

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# check for tables in check pending state; set integrity as needed

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Checking and fixing referential integrity"

pass=1

db2 -v connect to ${target_db} | tee -a $log

# collect a count of tables that are in check pending state ('C')

pending=$(db2 -x "select count(*) from syscat.tables where status = 'C'" | awk '{$1=$1};$1')

if [[ ${pending:-0} -eq 0 ]] ; then

echo "There are no tables in check pending state." | tee -a $log

else

# fix referential integrity on tables in check pending state

# this is performed in a loop because the parent tables must be fixed first

while [ ${pass} -le 10 ] && [ ${pending:-0} -gt 0 ]; do

echo " There are ${pending} tables to check [ Pass ${pass} of 10 ]" | tee -a $log

# create list of tables in check pending state and write out to file

db2 -x "select tabschema, tabname from syscat.tables where status = 'C' order by parents" > ${refresh_path}/${target_db}_RefChk_pass-${pass}.txt

# read contents of check pending table file and assign first string to tabschema and second string to tabname

while read tabschema tabname ; do

# append EXCEPT to the original table name for use as the exception table

exception_table="${tabname}_EXCEPT"

# check if exception table exists

check=$(db2 -x "select count(*) from syscat.tables where tabname = '${exception_table}'" | awk '{$1=$1};$1')

# if the exception table doesn't exist, create it

if [[ ${check:-0} -gt 0 ]] ; then

print "Exception table ${exception_table} already exists."

else

# create the exception table

db2 -v "create table ${exception_table} like ${tabschema}.${tabname}" | tee -a $log

errorChk $? "creating exception table"

# alter table to include column for storing exception reason

db2 -v "alter table ${exception_table} add column X_TS TIMESTAMP add column X_REAS CLOB(32k)" | tee -a $log

errorChk $? "altering the exception table"

# set the integrity for the table moving violating rows to the exception table

# this cmd is not checked for errors since some tables will fail until their parents are fixed first

db2 -v "set integrity for ${tabschema}.${tabname} immediate checked for exception in ${tabschema}.${tabname} use ${exception_table}" | tee -a $log

# check for violations that are not foreign key('f') or unique index ('I') violations

x=$(db2 -x "select count(*) from ${exception_table} where char(substr(X_REAS,6,1)) not in ('I','F')" | awk '{$1=$1};$1')

# if the exception table is empty, drop it

if [[ ${x:-0} -eq 0 ]] ; then

echo "There are no unexpected rows in the exception table."

db2 -v "drop table ${exception_table}" | tee -a $log

errorChk $? "dropping an exception table"

else

# if there are exceptions that are NOT from foreign keys or unique indexes, call this out

echo "There are ${x} exceptions in ${exception_table} -- Please review." | tee -a $log

done < ${refresh_path}/${target_db}_RefChk_pass-${pass}.txt

# perform a new check for tables in check pending state and repeat process as needed

pending=$(db2 -x "select count(*) from syscat.tables where status = 'C'" | awk '{$1=$1};$1')

pass=$(( pass + 1 ))

sleep 1

done

db2 -v terminate | tee -a $log

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# connect to and validate database

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Validating the database"

# connect to the target database

db2 -v connect to ${target_db} | tee -a $log

errorChk $? "connecting to database after restore"

# list and check the output of tablespace state (anything but 0x0000 = a problem)

db2 list tablespaces show detail | grep State | cut -d= -f2 | tee -a $log

if grep -vq 0x0000 ${refresh_path}/${target_db}_TS_check.out; then

endScript "One or more tablespaces is not NORMAL"

# perform a simple select against the target database to confirm it is queryable

db2 "select * from syscat.bufferpools" | tee -a $log

errorChk $? "running simple select after restore"

db2 -v terminate | tee -a $log

endStage

(( next++ ))

#-----------------------------------------------------------------------------------------#

# scaena

# take a post refresh backup

if [ ${stage} -eq ${next} ] && [ ${stop} -ge ${stage} ]; then

beginStage "Performing post refresh backup"

# perform a post refresh backup of the target database using the backup script

/usr/local/db2/admin_scripts/db2.backup_db.ksh -i ${instance} -d ${target_db} -c -q

endStage

(( next++ ))

db2 connect reset > /dev/null

db2 terminate > /dev/null

echo "" | tee -a $log

echo "Script completed with success" | tee -a $log

echo "" >> $log

exit 0

Search This Blog

Groovy DBA

DB2: db refresh script

Comments

Post a Comment

Popular posts from this blog

MySQL: To show privileges/ grants for a user

DB2: To check table status in the event of SQLCODE=-668, SQLSTATE=57007

Oracle: To clean up WRI$_ADV_OBJECTS